Introduction:
It is a known fact that, most of the time we create applications for Create, Select, Update, Delete. And every time, we end up with writing similar set of code for every execution of our stored procedures.
Our approach is constantly repeated like creating connection, creating command object, adding parameters etc and finally to execute.
I am just trying to write a wrapper class for the data application block.
Pre-assumptions:
We assume that, the database is MS SQL Server (widely used) we take advantage of Microsoft Data Application Block. I am attaching the dll file.
Why do I need it?
Mainly Re-Usability of code.
Once you come up with a generic class for your data access, you can use it anywhere. Once you are finished with it, you can achieve the task with very less amount of coding.
Limitations:
For now, the code is only for stored procedures. Extend it as you wish. Please overload the methods if you wish to have. Likewise, you can also have extended methods as we have in our SqlHelper class. (Data application block).
Code:
Imports System
Imports System.Data
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
''' <summary>
''' This is a generic DAC layer.
''' </summary>
Namespace Ellaar.DAC
Public Class DAC
' Execute the stored procedure
Public Shared Function Execute(ByVal StoredProcName As String) As DataSet
' Generic collection of my DacParameter object
Dim ParameterPairs As List(Of Parameters.DacParameter) = Parameters.MyParameters
Dim ds As DataSet
' Validation for Parameter collection
Try
' if you want to force the user to give parameters, use this code
If ParameterPairs.Count <= 0 Then
'throw new Exception("Invalid parameter supply. Check your BL Method");
End If
Catch Ex As Exception
Throw (Ex)
End Try
' Get connection String - I've created this for web app. Hence it is reading from web.config.
' You can modify this as you wish.
Dim rootWebConfig As System.Configuration.Configuration = System.Web.Configuration.WebConfigurationManager. OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath)
Dim ConnString As String = ""
If 0 < rootWebConfig.ConnectionStrings.ConnectionStrings.Count Then
ConnString = rootWebConfig.ConnectionStrings.ConnectionStrings("DfltConnection").ToString()
End If
' Using a Transaction Here: To ensure data integrity
Dim Trans As SqlTransaction
Using Conn As New SqlConnection(ConnString)
Conn.Open()
Trans = Conn.BeginTransaction()
Try
' If there is any parameters then iterate through the collection
' and bind it
If ParameterPairs.Count > 0 Then
Dim arparams As SqlParameter() = New SqlParameter(ParameterPairs.Count - 1) {}
Dim Count As Integer = 0
For Each pObject As Parameters.DacParameter In ParameterPairs
arparams(Count) = New SqlParameter(pObject.Name, pObject.Type)
arparams(Count).Value = pObject.Value
'Increment counter
Count += 1
Next
' Execute stored procedure
ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName, arparams)
Else
' else I just need to execute it Eg: Procedures that have only SELECT queries
ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName)
End If
' Yes, go ahead with Committing the transaction
Trans.Commit()
Catch Ex As Exception
Trans.Rollback()
Throw (Ex)
Finally
If Conn.State = ConnectionState.Open Then
Conn.Close()
Conn.Dispose()
End If
Parameters.MyParameters.Clear()
End Try
Return ds
End Using
End Function
' My Parameters class
Public Class Parameters
Protected Friend Shared MyParameters As New List(Of DacParameter)()
Public Shared Sub Add(ByVal Name As String, ByVal dbtype As SqlDbType, ByVal value As Object)
Dim KeyValuePair As New DacParameter()
KeyValuePair.Name = Name
KeyValuePair.Type = dbtype
KeyValuePair.Value = value
MyParameters.Add(KeyValuePair)
End Sub
' class param, type , value class : Aggregation
Public Class DacParameter
Private _ParameterName As String
Public Property Name() As String
Get
Return _ParameterName
End Get
Set(ByVal value As String)
_ParameterName = value
End Set
End Property
Private _Type As System.Data.SqlDbType
Public Property Type() As System.Data.SqlDbType
Get
Return _Type
End Get
Set(ByVal value As System.Data.SqlDbType)
_Type = value
End Set
End Property
Private _value As Object
Public Property Value() As Object
Get
Return _value
End Get
Set(ByVal value As Object)
_value = value
End Set
End Property
End Class
End Class
End Class
End Namespace
How do I use it? (Some examples):
- To create a State
DAC.Parameters.Add("@StateName", SqlDbType.VarChar, StateName)
DAC.Parameters.Add("@StateAbbr", SqlDbType.VarChar, StateAbbr)
DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath)
DAC.Execute("spInsertState")
- To create a City
DAC.Parameters.Add("@CityName", SqlDbType.VarChar, CityName)
DAC.Parameters.Add("@MetroId", SqlDbType.Int, MetroId)
DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath)
DAC.Execute("spInsertCity")
- SPs without any parameters
DataSet ds = DAC.Execute("spGetStates")
Conclusion:
I hope this could of helpful for you. Please try this and extend the class as you wish. Please do post your suggestions and feedback.
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# Corner (http://www.c-sharpcorner.com/).