Blogs - StoredProcedure Class

DEVSHED Blog

As always, I welcome your comments!

StoredProcedure Class

Dec 9

Written by: Steve Gray
12/9/2010 4:16 PM

This is the class that I use to abstract the Enterprise Library DAAB. There are other classes, found here

Code Snippet

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql
Imports System.Configuration
 
Public Class storedProcedure
    Dim _storedProcName As String
    Dim _db As String
    Public commandParameters As System.Collections.Generic.List(Of commandParameter)
    Public CommandTimeout As Int16
    Dim cs As New Dictionary(Of String, String)
 
    Sub New(ByVal StoredProcName As String, ByVal db As String)
        _storedProcName = StoredProcName
        _db = db.ToUpper
        commandParameters = New System.Collections.Generic.List(Of commandParameter)
 
        cs.Add("NGB01", "Data Source=ngb-sql-05;User Id=4pennyapp;Password=app4me;Initial Catalog=NGB01")
        cs.Add("ELSTN", "Data Source=ngb-sql-05;User Id=4pennyapp;Password=app4me;Initial Catalog=ELSTN")
        cs.Add("NGBOED", "Data Source=ngb-sql-05;User Id=4pennyapp;Password=app4me;Initial Catalog=NGBOED")
        cs.Add("STONE", "Data Source=ngb-sql-05;User Id=4pennyapp;Password=app4me;Initial Catalog=STONE")
        cs.Add("INTRANET2", "Data Source=ngb-sql-05;User Id=4pennyapp;Password=app4me;Initial Catalog=Intranet2")
        cs.Add("TWO", "Data Source=vmGP11;User Id=4pennyapp;Password=app4me;Initial Catalog=TWO")
    End Sub
 
    Function execute(ByVal timeout As Int32, ByRef cps As System.Collections.Generic.List(Of commandParameter)) As Int32
        Dim strError As String = ""
        Try
 
            Dim aParams As SqlParameter() = Nothing
            Dim a As Int16 = 0
 
            Dim db As Database
            db = New SqlDatabase(cs(_db))
 
            Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)
 
            If timeout > 0 Then
                dbCommand.CommandTimeout = timeout
            End If
 
            For Each cp As commandParameter In commandParameters
                Select Case cp.ParamDirection
                    Case ParameterDirection.Input
                        db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
                    Case ParameterDirection.InputOutput
                        'not handled
                    Case ParameterDirection.Output
                        db.AddOutParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamSize)
                    Case ParameterDirection.ReturnValue
                        'not handled
                End Select
            Next
            db.AddParameter(dbCommand, "@ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, String.Empty, System.Data.DataRowVersion.Default, 0)
 
            db.ExecuteNonQuery(dbCommand)
            For Each cp As commandParameter In commandParameters
                cp.ParamValue = db.GetParameterValue(dbCommand, cp.ParamName)
            Next
 
            Dim rcp As commandParameter
            rcp = New commandParameter("@Return_Value", 0, DbType.Int16)
            rcp.ParamValue = db.GetParameterValue(dbCommand, "@ReturnValue")
            commandParameters.Add(rcp)
 
            cps = commandParameters
            Return rcp.ParamValue
        Catch ex As Exception
            strError = "query: " & _storedProcName & "<BR>"
            For Each cp As commandParameter In commandParameters
                strError += "params: " & cp.ParamName & " = " & cp.ParamValue.ToString & "<br>"
            Next
 
            ex.Source = strError
            Throw ex
 
        End Try
 
    End Function
    Function execute() As Int32
        Dim intTimeout As Int32 = -1
        Dim cp As New System.Collections.Generic.List(Of commandParameter)
        Return execute(intTimeout, cp)
    End Function
    Function execute(ByRef cp As System.Collections.Generic.List(Of commandParameter)) As Int32
        Dim intTimeout As Int32 = -1
        Return execute(intTimeout, cp)
    End Function
 
    Function executeScalar() As Object
        Dim strError As String = ""
        Try
 
            Dim aParams As SqlParameter() = Nothing
            Dim a As Int16 = 0
            Dim db As Database '= DatabaseFactory.CreateDatabase(_db)
            db = New SqlDatabase(cs(_db))
            Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)
            For Each cp As commandParameter In commandParameters
                db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
            Next
 
            Return db.ExecuteScalar(dbCommand)
        Catch ex As Exception
            strError = "query: " & _storedProcName & "<BR>"
            For Each cp As commandParameter In commandParameters
                strError += "params: " & cp.ParamName & " = " & cp.ParamValue.ToString & "<br>"
            Next
            strError += ex.Message
            Throw New Exception(strError)
 
        End Try
 
        executeScalar = Nothing
    End Function
    Function getReader(ByVal timeout As Int32) As SqlDataReader
        Dim strError As String = ""
        Try
 
            Dim aParams As SqlParameter() = Nothing
            Dim a As Int16 = 0
            Dim db As Database '= DatabaseFactory.CreateDatabase(_db)
            db = New SqlDatabase(cs(_db))
            Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)
            If timeout > 0 Then
                dbCommand.CommandTimeout = timeout
            End If
            For Each cp As commandParameter In commandParameters
                db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
            Next
 
            Return db.ExecuteReader(dbCommand)
        Catch ex As Exception
            strError = "query: " & _storedProcName & "<BR>"
            For Each cp As commandParameter In commandParameters
                strError += "params: " & cp.ParamName & " = " & cp.ParamValue.ToString & "<br>"
            Next
 
            ex.Source = strError
            Throw ex
 
        End Try
        getReader = Nothing
 
    End Function
    Function getReader() As SqlDataReader
        getReader = getReader(-1)
 
    End Function
    Function getTable() As DataTable
        getTable = getTable(-1)
    End Function
 
    Function getTable(ByVal timeout As Int32) As DataTable
        Dim strError As String = ""
        Try
 
            Dim aParams As SqlParameter() = Nothing
            Dim a As Int16 = 0
 
            Dim db As Database
            db = New SqlDatabase(cs(_db))
 
            Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)
 
            'set the command timeout
            If timeout > 0 Then
                dbCommand.CommandTimeout = timeout
            End If
 
            For Each cp As commandParameter In commandParameters
                db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
            Next
 
            Return db.ExecuteDataSet(dbCommand).Tables(0)
        Catch ex As Exception
            strError = "query: " & _storedProcName & "<BR>"
            For Each cp As commandParameter In commandParameters
                If Not cp.ParamValue Is Nothing Then
                    strError += "params: " & cp.ParamName & " = " & cp.ParamValue.ToString & "<br>"
                Else
                    strError += "params: " & cp.ParamName & " = Nothing<br>"
                End If
            Next
 
            ex.Source = strError
            Throw ex
        End Try
        getTable = Nothing
 
    End Function
 
End Class

Trackback Print

Tags:

Categories:

Location: Blogs Parent Separator

DEVSHED Blogs Child Separator

VB.NET

2 comment(s) so far...

Re: StoredProcedure Class

Very Nice Class I want to try it out,
Do you by any chance have the commandParameters.vb part of this also to build the class for commandParameters? I could try and rewrite it but I am afraid I may miss something.

Also, do you have an example of what should be passed to it to get it working as an example? 2 examlples would be better, one with 1 parameter and the 2nd with another 2 or more parameters.

Finally, can this pass back an RS of any type to populate say a datagrid or should I leave that to do within the program still?

Let me know and thanks so much,
Mike

By Mike on 12/27/2010 10:19 AM

Re: StoredProcedure Class

NP. The other classes that you'll need are here
http://devshed.us/Indexes/DataAccess.aspx

By Steve Gray on 12/27/2010 10:31 AM

As always, I welcome your comments!