I use a couple of very simply functions that I build into a common class for a lot of my projects. I’ve always tended to build onto my base classes functions that help speed up development without adding too much weight to any project.
There may be much better ways of doing this and I would love for anyone reading to feel free and comment additional methods/information or drawbacks to the information displayed here.
To begin, go ahead and create a class file (for your common functions) if you don’t already have one.
Enumeration(s)
I am a big fan of enumerations, simply because of their strongly typed usage. I’ve added the following enumeration to help with the ADO Helper functions.
Enum SQLExecuteMode
save rollback
End Enum
The ADO.NET Wrapper Object
This wrapper object takes the place of declaring the SqlCommand Object, SQL string, SQLConnection, and parameters.
Public Class objSQLDB
Private _SQL As String
Private _OverrideConnectionString As String
Private _cmd As New SqlCommand
Public Property SQL() As String
Get
Return _SQL
End Get
Set(ByVal value As String)
_SQL = value
End Set
End Property
Public Property OverrideConnectionString() As String
Get
Return _OverrideConnectionString
End Get
Set(ByVal value As String)
_OverrideConnectionString = value
End Set
End Property
Public Property SQLCommand() As SqlCommand
Get
Return _cmd
End Get
Set(ByVal value As SqlCommand)
_cmd = value
End Set
End Property
Public Function RetrieveDataTable() As DataTable
'NOTE: Get proper ConnectionString
Dim strConnectionString As String = My.Settings.CHMS_DB
If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString
'NOTE: Create connection
Dim MyConnection As New SqlConnection(strConnectionString)
Dim cmd As SqlCommand = _cmd
cmd.Connection = MyConnection
cmd.CommandText = _SQL
'NOTE: Fill DataTable with sql results
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)
'NOTE: Clear all objects not being returned from memory
MyConnection = Nothing
cmd = Nothing
da = Nothing
Return dt
End Function
Public Function ExecuteNonQuery(Optional ByVal executeMode As SQLExecuteMode = SQLExecuteMode.save) As Boolean
'NOTE: Get proper ConnectionString
Dim strConnectionString As String = My.Settings.CHMS_DB
If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString
'NOTE: Create connection
Dim MyConnection As New SqlConnection(strConnectionString)
Dim cmd As SqlCommand = _cmd
MyConnection.Open() 'Connection must be open prior to creation of transaction
Dim myTransaction As SqlTransaction = MyConnection.BeginTransaction()
cmd.Transaction = myTransaction
cmd.Connection = MyConnection
cmd.CommandText = _SQL
'NOTE: Execute SQL
Try
cmd.ExecuteNonQuery()
Select Case executeMode
Case SQLExecuteMode.save
myTransaction.Commit()
Case SQLExecuteMode.rollback
myTransaction.Rollback()
End Select
Catch ex As Exception
myTransaction.Rollback()
Throw New Exception("Error while attempting to write to database")
Return False
Finally
MyConnection.Close()
MyConnection = Nothing
cmd = Nothing
End Try
Return True
End Function
Public Function ExecuteScalar() As Object
'NOTE: Get proper ConnectionString
Dim strConnectionString As String = My.Settings.CHMS_DB
If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString
'NOTE: Create connection
Dim MyConnection As New SqlConnection(strConnectionString)
Dim cmd As SqlCommand = _cmd
cmd.Connection = MyConnection
cmd.CommandText = _SQL
'NOTE: Retrieve Single Value
Dim objSQLReturn As Object
Try
MyConnection.Open()
objSQLReturn = cmd.ExecuteScalar()
MyConnection.Close()
Catch ex As Exception
Throw New Exception("Error while attempting to retrieve value from database")
Return False
End Try
'NOTE: Clear all objects not being returned from memory
MyConnection = Nothing
cmd = Nothing
Return objSQLReturn
End Function
End Class
How I Use It
I use this any time I am interacting with the database for such actions as retrieving datatables, executing stored procedures, insert/updating/deleting SQL data, whatever.
Retrieving a DataTable from a Stored Procedure
Here is a brief example of how I could retrieve a Datatable from a SPROC I’ve already created:
Public Function getEmployeeReport(ByVal lngCompanyID as Integer, _
ByVal dtmFilterBeginDate As DateTime, _
ByVal dtmFilterEndDate As DateTime, _
ByVal lngUserID As Integer) As DataTable
objSQLDB.SQL = "dbo.report_amt_employee"
objSQLDB.SQLCommand.CommandType = CommandType.StoredProcedure
objSQLDB.SQLCommand.Parameters.AddWithValue("@lngCompanyID", lngCompanyID)
objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterBeginDate", dtmFilterBeginDate)
objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterEndDate", dtmFilterEndDate)
objSQLDB.SQLCommand.Parameters.AddWithValue("@lngUserID", lngUserID)
Return objSQLDB.RetrieveDataTable
End Function
Nicholas Barger's Blog 