Transaction for DataSet and DataAdaptor RRS feed

  • Question

  • I have the following code in VB.Net to call a Stored Proceedure which works fine.
    But now I need to implement SQL transactions for this code.
    Does anybody knows this can help me to convert the code in such a way that it can be in between Begin and Commit Transaction.
    I have heard that we need to use CommandType and CommandText and ExecuteNonQuery to do this.

    Please advice.

    Thanks in advance.

    Dim MyDataAdapter As SqlDataAdapter
    Dim DS As DataSet

    MyDataAdapter = New SqlDataAdapter("SaveCC_ActivityLog", MyConnection)
    MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

    MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CaseStatus", SqlDbType.VarChar, 1024))
    MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ActDate", SqlDbType.SmallDateTime, 8))

    MyDataAdapter.SelectCommand.Parameters("@CaseStatus").Value = Trim(cmbVStatus.Text)
    MyDataAdapter.SelectCommand.Parameters("@ActDate").Value = Trim(DateTime.Now)

    DS = New DataSet()

    If Me.dgActivityLogTag(i) = Nothing Then
    Me.dgActivityLogTag(i) = DS.Tables(0).Rows(0).Item(0).ToString()
    End If
    Monday, February 18, 2008 2:14 AM

All replies

  • Why wouldn't you put the BEGIN TRANSACTION and COMMIT into the stored procedure?  Are there any circumstances under which you would not want the SP to run as a transaction?

    Monday, February 18, 2008 9:56 PM
  • Thanks for replying.

    Actually the code you saw there is only the tip of the ice.

    There are more than 20 stored proceedure in the same way inside one single function. they are being called one by one.

    So if any one of the proceedure fails or return error, then I need to rollback, else at last i commit.

    But now if I need to change any stored proceedures, then it will be very troublesome and time consuming.

    So is there any way.

    Wednesday, February 20, 2008 5:08 AM
  • The SqlConnection object has a BeginTransaction method that returns a SqlTransaction object.  You provide both the connection and the transaction to each SqlCommand that you execute, and call the SqlTransaction's Rollback or Commit methods when you're done.


    Generally speaking, creating a transaction and then executing 20 stored procedures within its scope is something you should be concerned about.  You want to avoid long-duration transactions, because when the database obtains a lock inside a transaction, it holds that lock until the transaction is committed.  There are lots of scenarios where long-running transactions in multi-user systems can create deadlocks, especially when developers are cobbling things together by executing a bunch of SPs in sequence.


    Wednesday, February 20, 2008 9:44 PM