• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

SQL BeginTransaction Command

HI i have the below code. Its doing the job i want it to perform but i was wondering is the a better way to achieve what i am doing below.

if you are unable to understand anything or need additional info, please do not hesistate to ask

i am willing to give extra points to anyone who can help me out
Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter
 
        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        MyConnection.Open()
        'Create a DataAdapter, and then provide the name of the stored procedure.
        MyDataAdapter = New SqlDataAdapter("sp_GetTXNNumber", MyConnection)
 
        'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 
        'Create and add an output parameter to Parameters collection. 
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@TXNNumber", SqlDbType.Int, 8))
 
        'Set the direction for the parameter. This parameter returns the Rows returned.
        MyDataAdapter.SelectCommand.Parameters("@TXNNumber").Direction = ParameterDirection.Output
 
        DS = New DataSet() 'Create a new DataSet to hold the records.
        MyDataAdapter.Fill(DS, "TransactionNumber") 'Fill the DataSet with the rows returned.
 
        ' Start a local transaction
        Dim myTrans As SqlTransaction = MyConnection.BeginTransaction()
 
        Dim myCommand As New SqlCommand()
        myCommand.Connection = MyConnection
        myCommand.Transaction = myTrans
 
        Try
 
            myCommand.CommandText = "Insert into TXNBonusPoints (MemberCode, BonusDate, ReasonCode, BonusPoints, Comments, TXNNumber, UserAllowed, Active) values (@MemberCode , @BonusDate, @ReasonCode, @BonusPoints, @Comments, @TXNNumber, @UserAllowed, @Active)"
            Dim Param As New SqlParameter
            Param.ParameterName = "@MemberCode"
            Param.Value = txt_MemberCode.Text
            myCommand.Parameters.Add(Param)
 
            Dim Param1 As New SqlParameter
            Param1.ParameterName = "@BonusDate"
            Param1.Value = Date.Today.ToString("dd-MMM-yyyy")
            myCommand.Parameters.Add(Param1)
 
            Dim Param2 As New SqlParameter
            Param2.ParameterName = "@ReasonCode"
            Param2.Value = DDL_reasonCode.SelectedValue
            myCommand.Parameters.Add(Param2)
 
            Dim Param3 As New SqlParameter
            Param3.ParameterName = "@BonusPoints"
            Param3.Value = CInt(txt_BonusPoints.Text)
            myCommand.Parameters.Add(Param3)
 
            Dim Param4 As New SqlParameter
            Param4.ParameterName = "@Comments"
            Param4.Value = txt_Comments.Text
            myCommand.Parameters.Add(Param4)
 
            Dim Param5 As New SqlParameter
            Param5.ParameterName = "@TXNNumber"
            Param5.Value = MyDataAdapter.SelectCommand.Parameters(0).Value
            myCommand.Parameters.Add(Param5)
 
            Dim Param6 As New SqlParameter
            Param6.ParameterName = "@UserAllowed"
            Param6.Value = User.Identity.Name()
            myCommand.Parameters.Add(Param6)
 
            Dim Param7 As New SqlParameter
            Param7.ParameterName = "@Active"
            Param7.Value = 1
            myCommand.Parameters.Add(Param7)
 
            myCommand.ExecuteNonQuery()
            myCommand.Parameters.Clear()
 
            myCommand.CommandText = "Insert into TXNPointSummary (TXNNumber, MemberCode, PointsType, TXNDate, Points, UserUpdated, Comments) values (@TXNNumber, @MemberCode, @PointsType, @TXNDate, @Points, @UserUpdated, @Comments)"
 
            Dim SumParam As New SqlParameter
            SumParam.ParameterName = "@TXNNumber"
            SumParam.Value = MyDataAdapter.SelectCommand.Parameters(0).Value
            myCommand.Parameters.Add(SumParam)
 
            Dim SumParam1 As New SqlParameter
            SumParam1.ParameterName = "@PointsType"
            SumParam1.Value = "BP" 'Bp = Bonus Points
            myCommand.Parameters.Add(SumParam1)
 
            Dim SumParam2 As New SqlParameter
            SumParam2.ParameterName = "@MemberCode"
            SumParam2.Value = txt_MemberCode.Text
            myCommand.Parameters.Add(SumParam2)
 
            Dim SumParam3 As New SqlParameter
            SumParam3.ParameterName = "@TXNDate"
            SumParam3.Value = Date.Today.ToString("dd-MMM-yyyy")
            myCommand.Parameters.Add(SumParam3)
 
            Dim SumParam4 As New SqlParameter
            SumParam4.ParameterName = "@Points"
            SumParam4.Value = txt_BonusPoints.Text
            myCommand.Parameters.Add(SumParam4)
 
            Dim SumParam5 As New SqlParameter
            SumParam5.ParameterName = "@UserUpdated"
            SumParam5.Value = User.Identity.Name()
            myCommand.Parameters.Add(SumParam5)
 
            Dim SumParam6 As New SqlParameter
            SumParam6.ParameterName = "@Comments"
            SumParam6.Value = txt_Comments.Text
            myCommand.Parameters.Add(SumParam6)
 
            myCommand.ExecuteNonQuery()
 
            ' Attempt to commit the transaction.
            myTrans.Commit()
            Message = "Bonus Point Successfully Entered"
            MyMessageBoxInfo.Show(MyMessageBox.MessageType.Success, Message, 120, 300)
           MyConnection.Close()

Open in new window

0
AsishRaj
Asked:
AsishRaj
  • 3
  • 2
1 Solution
 
RiteshShahCommented:
you can use using(transaction) so that if transaction doesn't get complete, it rollback its own, have a look at

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_23599902.html?eeSearch=true
0
 
AsishRajAuthor Commented:
thanks Ritesh, but i have handled those things in my original code.

What i am after is -> Can the above snippet code be simplified to do what it currently does.

in first step it access the stored procedure to retrieve new transaction number to use and then it updates two different table using MyConnection.BeginTransaction() .....

i have Try Catch error properly defined

0
 
kssoftwareCommented:
From a code simplification point of view, you can use the AddWithValue method of hte parameters collection of the command object as shown in the reformatted code snippet.  This allows you to specify the parameters and the value in a simple statement: AddWithValue accepts the Parameters Name and Value but not additional attributes like parameter DataType, parameter Direction. If you need to pass all those you will need to use Parameters.Add.

I have also converted the dataAdapter to an sqlcommand object and simply retrieved the transaction number from the parameter "@TXNNumber" into a local variable - that way the command is closed and disposed as it leaves the small "With... End with" block - I use the ExecuteNonQuery method as we don;t really want a recordset returned, merely a value of the next transaction number:  a bit kinder to the database, I suppose and a bit more memory efficient.

Also, if this is a SQL database, with the transaction date parameters (@BonusDate, @TXNDate) you might consider using a default value of getdate() on the field in the database, thereby removing the need for those parameters.

Finally, you could move the entire code into a stored procedure in the database and let it do the work of beginning the transaction and committing it, or rolling back on error.

Dim MyConnection As SqlConnection
        Dim MyDataAdapter As New SqlCommand
        Dim _txnumber As String
        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        MyConnection.Open()
        'Create a DataAdapter, and then provide the name of the stored procedure.
        With MyDataAdapter
            .Connection = MyConnection
            .CommandText = "sp_GetTXNumber"
            .CommandType = CommandType.StoredProcedure
            Dim _ptxnumber As New SqlClient.SqlParameter
            With _ptxnumber
                .ParameterName = "@TXNNumber"
                .SqlDbType = SqlDbType.Int
                .Direction = ParameterDirection.Output
            End With
            .Parameters.Add(_ptxnumber)
            .ExecuteNonQuery()
            _txnumber = .Parameters("@TXNNumber").Value.ToString
 
        End With
        ' Start a local transaction
        Dim myTrans As SqlTransaction = MyConnection.BeginTransaction()
 
        Dim myCommand As New SqlCommand()
        myCommand.Connection = MyConnection
        myCommand.Transaction = myTrans
 
        Try
 
            myCommand.CommandText = "Insert into TXNBonusPoints (MemberCode, BonusDate, ReasonCode, BonusPoints, Comments, TXNNumber, UserAllowed, Active) values (@MemberCode , @BonusDate, @ReasonCode, @BonusPoints, @Comments, @TXNNumber, @UserAllowed, @Active)"
            myCommand.Parameters.AddWithValue("@MemberCode", txt_MemberCode.Text)
            myCommand.Parameters.AddWithValue("@BonusDate", Date.Today.ToString("dd-MMM-yyyy"))
            myCommand.Parameters.AddWithValue("@ReasonCode", DDL_reasonCode.SelectedValue)
            myCommand.Parameters.AddWithValue("@BonusPoints", CInt("0" & txt_BonusPoints.Text))
            myCommand.Parameters.AddWithValue("@Comments", txt_Comments.Text)
            myCommand.Parameters.AddWithValue("@TXNNumber", _txnumber)
            myCommand.Parameters.AddWithValue("@UserAllowed", User.Identity.Name())
            myCommand.Parameters.AddWithValue("@Active", 1)
 
            myCommand.ExecuteNonQuery()
            myCommand.Parameters.Clear()
 
            myCommand.CommandText = "Insert into TXNPointSummary (TXNNumber, MemberCode, PointsType, TXNDate, Points, UserUpdated, Comments) values (@TXNNumber, @MemberCode, @PointsType, @TXNDate, @Points, @UserUpdated, @Comments)"
            myCommand.Parameters.AddWithValue("@TXNNumber", _txnumber)
            myCommand.Parameters.AddWithValue("@PointsType", "BP")
            myCommand.Parameters.AddWithValue("@MemberCode", txt_MemberCode.Text)
            myCommand.Parameters.AddWithValue("@TXNDate", Date.Today.ToString("dd-MMM-yyyy"))
            myCommand.Parameters.AddWithValue("@Points", txt_BonusPoints.Text)
            myCommand.Parameters.AddWithValue("@UserUpdated", User.Identity.Name())
            myCommand.Parameters.AddWithValue("@Comments", txt_Comments.Text)
            myCommand.ExecuteNonQuery()
 
            ' Attempt to commit the transaction.
            myTrans.Commit()
            Message = "Bonus Point Successfully Entered"
            MyMessageBoxInfo.Show(MyMessageBox.MessageType.Success, Message, 120, 300)
            MyConnection.Close()

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
kssoftwareCommented:
If you would like me to send a copy of the stored procedure, let me know and I'll be happy to oblige.
0
 
AsishRajAuthor Commented:
kssoftware:

i will get in touch shortly.
0
 
AsishRajAuthor Commented:
sorry 4 da delay, i have currently got some other higher priority section to complete. this one should comeback up in another fortnight

Sorry.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now