?
Solved

Write to SQL using DataSet in C#

Posted on 2011-03-04
4
Medium Priority
?
628 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

741 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