Click here to Skip to main content
15,069,408 members
Articles / Web Development / ASP.NET
Tip/Trick
Posted 17 Sep 2013

Stats

11.4K views
4 bookmarked

Good Universal Paramerter and Stored Procedure Execution

Rate me:
Please Sign up or sign in to vote.
3.25/5 (3 votes)
16 Oct 2013CPOL
Check this for a good universal way of running stored procedures..

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:

VB
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 :

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Robert Bettinelli
Web Developer Nottawasaga Valley Conservation Authority
Canada Canada
I have background in programming(many languages), web design, hardware and software. Currently I hold a lead database programming position for the Nottawasaga Valley Conservation Authority... Programming in vb.net and java comes 2nd nature to me and of course I do this for fun. I also used to design in cobol and pascal and run a BBS(anyone remember what this was?). Anyways, drop me a line maybe I can help you.

Comments and Discussions

 
GeneralMy vote of 1 Pin
stefanveliki18-Sep-13 3:18
Memberstefanveliki18-Sep-13 3:18 
GeneralRe: My vote of 1 Pin
Robert Bettinelli18-Sep-13 3:33
MemberRobert Bettinelli18-Sep-13 3:33 
GeneralMy vote of 4 Pin
iMaker.ph17-Sep-13 12:39
MemberiMaker.ph17-Sep-13 12:39 
GeneralRe: My vote of 4 Pin
stefanveliki18-Sep-13 3:20
Memberstefanveliki18-Sep-13 3:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.