Solved

Write to SQL using DataSet in C#

Posted on 2011-03-04
4
608 Views
Last Modified: 2012-08-13
I have code that imports data from Excel into a DataSet in ASP.NET using C# code.  The code workws for importing the data from Excel.  What I need to know now is how I would take that data from the DataSet and write it to an SQL table.  The following is the code I am usin to import from Excel:

// Create connection string variable. Modify the "Data Source"
        // parameter as appropriate for your environment.
        String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + Server.MapPath("ExcelFile.xlsx") + ";" +
            "Extended Properties=Excel 12.0";

        // Create connection object by using the preceding connection string.
        OleDbConnection objConn = new OleDbConnection(sConnectionString);

        // Open connection with the database.
        objConn.Open();

        // The code to follow uses a SQL SELECT command to display the data from the worksheet.

        // Create new OleDbCommand to return data from worksheet.
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

        // Create new OleDbDataAdapter that is used to build a DataSet
        // based on the preceding SQL SELECT statement.
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

        // Pass the Select command to the adapter.
        objAdapter1.SelectCommand = objCmdSelect;

        // Create new DataSet to hold information from the worksheet.
        DataSet objDataset1 = new DataSet();

        // Fill the DataSet with the information from the worksheet.
        objAdapter1.Fill(objDataset1, "ExcelData");
        // Bind data to DataGrid control.
        GridView2.DataSource = objDataset1.Tables[0].DefaultView;
        GridView2.DataBind();

        // Clean up objects.
        objConn.Close();
0
Comment
Question by:melli111
  • 3
4 Comments
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35038082
You need to store all data in a dataset, which should be same as the table you need to update.

Please follow the instruction given in this artical. You need to read #8, #9, #10

http://support.microsoft.com/kb/307587
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35038096
0
 
LVL 15

Author Comment

by:melli111
ID: 35060896
I am a bit confused.  I understand the part about using the Fill() method for a DataSet to populate the DataSet with.  What I don't understand is how I actually write to the database table.  The link you referenced tells how to add record to the DataSet:

DataTable tblAuthors;
            tblAuthors = dsPubs.Tables["Authors"];

            DataRow drCurrent;
            // Obtain a new DataRow object from the DataTable.
            drCurrent = tblAuthors.NewRow();

            // Set the DataRow field values as necessary.
            drCurrent["au_id"] = "993-21-3427";
            drCurrent["au_fname"] = "George";
            drCurrent["au_lname"] = "Johnson";
            drCurrent["phone"] = "800 226-0752";
            drCurrent["address"] = "1956 Arlington Pl.";
            drCurrent["city"] = "Winnipeg";
            drCurrent["state"] = "MB";
            drCurrent["contract"] = 1;

            // Pass that new object into the Add method of the DataTable.
            tblAuthors.Rows.Add(drCurrent);

What I do not understand is what actually adds the record to the database?  Is it the Add() method?  I cannot add hard-coded calues to the DataSet like is done here, so my question is how do I take the values that I have placed into the DataSet with the Fill() method and place those values into the database?
0
 
LVL 9

Accepted Solution

by:
gdupadhyay earned 500 total points
ID: 35084680
You need to use data Adapter update method.
dataAdapter.Update(DataSet, "TableName")

For example (See below Code):
Dim selectCMD As SqlCommand = New SqlCommand("SELECT * from dwbprocref", objConn)     
selectCMD.CommandTimeout = 30     

Dim custDA As SqlDataAdapter = New SqlDataAdapter     
custDA.SelectCommand = selectCMD 
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)     

custCB.QuotePrefix = "["     custCB.QuoteSuffix = "]"      
Dim dRow As DataRow     
Dim dTable As DataTable 
 objConn.Open()      
Dim custDS As DataSet = New DataSet     
custDA.Fill(custDS, "dwbprocref")      

For Each dRow In custDS.Tables(0).Rows         
	If dRow.Item("pr_format") = "MDV" Then             
		dRow.Item("pr_tester") = "X"         
	End If         
		    
Next     

custDA.Update(custDS, "dwbprocref")


objConn.Close()

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now