none
How I can user ADO.NET Connection transaction? RRS feed

  • Question

  •  

    Hi,

    I need to perform a transaction operation.  I have an appointment record and one or more procedures that must go with it. No appointment allowed to be inserted in the table without at least one procedure. So what I did is create an appointment object that contains the appointment information and a custom collection for the appointment procedures.

    In the user interface tier the appointment object get filled with appointment information and the procedures collection has at least one procedure, then I send the appointment object to my data access tier for processing the insert operation.

    I used a stored procedure for inserting the appointment data and a dynamic SQL commands generation function to build all the necessary SQL statements for the appointment procedures.

    Please I need you help in this regard. Is my way of handling this issue correct? Are there any other ways that I can use to accomplish this task?

    Thank you.

    My Code:

    Public Function AddAppointment(ByVal oAppointment As InfoAppointment) As Boolean

            Dim boolSeccessed As Boolean = False

     

            Call ClearMessages()

     

            If oAppointment.ProcedureLines.Count = 0 Then

               MyBase.InfoMessage = "Appointment Procedure Must be Supplied"

     

            End If

     

            Dim oTransaction As SqlTransaction

     

            Try

                Dim oCon As New SqlConnection(MyBase.ConnectionString)

     

                Using oCon

                    oCon.Open()

                    Dim cmd As New SqlCommand("SpAppointment_Insert")

                    oTransaction = oCon.BeginTransaction()

                    Using cmd

                        cmd.CommandType = CommandType.StoredProcedure

                        cmd.Connection = oCon

                        cmd.Transaction = oTransaction

                        With cmd.Parameters

                            .Add("@AppointmentDate", SqlDbType.DateTime).Value = intToField(oAppointment.AppointmentNo)                       

                            .Add("@StartTime", SqlDbType.DateTime).Value = DateToField(oAppointment.StartTime.ToLongDateString)

                            .Add("@EndTime", SqlDbType.DateTime).Value = DateToField(oAppointment.EndTime.ToLongDateString)

                            .Add("@MRFileId", SqlDbType.Int).Value = intToField(oAppointment.MRFileId)

                            .Add("@PhysicianNo", SqlDbType.Int).Value = intToField(oAppointment.PhysicianNo)

                           

                            .Add(New SqlParameter("@AppointmentNo", SqlDbType.Int))

                            .Item("@AppointmentNo").Direction = ParameterDirection.Output

                            .Add(New SqlParameter("@RTCode", SqlDbType.Int))

                            .Item("@RTCode").Direction = ParameterDirection.Output

                            .Add(New SqlParameter("@RTMessage", SqlDbType.VarChar, 100))

                            .Item("@RTMessage").Direction = ParameterDirection.Output

                            .Add(New SqlParameter("@FieldInError", SqlDbType.VarChar, 50))

                            .Item("@FieldInError").Direction = ParameterDirection.Output

                        End With

     

                        MyBase.RowsAffected = cmd.ExecuteNonQuery()

     

                        If CInt(cmd.Parameters("@RTCode").Value) = 0 Then

                            MyBase.Identity = FieldToint32(cmd.Parameters("@AppointmentNo").Value)

                            MyBase.InfoMessage = FieldToStr(cmd.Parameters("@RTMessage").Value)

                            boolSeccessed = True

                        Else

                            MyBase.InfoMessage = FieldToStr(cmd.Parameters("@RTMessage").Value)

                            MyBase.FieldInError = FieldToStr(cmd.Parameters("@FieldInError").Value)

                            Throw New Exception

                        End If

     

                        Dim i As Integer

                        Dim oProcedure As InfoProcedureLine

                        For Each oProcedure In oAppointment.ProcedureLines

                            If Not IsNothing(oAppointment.ProcedureLines.Item(i)) Then

                                cmd.CommandType = CommandType.Text

                                cmd.CommandText = SqlInsert(oAppointment.ProcedureLines.Item(i))

                                cmd.ExecuteNonQuery()

                            End If

                        Next

     

                        oTransaction.Commit()

                    End Using

                End Using

     

            Catch ex As Exception

                MyBase.ExceptionMessage = ex.Message

     

                Try

                    oTransaction.Rollback()

                Catch sqlex As SqlException

                    If Not oTransaction.Connection Is Nothing Then

                        MyBase.InfoMessage = "An exception of type " & ex.GetType().ToString() & _

                          " was encountered while attempting to roll back the transaction."

                    End If

                End Try

     

            Return boolSeccessed

        End Function

    Saturday, January 24, 2009 5:04 PM

Answers

  • You do not need to pass record, all you need to do is to pass all child values as separate parameters and execute appropriate SQL statement(s) inside of SP. Try to avoid dynamic SQL since it affect performance of application and leads to potential SQL injection vulnerability.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Sami2020 Thursday, January 29, 2009 10:58 PM
    Thursday, January 29, 2009 5:36 PM
    Moderator

All replies

  • What are your doubt? Do you see any issues on your side? From the maintenance point of view I would suggest to use stored procedures for all SQL statements. If you do it partially (some code in SP and some dynamically built), it shows inconsistency in design and it is harder to maintain such application. From the transaction point of view you do it correctly, inserting parent record first and then inserting related records. I also would recommend to change Throw New Exception statement to some sort of custom exception that derives from ApplicationException and carries custom error message, otherwise you will end up with the exception which provide no information and you do not know if it failed because something happened in application or because of your own thrown exception.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, January 28, 2009 10:37 AM
    Moderator
  •  

    About the custom exception I'm going to take care of that in a later stage because I'm still working in the code to find the best approach that can handle my transaction issue. My doubt is the way I'm handling the transaction by using SP for the parent record and a dynamic SQL for related records.  

    Now you are recommending me to use SP for both parent and Childs records. So other than looping to create the dynamic SQL I should instead use the looping to pass the child record as a parameter to a function that executes another SP for inserting the Child record.

    Wednesday, January 28, 2009 3:58 PM
  • You do not need to pass record, all you need to do is to pass all child values as separate parameters and execute appropriate SQL statement(s) inside of SP. Try to avoid dynamic SQL since it affect performance of application and leads to potential SQL injection vulnerability.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Sami2020 Thursday, January 29, 2009 10:58 PM
    Thursday, January 29, 2009 5:36 PM
    Moderator