Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Write to SQL using DataSet in C#

Posted on 2011-03-04
4
Medium Priority
?
672 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

581 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