Data not updating in database - SqlTransaction

Question

I am using Sqltransation to update data from sourcedt to datatable table. Below two lines, first I am merging data from clonedt to sourcedt. Then trying to update it to database table.

SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlTransaction trans = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(strCommand, con, trans)
SqlDataAdapter da = new SqlDataAdapter(cmd)

SqlCommandBuilder cmb = new SqlCommandBuilder(da);
da.InsertCommand = cmb.GetInsertCommand();
da.UpdateCommand = cmb.GetUpdateCommand();

da.InsertCommand.Transaction = trans;
da.UpdateCommand.Transaction = trans;

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

da.Fill(sourceDt);

sourceDt.Merge(clonedDt, false, MissingSchemaAction.AddWithKey);
int count = da.Update(sourceDt);
trans.Commit();   //Commit the changes to database

The rest of the code is fine, issue seems to be in this commit statement only. How should I find whats wrong?

Changing a DataTable doesn't update anything on the database. You are updating an _in memory_ object. You need a DbDataAdapter or execute a DbCommand row by row. — Steve, Jan 03 '18 at 15:55
Hello steve, I have updated the post, so you will getto know more abt how I am trying to update the database table. — Wocugon, Jan 03 '18 at 16:00
Using statements will help this code out. Otherwise it is a well documented issue https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx — , Jan 03 '18 at 16:17

Steve · Answer 1 · 2018-01-03T16:24:29.603

2

You need to pass the sourceDt to the dataadapter Update method

da.Update(sourceDt);

I don't think you need the transaction at all. But you need to experiment with that.

Also it is a good practice to always enclose your disposable objects inside a using statement. This will correctly close the connection and dispose its resources back to the OS. Also the transaction if not committed will be automatically rolled back

using(SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    using(SqlTransaction trans = con.BeginTransaction())
    using(SqlCommand cmd = new SqlCommand(strCommand, con, trans))
    using(SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        SqlCommandBuilder cmb = new SqlCommandBuilder(da);
        cmb.GetInsertCommand().Transaction = trans;
        cmb.GetUpdateCommand().Transaction = trans;
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        da.Fill(sourceDt);
        sourceDt.Merge(clonedDt, false, MissingSchemaAction.AddWithKey);
        int count = da.Update(sourceDt);
        trans.Commit();
    }
}

edited Jan 03 '18 at 16:24

answered Jan 03 '18 at 16:08

Steve

213,761
22
232
286

Bro I have already tried that, didnt work. Can you help me correct the code.. means getting rid of trans. I think its basically used for rollback purpose. – Wocugon Jan 03 '18 at 16:13
What doesn't work? Let's skip the transaction problem for now. If the Update method fails to update the database table then it is probably caused by the rows in the DataTable object that have all their RowState to Unchanged. Do you call somewhere AcceptChanges on that cloneDt table? – Steve Jan 03 '18 at 16:15
No I am not calling AcceptChanges on clonedt. – Wocugon Jan 03 '18 at 16:22
I think even the update() is working fine coz, int count = da.Update(sourceDt);... I am getting count = 9. means 9 rows are updated/affected. – Wocugon Jan 03 '18 at 16:22
Uhm, this changes the point. How do you check that nothing has been updated? Did you use |DataDirectory| in your connection string? – Steve Jan 03 '18 at 16:24
Cause records from sourcedt are not inserted/updated in database table. Tho I am getting int count = 9. [int count = da.Update(sourceDt)]. And sorry I didnt get the DataDirectory part. Can you please explain a little more. – Wocugon Jan 03 '18 at 16:28
What tool do you use to look at the table on the database. Are you using the same database with this external tool? When you use the DataDirectory substitution string in the connection string surprises like this are in order [Why saving changes to the database fails?](https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails) – Steve Jan 03 '18 at 16:30
:\ No sorry, I am doing nothing like that, I am just using simple one, SqlConnection con = new SqlConnection(Data Source=XYZ-PC;Initial Catalog = ITTS; User ID = sa; Password = 123123123; Integrated Security = True; MultipleActiveResultSets = true") – Wocugon Jan 03 '18 at 16:35
Tools? Just SQL management studio. firing select query to check database table. – Wocugon Jan 03 '18 at 16:37
I have one last idea. Try to remove the Transaction and check if this is the cause of the problem – Steve Jan 03 '18 at 16:37
mm apart from this... If you had a sourcedt and you wanted it to update/insert records from it to database table. How wold you do it? Can you provide me with any similar tutorial or reference. Would be very helpful for me! – Wocugon Jan 03 '18 at 16:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162439/discussion-between-steve-and-sobmac). – Steve Jan 03 '18 at 16:41

Data not updating in database - SqlTransaction

1 Answers1