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
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()
.