I am importing an Excel file (only about 1000 records) into a dedicated SQL Server database. Since I need to work with incoming data from Excel (add a GUID for each row, some data conversions), I want to do this row by row and do not want bulk import (I have nothing against transactions).
I am confused about how to do it right. I can either use SQLCommandwith such parameters:
SqlCommand sqlCommand = new SqlCommand("insert into TestTable(GUID,Name,Pricing) values(@GUID,@Name,@Pricing)", sqlConn);
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
sqlCommander.Parameters.Clear();
String refGUID = Guid.NewGuid().ToString();
sqlCommander.Parameters.AddWithValue("GUID", refGUID);
sqlCommander.Parameters.AddWithValue("Name", dr.ItemArray[0]);
sqlCommander.Parameters.AddWithValue("Pricing", dr.ItemArray[1]);
sqlCommander.ExecuteNonQuery();
}
Or I can use the "connected" mode as follows:
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT GUID, Name, Pricing FROM TestTable", sqlConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "TestTable");
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
DataRow row = myDataSet.Tables[0].NewRow();
row["GUID"] = refGUID;
row["Name"] = dr.ItemArray[0];
row["Pricing"] = dr.ItemArray[1];
myDataSet.Tables[0].Rows.Add(row);
dataAdapter.Update(myDataSet);
}
Now my questions are as follows:
- Is it better to send a command
INSERTfor each line (it will be a method SQLCommand) or is it better to fill in a special one DataSet(2nd method)? I think "stupid" has 1000 attachments per SQL server? dataAdapter.Update(myDataSet) < - Excel ( ), ?- ? LINQ to SQL - (, - )?
DataSet, Excel, - , SQL-, ?
<h / "> In short: I want to import the Excel file into the SQL server in turn, while making changes to the imported data (and I do not want to use SSIS packages [because in addition to the data conversion. I do a lot more with the Excel file for example, importing it into Sharepoint and launching Workflow] or BizTalk)
& raquo; & raquo; How to do it beautifully?
In the end, I went ahead and bought "Sort Cells . " Aspose has a very good set of tools at its disposal.
source
share