Compare 2 datasets?

  • Thread starter Thread starter codejockey
  • Start date Start date
C

codejockey

I have a simple project that requires I take a set of data from an Excel
spreadsheet, compare it to a table in SQL Server (where column names match),
and if there are changes in the Excel sheet, update the SQL Server table
with said changes.

The datasets are about 2000 rows. My thinking is performing a check row by
row is inefficient, so I thought about how I could compare the two as
datasets and resolve differences. The SQL Server table has a primary key
column, but is otherwise identical to the spreadsheet.

How can I do a simple, efficient comparison?
 
Fill a dataset using Excel as a Datasource (excelDataSet). No, configure a
SqlDataAdapter and set the Optimistic Concurrency Property to false in the
wizard (it's on the second to last tab if I remember correctly, it's a
button in the bottom left hand corner that says "Advanced" or something like
that. You can fire updates against it from there. However, your rowstates
will be modified so you'll need to walk the table. This isn't the most
efficient thing in the world, but that's the way the adapter fires updates.

however, if you are sure that you want to SqlServer table to match the
ExcelSheet, why not delete the rows from the SqlServer, do the same thign
with the excel sheet mentioned above except don't worry abou tthe
concurrency option. Instead, set the acceptchangesduringfill property to
false, then just call update.
http://www.knowdotnet.com/articles/datasetmerge.html
--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
William:

Thanks for the reply. I havent been able to get the sample to work.

I notice the cursor cycling through the Excel records, but the SQL table
always shows up empty. Notice anything wrong here?

private void BeginImport()

{

string xlConn;

xlConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath(MakeDateFileName()) + ";Extended Properties=Excel 8.0;";

SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("db
Conn").ToString());

objConn.Open();

SqlTransaction objTrans = objConn.BeginTransaction();

try

{

OleDbDataAdapter objOLEDA = new OleDbDataAdapter("my select statement FROM
[Sheet1$]", xlConn);

objOLEDA.AcceptChangesDuringFill = false;

DataSet xlDataSet = new DataSet();

// we now have our Excel source data.

objOLEDA.Fill(xlDataSet, "xlData");

SqlDataAdapter objSQLDA = new SqlDataAdapter();

objSQLDA.SelectCommand = new SqlCommand();

objSQLDA.SelectCommand.Transaction = objTrans;

objSQLDA.SelectCommand.CommandText = "my select statement identical to
Excels FROM tblDealerCustomers";

objSQLDA.SelectCommand.Connection = objConn;

SqlCommandBuilder objCB = new SqlCommandBuilder(objSQLDA);

objSQLDA.AcceptChangesDuringFill = false;

objSQLDA.Update(xlDataSet, "xlData");


}

catch (Exception ex)

{

objTrans.Rollback();

PrintError(ex);

}

finally

{

objConn.Close();


}




}
 
Back
Top