Compare 2 datasets?

[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?

[William Ryan eMVP]

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 acceptchangesdu ringfill 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/
"codejockey " <[e-mail] > wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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?

[codejockey]

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=Micro soft.Jet.OLEDB. 4.0;Data Source=" +
Server.MapPath( MakeDateFileNam e()) + ";Extended Properties=Exce l 8.0;";

SqlConnection objConn = new
SqlConnection(S ystem.Configura tion.Configurat ionSettings.App Settings.Get("d b
Conn").ToString ());

objConn.Open();

SqlTransaction objTrans = objConn.BeginTr ansaction();

try

{

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

objOLEDA.Accept ChangesDuringFi ll = false;

DataSet xlDataSet = new DataSet();

// we now have our Excel source data.

objOLEDA.Fill(x lDataSet, "xlData");

SqlDataAdapter objSQLDA = new SqlDataAdapter( );

objSQLDA.Select Command = new SqlCommand();

objSQLDA.Select Command.Transac tion = objTrans;

objSQLDA.Select Command.Command Text = "my select statement identical to
Excels FROM tblDealerCustom ers";

objSQLDA.Select Command.Connect ion = objConn;

SqlCommandBuild er objCB = new SqlCommandBuild er(objSQLDA);

objSQLDA.Accept ChangesDuringFi ll = false;

objSQLDA.Update (xlDataSet, "xlData");
}

catch (Exception ex)

{

objTrans.Rollba ck();

PrintError(ex);

}

finally

{

objConn.Close() ;
}


}

"William Ryan eMVP" <do********@com cast.nospam.net > wrote in message
news:u2******** ******@tk2msftn gp13.phx.gbl...

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 acceptchangesdu ringfill 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/
"codejockey " <[e-mail] > wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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?