Imports System.Data.SqlClient
Imports System.Net
Public Class UsageExample
Sub Main()
'This is an example of calling a stored procedure with one line of code.
'in order to make this work, copy the following three classes into your code, then call them as you see below.
'this will execute a stored procedure
QuickDataAccess.dd_MyStoredProcedure("123", "GPSQL", "TWO").execute()
'this will call a stored procedure and return a dataset
Dim oDT As DataTable = QuickDataAccess.dd_MyStoredProcedure("123", "GPSQL", "TWO").getTable
End Sub
End Class
Public Class QuickDataAccess
'===================================================================================
'you'll need one method here for each stored procedure that you want to call
'===================================================================================
Public Shared Function dd_MyStoredProcedure(ByVal myParameter As String, strSqlServer As String, strDatabase As String) As storedProcedure
Dim sp As New storedProcedure("dd_MyStoredProcedure", strSqlServer, strDatabase)
sp.commandParameters.Add(New commandParameter("@vchrLocationCode", IIf(myParameter Is Nothing, System.DBNull.Value, myParameter), DbType.String))
Return sp
End Function
End Class
Public Class commandParameter
'===================================================================================
'nothing exciting here. :)
'===================================================================================
Public ParamName As String
Public ParamValue As Object
Public ParamType As System.Data.DbType
Public ParamDirection As System.Data.ParameterDirection
Public ParamSize As Int32
Sub New(ByVal paramName As String, ByVal paramValue As Object, ByVal paramType As System.Data.DbType)
Me.ParamName = paramName
Me.ParamValue = paramValue
Me.ParamType = paramType
Me.ParamDirection = ParameterDirection.Input
Me.ParamSize = 0
End Sub
Sub New(ByVal paramName As String, ByVal paramValue As Object, ByVal paramType As System.Data.SqlDbType, ByVal paramDirection As System.Data.ParameterDirection, ByVal paramSize As Int32)
Me.ParamName = paramName
Me.ParamValue = paramValue
Me.ParamType = paramType
Me.ParamDirection = paramDirection
Me.ParamSize = paramSize
End Sub
End Class
Public Class storedProcedure
'===================================================================================
'this class makes it all work, the ADO access code is here.
'there are two methods, execute and gettable.
'examples of their usage are above
'===================================================================================
Public commandParameters As System.Collections.Generic.List(Of commandParameter)
Dim mstrStoredProcName As String
Dim mstrCS As String
Sub New(ByVal StoredProcName As String, ByVal strSQLServer As String, strDatabase As String)
mstrStoredProcName = StoredProcName
mstrCS = String.Format("Data Source={0};Initial Catalog={1};User Id=myUser;Password=myPass", strSQLServer, strDatabase)
commandParameters = New System.Collections.Generic.List(Of commandParameter)
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
End Sub
Function execute() As Int64
Dim strError As String = ""
Dim intRetVal As Int64 = 0
Dim oCmd As SqlCommand
Try
Using oConn As New SqlConnection(mstrCS)
' Create the command and set its properties.
oCmd = New SqlCommand()
oCmd.Connection = oConn
oCmd.CommandText = mstrStoredProcName
oCmd.CommandType = CommandType.StoredProcedure
' Add the input parameter and set its properties.
Dim parameter As SqlParameter
For Each cp As commandParameter In commandParameters
parameter = New SqlParameter
Select Case cp.ParamName
Case "@vchrError"
parameter.ParameterName = cp.ParamName
parameter.SqlDbType = SqlDbType.Char
parameter.Direction = ParameterDirection.Output
parameter.Size = 200
parameter.Value = cp.ParamValue
Case Else
parameter.ParameterName = cp.ParamName
parameter.DbType = cp.ParamType
parameter.Direction = cp.ParamDirection
parameter.Value = cp.ParamValue
If parameter.Direction = ParameterDirection.Output Then
parameter.Size = cp.ParamSize
End If
End Select
oCmd.Parameters.Add(parameter)
Next
parameter = New SqlParameter
parameter.ParameterName = "@ReturnValue"
parameter.SqlDbType = SqlDbType.Int
parameter.Direction = ParameterDirection.ReturnValue
oCmd.Parameters.Add(parameter)
' Open the connection and execute the reader.
ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3
oConn.Open()
oCmd.ExecuteNonQuery()
'these are the return parameters. If any are declared as OUTPUT, they'll be here.
'cps = oCmd.Parameters
'get the return parameter
intRetVal = oCmd.Parameters("@ReturnValue").Value
End Using
Catch ex As System.Data.SqlClient.SqlException
strError = ex.Message
Dim ei As New Exception(strError)
Throw New Exception(ex.Message, ei)
Catch ex As Exception
strError = ex.Message
Dim ei As New Exception(strError)
Throw New Exception(ex.Message, ei)
End Try
Return intRetVal
End Function
Function getTable() As DataTable
Dim strError As String = ""
Dim oDT As DataTable = Nothing
Dim strCS As String = mstrCS
Try
Using oConn As New SqlConnection(strCS)
' Create the command and set its properties.
Dim oCmd As SqlCommand = New SqlCommand()
oCmd.Connection = oConn
oCmd.CommandText = mstrStoredProcName
oCmd.CommandType = CommandType.StoredProcedure
' Add the input parameter and set its properties.
For Each cp As commandParameter In commandParameters
Dim parameter As New SqlParameter()
parameter.ParameterName = cp.ParamName
parameter.DbType = cp.ParamType
parameter.Direction = ParameterDirection.Input
parameter.Value = cp.ParamValue
oCmd.Parameters.Add(parameter)
Next
' Open the connection and execute the reader.
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
oConn.Open()
Dim oDA As New SqlDataAdapter()
oDA.SelectCommand = oCmd
Dim oDS As New DataSet
oDA.Fill(oDS)
oDT = oDS.Tables(0)
oDT.TableName = mstrStoredProcName
End Using
Catch ex As System.Data.SqlClient.SqlException
strError = ex.Message
Dim ei As New Exception(strError)
Throw New Exception(ex.Message, ei)
Catch ex As Exception
strError = ex.Message
Dim ei As New Exception(strError)
Throw New Exception(ex.Message, ei)
End Try
getTable = oDT
End Function
End Class