Developing Abstract Functions for Accessing Stored Procedures

Introduction

If you are using an application which uses Stored Procedures to access and/or update all of its data, this is a technique that I like to use to minimize having parameter names in multiple places and which allows you to maintain this information much more easily.

Background

I started working for a company that had a two year old web portal, and I faced some frustration whenever I would create functions to update or access data. All of the data access methods were broken into six layers.

1. The first layer was of course the data itself.
2. Next, there was a Stored Procedure that was used to access the raw data and de-normalize it.
3. Then, in the application, there was a function that would retrieve data and add it to the cache.
4. Then, a function that would call the Stored Procedure by name and define its parameters.
5. Another function was responsible for trying to get data from the cache. If it was unsuccessful, it would call the function above.
6. The call to the original function is made from the code-behind to add content to a page.

I didn't like this at all, so I set up a new class and had a function that would call each Stored Procedure by its name. The one problem I had is parameters. So, I first get the schema information from SQL Server about the Stored Procedure. This information will contain parameter names and data types. This allowed me to cast the data as the appropriate type and loop through the parameters.

Using the code

This the the SQL Stored Procedure to get the schema information:

CREATE PROCEDURE [dbo].[GetStoredProcedureParametersFromName]

-- Add the parameters for the stored procedure here

@stored_procedure_name varchar(150)

AS

BEGIN

SELECT 

dbo.syscolumns.name AS ColName, 
dbo.systypes.name as ColType,
dbo.syscolumns.length AS ColLen,
ex.value

FROM 

dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype

left outer join sys.extended_properties ex on ex.major_id = dbo.sysobjects.id

and dbo.syscolumns.name = ex.name

WHERE 

(dbo.sysobjects.name = @stored_procedure_name) 

AND 

(dbo.systypes.status <> 1) AND dbo.sysobjects.xtype = 'P'

order by ColName 

END

This is the function that calls the Stored Procedure which does have the parameter names hardcoded:

Public Function GetParametersFromStoredProcedure(
    ByVal storedProcedureName As String) As DataTable
    Dim strSQLQuery As String = "GetStoredProcedureParametersFromName"
    Dim strTableToWatch As String = "" ' this is bogus
    Dim DT As DataTable
    Try
        Dim objSQLCmd As New SqlClient.SqlCommand
        With objSQLCmd
            .CommandText = strSQLQuery
            .CommandType = CommandType.StoredProcedure
            With .Parameters
                .Add(CreateSQLParameter("stored_procedure_name",
                    storedProcedureName,
                    SqlDbType.VarChar, ParameterDirection.Input))
            End With
        End With
        'This is a function that retrieves data from the command and 
        'adds it to the cache.
        DT = ReturnDataTableAddToCache(objSQLCmd,
            storedProcedureName + "schema:", strTableToWatch)
        Return DT
    Finally
        DT = Nothing
    End Try
End Function

This function runs the Stored Procedure from its name. The parameters need to be sent as a string that has each entry separated by a | in alphabetical order. This allows you to separate the entries and collect them based on the index in the array. The getSqlDBTypeFromString function will return the appropriate data type based on the data type from the SQL schema information. Depending on your parameters, you may need to add more to cast them into the appropriate SQL type.

''' <summary />
''' This procedure will disect the parameters from the pipes (|) into
''' seperate values
''' </summary />
''' <param name="""strTableToWatch""" />The table in the Database
'''     to monitor for changes when you want to
''' release the cache</param />
''' <param name="""strCacheName""" />The key that will store the cached values</param />
''' <param name="""storedProcedureName""" />The name of the stored procedure</param />
''' <param name="""parameterCollection""" />A string delimited by a | for
'''               the parameters.  The type casting will be
''' done in this method so pass strings.</param />
''' <param name="""uniqueFields""" />A string delimited by a | for the uniqueFields</param />
''' <returns /></returns /> 
''' <remarks /></remarks />
Public Function GetDataTableFromStoredProcedureName(ByVal strTableToWatch As String,
       ByVal strCacheName As String, & _
       ByVal storedProcedureName As String,
       Optional ByVal parameterCollection As String = "", & _ 
    Optional ByVal uniqueFields As String = "") As DataTable

    Dim parameterCount As Integer = 0
    'Get the list of parameters and data types from SQL for this stored procedure
    Dim neededParameters As DataTable = GetParametersFromStoredProcedure(
        storedProcedureName)
    Dim returnDataTable As DataTable = Nothing
    Dim ParameterCollectionArray() As String
    Dim delimiter() As Char = {Chr(124)}

    '3/27/08 BJK < Start create cache name intelligently
    If strCacheName = "" Then
        strCacheName = parameterCollection + storedProcedureName

    End If

    'try to get the data from cache
    Try
        returnDataTable = DirectCast(
            HttpContext.Current.Cache.Item(strCacheName), DataTable)
    Finally
    End Try
    If returnDataTable Is Nothing Then
        Try
            Dim objSQLCmd As New SqlClient.SqlCommand

            With objSQLCmd
                .CommandText = storedProcedureName
                .CommandType = CommandType.StoredProcedure

                If parameterCollection <> "" Then
                    ParameterCollectionArray =
                        parameterCollection.Split(delimiter)
                    For parameterCount = 0 To neededParameters.Rows.Count - 1
                        Dim parameterObject As Object = CType(
                            ParameterCollectionArray(parameterCount), Object)
                        With .Parameters
                            Dim parameterInfo As DataRow = neededParameters.Rows(
                                parameterCount)
                            .Add(CreateSQLParameter(
                                parameterInfo.Item("ColName").ToString(),
                                parameterObject, & _ 
        getSqlDBTypeFromString(parameterInfo.Item("ColType").ToString()),
            ParameterDirection.Input))
                        End With
                    Next parameterCount
                End If

            End With
            returnDataTable = ReturnDataTableAddToCache(objSQLCmd, strCacheName,
                strTableToWatch)

        Finally
        End Try
    End If

    If uniqueFields = "" Then
        Return returnDataTable
    Else
        'get unique information
        ParameterCollectionArray = uniqueFields.Split(delimiter)
        Return returnDataTable.DefaultView.ToTable(True, ParameterCollectionArray)
    End If
    '3/27/08 BJK > End
End Function

Public Function getSqlDBTypeFromString(ByVal typeName As String) As SqlDbType
    Select Case True
        Case typeName.Contains("varchar")
            Return SqlDbType.VarChar
        Case typeName.Contains("int")
            Return SqlDbType.Int
        Case typeName.Contains("datetime")
            Return SqlDbType.DateTime
        Case typeName.Contains("bit")
            Return SqlDbType.Bit
    End Select
End Function

This is the procedure I use to call Update and Insert queries. It uses the same logic with parameters though. You may want to use ExecuteScalar if you want to return SCOPE_IDENTITY().

Public Function ExecuteStoredProcedureByName(ByVal storedProcedureName As String, & _
       Optional ByVal parameterCollection As String = "") As Integer

    Dim parameterCount As Integer = 0
    'Get the list of parameters and data types from SQL for this stored procedure
    Dim neededParameters As DataTable = GetParametersFromStoredProcedure(
        storedProcedureName)

    Try
        Dim objSQLCmd As New SqlClient.SqlCommand

        With objSQLCmd
            .CommandText = storedProcedureName
            .CommandType = CommandType.StoredProcedure

            If parameterCollection <> "" Then
                Dim s() As Char = {Chr(124)}
                Dim ParameterCollectionArray() As String = 
                    parameterCollection.Split(s)
                For parameterCount = 0 To neededParameters.Rows.Count - 1
                    Dim parameterInfo As DataRow = neededParameters.Rows(
                        parameterCount)
                    Dim parameterName As String = parameterInfo.Item(
                        "ColName").ToString()
                    Dim parameter As Object = ParameterCollectionArray(
                        parameterCount)
                    Try
                        If parameter.ToString() = "DBNull" Then
                            parameter = DBNull.Value
                        End If
                    Catch ex As Exception

                    End Try
                    Dim parameterObject As Object = CType(parameter, Object)
                    With .Parameters
                        .Add(CreateSQLParameter(parameterName, parameterObject, & _ 
        getSqlDBTypeFromString(parameterInfo.Item("ColType").ToString()),
            ParameterDirection.Input))
                    End With
                Next parameterCount
            End If

        End With

        Return ExecuteNonQueryReturnRowsAffected(objSQLCmd)
    Finally
    End Try
End Function

Public Function ExecuteNonQueryReturnRowsAffected(ByVal objSQLCmd _
                As SqlCommand) As Integer
'not threaded

    Using objSQLcn As New SqlConnection(CurrentSQLConnectionString)

        Try
            objSQLcn.Open()
            objSQLCmd.Connection = objSQLcn
            Return objSQLCmd.ExecuteNonQuery()
        Catch
            Return 0
        Finally
            objSQLCmd = Nothing
        End Try
    End Using
End Function

Points of interest

This has been a great way to reduce code and application management for us.

History

None so far.