• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This is a .Net COM Object that performs a few common database operations, such as opening/closing t

This is a .Net COM Object that performs a few common database operations, such as opening/closing t

This is a .Net COM Object that performs a few common database operations, such as opening/closing the database, getting a dataset, and

Rate This is a .Net COM Object that performs a few common database operations, such as opening/closing t

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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