开发用于访问存储过程的抽象函数

引言

如果您使用的应用程序使用存储过程来访问和/或更新其所有数据，这是一种我喜欢使用的技术，可以最大限度地减少在多个地方使用参数名称，并允许您更轻松地维护此信息。

背景

我开始为一家拥有两年历史的门户网站的公司工作，每当我创建用于更新或访问数据的函数时，我都会感到有些沮丧。 所有的数据访问方法被分解为六个层。

1. 第一层当然是数据本身。
2. 接下来，有一个存储过程用于访问原始数据并进行反范式化。
3. 然后，在应用程序中，有一个函数用于检索数据并将其添加到缓存中。
4. 然后，一个函数将通过名称调用存储过程并定义其参数。
5. 另一个函数负责尝试从缓存中获取数据。如果失败，它将调用上面的函数。
6. 从代码隐藏调用原始函数以将内容添加到页面中。

我一点也不喜欢这样，所以我设置了一个新类，并使用一个函数来通过其名称调用每个存储过程。 我遇到的一个问题是参数。 因此，我首先从 SQL Server 获取有关存储过程的模式信息。 此信息将包含参数名称和数据类型。 这使我能够将数据转换为适当的类型并循环遍历参数。

使用代码

这是获取模式信息的 SQL 存储过程

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

这是调用存储过程的函数，该函数确实将参数名称硬编码

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

此函数通过其名称运行存储过程。 参数需要作为字符串发送，每个条目用 | 分隔并按字母顺序排列。 这允许您分隔条目并根据数组中的索引收集它们。 getSqlDBTypeFromString 函数将根据 SQL 模式信息中的数据类型返回适当的数据类型。 根据您的参数，您可能需要添加更多内容以将它们转换为适当的 SQL 类型。

''' <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

这是我用来调用 Update 和 Insert 查询的过程。 它也使用相同的参数逻辑。 如果您想返回 SCOPE_IDENTITY()，您可能需要使用 ExecuteScalar。

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

关注点

这对我们来说是减少代码和应用程序管理的好方法。

历史

目前还没有。