CODEWITHSUNDEEP
c#sql-server
Wocugon
Wocugon

Reputation: 586

Data not updating in database - SqlTransaction

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?

Upvotes: 0

Views: 643

Answers (1)

Steve
Steve

Reputation: 216293

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();
    }
}

Upvotes: 2

Related Questions