none
Multiple Update using one sqlconnection with sqltransaction RRS feed

  • Question

  • I have used sqlbulkcopy to performed a multiple inserts in the database.

    I would like to ask how do i performed a multiple updates in the database using one sqlconnection with sqltransaction

    I already tried using multiple updates using xml data but the nvarchar datatype has a maximum length; i always exceed the maximum length if i pass too much data to that parameter in my stored procedure.

    and also i tried using ntext as my datatype it throws exception when i execute the stored procedure

    I would like to ask what are the possible alternatives to implement a multiple updates

    Thanks

    Monday, April 27, 2009 6:21 AM

Answers

  • Here is a sample for you.  The basic idea is get the modified values into a DataTable, then use SqlDataAdapter.Update and BatchSize for batching.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Collections;
    
    namespace BulkUpdate
    {
      class Program
      {
        static SqlConnection conn = null;
        static void Main(string[] args)
        {
          const int batchSize = 100;
    
          // Build our input DataTable with data for updating.
          DataTable updateRecords = new DataTable("t");
          updateRecords.Columns.Add("pkey", typeof(int));
          updateRecords.Columns.Add("pvalue", typeof(int));
          object[] row = new object[2];
          for (int i = 1; i <= batchSize; i++)
          {
            row[0] = i;
            row[1] = i+1;
            updateRecords.Rows.Add(row);
          }
          
          // Accept changes, then go back thru and alter all rows to be "modified".
          // Normally you would suck down the table from server and modify rows in
          // datagrid to do this, here I'm just doing it manually.
          updateRecords.AcceptChanges();
    
          foreach (DataRow r in updateRecords.Rows)
          {
            r.SetModified();
          }
    
          // Open connection to sql and create test database.
          conn = new SqlConnection("server=.;integrated security=true;database=master");
          conn.Open();
    
          // Drop and re-create test db.
          xsql_ne("drop database updateTest1234");
          xsql("create database updateTest1234");
          
          xsql("use updateTest1234");
    
          // Create table and insert batchSize records to update.
          xsql("create table updateTest1234(pkey int primary key, pvalue int)");
    
          // Insert batchSize records.
          xsql("set nocount on declare @i int set @i=1 while (@i<=" + 
            batchSize.ToString() + 
            ") begin insert updateTest1234 values (@i, @i) set @i = @i + 1 end");
    
          // Verify we have batchsize records.
          Console.WriteLine("Inserted {0} records, found {1} records.", 
            batchSize, xsql_scalar("select count(*) from updateTest1234"));
          Console.WriteLine("Sum of {0} records is {1}.", 
            batchSize, xsql_scalar("select sum(pvalue) from updateTest1234"));
          Console.WriteLine("Running update test...");
          
          SqlTransaction tran = conn.BeginTransaction();
    
          SqlDataAdapter da = new SqlDataAdapter(null, conn);
          da.UpdateCommand = 
            new SqlCommand("update updateTest1234 set pvalue=@p1 where pkey=@p2", conn, tran);
          da.UpdateCommand.CommandTimeout = 60;
          da.UpdateCommand.Parameters.Add("@p1", SqlDbType.Int, 0, "pvalue");
          da.UpdateCommand.Parameters.Add("@p2", SqlDbType.Int, 0, "pkey");
          da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
          da.SelectCommand = null;	// Set these to NULL to ensure they don't execute.
          da.InsertCommand = null;	// Set these to NULL to ensure they don't execute.
          da.UpdateBatchSize = batchSize;
    
          try
          {
            // This will send a single request to server with batch size of batchSize.
            da.Update(updateRecords);
          }
          catch (Exception ex)
          {
            Console.WriteLine(ex.Message);
          }
          
          // Commit transaction.
          tran.Commit();
          
          // Now get sum of values again.
          Console.WriteLine("Sum of {0} records is {1}.", 
            batchSize, xsql_scalar("select sum(pvalue) from updateTest1234"));
    
          da.Dispose();
          conn.Close();
          
          Console.WriteLine("Test complete, press <enter> to exit.");
          Console.ReadLine();
    
        }
        static void xsql_ne(string sql)
        {
          try { xsql(sql); }
          catch (Exception) { };
        }
        static void xsql(string sql)
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
          }
        }
        static int xsql_scalar(string sql)
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return (int)cmd.ExecuteScalar();
          }
        }
      }
    }
    
    Monday, May 4, 2009 12:43 AM
    Moderator
  • Hi Matt,

    Thanks for the help
    • Marked as answer by zid8ne Monday, May 4, 2009 12:39 PM
    Monday, May 4, 2009 12:39 PM

All replies

  • Hi, try this

     

    Dim conn As New sqlclient.sqlconnection(connectionstring)

     

    Dim Tran As sqlclienct.sqlTransaction

     

    Dim cmd As New sqlcleint.sqlclientCommand

     

    Dim Success As Boolean = True

    conn.Open()

    Tran = conn.BeginTransaction()

     

    Try

    strSQL =

    "insert into talbename"

    cmd =

    New sqlclient.sqlclientCommand(strSQL, conn)

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()



     

    strSQL1 = "insert into talbename"

    cmd =

    New sqlclient.sqlclientCommand(strSQL1, conn)

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()



    Tran.Commit()

     

    Catch ex As Exception

    Tran.Rollback()

    Success =

    False

     

    Finally

    conn.Close()

     

    End Try

     

     


    Hope This One Can Help.
    Thanks
    To Be Happy Is To Be YourSelf
    Please remember to mark my reply as Proposed as Answers if it's the answer to your question.
    Monday, April 27, 2009 6:55 AM
  • I would like to performed a multiple updates to the table.

    e.g.
    100 records to be updated using one sqlconnection, sqlcommand with sqltransaction
    Tuesday, April 28, 2009 12:50 AM
  • Hi, try this

    Dim conn As New sqlclient.sqlconnection(connectionstring)

    Dim Tran As sqlclienct.sqlTransaction

     

    Dim cmd As New sqlcleint.sqlclientCommand

     

    Dim Success As Boolean = True

     

     

    conn.Open()

    Tran = conn.BeginTransaction()

     

     

    Try

    strSQL =

     

    cmd =

     

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()

    New sqlclient.sqlclientCommand(strSQL, conn)

     

    strSQL1 = "insert into talbename"Catch ex As Exception False

    Finally

    conn.Close()

     

     

    End Try

    cmd =

    New sqlclient.sqlclientCommand(strSQL1, conn)


    strSQL2 = "Update [tablename] set .........." 

    cmd =

     

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()

     

     

    strSQL3 = "delete from talbename"



    cmd =

     

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()

     

     

    Tran.Commit()

     

     

    Tran.Rollback()

    Success =

     

    New sqlclient.sqlclientCommand(strSQL3, conn)
    New sqlclient.sqlclientCommand(strSQL2, conn)

    cmd.Transaction = Tran

    cmd.ExecuteNonQuery()

    "insert into talbename"


    Hope This One Can Help.
    Thanks
    To Be Happy Is To Be YourSelf
    Please remember to mark my reply as Proposed as Answers if it's the answer to your question.
    Tuesday, April 28, 2009 1:30 AM

  • Hi Sayre,

    e.g.
    100 records to be updated using one sqlconnection, sqlcommand with sqltransaction

    Your sample code will not be able to performed a multiple update to the table using one sqlconnection, sqlcommand with sqltransaction because i have to iterate all the 100 records.

    Thursday, April 30, 2009 1:31 PM
  • Here is a sample for you.  The basic idea is get the modified values into a DataTable, then use SqlDataAdapter.Update and BatchSize for batching.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Collections;
    
    namespace BulkUpdate
    {
      class Program
      {
        static SqlConnection conn = null;
        static void Main(string[] args)
        {
          const int batchSize = 100;
    
          // Build our input DataTable with data for updating.
          DataTable updateRecords = new DataTable("t");
          updateRecords.Columns.Add("pkey", typeof(int));
          updateRecords.Columns.Add("pvalue", typeof(int));
          object[] row = new object[2];
          for (int i = 1; i <= batchSize; i++)
          {
            row[0] = i;
            row[1] = i+1;
            updateRecords.Rows.Add(row);
          }
          
          // Accept changes, then go back thru and alter all rows to be "modified".
          // Normally you would suck down the table from server and modify rows in
          // datagrid to do this, here I'm just doing it manually.
          updateRecords.AcceptChanges();
    
          foreach (DataRow r in updateRecords.Rows)
          {
            r.SetModified();
          }
    
          // Open connection to sql and create test database.
          conn = new SqlConnection("server=.;integrated security=true;database=master");
          conn.Open();
    
          // Drop and re-create test db.
          xsql_ne("drop database updateTest1234");
          xsql("create database updateTest1234");
          
          xsql("use updateTest1234");
    
          // Create table and insert batchSize records to update.
          xsql("create table updateTest1234(pkey int primary key, pvalue int)");
    
          // Insert batchSize records.
          xsql("set nocount on declare @i int set @i=1 while (@i<=" + 
            batchSize.ToString() + 
            ") begin insert updateTest1234 values (@i, @i) set @i = @i + 1 end");
    
          // Verify we have batchsize records.
          Console.WriteLine("Inserted {0} records, found {1} records.", 
            batchSize, xsql_scalar("select count(*) from updateTest1234"));
          Console.WriteLine("Sum of {0} records is {1}.", 
            batchSize, xsql_scalar("select sum(pvalue) from updateTest1234"));
          Console.WriteLine("Running update test...");
          
          SqlTransaction tran = conn.BeginTransaction();
    
          SqlDataAdapter da = new SqlDataAdapter(null, conn);
          da.UpdateCommand = 
            new SqlCommand("update updateTest1234 set pvalue=@p1 where pkey=@p2", conn, tran);
          da.UpdateCommand.CommandTimeout = 60;
          da.UpdateCommand.Parameters.Add("@p1", SqlDbType.Int, 0, "pvalue");
          da.UpdateCommand.Parameters.Add("@p2", SqlDbType.Int, 0, "pkey");
          da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
          da.SelectCommand = null;	// Set these to NULL to ensure they don't execute.
          da.InsertCommand = null;	// Set these to NULL to ensure they don't execute.
          da.UpdateBatchSize = batchSize;
    
          try
          {
            // This will send a single request to server with batch size of batchSize.
            da.Update(updateRecords);
          }
          catch (Exception ex)
          {
            Console.WriteLine(ex.Message);
          }
          
          // Commit transaction.
          tran.Commit();
          
          // Now get sum of values again.
          Console.WriteLine("Sum of {0} records is {1}.", 
            batchSize, xsql_scalar("select sum(pvalue) from updateTest1234"));
    
          da.Dispose();
          conn.Close();
          
          Console.WriteLine("Test complete, press <enter> to exit.");
          Console.ReadLine();
    
        }
        static void xsql_ne(string sql)
        {
          try { xsql(sql); }
          catch (Exception) { };
        }
        static void xsql(string sql)
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
          }
        }
        static int xsql_scalar(string sql)
        {
          using (SqlCommand cmd = conn.CreateCommand())
          {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return (int)cmd.ExecuteScalar();
          }
        }
      }
    }
    
    Monday, May 4, 2009 12:43 AM
    Moderator
  • Hi Matt,

    Thanks for the help
    • Marked as answer by zid8ne Monday, May 4, 2009 12:39 PM
    Monday, May 4, 2009 12:39 PM