3

I'm importing an Excel file (about 1000 records only) to a dedicated SQL Server database. As I need to work on the incoming data from Excel (add a GUID per row, some data conversions) I want to do it row by row and don't want to bulk import (I have nothing against transactions, though).

I'm confused on how to do it correctly. I can either use an SQLCommand with parameters like so:

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 like so:

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 the following:

  1. Is it better to send an INSERT command for each row (that would be the SqlCommand method) or is it better to fill a special DataSet (2nd Method)? I guess it is rather "stupid" to have 1000's of inserts to an SQL server?
  2. dataAdapter.Update(myDataSet) <-- should I do that AFTER iterating through all Excel rows or for each row (as shown in the example code above), would this magically create a transaction?
  3. Which method should I use? There also is LINQ to SQL - why not use that (performance maybe, because of another layer)?
  4. What happens to the DataSet when some error occurs when reading out the Excel file - is the update still pushed to the SQL server or is everything lost?

In short: I want to import an Excel file to an SQL server row-by-row whilst making changes on the data to be imported (and I don't want to use SSIS packages [because besides data conversion I am doing a lot more with the Excel file like importing it to Sharepoint and firing of Workflows] or BizTalk)
»» How to do that beautifully?
In the end I went ahead and bought Aspose Cells. Aspose has a very nice set of tools at their disposal.
Dennis G
  • 21,405
  • 19
  • 96
  • 133
  • 5
    Why don't you want to use SSIS? if the tool fits... – Mitch Wheat Oct 27 '10 at 14:43
  • Because I am simultaneously importing the Excel file to Sharepoint, and creating Infopath Forms and Tasks and firing off Workflows, it is not simply importing. I am also a lot more happy to have my hands "on the code" than using some SSIS package (which I have never created yet besides basic examples). – Dennis G Oct 27 '10 at 14:54
  • your question neglects to mention that new info....But it is almost certainly possible in SSIS... – Mitch Wheat Oct 27 '10 at 15:02
  • I edited the question to include that information. I am firing the code from Sharepoint, so deploying an SSIS package to the SQL server and calling that package from Sharepoint is not that smart I think. And yes even THAT information wasn't included, but I primarily want to understand the differences between data connections to find out the best way. – Dennis G Oct 27 '10 at 15:08

5 Answers5

4

You mention not wanting to use SSIS - but have you considered SqlBulkCopy? Then there is no need for anything except .NET yet you can still use the fastest/most-direct import.

This will accept a DataTable, so you can prepare your data in a DataTable and then pull the trigger. Transactions are optionally supported IIRC. For larger data you can also implement IDataReader to provide fully streaming upload (while still processing each row in transit).

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
1

Given your description of your problem: "I want to import an Excel file to an SQL server row-by-row whilst making changes on the data to be imported" - SSIS is the perfect tool for the job.

Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • I edited the post and commented on your previous comment to clarify. It is not as simple as importing some Excel file into SQL Server as other processes are started as well during the import. My question is more about understanding the differences between the different methods – Dennis G Oct 27 '10 at 15:04
  • SSIS is not a reliable way to exchange data between SQL and Excel. Too many bugs. – A-K Oct 27 '10 at 16:53
1

The dataSet will probably ending up sending the INSERT statements to the server, so in my opinion, is better to to just send the INSERT statements without the DataSet. You also can have more control over the process, like checking for individual rows for errors, logging, etc.

Eduardo Molteni
  • 38,786
  • 23
  • 141
  • 206
0

You could convert your processed data table into XML and pass that to a stored procedure in Sql server (in one query) and have your stored procedure parse the XML to create the records.

Chris
  • 2,340
  • 6
  • 40
  • 63
-1
INSERT INTO [dbo].[TableName]
           ([ColumnName1]
           ,[ColumnName2])
)
SELECT [ColumnName1]
           ,[ColumnName2]

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= PathToFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]
Grant
  • 1
  • 1
  • 1
    If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! – marc_s Oct 27 '10 at 15:36