Update sql database table by Datatable which I have made

Question

Update sql database table by Datatable which I have made

hmsanghavi

Mr or Mrs. 500

Points: 535
More actions
May 3, 2012 at 4:17 am

#260968

I have made a datatable.
Now i want to update that datatable records in existing sql database table.
By this I am doing bulk update.
please let me know how can I update all records of sql table by my datatable records?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

hmsanghavi (5/3/2012)
I have made a datatable.
Now i want to update that datatable records in existing sql database table.
By this I am doing bulk update.
please let me know how can I update all records of sql table by my datatable records?

From what you posted you can only get something like:

Update [My Sql Table] Set Col1 = sr.Col

From [My "made" datatable]

Please check the link at the bottom of my signature to find out how you can provide relevant details with your questions.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

hmsanghavi Mr or Mrs. 500 Points: 535 More actions · Answer 2

I making data table in application only.

using system.data.datatable.

In that I am collecting all records.

Now I want to update sql table with these data of datatable which i made.

please go through my code so you can get it well.

PositionUpdates.CommandText = "SELECT DRM.RunningBoard,DRM.DutyBoard,DRM.DriverId,DRM.PublicServiceCode,DRM.ServiceCode,DRM.DepotCode,DRM.Direction,DRM.EOJMethod,DRM.JourneyNo,CONVERT(datetime, MW.MessageTimeStamp,2) as MessageTimeStamp FROM DimRequestMasterTestHS DRM JOIN ETMMessageTestHS EM ON EM.ETMMessageTestHSID= DRM.ETMMessageID JOIN MessageWrapperTestHS1 MW ON EM.WrapperID= MW.ID WHERE ((case when MW.MessageTimeStamp like '%00/00/00%' or MW.MessageTimeStamp like '%03/01/01%' then '01/01/9999' else CONVERT(datetime, MW.MessageTimeStamp,2) end) between dateadd(day,datediff(day,2,'" + today.Date + "'),0) and dateadd(day,datediff(day,0,'" + today.Date + "'),0))";

PositionUpdates.Connection = myStagingConnection;

da.SelectCommand = PositionUpdates;

da.Fill(ds, "PositionUpdates");

I am filling this datatable.

now updating rows in that.

foreach (DataRow pr in ds.Tables["PositionUpdates"].Rows)

{

if (Convert.ToDateTime(pr["MessageTimeStamp"]) >= JourneyStart && Convert.ToDateTime(pr["MessageTimeStamp"]) <= JourneyEnd)

{

pr["RunningBoard"] = RunningBoard;

pr["DutyBoard"] = DutyBoard;

pr["DriverId"] = DriverId;

pr["PublicServiceCode"] = PublicServiceCode;

pr["ServiceCode"] = ServiceCode;

pr["DepotCode"] = DepotCode;

pr["Direction"] = Direction;

pr["EOJMethod"] = EOJMethod;

pr["JourneyNo"] = JourneyNo;

}

ds.Tables["PositionUpdates"].AcceptChanges();

SqlBulkCopy bulkCopy = new SqlBulkCopy(myStagingConnection, SqlBulkCopyOptions.TableLock, null);

bulkCopy.DestinationTableName = "dbo.DimRequestMasterTestHS";

bulkCopy.BatchSize = ds.Tables["PositionUpdates"].Rows.Count;

bulkCopy.WriteToServer(ds.Tables["PositionUpdates"], DataRowState.Modified);

bulkCopy.Close();

I am confused because only some of column I am updating.

Number of columns in datatable which i made and number of columns in sql table are different.