Data Access Layer to execute Stored Procedures in VB.NET

This article explains about executing a single stored procedure or batch of stored procedures from the application layer. As Data access layer completely decoupled from Application layer we just need to change the Application layer in case of any change in underlying database schema.

This article explains primarily about calling stored procedures which does not return any result set. This can be extended to handle those stored procedures also.

Data Access Layer.

This data access layer contains the following key elements.

• ParamData Structure
• StoredProcedure class
• StoredProcedureCollection class
• Execute class

ParamData structure will contain parameter name, parameter value and data type.

Structure ParamData
Public pName, pValue As String
Public pDataType As SqlDbType
Public Sub New(ByVal pName As String, ByVal pDataType As SqlDbType, ByVal pValue As String)
Me.pName=pName
Me.pDataType=pDataType
Me.pValue=pValue
End Sub
End Structure

Stored Procedure class which will have methods to SetParam and Getparam to set and get the parameter list. This will be added to array list which will contain ParamData structures.

Public Sub SetParam(ByVal pName As String, ByVal pDataType As SqlDbType, ByVal pValue As String)
Dim pData As ParamData = New ParamData(pName,pDataType,pValue)
sParams.Add(pData)
End Sub

Similarly to get the parameter list GetParams method was defined which will return array list of ParamData structures.

Public Function GetParams() As ArrayList
If Not(sParams Is Nothing) Then
Return sParams
Else
Return Nothing
End If
End Function

StoredProcedureCollection class contains the collection of stored procedure classes. This class has ADD and Remove methods to add the StroredProcedure class and remove when it is not needed.

Public Sub add(ByVal value As StoredProcedure)
List.Add(value)
End Sub
Public Sub Remove(ByVal index As Integer)
If index > Count - 1 OrElse index < 0 Then
Console.WriteLine("No data to remove")
Else
List.RemoveAt(index)
End If
End Sub

This will also have ITEM method to get the StoredProcedure from the List.

Public Function Item(ByVal Index As Integer) As StoredProcedure
Return CType(List(Index), StoredProcedure)
End Function

Execute class has a static method ExecuteSps to execute the stored procedures from the collection.

Public Shared Function ExecuteSps(ByVal spCollection As StoredProcedureCollection, ByVal Connection As SqlConnection) As Boolean
Try
For Each spData As StoredProcedure In spCollection
Dim cmd As SqlCommand = New SqlCommand
Dim i As Integer = 0
If Connection.State <> ConnectionState.Open Then
Connection.Open()
End If
cmd.Connection = Connection
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = spData.ProcName
Dim myEnumerator As IEnumerator = spData.GetParams().GetEnumerator()
Do While myEnumerator.MoveNext()
Dim pData As ParamData = CType(myEnumerator.Current, ParamData)
cmd.Parameters.Add(pData.pName, pData.pDataType)
cmd.Parameters(i).Value = pData.pValue
i = i + 1
Loop
cmd.ExecuteNonQuery()
Next spData
Return True
Catch exc As Exception
Return False
End Try
End Function

Please find the code snippet in <<DataAccessLayer.ZIP>> file.

Application Layer.

In the application layer we have to add the reference of DataAccessLayer.dll. After adding this reference we can call the data access layer functions as per our need. The advantage here is we can dynamically add/remove the parameters as and whenever there is change stored procedure definition.

Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
Dim connection As SqlConnection = New SqlConnection()
'change this connect string as per your environment
Dim connectString As String = "Persist Security Info=False;Integrated Security=SSPI;database=Pictures;server=.;Connect Timeout=60"
connection.ConnectionString=connectString
If connection.State<>ConnectionState.Open Then
connection.Open()
End If
Dim spCollection As DataAccessLayer.StoredProcedureCollection = New DataAccessLayer.StoredProcedureCollection()
Dim spData As DataAccessLayer.StoredProcedure = New DataAccessLayer.StoredProcedure()
spData.ProcName=txtSpName.Text
spData.SetParam(txtParam1.Text,SqlDbType.VarChar,txtParamValue1.Text)
spData.SetParam(txtParam2.Text,SqlDbType.VarChar,txtParamValue2.Text)
spCollection.add(spData)
If DataAccessLayer.Execute.ExecuteSps(spCollection,connection) Then
MessageBox.Show("Successfully executed")
Else
MessageBox.Show("Not executed Successfully")
End If
End Sub

Please find the code snippet for using data access layer from the
<<CallingAPP.zip>> file.

NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).