Uso corretto di stored procedure con SqlClient, SqlParameters e SqlTransazioni

Per la serie “Non si finisce mai di imparare“, un bel giorno ci siamo accorti di un baco all’interno di ADO.NET (versione 1.1) che si manifesta in concomitanza di una serie precisa di eventi: apriamo la connessione al database (SqlConnection), iniziamo una transazione (SqlTransaction), tramite un SqlCommand richiamiamo la stored procedure passandogli la connessione, la transazione ed i suoi parametri correttamente valorizzati e… per qualche motivo l’unico risultato che si ottiene è un errore che ci informa che la stored procedure deve essere eseguita all’interno della transazione che risulta aperta sulla connessione. Ma come? Io ho passato la transazione all’SqlCommand!

Per venirne a capo abbiamo dovuto fare ricorso, oltre che alle tecniche di debug classiche, anche al buon vecchio Reflector: nel codice, ad un certo punto veniva invocato un SqlCommandBuilder.DeriveParameters: questo metodo, che è definito come Shared (credo si traduca in static per gli amanti di C#), è richiamabile dalla classe senza bisogno di instanziare un oggetto.

Nello screenshot sottostante vi mostro parte del codice incriminato (le righe evidenziate sono quelle che riportano effettivamente l’errore):

Le righe evidenziate mostrano le istruzioni affette dal baco
Le righe incriminate

La soluzione è apparsa subito semplice: riscriviamoci la DeriveParameters (tanto più che è Shared, quindi in teoria non necessita di proprietà particolari della class SqlCommandBuilder), aggiungendo la valorizzazione della transazione nelle due righe incriminate e siamo a posto. Più o meno…

Perché, tanto per cominciare nelle prime righe righe del codice si fa riferimento ad una serie di eccezioni definite nella classe ADP, definita come Friend nella namespace System.Data.

Perciò abbiamo cominciato a riscrivere il codice di DeriveParameters:

  Private Function DeriveParameters(ByRef cmd As SqlCommand) As Boolean
    Dim result As Boolean
    Dim cmdText As String
    Dim strArray As String()
    Dim command As SqlCommand
    Dim list As ArrayList
    result = (cmd.CommandType = CommandType.StoredProcedure)
    If result Then
      strArray = ParseProcedureName(cmd.CommandText)
      If (Not strArray(1) Is Nothing) Then
        cmdText = ("[" & strArray(1) & "]..sp_procedure_params_rowset")
        If (Not strArray(0) Is Nothing) Then
          cmdText = (strArray(0) & "." & cmdText)
        End If
      Else
        cmdText = "sp_procedure_params_rowset"
      End If
      command = New SqlCommand(cmdText, cmd.Connection, cmd.Transaction)
      command.CommandType = CommandType.StoredProcedure
      command.Parameters.Add(New SqlParameter("@procedure_name", SqlDbType.NVarChar, &HFF))
      command.Parameters.Item(0).Value = strArray(3)
      list = New ArrayList
      Try
        Dim ds As New DataSet
        Dim dataAdapter As New SqlDataAdapter(command)
        dataAdapter.Fill(ds)
        Dim dtParms As DataTable = ds.Tables(0)
        Dim parameter As SqlParameter
        For Each dr As DataRow In dtParms.Rows
          parameter = New SqlParameter
          parameter.ParameterName = CStr(dr("PARAMETER_NAME"))
          parameter.SqlDbType = GetSqlDbTypeFromOleDbType(CShort(dr("DATA_TYPE")), CStr(dr("TYPE_NAME")))
          Dim obj2 As Object = dr("CHARACTER_MAXIMUM_LENGTH")
          If TypeOf obj2 Is Integer Then
            parameter.Size = CInt(obj2)
          End If
          parameter.Direction = ParameterDirectionFromOleDbDirection(CShort(dr("PARAMETER_TYPE")))
          If (parameter.SqlDbType = SqlDbType.Decimal) Then
            parameter.Scale = CByte((CShort(dr("NUMERIC_SCALE")) And &HFF))
            parameter.Precision = CByte((CShort(dr("NUMERIC_PRECISION")) And &HFF))
          End If
          list.Add(parameter)
        Next
      Catch ex As Exception
        result = False
      End Try
      If result _
      AndAlso (list.Count = 0) Then
        result = False
      End If
      If result Then
        cmd.Parameters.Clear()
        Dim param As Object
        For Each param In list
          cmd.Parameters.Add(param)
        Next
      End If
    End If  '  result = (cmd.CommandType = CommandType.StoredProcedure)
    Return result
  End Function

Nel fare ciò, è stato necessario riscrivere anche alcune procedure che erano richiamate dalla DeriveParameters originale:

  Private Shared Function ParseProcedureName(ByVal procedure As String) As String()
    Dim strArray As String() = New String(3) {}
    Dim temp As String()
    If (Not procedure Is Nothing) _
    AndAlso (procedure.Length > 0) Then
      temp = procedure.Split("."c)
      Select Case temp.Length
        Case 1
          strArray(3) = procedure
          strArray(2) = Nothing
          strArray(1) = Nothing
          strArray(0) = Nothing
        Case 2
          strArray(3) = temp(1)
          strArray(2) = temp(0)
          strArray(1) = Nothing
          strArray(0) = Nothing
        Case 3
          strArray(3) = temp(2)
          strArray(2) = temp(1)
          strArray(1) = temp(0)
          strArray(0) = Nothing
        Case 4
          strArray(3) = temp(3)
          strArray(2) = temp(2)
          strArray(1) = temp(1)
          strArray(0) = temp(0)
      End Select
    End If
    Return strArray
  End Function

  Private Function ParameterDirectionFromOleDbDirection(ByVal oledbDirection As Short) As ParameterDirection
    Dim result As ParameterDirection
    Select Case oledbDirection
      Case 2
        result = ParameterDirection.InputOutput
      Case 3
        result = ParameterDirection.Output
      Case 4
        result = ParameterDirection.ReturnValue
      Case Else
        result = ParameterDirection.Input
    End Select
    Return result
  End Function

  Friend Shared Function GetSqlDbTypeFromOleDbType(ByVal dbType As Short, ByVal typeName As String) As SqlDbType
    Dim result As SqlDbType
    Dim oleType As OleDbType = CType(dbType, OleDbType)
    result = SqlDbType.Variant
    Select Case oleType
      Case OleDbType.SmallInt, OleDbType.UnsignedSmallInt
        result = SqlDbType.SmallInt
      Case OleDbType.Integer
        result = SqlDbType.Int
      Case OleDbType.Single
        result = SqlDbType.Real
      Case OleDbType.Double
        result = SqlDbType.Float
      Case OleDbType.Currency
        If (typeName = "money") Then
          result = SqlDbType.Money
        Else
          result = SqlDbType.SmallMoney
        End If
      Case OleDbType.Date, OleDbType.Filetime
        If (typeName = "datetime") Then
          result = SqlDbType.DateTime
        Else
          result = SqlDbType.SmallDateTime
        End If
      Case OleDbType.BSTR
        If (typeName = "nchar") Then
          result = SqlDbType.NChar
        Else
          result = SqlDbType.NVarChar
        End If
      Case OleDbType.IDispatch, OleDbType.Error, OleDbType.IUnknown, CType(15, OleDbType), OleDbType.UnsignedInt
        result = SqlDbType.Variant
      Case OleDbType.Boolean
        result = SqlDbType.Bit
      Case OleDbType.Variant
        result = SqlDbType.Variant
      Case OleDbType.Decimal
        result = SqlDbType.Decimal
      Case OleDbType.TinyInt, OleDbType.UnsignedTinyInt
        result = SqlDbType.TinyInt
      Case OleDbType.BigInt
        result = SqlDbType.BigInt
      Case OleDbType.Binary, OleDbType.VarBinary
        If (typeName = "binary") Then
          result = SqlDbType.Binary
        Else
          result = SqlDbType.VarBinary
        End If
      Case OleDbType.Char, OleDbType.VarChar
        If (typeName = "char") Then
          result = SqlDbType.Char
        Else
          result = SqlDbType.VarChar
        End If
      Case OleDbType.WChar, OleDbType.VarWChar
        If (typeName = "nchar") Then
          result = SqlDbType.NChar
        Else
          result = SqlDbType.NVarChar
        End If
      Case OleDbType.Numeric
        result = SqlDbType.Decimal
      Case (OleDbType.Binary Or OleDbType.Single)
        result = result
      Case OleDbType.DBDate, OleDbType.DBTime, OleDbType.DBTimeStamp
        If (typeName = "datetime") Then
          result = SqlDbType.DateTime
        Else
          result = SqlDbType.SmallDateTime
        End If
      Case OleDbType.Guid
        result = SqlDbType.UniqueIdentifier
      Case OleDbType.LongVarChar
        result = SqlDbType.Text
      Case OleDbType.LongVarWChar
        result = SqlDbType.NText
      Case OleDbType.LongVarBinary
        result = SqlDbType.Image
    End Select
    Return result
  End Function

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*