• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

Write to SQL using DataSet in C#

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.

        // 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;

        // Clean up objects.
  • 3
1 Solution
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

melli111SharePoint Administrator / DeveloperAuthor Commented:
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.

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?
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 
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         

custDA.Update(custDS, "dwbprocref")


Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now