by Sean Iannuzzi (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 13th November 2002
Date Added: Mon 8th February 2021
Rating: (1 Votes)
This is a .Net COM Object that performs a few common database operations, such as opening/closing the database, getting a dataset, and
Public Class IDBMngr
Enum GetDataSet_RT
DataSet = 1
DataSetTableCollection = 2
DataSetRowCollection = 3
End Enum
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "9655C183-9582-4E9C-970E-780AAC1DDEC3"
Public Const InterfaceId As String = "5156F724-4A03-441C-BCB7-27986D6205FA"
Public Const EventsId As String = "FA1154AF-0646-4D4A-B3C4-C74C36604385"
#End Region
' 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
Public Function GetDataSet(ByVal ConnectionString As String, ByVal CommandText As String, ByVal CommandType As Data.CommandType, Optional ByVal ReturnType As GetDataSet_RT = 1) As Object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:
' To retrieve a dataset
'
' Parameters:
' ConnectionString - Database Connection String
' CommandText - Query, SP Name, or Table Name
' CommandType - Command Type - Table, SP, or Text
' ReturnType - Dataset, table collection, row collection
'
' Notes:
' Can return multiple datasets
' Uses the Data Adapter, Command, and Connection Objects
' Make a call to mfDS_LoadDataSet that actually loads the data
'
' Modifications:
' 08/31/2002 New
' SBI
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lobj_Sql_DA As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter()
Dim lobj_SQL_CMD As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
Dim lobj_SQL_CON As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
'ready the data adapter and set the command
lobj_Sql_DA.SelectCommand = lobj_SQL_CMD
lobj_SQL_CMD.CommandType = CommandType
lobj_SQL_CMD.CommandText = CommandText
lobj_SQL_CMD.Connection = lobj_SQL_CON
'set the connection
lobj_SQL_CON.ConnectionString = ConnectionString
'return data object back
GetDataSet = mfDS_LoadDataSet(lobj_SQL_CON, lobj_Sql_DA, ReturnType)
End Function
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
'need two functions one to load the data and one to fill the dataset
Private Function mfDS_LoadDataSet(ByVal pvobj_SQL_CON As Data.SqlClient.SqlConnection, ByVal pvObj_Sql_DA As System.Data.SqlClient.SqlDataAdapter, Optional ByVal ReturnType As GetDataSet_RT = 1) As Object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:
' To load a dataset
'
' Parameters:
' pvobj_SQL_CON - Database Connection String
' pvObj_Sql_DA - Sql Data Adapter
' ReturnType - Dataset, table collection, row collection
'
' Notes:
' Can return multiple datasets
' Uses the Data Adapter and Connection Objects
' Make a call to mSub_FillDataSet that actually fills the adapter
'
' Modifications:
' 08/31/2002 New
' SBI
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lobj_DataSetTemp As DataSet = New DataSet()
Try
'Attempt to fill the temporary dataset.
Call mSub_FillDataSet(lobj_DataSetTemp, pvobj_SQL_CON, pvObj_Sql_DA)
Catch eFillDataSet As System.Exception
'Add your error handling code here.
Throw eFillDataSet
End Try
Select Case ReturnType
Case GetDataSet_RT.DataSet
mfDS_LoadDataSet = lobj_DataSetTemp
Case GetDataSet_RT.DataSetTableCollection
mfDS_LoadDataSet = lobj_DataSetTemp.Tables
Case GetDataSet_RT.DataSetRowCollection
mfDS_LoadDataSet = lobj_DataSetTemp.Tables(0).Rows
End Select
End Function
Private Sub mSub_FillDataSet(ByVal pvObj_DataSet As Data.DataSet, ByVal pvobj_SQL_CON As Data.SqlClient.SqlConnection, ByVal pvObj_Sql_DA As System.Data.SqlClient.SqlDataAdapter)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:
' To fill the data adapter
'
' Parameters:
' pvObj_DataSet - Dataset
' pvobj_SQL_CON - Database Connection String
' pvObj_Sql_DA - Sql Data Adapter
'
' Notes:
' Binds the data to the data adapter from the command set
'
' Modifications:
' 08/31/2002 New
' SBI
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Turn off constraint checking before the dataset is filled.
'This allows the adapters to fill the dataset without concern
'for dependencies between the tables.
pvObj_DataSet.EnforceConstraints = False
Try
'Open the connection.
pvobj_SQL_CON.Open()
'Attempt to fill the dataset through the OleDbDataAdapter1.
pvObj_Sql_DA.Fill(pvObj_DataSet)
Catch fillException As System.Exception
'Add your error handling code here.
Throw fillException
Finally
'Turn constraint checking back on.
pvObj_DataSet.EnforceConstraints = True
'Close the connection whether or not the exception was thrown.
pvobj_SQL_CON.Close()
End Try
End Sub
Public Function ExecuteNonQuery(ByVal ConnectionString As String, ByVal CommandText As String, ByVal CommandType As Data.CommandType) As Boolean
Dim lobj_SQL_CON As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection()
Dim lobj_SQL_CMD As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
'Me.mobj_Sql_DA.SelectCommand = Me.mobj_SQL_CMD
lobj_SQL_CMD.CommandType = CommandType
lobj_SQL_CMD.CommandText = CommandText
lobj_SQL_CMD.Connection = lobj_SQL_CON
'set the connection
lobj_SQL_CON.ConnectionString = ConnectionString
Try
'Open the connection.
lobj_SQL_CON.Open()
'execute the statement
lobj_SQL_CMD.ExecuteNonQuery()
ExecuteNonQuery = True
Catch fillException As System.Exception
'Add your error handling code here.
Throw fillException
ExecuteNonQuery = False
Finally
'Close the connection whether or not the exception was thrown.
lobj_SQL_CON.Close()
End Try
End Function
End Class
No comments have been posted about This is a .Net COM Object that performs a few common database operations, such as opening/closing t. Why not be the first to post a comment about This is a .Net COM Object that performs a few common database operations, such as opening/closing t.