how to use sqltransaction in c

Question

I am using following code to execute two commands at once  I used sqltransaction to assure either all command get executed or rolled back When I run my program without  transaction  it run properly but when I use  transaction  with them they show error  My code is as follow   SqlTransaction transaction   connectionsql BeginTransaction     try       SqlCommand cmd1   new SqlCommand  select account name from master account where NOT account name    BANK  AND NOT account name    LOAN    connectionsql       SqlDataReader dr1   cmd1 ExecuteReader        while  dr1 Read                  comboBox1 Items Add dr1 0  ToString   Trim               cmd1 Dispose        dr1 Dispose         SqlCommand cmd2   new SqlCommand  select items from rate  connectionsql       SqlDataReader dr2   cmd2 ExecuteReader        while  dr2 Read                  comboBox2 Items Add dr2 0  ToString   Trim               cmd2 Dispose        dr2 Dispose        transaction Commit         dateTimePicker4 Value   dateTimePicker3 Value    catch Exception ex        transaction Rollback        MessageBox Show ex ToString         and error

User · Answer

You can create a SqlTransaction from a SqlConnection   And use it to create any number of SqlCommands  SqlTransaction transaction   connection BeginTransaction    var cmd1   new SqlCommand command1Text  connection  transaction   var cmd2   new SqlCommand command2Text  connection  transaction     Or   var cmd1   new SqlCommand command1Text  connection  connection BeginTransaction     var cmd2   new SqlCommand command2Text  connection  cmd1 Transaction       If the failure of commands never cause unexpected changes don t use transaction   if the failure of commands might cause unexpected changes put them in a Try Catch block and rollback the operation in another Try Catch block   Why another try catch  According to MSDN      Try Catch exception handling should always be used when rolling back a transaction  A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server    Here is a sample code   string connStr     connection string    string cmdTxt     t-sql command text     using  var conn   new SqlConnection connStr         conn Open        var cmd   new SqlCommand cmdTxt  conn  conn BeginTransaction           try               cmd ExecuteNonQuery              before this line  nothing has happened yet         cmd Transaction Commit              catch System Exception ex                  You should always use a Try Catch for transaction s rollback         try                       cmd Transaction Rollback                      catch System Exception ex2                        throw ex2                    throw ex             conn Close           The transaction is rolled back in the event it is disposed before Commit or Rollback is called    So you don t need to worry about app being closed

User · Answer

First you don t need a transaction since you are just querying select statements and since they are both select statement you can just combine them into one query separated by space and use Dataset to get the all the tables retrieved  Its better this way since you made only one transaction to the database because database transactions are expensive hence your code is faster  Second of you really have to use a transaction  just assign the transaction to the SqlCommand like  sqlCommand Transaction   transaction    And also just use one SqlCommand don t declare more than one  since variables consume space and we are also on the topic of making your code more efficient  do that by assigning commandText to different query string and executing them like  sqlCommand CommandText    select   from table1   sqlCommand ExecuteNonQuery    sqlCommand CommandText    select   from table2   sqlCommand ExecuteNonQuery

User · Answer

The following example creates a SqlConnection and a SqlTransaction  It also demonstrates how to use the BeginTransaction  Commit  and Rollback methods  The transaction is rolled back on any error  or if it is disposed without first being committed  Try Catch error handling is used to handle any errors when attempting to commit or roll back the transaction   private static void ExecuteSqlTransaction string connectionString        using  SqlConnection connection   new SqlConnection connectionString                 connection Open             SqlCommand command   connection CreateCommand            SqlTransaction transaction              Start a local transaction          transaction   connection BeginTransaction  SampleTransaction                Must assign both transaction object and connection             to Command object for a pending local transaction         command Connection   connection          command Transaction   transaction           try                       command CommandText                    Insert into Region  RegionID  RegionDescription  VALUES  100   Description                 command ExecuteNonQuery                command CommandText                    Insert into Region  RegionID  RegionDescription  VALUES  101   Description                 command ExecuteNonQuery                    Attempt to commit the transaction              transaction Commit                Console WriteLine  Both records are written to database                       catch  Exception ex                        Console WriteLine  Commit Exception Type   0    ex GetType                 Console WriteLine    Message   0    ex Message                   Attempt to roll back the transaction               try                               transaction Rollback                              catch  Exception ex2                                   This catch block will handle any errors that may have occurred                     on the server that would cause the rollback to fail  such as                     a closed connection                  Console WriteLine  Rollback Exception Type   0    ex2 GetType                     Console WriteLine    Message   0    ex2 Message                                     See SqlTransaction Class

User · Answer

Well  I don t understand why are you used transaction in case when you make a select   Transaction is useful when you make changes  add  edit or delete  data from database   Remove transaction unless you use insert  update or delete statements

User · Answer

Update or Delete with sql transaction   private void SQLTransaction        try        string sConnectionString    My Connection String        string query    UPDATE  dbo   MyTable  SET ColumnName     0   WHERE ID    1          SqlConnection connection   new SqlConnection sConnectionString        SqlCommand command   connection CreateCommand         connection Open         SqlTransaction transaction   connection BeginTransaction           command Transaction   transaction       try          foreach DataRow row in dt MyData Rows             command CommandText   string Format query  row  ColumnName   ToString    row  ID   ToString              command ExecuteNonQuery                    transaction Commit           catch  Exception ex           transaction Rollback           MessageBox Show ex Message   Error                catch  Exception ex         MessageBox Show  Problem connect to database     Error

User · Answer

You have to tell your SQLCommand objects to use the transaction   cmd1 Transaction   transaction    or in the constructor   SqlCommand cmd1   new SqlCommand  select      connectionsql  transaction     Make sure to have the connectionsql object open  too   But all you are doing are SELECT statements   Transactions would benefit more when you use INSERT  UPDATE  etc type actions

[c#] how to use sqltransaction in c#

Examples related to c#

Examples related to sql-server

Examples related to sqltransaction