Write to SQL using DataSet in C#
Posted on 2011-03-04
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.
// Bind data to DataGrid control.
GridView2.DataSource = objDataset1.Tables.DefaultView;
// Clean up objects.