Good Universal Paramerter and Stored Procedure Execution

Introduction

I hated adding lots of get / update / insert etc., web services / or routines to do the same thing all the time.. So to use one use for all .. I built a universal data layer utility..

Background

Pass the parameters and the values in a array along with the stored procedure name.. let the function do the work and wait.. In three lines you can accomplish a lot. And no rewriting of the same procedure code over and over...

Using the code

So first off have a web service or a module that has your connection code and the handler function. You also have to have the Stored Procedure written.. And then execute your code:

In your web application:

Dim inVar() As String = {"cat", "catType"}
Dim inVal() As Object = {ddCat.SelectedValue, "A"}
Dim dbDs As Dataset = spx_Uni("spg_getAList", inVar, inVal)

In your web service or module :

Imports System.Data.SqlClient
Imports System.Globalization

.
.
.

Public Function GetConnectionString() As String
' Add your connection string info.. 
Data Source="[your server];Initial Catalog=[your database];
           persist security info=True;Integrated Security=SSPI;"
End Function

.
.
.


Function spx_Uni(storedProcedure As String, inSpVariables() As String, inSpValues() As Object) As DataSet
    Dim dbConn As SqlConnection
    Dim dbCmd As SqlCommand
    Dim dbPar As SqlParameter
    Dim dbAdp As SqlDataAdapter
    Dim dbDs As New DataSet
    dbDs.Locale = CultureInfo.CurrentCulture

    dbConn = New SqlConnection(GetConnectionString())
    dbConn.Open()
    dbCmd = dbConn.CreateCommand()
    dbCmd.CommandText = storedProcedure
    dbCmd.CommandType = CommandType.StoredProcedure
    dbCmd.CommandTimeout = 300

    If inSpVariables.Length > 0 Then
        For x As Integer = 0 To inSpVariables.Length - 1
            Dim objct As Object = inSPValues(x)
            Dim sel As String = objct.GetType.ToString
            dbPar = New SqlParameter
            dbPar.ParameterName = "@" + inSpVariables(x)
            dbPar.Value = DBNull.Value
            If inSPValues(x) IsNot Nothing Then
                Select Case sel
                    Case "System.Boolean"
                        dbPar.SqlDbType = SqlDbType.Bit
                        dbPar.Value = Convert.ToBoolean(inSPValues(x))
                    Case "System.DateTime"
                        dbPar.SqlDbType = SqlDbType.DateTime
                        If IsDate(inSPValues(x)) Then
                            dbPar.Value = Convert.ToDateTime(inSPValues(x))
                        End If
                    Case "System.Int32"
                        dbPar.SqlDbType = SqlDbType.Int
                        If Convert.ToInt32(inSPValues(x)) <> 0 Then
                            dbPar.Value = Convert.ToInt32(inSPValues(x))
                        End If
                    Case "System.String"
                        dbPar.SqlDbType = SqlDbType.NVarChar
                        If Not String.IsNullOrEmpty(inSPValues(x)) Then
                            dbPar.Value = inSPValues(x).ToString
                        End If
                    Case "System.Date"
                        dbPar.SqlDbType = SqlDbType.Date
                        If IsDate(inSPValues(x)) Then
                            dbPar.Value = Convert.ToDateTime(inSPValues(x))
                        End If
                End Select
                dbPar.Direction = ParameterDirection.Input
                dbCmd.Parameters.Add(dbPar)
            End If
        Next
        dbAdp = New SqlDataAdapter(dbCmd)
        dbAdp.Fill(dbDs)
    End If
    dbConn.Close()
    spx_Uni = dbDs
End Function

Points of Interest

Interesting stuff.. went through a bunch of revisions.. This may require some further error trapping but otherwise .. This functions very well..

History

• 09-15-2013 - 1 version .. Working.
• 10-16-2013 - 2 version .. Working - Updated some logics and converted some older (faster conversion to the .NET form Convert.To().