This code can be used for SQL data inserts or to retrieve information from database.
I hope this will make a lot of people's life easy
This class dbhelper will provide full code to open or close the databases, execute any sql commands retrieve dataset or data rows.
It also covers all the transaction based or transaction less connection including ODBC, OLEDB or SQL connection types.
This code can be the base for VB.net class for the extensions further and please note this class doesn't have error handling as desired and it is expected that user can add error handling as per their errorhandler logic.
Full code is below:
Imports System.Data.Odbc
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Info
Public Class DBHelper
Dim oConn As OdbcConnection
Dim sConn As SqlConnection
Dim olConn As OleDbConnection
Dim oCmd As OdbcCommand
Dim sCmd As SqlCommand
Dim olCmd As OleDbCommand
Dim isTran As Boolean
Private oTrans As OdbcTransaction
Private sTrans As SqlTransaction
Private olTrans As OleDbTransaction
Dim connStr As String
Dim strDBConfFile = "C:\Temp\DBConfig.ini"
Dim ISODBC As Boolean
Dim ISOLEDB As Boolean
Dim ISSQL As Boolean
'Open connection to database
Public Function Connect() As Boolean
If ISODBC Then
If oConn Is Nothing Then
oConn = New OdbcConnection(connStr)
End If
If oConn.State = ConnectionState.Closed Then
oConn.Open()
End If
ElseIf ISOLEDB Then
If olConn Is Nothing Then
olConn = New OleDbConnection(connStr)
End If
If olConn.State = ConnectionState.Closed Then
olConn.Open()
End If
Else
If sConn Is Nothing Then
sConn = New SqlConnection(connStr)
End If
If sConn.State = ConnectionState.Closed Then
sConn.Open()
End If
End If
End Function
Public Sub BeginTransactionWithLock()
'Begin transaction and keep lock on the tables
If isTran Then Return
If ISODBC Then
If oConn.State = ConnectionState.Closed Then
oConn.Open()
End If
oTrans = oConn.BeginTransaction(IsolationLevel.Serializable)
isTran = True
ElseIf ISOLEDB Then
If olConn.State = ConnectionState.Closed Then
olConn.Open()
End If
olTrans = olConn.BeginTransaction(IsolationLevel.Serializable)
isTran = True
Else
If sConn.State = ConnectionState.Closed Then
sConn.Open()
End If
sTrans = sConn.BeginTransaction(IsolationLevel.Serializable)
isTran = True
End If
End Sub
'Begin transaction
Public Sub BeginTransaction()
If isTran Then
Return
If ISODBC Then
If oConn.State = ConnectionState.Closed Then
oConn.Open()
End If
oTrans = oConn.BeginTransaction()
isTran = True
ElseIf ISOLEDB = True Then
If olConn.State = ConnectionState.Closed Then
olConn.Open()
End If
olTrans = olConn.BeginTransaction()
isTran = True
Else
If sConn.State = ConnectionState.Closed Then
sConn.Open()
End If
sTrans = sConn.BeginTransaction()
isTran = True
End If
End Sub
'Commit transaction if successful
Public Sub CommitTransaction()
If Not isTran Then Return
If ISODBC Then
oTrans.Commit()
oConn.Close()
oTrans = Nothing
isTran = False
ElseIf ISOLEDB = True Then
olTrans.Commit()
olConn.Close()
olTrans = Nothing
isTran = False
Else
sTrans.Commit()
sConn.Close()
sTrans = Nothing
isTran = False
End If
End Sub
'Rollback transaction if not successful
Public Sub RollBackTransaction()
If Not isTran Then Return
If ISODBC Then
oTrans.Rollback()
oConn.Close()
oTrans = Nothing
isTran = False
ElseIf ISOLEDB Then
olTrans.Rollback()
olConn.Close()
olTrans = Nothing
isTran = False
Else
sTrans.Rollback()
sConn.Close()
sTrans = Nothing
isTran = False
End If
End Sub
'Close the database connection
Public Sub CloseConn()
Try
If ISODBC Then
If Not oConn Is Nothing Then
If Not oConn.State = ConnectionState.Closed Then
oConn.Close()
End If
End If
ElseIf ISOLEDB Then
If Not olConn Is Nothing Then
If Not olConn.State = ConnectionState.Closed Then
olConn.Close()
End If
End If
Else
If Not sConn Is Nothing Then
If Not sConn.State = ConnectionState.Closed Then
sConn.Close()
End If
End If
End If
Catch ex As Exception
Throw ex
End Try
End Sub
'just executes the command either insert, update or delete and returns true or false
Public Function ExecuteQuery(ByVal strCmdTxt As String) As Boolean
Dim intRows As Integer
If ISODBC Then
If oConn.State = ConnectionState.Closed Then
Connect()
End If
oCmd = New OdbcCommand
oCmd.Connection = oConn
oCmd.CommandText = strCmdTxt
oCmd.CommandType = CommandType.Text
If Not isTran Then
intRows = oCmd.ExecuteNonQuery()
oConn.Close()
Else
oCmd.Transaction = oTrans
intRows = oCmd.ExecuteNonQuery()
End If
If intRows > 0 Then
ExecuteQuery = True
Exit Function
Else
ExecuteQuery = False
Exit Function
End If
ElseIf ISOLEDB Then
If olConn.State = ConnectionState.Closed Then
Connect()
End If
olCmd = New OleDbCommand
olCmd.Connection = olConn
olCmd.CommandText = strCmdTxt
olCmd.CommandType = CommandType.Text
If Not isTran Then
intRows = olCmd.ExecuteNonQuery
olConn.Close()
Else
olCmd.Transaction = olTrans
intRows = olCmd.ExecuteNonQuery
End If
If intRows > 0 Then
ExecuteQuery = True
Exit Function
Else
ExecuteQuery = False
Exit Function
End If
Else
If sConn.State = ConnectionState.Closed Then
Connect()
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
sCmd.CommandType = CommandType.Text
If Not isTran Then
intRows = sCmd.ExecuteNonQuery()
sConn.Close()
Else
sCmd.Transaction = sTrans
intRows = sCmd.ExecuteNonQuery()
End If
If intRows > 0 Then
ExecuteQuery = True
Exit Function
Else
ExecuteQuery = False
Exit Function
End If
End If
End Function
'returns Primary key after executing an insert command
Public Function ExecuteAndGetID(ByVal strCmdTxt As String, Optional ByVal blnNonID As Boolean = False) As String
If ISODBC Then
If oConn.State = ConnectionState.Closed Then
Connect()
End If
If Not blnNonID Then
strCmdTxt = strCmdTxt & " ; select scope_Identity();"
End If
oCmd = New OdbcCommand
oCmd.Connection = oConn
oCmd.CommandText = strCmdTxt
oCmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetID = CStr(oCmd.ExecuteScalar())
oConn.Close()
Else
oCmd.Transaction = oTrans
ExecuteAndGetID = CStr(oCmd.ExecuteScalar())
End If
ElseIf ISOLEDB Then
If olConn.State = ConnectionState.Closed Then
Connect()
End If
If Not blnNonID Then
strCmdTxt = strCmdTxt & " ; select scope_Identity();"
End If
olCmd = New OleDbCommand
olCmd.Connection = olConn
olCmd.CommandText = strCmdTxt
olCmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetID = CStr(olCmd.ExecuteScalar())
olConn.Close()
Else
olCmd.Transaction = olTrans
ExecuteAndGetID = CStr(olCmd.ExecuteScalar())
End If
Else
If sConn.State = ConnectionState.Closed Then
Connect()
End If
If Not blnNonID Then
strCmdTxt = strCmdTxt & " ; select scope_Identity();"
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
sCmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetID = CStr(sCmd.ExecuteScalar())
sConn.Close()
Else
sCmd.Transaction = sTrans
ExecuteAndGetID = CStr(sCmd.ExecuteScalar())
End If
End If
End Function
'returns Odbcdatareader after executing a odbc command
Public Overloads Function ExecuteAndGetRowODBC(ByVal strCmdTxt As String) As OdbcDataReader
If oConn.State = ConnectionState.Closed Then
Connect()
End If
oCmd = New OdbcCommand
oCmd.Connection = oConn
oCmd.CommandText = strCmdTxt
oCmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetRowODBC = oCmd.ExecuteReader
'conn.Close()
Else
oCmd.Transaction = oTrans
ExecuteAndGetRowODBC = oCmd.ExecuteReader
End If
End Function
'returns Odbcdatareader after executing a odbc command and odbc parameters
Public Overloads Function ExecuteAndGetRowODBC(ByVal strCmdTxt As String, ByVal parameters() As OdbcParameter) As OdbcDataReader
If oConn.State = ConnectionState.Closed Then
Connect()
End If
oCmd = New OdbcCommand
If Not parameters Is Nothing Then
For Each p As OdbcParameter In parameters
oCmd.Parameters.Add(p)
Next
End If
oCmd.CommandText = strCmdTxt
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Connection = oConn
If Not isTran Then
ExecuteAndGetRowODBC = oCmd.ExecuteReader()
'conn.Close()
Else
oCmd.Transaction = oTrans
ExecuteAndGetRowODBC = oCmd.ExecuteReader
End If
End Function
'returns Oledbdatareader after executing a oledb command and oledb parameters
Public Overloads Function ExecuteAndGetRowOLEDB(ByVal strCmdTxt As String) As OleDbDataReader
If olConn.State = ConnectionState.Closed Then
Connect()
End If
olCmd = New OleDbCommand
olCmd.CommandText = strCmdTxt
olCmd.CommandType = CommandType.Text
olCmd.Connection = olConn
If Not isTran Then
ExecuteAndGetRowOLEDB = olCmd.ExecuteReader()
'conn.Close()
Else
olCmd.Transaction = olTrans
ExecuteAndGetRowOLEDB = olCmd.ExecuteReader
End If
End Function
'returns Oledbdatareader after executing a oledb command and oledb parameters
Public Overloads Function ExecuteAndGetRowOLEDB(ByVal strCmdTxt As String, ByVal parameters() As OleDbParameter) As OleDbDataReader
If olConn.State = ConnectionState.Closed Then
Connect()
End If
olCmd = New OleDbCommand
If Not parameters Is Nothing Then
For Each p As OleDbParameter In parameters
olCmd.Parameters.Add(p)
Next
End If
olCmd.CommandText = strCmdTxt
olCmd.CommandType = CommandType.StoredProcedure
olCmd.Connection = olConn
If Not isTran Then
ExecuteAndGetRowOLEDB = olCmd.ExecuteReader()
'conn.Close()
Else
olCmd.Transaction = olTrans
ExecuteAndGetRowOLEDB = olCmd.ExecuteReader
End If
End Function
'returns sqldatareader after executing a sql command and sql parameters
Public Overloads Function ExecuteAndGetRowSQL(ByVal strCmdTxt As String, ByVal parameters() As SqlParameter) As SqlDataReader
If sConn.State = ConnectionState.Closed Then
Connect()
End If
If Not parameters Is Nothing Then
For Each p As SqlParameter In parameters
olCmd.Parameters.Add(p)
Next
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
sCmd.CommandType = CommandType.StoredProcedure
If Not isTran Then
ExecuteAndGetRowSQL = sCmd.ExecuteReader
'conn.Close()
Else
sCmd.Transaction = sTrans
ExecuteAndGetRowSQL = sCmd.ExecuteReader
End If
End Function
'This function will execute a command and returns SQL Data reader
Public Overloads Function ExecuteAndGetRowSQL(ByVal strCmdTxt As String) As SqlDataReader
If sConn.State = ConnectionState.Closed Then
Connect()
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
sCmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetRowSQL = sCmd.ExecuteReader
'conn.Close()
Else
sCmd.Transaction = sTrans
ExecuteAndGetRowSQL = sCmd.ExecuteReader
End If
End Function
' This overloaded getDataset function will return a dataset provided if sql parameters are provided it will execute the stored procedure else it will execute the direct sql command provided in strCmdTxt
Public Overloads Function getDataset(ByVal strCmdTxt As String) As DataSet
Dim ds As DataSet = New DataSet
If ISODBC Then
Dim da As OdbcDataAdapter = New OdbcDataAdapter
If oConn.State = ConnectionState.Closed Then
Connect()
End If
oCmd = New OdbcCommand
oCmd.Connection = oConn
oCmd.CommandText = strCmdTxt
oCmd.CommandType = CommandType.Text
If isTran Then
oCmd.Transaction = oTrans
End If
da.SelectCommand = oCmd
da.Fill(ds)
If Not isTran Then
oConn.Close()
End If
ElseIf ISOLEDB Then
Dim da As OleDbDataAdapter = New OleDbDataAdapter
If olConn.State = ConnectionState.Closed Then
Connect()
End If
olCmd = New OleDbCommand
olCmd.Connection = olConn
olCmd.CommandText = strCmdTxt
olCmd.CommandType = CommandType.Text
If isTran Then
olCmd.Transaction = olTrans
End If
da.SelectCommand = olCmd
da.Fill(ds)
If Not isTran Then
olConn.Close()
End If
Else
Dim da As SqlDataAdapter = New SqlDataAdapter
If sConn.State = ConnectionState.Closed Then
Connect()
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
sCmd.CommandType = CommandType.Text
If isTran Then
sCmd.Transaction = sTrans
End If
da.SelectCommand = sCmd
da.Fill(ds)
If Not isTran Then
sConn.Close()
End If
End If
Return ds
End Function
' This overloaded getDataset function will return a dataset provided if sql parameters are provided it will execute the stored procedure else it will execute the direct sql command provided in strCmdTxt but it uses OLEDB connection'
Public Overloads Function getDataset(ByVal strCmdTxt As String, ByVal oleParameters() As OleDbParameter) As DataSet
Dim ds As DataSet = New DataSet
Dim da As OleDbDataAdapter = New OleDbDataAdapter
If olConn.State = ConnectionState.Closed Then
Connect()
End If
olCmd = New OleDbCommand
olCmd.Connection = olConn
olCmd.CommandText = strCmdTxt
If Not oleParameters Is Nothing Then
olCmd.CommandType = CommandType.StoredProcedure
For Each p As OleDbParameter In oleParameters
olCmd.Parameters.Add(p)
Next
Else
olCmd.CommandType = CommandType.Text
End If
If isTran Then
olCmd.Transaction = olTrans
End If
da.SelectCommand = olCmd
da.Fill(ds)
If Not isTran Then
olConn.Close()
End If
Return ds
End Function
' This overloaded getDataset function will return a dataset provided if sql parameters are provided it will execute the stored procedure else it will execute the direct sql command provided in strCmdTxt but this will use ODBC connection
Public Overloads Function getDataset(ByVal strCmdTxt As String, ByVal odbcParameters() As OdbcParameter) As DataSet
Dim ds As DataSet = New DataSet
Dim da As OdbcDataAdapter = New OdbcDataAdapter
If oConn.State = ConnectionState.Closed Then
Connect()
End If
oCmd = New OdbcCommand
oCmd.Connection = oConn
oCmd.CommandText = strCmdTxt
If Not odbcParameters Is Nothing Then
oCmd.CommandType = CommandType.StoredProcedure
For Each p As OdbcParameter In odbcParameters
oCmd.Parameters.Add(p)
Next
Else
oCmd.CommandType = CommandType.Text
End If
If isTran Then
oCmd.Transaction = oTrans
End If
da.SelectCommand = oCmd
da.Fill(ds)
If Not isTran Then
oConn.Close()
End If
Return ds
End Function
' This overloaded getDataset function will return a dataset provided if sql parameters are provided it will execute the stored procedure else it will execute the direct sql command provided in strCmdTxt
Public Overloads Function getDataset(ByVal strCmdTxt As String, ByVal sqlParameters() As SqlParameter) As DataSet
Dim ds As DataSet = New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter
If sConn.State = ConnectionState.Closed Then
Connect()
End If
sCmd = New SqlCommand
sCmd.Connection = sConn
sCmd.CommandText = strCmdTxt
If Not sqlParameters Is Nothing Then
sCmd.CommandType = CommandType.StoredProcedure
For Each p As SqlParameter In sqlParameters
sCmd.Parameters.Add(p)
Next
Else
sCmd.CommandType = CommandType.Text
End If
If isTran Then
sCmd.Transaction = sTrans
End If
da.SelectCommand = sCmd
da.Fill(ds)
If Not isTran Then
sConn.Close()
End If
Return ds
End Function
' Initializing the class by passing the Site/AppID that will determine which connection string to use.
Public Sub New(ByVal strSiteID As String)
Dim ss As siteSettings
Dim strTest = strSiteID.Substring(strSiteID.Length - 1)
ss = New siteSettings
ss.ConnString1 = System.Configuration.ConfigurationManager.AppSettings("ConnString1")
ss.ConnString2 = System.Configuration.ConfigurationManager.AppSettings("ConnString2")
ss.ConnString3 = System.Configuration.ConfigurationManager.AppSettings("ConnString3")
If Val(ss.ISOLEDB) = 1 Then
ISOLEDB = True
End If
If Val(ss.ISODBC) = 1 Then
ISODBC = True
End If
If Val(ss.ISSQL) = 1 Then
ISSQL = True
End If
If strTest = "1" Then
connStr = ss.ConnString1
ElseIf strTest = "2" Then
connStr = ss.ConnString2
ElseIf strTest = "3" Then
connStr = ss.ConnString3
End If
If Not Connect() Then
Exit Sub
End If
End Sub
Public Sub New()
ReadDatabseConfig(strDBConfFile)
If Not Connect() Then
Exit Sub
End If
End Sub
' If you want to put the config in .ini file then you can use this function to retrieve the information from the *.ini file
Private Function ReadDatabseConfig(ByVal filename As String) As Boolean
Dim result As Boolean
Dim strDataSource As String = ""
Dim strInitialCatalog As String = ""
Dim strUserID As String = ""
Dim strPassword As String = ""
Dim strAppName As String = ""
Dim strODBC As String = ""
Dim strUID As String = ""
Dim strPwd As String = ""
Dim strDSN As String = ""
Dim sr As New IO.StreamReader(filename)
Dim ln As String = sr.ReadLine
While IsNothing(ln) = False
If ln.StartsWith("ISODBC") = True Then
strODBC = getConfigValue(ln)
If strODBC = "1" Then
ISODBC = True
ElseIf strODBC = "2" Then
ISOLEDB = True
Else
ISSQL = True
End If
ElseIf ln.StartsWith("DSN") = True Then
strDSN = getConfigValue(ln)
ElseIf ln.StartsWith("UID") = True Then
strUID = getConfigValue(ln)
ElseIf ln.StartsWith("PWD") = True Then
strPwd = getConfigValue(ln)
ElseIf ln.StartsWith("DataSource") = True Then
strDataSource = getConfigValue(ln)
ElseIf ln.StartsWith("DBaseName") = True Then
strInitialCatalog = getConfigValue(ln)
ElseIf ln.StartsWith("UserID") Then
strUserID = getConfigValue(ln)
ElseIf ln.StartsWith("Password") = True Then
strPassword = getConfigValue(ln)
ElseIf ln.StartsWith("AppName") Then
strAppName = getConfigValue(ln)
End If
ln = sr.ReadLine
End While
sr.Close()
If strAppName = "" Then
strAppName = "PraTestApp"
End If
If ISODBC Then
connStr = "connection string here"
End If
result = True
ReadDatabseConfig = result
Return result
End Function
Private Function getConfigValue(ByVal line As String) As String
Dim values() As String = Split(line, "=")
Dim reply As String = values(1).Trim
Return reply
End Function
Protected Overrides Sub Finalize()
CloseConn()
MyBase.Finalize()
End Sub
End Class
Read more!
Posts
