locked
How to insert transaction of more than one row RRS feed

  • Question

  • My problem is that i have more than 1 rows in a data gridview, those rows are manually added via textbox entries (Say of an order form and the form contains more than one orders) and the Datagridview is not bound to any control. now what would be the best way to insert all the rows into a SQL Server table. like if i insert it one by one, there may be a connection break and the data may be incomplete in the database. if i use a transaction in SQL Stored Procedure that even doesn't work as it will look at the current entry and not all the rows in the datagrid view. this is a great problem for me to look for to keep my data integrity intact and insert all the rows successfully into SQL Server Database.
    Monday, May 24, 2010 11:57 AM

Answers

All replies

  • Take a look at

    http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx

    The only change you want to make in the try block, you need to loop through the changed rows in your datagridview.

    • Proposed as answer by liurong luo Thursday, May 27, 2010 4:56 AM
    • Marked as answer by NET HAWK Thursday, May 27, 2010 5:31 AM
    Tuesday, May 25, 2010 6:00 AM
  • What about using Transactions with SQL Command.Wouldn't it do the needful?
    Thursday, May 27, 2010 5:36 AM
  • What about using Transactions with SQL Command.Wouldn't it do the needful?


    Not sure what you meant Here is a code snippet:

    Using connection As New SqlConnection(connectionString)
      connection.Open()
    
      ' Start a local transaction.
      Dim sqlTran As SqlTransaction = connection.BeginTransaction()
    
      ' Enlist a command in the current transaction.
      Dim command As SqlCommand = connection.CreateCommand()
      command.Transaction = sqlTran '<== you assigned the transaction to the command at this point
    
      Try
       
        For each row in YourDataGridView.Rows
    
           'Insert the data into your database
        Next
        
        ' Commit the transaction
        sqlTran.Commit() '<--- commit if no error occurs
        Console.WriteLine("Both records were written to database.")
    
      Catch ex As Exception
        ' Handle the exception if the transaction fails to commit.
        Console.WriteLine(ex.Message)
    
        Try
          ' Attempt to roll back the transaction.
          sqlTran.Rollback() <=== otherwise rollback your insert
     
        Catch exRollback As Exception
          ' Throws an InvalidOperationException if the connection 
          ' is closed or the transaction has already been rolled 
          ' back on the server.
          Console.WriteLine(exRollback.Message)
        End Try
      End Try
    End Using
    
    
    • Proposed as answer by liurong luo Friday, May 28, 2010 8:51 AM
    Thursday, May 27, 2010 6:14 AM
  • Yes! This is the right answer to my question. For multiple inserts transactions are the best choice to ensure all the lines are written to the database or all the lines were not written. there is no intermediate in this solution.

     

    Thanks!

    Thursday, May 27, 2010 6:26 AM