DBConnection - Used to connect to an SQL Server, allows you to open recordsets and transactions as well as executing SQLs with a single instruction.
Imports Microsoft.SqlServer
Public Class DBConnection
Private mstrDatabaseName As String
Private mstrServer As String
Private mstrUserName As String
Private mstrPassword As String
Private mTransaction As SqlClient.SqlTransaction = Nothing
Friend SQLDatabase As SqlClient.SqlConnection
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
' ///////////////////////////////////////////////////////////////////////////////
' Databse Name
Public Property DatabaseName() As String
Get
Return mstrDatabaseName
End Get
Set(ByVal value As String)
mstrDatabaseName = value
End Set
End Property
' ///////////////////////////////////////////////////////////////////////////////
' Server
Public Property Server() As String
Get
Return mstrServer
End Get
Set(ByVal value As String)
mstrServer = value
End Set
End Property
' ///////////////////////////////////////////////////////////////////////////////
' UserName
Public Property Username() As String
Get
Return mstrUserName
End Get
Set(ByVal value As String)
mstrUserName = value
End Set
End Property
' ///////////////////////////////////////////////////////////////////////////////
' Password
Public Property Password() As String
Get
Return mstrPassword
End Get
Set(ByVal value As String)
mstrPassword = value
End Set
End Property
' ///////////////////////////////////////////////////////////////////////////////
' Set All 4 properties
Public Sub SetDatabase(ByVal strServer As String, ByVal strDatabase As String, ByVal strUsername As String, ByVal strPassword As String)
mstrServer = strServer
mstrDatabaseName = strDatabase
mstrPassword = strPassword
mstrUserName = strUsername
End Sub
' ///////////////////////////////////////////////////////////////////////////////
' Connects to the database that we've been given the details for
Public Function ConnectDatabase() As Boolean
Dim strConnectionString As String
Dim dbTest As SqlClient.SqlConnection
' Create a connection string.
strConnectionString = "Data Source=" + mstrServer + "; Initial Catalog=" + mstrDatabaseName + ";"
If (mstrUserName <> "" And mstrPassword <> "") Then
strConnectionString = strConnectionString + "User Id=" + mstrUserName + ";"
strConnectionString = strConnectionString + "Password=" + mstrPassword + ";"
Else
strConnectionString = strConnectionString + "Trusted_Connection=Yes;"
End If
' Connect to the database
dbTest = New SqlClient.SqlConnection(strConnectionString)
dbTest.Open()
' If we've opened then store the connection otherwise, exit
If (dbTest.State = ConnectionState.Open) Then
If (Not SQLDatabase Is Nothing) Then
SQLDatabase.Close()
End If
SQLDatabase = Nothing
SQLDatabase = dbTest
dbTest = Nothing
Return True
Else
dbTest = Nothing
Return False
End If
End Function
' /////////////////////////////////////////////////////////////////////
' Begins a tranasction
Public Sub BeginTransaction()
If (mTransaction Is Nothing) Then
mTransaction = SQLDatabase.BeginTransaction()
Else
Throw New Exception("Transaction is already open!")
End If
End Sub
Public Sub CommitTransaction()
If (mTransaction Is Nothing) Then
Throw New Exception("There is no transaction open!")
Else
mTransaction.Commit()
mTransaction = Nothing
End If
End Sub
Public Sub RollbackTransaction()
If (mTransaction Is Nothing) Then
Throw New Exception("There is no transaction open!")
Else
mTransaction.Rollback()
mTransaction = Nothing
End If
End Sub
' /////////////////////////////////////////////////////////////////////
' Open recordSet Opens a recordSet Object and then does stuff with it.
Public Function OpenRecordSet(ByVal strSQL As String) As DBRecordSet
Dim retData As DBRecordSet = New DBRecordSet
retData.Database = Me
retData.OpenRecordSet(strSQL)
Return retData
End Function
' /////////////////////////////////////////////////////////////////////
' Executes an SQL that Needs Executing
Public Function Execute(ByVal strSQL As String, Optional ByRef RecordsAffected As Long = 0) As Boolean
Dim cmdCommand As SqlClient.SqlCommand
cmdCommand = New SqlClient.SqlCommand(strSQL, SQLDatabase, mTransaction)
RecordsAffected = cmdCommand.ExecuteNonQuery()
Execute = True
' Clean and Exit
cmdCommand = Nothing
End Function
' /////////////////////////////////////////////////////////////////////
' Clean up and Exit
Protected Overrides Sub Finalize()
If (Not mTransaction Is Nothing) Then
mTransaction.Rollback()
mTransaction = Nothing
End If
SQLDatabase = Nothing
MyBase.Finalize()
End Sub
End Class
DBRecordSet - Used to manipulate a recordset handed back by DBConnection, similar to the ADO recordset object it will allow you to move forward and backward through the records as well as giving you a way to determine BOF & EOF.
Public Class DBRecordSet
Private mdbConn As DBConnection
Private mdbData As DataTable
Private mlngCounter As Long
'///////////////////////////////////////////////////////////////////////////////
' Pass the database through
Default Public ReadOnly Property Fields(ByVal FieldName As String) As Object
Get
If (mdbData Is Nothing) Then
Return Nothing
Else
If (mlngCounter >= 0) Then
Return mdbData.Rows(mlngCounter).Item(FieldName)
Else
Return Nothing
End If
End If
End Get
End Property
Default Public ReadOnly Property Fields(ByVal FieldIndex As Integer) As Object
Get
If (mdbData Is Nothing) Then
Return Nothing
Else
If (mlngCounter >= 0) Then
Return mdbData.Rows(mlngCounter).Item(FieldIndex)
Else
Return Nothing
End If
End If
End Get
End Property
Public Property Database() As DBConnection
Get
Return mdbConn
End Get
Set(ByVal value As DBConnection)
mdbConn = Nothing
mdbConn = value
End Set
End Property
'///////////////////////////////////////////////////////////////////////////////
Public Sub New()
End Sub
Public Sub New(ByRef Connection As DBConnection)
Me.Database = Connection
End Sub
Friend Function OpenDataSetFromSP(ByVal StoredProcName As String) As DataSet
Dim varReturn As Long
Return OpenDataSetFromSP(StoredProcName, varReturn, Nothing)
End Function
'///////////////////////////////////////////////////////////////////////////////
' Open the dataset
Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
Dim dbData As SqlClient.SqlDataAdapter
Dim dbCommand As SqlClient.SqlCommand
Dim parameter As SqlClient.SqlParameter
Dim lngCounter As Long
Dim ds As DataSet
OpenDataSetFromSP = Nothing
Try
dbCommand = New SqlClient.SqlCommand(StoredProcName, Me.Database.SQLDatabase)
dbCommand.CommandType = CommandType.StoredProcedure
LoadParams(dbCommand)
lngCounter = 0
If (dbCommand.Parameters.Count > 0) Then
For Each parameter In dbCommand.Parameters
If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
If (Args.Length >= lngCounter) Then
parameter.Value = Args(lngCounter)
End If
lngCounter = lngCounter + 1
End If
Next
End If
' Execute what we need
dbData = New SqlClient.SqlDataAdapter(dbCommand)
ds = New DataSet
' Shove indataSet
dbData.Fill(ds)
OpenDataSetFromSP = ds
' Retrieve the Result?
For Each parameter In dbCommand.Parameters
If (parameter.Direction = ParameterDirection.ReturnValue) Then
ReturnValue = parameter.Value
End If
Next
' Exit
Catch ex As Exception
'DB.GenerateError("DbRecordSet::OpenDataSetFromSP : " + StoredProcName, ex)
OpenDataSetFromSP = Nothing
End Try
' Clean up
dbCommand = Nothing
dbData = Nothing
End Function
' //////////////////////////////////////////////////////////////////////////////////////////////
' Caches and loads cached params for a stored procedure.... saves on the call backs
Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
Dim strProcName As String = dbCommand.CommandText
Dim dbparameter As Collection = Nothing
Dim parameter As SqlClient.SqlParameter = Nothing
Static dicParams As Collection
If (dicParams Is Nothing) Then
dicParams = New Collection
End If
' Find this itme in the list
If (dicParams.Contains(strProcName)) Then
dbparameter = dicParams(strProcName)
If (Not dbparameter Is Nothing) Then
For Each parameter In dbparameter
dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
Next
End If
Else
SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
' now record the params
For Each parameter In dbCommand.Parameters
If (dbparameter Is Nothing) Then
dbparameter = New Collection
End If
dbparameter.Add(CType(parameter, ICloneable).Clone)
Next
dicParams.Add(dbparameter, strProcName)
End If
End Sub
'///////////////////////////////////////////////////////////////////////////////
' The Dataset object, just return tis all.
Friend Function OpenDataSet(ByVal strSQL As String) As DataSet
Dim datData As DataSet = Nothing
Dim objCommand As SqlClient.SqlCommand
Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()
Try
objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
objAdapter.SelectCommand = objCommand
datData = New DataSet
objAdapter.Fill(datData)
objAdapter.Dispose()
objCommand.Dispose()
Catch ex As Exception
End Try
objAdapter = Nothing
objCommand = Nothing
Return datData
End Function
'///////////////////////////////////////////////////////////////////////////////
' Opens the recordSByVal strSQL As String) As Boolean
Friend Function OpenRecordSet(ByVal strSQL As String) As Boolean
Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()
Dim objCommand As SqlClient.SqlCommand
OpenRecordSet = False
Try
mlngCounter = -1
mdbData = Nothing
objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
objAdapter.SelectCommand = objCommand
mdbData = New DataTable
objAdapter.Fill(mdbData)
objAdapter.Dispose()
objCommand.Dispose()
If (mdbData.Rows.Count > 0) Then
mlngCounter = 0
End If
OpenRecordSet = True
Catch ex As Exception
Throw
End Try
objCommand = Nothing
objAdapter = Nothing
End Function
' ////////////////////////////////////////////////////////////////////////////////////
' MoveNext, MovePrevious, MoveFirst and MoveLast, self explanatory
Public Function MoveLast() As Long
If (mdbData Is Nothing) Then
Return 0
Else
mlngCounter = mdbData.Rows.Count - 1
Return mlngCounter
End If
End Function
Public Sub MoveFirst()
mlngCounter = 0
End Sub
Public Function MoveNext() As Long
If (mdbData Is Nothing) Then
Return 0
Else
mlngCounter = mlngCounter + 1
If (mlngCounter > mdbData.Rows.Count) Then
mlngCounter = mdbData.Rows.Count
End If
Return mlngCounter
End If
End Function
Public Function MovePrevious() As Long
mlngCounter = mlngCounter - 1
If (mlngCounter < 0) Then
mlngCounter = 0
End If
Return mlngCounter
End Function
' ////////////////////////////////////////////////////////////////////////////////////
' EOF & BOF, checks to see if we're at the end or begining of the recordSet
Public Function EOF() As Boolean
EOF = False
If (Not mdbData Is Nothing) Then
If (mlngCounter >= mdbData.Rows.Count Or mdbData.Rows.Count = 0) Then
' DO the move last thing incase we are over the top on our recordcount
EOF = True
End If
End If
End Function
Public Function BOF() As Boolean
BOF = False
If (Not mdbData Is Nothing) Then
If (mlngCounter <= 0 And mdbData.Rows.Count > 0) Then
mlngCounter = 0
BOF = True
End If
End If
End Function
' ////////////////////////////////////////////////////////////////////////////////////
' SImilar to the .close command in recordSets, just frees up the data.
Public Sub Close()
mdbData = Nothing
End Sub
' ////////////////////////////////////////////////////////////////////////////////////
' Clean up and Exit
Protected Overrides Sub Finalize()
mdbData = Nothing
mdbConn = Nothing
MyBase.Finalize()
End Sub
End Class
That is basically that with these two classes you can go around and do what you want on the SQL server... for example
' Open a connection to the database
Dim myConnection As DBConnection = New DBConnection
myConnection.SetDatabase("MYSERVER\Instance", "DatabaseName", "", "")
' Execute an SQL Directly
myConnection.Execute("UPDATE [Article] SET [Data] = [Data] + 1")
' Open a recordSet
Dim myRecordSet As New DBRecordSet(myConnection)
' Execute Straight record return SQLs
myRecordSet.OpenRecordSet("SELECT * FROM [Article]")
If (Not myRecordSet.EOF) Then
Dim lngData As Long = myRecordSet.Fields("Data")
End If
' Return Datasets from SQLs
Dim myData As DataSet = myRecordSet.OpenDataSet("SELECT * FROM [Article]")
' Exeute Stored Procedures
Dim lngReturn As Integer
Dim myData As DataSet = myRecordSet.OpenDataSetFromSP("dba_CheckData", lngReturn, 1, 2, 3, "Some Parameter", 2341)
' That's that
No comments:
Post a Comment