Solved

Write to SQL using DataSet in C#

Posted on 2011-03-04
4
613 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 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