This Class, implemented in VB .Net, is used to connect and execute queries on Microsoft SQL Server database. It uses Data.SqlClient library to perform database operations.
1. Public Shared Function OpenConnection() As Boolean
This function is used to open the database connection based on the given connection string. cnnString is a data member of this class which will contain the connection string (Parameters required for connecting to a database).
2. Public Shared Function CloseConnection() As Boolean
This function closes the database connection and returns the status.
3. Public Shared Function ExecuteQuery(ByVal strSQL As String) As SqlDataReader
ExecuteQuery function is used to execute a SELECT query and returns the output as a SqlDataReader object.
4. Public Shared Function ExecuteUpdate(ByVal strSQL As String) As Boolean
ExecuteUpdate is used execute queries other than SELECT and returns the status as boolean
5. Public Shared Function ExecuteBatchUpdate(ByVal colSQL As Collection) As Boolean
ExecuteBatchUpdate is used to execute a set of NON-SELECT queries as a batch. The queries will be executed as batch and ensures all queries in the batch are executed by using SqlTransaction class.
6. Public Shared Function BackupDatabase(ByVal strDatabse As String, ByVal strFileName As String, ByVal strBackupName As String) As Boolean
BackupDatabase is used to create a backup file of the database. It takes name of database, backup filename, and name of backup as arguments and creates the backup file.
7. Public Shared Function RestoreDatabase(ByVal strDatabse As String, ByVal strBackupFileName As String, ByVal strRestorePath As String) As Boolean
BackupDatabase is used to restore the database from a backup file. It takes name of database, backup filename, and restore path as arguments and restores the database.
How to use this class?
Following is an ideal usage of this class. Here ConnectionManager class is used to connect to an MS-SQL Server database and to insert a record to a table.
ConnectionManager.cnnString=”Data Source=MY_DB_SERVER;Initial Catalog=dbMyDB;User ID=my_username;Password=my_password;”
If ConnectionManager.OpenConnection()=True then
If ConnectionManager.ExecuteUpdate (“INSERT INTO tblMyTable VALUES(100,’csnotes’”,’csnotes@gmail.com’)=True then
MsgBox “New record has been Inserted”
Else
MsgBox “Faioled to insert record”
End If
ConnectionManager. CloseConnection()
End if
Here MY_DB_SERVER is the name of the database server.
dbMyDB is the name of the database.
my_username is the name of the user to connect to the database server.
my_password is the password of the database user.
Source Code of the class
'Imports the SqlClient library to connect to database
Imports System.Data.SqlClient
Public Class ConnectionManager
Public Shared cnnString As String
Public Shared cnnSQLClient As SqlConnection
'Function to open database connection
Public Shared Function OpenConnection() As Boolean
Dim blnOpenStatus As Boolean
Try
cnnSQLClient = New SqlConnection
cnnSQLClient.ConnectionString = cnnString
cnnSQLClient.Open()
blnOpenStatus = True
Catch ex As Exception
cnnSQLClient = Nothing
blnOpenStatus = False
End Try
Return blnOpenStatus
End Function
'Function to close database connection
Public Shared Function CloseConnection() As Boolean
Dim blnCloseStatus As Boolean
Try
cnnSQLClient.Close()
cnnSQLClient = Nothing
blnCloseStatus = True
Catch ex As Exception
cnnSQLClient = Nothing
blnCloseStatus = False
End Try
Return blnCloseStatus
End Function
'Function to execute SELECT queries
Public Shared Function ExecuteQuery(ByVal strSQL As String) As SqlDataReader
Dim cmdCommand As SqlCommand
Dim drReader As SqlDataReader
Try
cmdCommand = New SqlCommand() cmdCommand.Connection = cnnSQLClient
cmdCommand.CommandText = strSQL
drReader = cmdCommand.ExecuteReader()
Catch ex As Exception
drReader = Nothing
End Try
Return drReader
End Function
'Function to execute NON SELECT queries
Public Shared Function ExecuteUpdate(ByVal strSQL As String) As Boolean
Dim cmdCommand As SqlCommand
Dim blnStatus As Boolean = False
Try
cmdCommand = New SqlCommand()
cmdCommand.Connection = cnnSQLClient
cmdCommand.CommandText = strSQL
cmdCommand.ExecuteNonQuery()
blnStatus = True
Catch ex As Exception
blnStatus = False
End Try
Return blnStatus
End Function
'Function to execute a batch of queries
Public Shared Function ExecuteBatchUpdate(ByVal colSQL As Collection) As Boolean
Dim cmdCommand As SqlCommand
Dim blnStatus As Boolean
Dim trTransaction As SqlTransaction = Nothing
Dim strSQL As String
Try
cmdCommand = New SqlCommand()
cmdCommand.Connection = cnnSQLClient
trTransaction = cnnSQLClient.BeginTransaction()
cmdCommand.Transaction = trTransaction
For Each strSQL In colSQL
cmdCommand.CommandText = strSQL
cmdCommand.ExecuteNonQuery()
Next
trTransaction.Commit()
blnStatus = True
Catch ex As Exception
trTransaction.Rollback()
blnStatus = False
End Try
trTransaction.Dispose()
trTransaction = Nothing
Return blnStatus
End Function
'Function to backup the database
Public Shared Function BackupDatabase(ByVal strDatabse As String, ByVal strFileName As String, ByVal strBackupName As String) As Boolean
Dim cmdCommand As SqlCommand
Dim strSQL As String
Dim blnRetVal As Boolean
Try
cmdCommand = New SqlCommand
strSQL = "BACKUP DATABASE [" & strDatabse & "] TO " _
& " DISK=N'" & strFileName & "' " _
& " WITH NOFORMAT, INIT, NAME=N'" & strBackupName & "', " _
& " SKIP, NOREWIND, NOUNLOAD, STATS = 10"
cmdCommand.CommandText = strSQL
cmdCommand.Connection = ConnectionManager.cnnSQLClient
cmdCommand.ExecuteNonQuery()
blnRetVal = True
Catch ex As Exception
blnRetVal = False
End Try
Return blnRetVal
End Function
'Function to restore the database from backup file
Public Shared Function RestoreDatabase(ByVal strDatabse As String, ByVal strBackupFileName As String, ByVal strRestorePath As String) As Boolean
Dim cmdCommand As SqlCommand
Dim strSQL As String
Dim blnRetVal As Boolean
Try
cmdCommand = New SqlCommand
strSQL = "RESTORE DATABASE [" & strDatabse & "]" _
& " FROM DISK = N'" & strBackupFileName & "' " _
& " WITH FILE = 1, " _
& " MOVE N'" & strDatabse & "_Data' TO N'" & strRestorePath & "\" & strDatabse & "_Data.mdf', " _
& " MOVE N'" & strDatabse & "_Log' TO N'" & strRestorePath & "\" & strDatabse & "_Log.ldf', " _
& " NOUNLOAD, STATS = 10"
cmdCommand.CommandText = strSQL
cmdCommand.Connection = ConnectionManager.cnnSQLClient
cmdCommand.ExecuteNonQuery()
blnRetVal = True
Catch ex As Exception
blnRetVal = False
End Try
Return blnRetVal
End Function
End Class
This class can be used to connect to databases other than MS-SQL Server by changing the library from SqlClient to Odbc or OleDb. If you are changing the library, connection string also has to be changed accordingly. Some of the functions like BackupDatabase, RestoreDatabase, etc. also may require some modifications, based on the specifications of the database server.
No comments:
Post a Comment