Imports System.Data.SqlClient
Imports System.Data.ConnectionState
Public Class SQLStoredProcedure
#Region "Properties"
Private _CnnStr As String 'connection string
Private _Cnn As New SqlConnection 'connection object
Private _TimeOut As Integer 'seconds before a timeout occurs
Public Property ConnectionString() As String
Get
Return _CnnStr
End Get
Set(ByVal Value As String)
_CnnStr = Value
End Set
End Property
Public ReadOnly Property ConnectionState() As String
Get
Return _Cnn.State.ToString
End Get
End Property
Public Property TimeOut() As Integer
Get
Return _TimeOut
End Get
Set(ByVal Value As Integer)
_TimeOut = Value
End Set
End Property
#End Region
#Region "Private Functions"
Private Sub OpenConnection()
If _Cnn.State = Closed Then _Cnn.Open() 'if the connect is closed, open it
End Sub
Private Sub CloseConnection(ByVal Close As Boolean)
If Not _Cnn.State = Closed And Close Then _Cnn.Close() 'if the connection is not closed and you want to close it, then do so
End Sub
Private Function CreateCommand(ByVal SP As String, ByVal Data As ArrayList) As SqlCommand
Dim CmdParameters As New SqlCommand
Dim NewCommand As New SqlCommand
Dim Reader As SqlDataReader
Dim X As Integer
With CmdParameters 'define SP to get parameters
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetParameters_s"
.Parameters.Add("@SPName", SqlDbType.NVarChar, 50).Value = SP
.CommandTimeout = _TimeOut
.Connection = _Cnn 'Connect
End With
With NewCommand 'define command object to return
.CommandType = CommandType.StoredProcedure
.CommandText = SP
.CommandTimeout = _TimeOut
End With
Reader = CmdParameters.ExecuteReader
While Reader.Read 'for each parameter
If Reader.Item("Direction") = 0 Then 'create and assign if it has a non-null value
If IsDBNull(Data.Item(X)) = False Then NewCommand.Parameters.Add(Reader.Item("Parameter"), ConvertString(Reader.Item("DataType")), Reader.Item("Length")).Value = Data.Item(X)
X = X + 1 'go to the next item in the array of data
Else 'create output parameter
NewCommand.Parameters.Add(Reader.Item("Parameter"), ConvertString(Reader.Item("DataType")), Reader.Item("Length")).Direction = ParameterDirection.Output
End If
End While
Reader.Close()
Return NewCommand
End Function
Private Function ConvertString(ByVal DataType As String) As SqlDbType
Select Case DataType 'convert sql data type to a object of type SqlDbType
Case "bigint"
ConvertString = SqlDbType.BigInt
Case "binary"
ConvertString = SqlDbType.Binary
Case "bit"
ConvertString = SqlDbType.Bit
Case "char"
ConvertString = SqlDbType.Char
Case "datetime"
ConvertString = SqlDbType.DateTime
Case "decimal"
ConvertString = SqlDbType.Decimal
Case "float"
ConvertString = SqlDbType.Float
Case "image"
ConvertString = SqlDbType.Image
Case "int"
ConvertString = SqlDbType.Int
Case "money"
ConvertString = SqlDbType.Money
Case "nchar"
ConvertString = SqlDbType.NChar
Case "ntext"
ConvertString = SqlDbType.NText
Case "numberic"
ConvertString = SqlDbType.Decimal
Case "nvarchar"
ConvertString = SqlDbType.NVarChar
Case "real"
ConvertString = SqlDbType.Real
Case "smalldatetime"
ConvertString = SqlDbType.SmallDateTime
Case "samllint"
ConvertString = SqlDbType.SmallInt
Case "smallmoney"
ConvertString = SqlDbType.SmallMoney
Case "sql_variant"
ConvertString = SqlDbType.Variant
Case "sysname"
ConvertString = SqlDbType.NVarChar
Case "text"
ConvertString = SqlDbType.Text
Case "timestamp"
ConvertString = SqlDbType.Timestamp
Case "tinyint"
ConvertString = SqlDbType.TinyInt
Case "uniqueidentifier"
ConvertString = SqlDbType.UniqueIdentifier
Case "varbinary"
ConvertString = SqlDbType.VarBinary
Case "varchar"
ConvertString = SqlDbType.VarChar
Case "variant"
ConvertString = SqlDbType.Variant
End Select
Return ConvertString
End Function
#End Region
#Region "Public Functions"
Public Sub New()
End Sub
Public Sub New(ByVal ConnectString As String, Optional ByVal TimeOut As Integer = 60)
_CnnStr = ConnectString
_Cnn.ConnectionString = _CnnStr
_TimeOut = TimeOut
End Sub
Public Sub Close()
CloseConnection(True) 'close connection
End Sub
Public Function GetData(ByVal StoredProcedure As String, ByVal Values As ArrayList, Optional ByVal Close As Boolean = True) As DataTable
Dim Cmd As SqlCommand
Dim Cnn As New SqlConnection(_CnnStr)
Dim NewTable As New DataTable
Dim Rdr As SqlDataReader
Dim Row As DataRow
Dim HeaderLoop, RowLoop As Integer 'count loops
OpenConnection()
Cmd = CreateCommand(StoredProcedure, Values) 'create command object
Cmd.Connection = _Cnn
NewTable.TableName = "tbl_" & StoredProcedure
Rdr = Cmd.ExecuteReader
For HeaderLoop = 0 To Rdr.FieldCount - 1 'create cloumns (name, data type)
NewTable.Columns.Add(Rdr.GetName(HeaderLoop), Type.GetType(Rdr.GetFieldType(HeaderLoop).ToString))
Next
While Rdr.Read 'populate table
Row = NewTable.NewRow 'create a new row to be added
For RowLoop = 0 To Rdr.FieldCount - 1
Row(RowLoop) = Rdr.Item(RowLoop) 'populate columns in the row
Next
NewTable.Rows.Add(Row) 'add the row
End While
Rdr.Close()
CloseConnection(Close)
Return NewTable
End Function
Public Function SetData(ByVal StoredProcedure As String, ByVal Values As ArrayList, Optional ByVal Close As Boolean = True) As ArrayList
Dim OutputValues As New ArrayList
Dim Cmd As SqlCommand
Dim P As SqlParameter
Dim X As Integer
OpenConnection()
Cmd = CreateCommand(StoredProcedure, Values) 'create command object
Cmd.Connection = _Cnn
Cmd.ExecuteNonQuery()
For Each P In Cmd.Parameters 'for each output parameter add output value to array
If P.Direction = ParameterDirection.Output Then
OutputValues.Add(P.Value)
End If
Next
CloseConnection(Close)
Return OutputValues
End Function
#End Region
End Class