468,133 Members | 1,261 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

Compare 2 Datasets

Please forgive the repost, but I'm trying to avoid the hack I want to
implement since I cant get this sample to work. Can anyone help?

***********************
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.ConfigurationSe ttings.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();
}


}

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:u2**************@tk2msftngp13.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 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/
"codejockey" <cj@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.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?



Nov 16 '05 #1
0 1299

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by codejockey | last post: by
2 posts views Thread by gibster | last post: by
1 post views Thread by Mark | last post: by
4 posts views Thread by Justin Emlay | last post: by
reply views Thread by Frank | last post: by
reply views Thread by austenr | last post: by
6 posts views Thread by Al | last post: by
12 posts views Thread by BillE | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.