How to insert transaction of more than one row

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.

Answer 1

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.

Answer 2

What about using Transactions with SQL Command.Wouldn't it do the needful?

Answer 3

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

Answer 4

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!