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.
- The first layer was of course the data itself.
- Next, there was a Stored Procedure that was used to access the raw data and de-normalize it.
- Then, in the application, there was a function that would retrieve data and add it to the cache.
- Then, a function that would call the Stored Procedure by name and define its parameters.
- Another function was responsible for trying to get data from the cache. If it was unsuccessful, it would call the function above.
- 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]
@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 = ""
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
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.
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
Dim neededParameters As DataTable = GetParametersFromStoredProcedure(
storedProcedureName)
Dim returnDataTable As DataTable = Nothing
Dim ParameterCollectionArray() As String
Dim delimiter() As Char = {Chr(124)}
If strCacheName = "" Then
strCacheName = parameterCollection + storedProcedureName
End If
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
ParameterCollectionArray = uniqueFields.Split(delimiter)
Return returnDataTable.DefaultView.ToTable(True, ParameterCollectionArray)
End If
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
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
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.