How can I use my own connection class with a strongly typed dataset?

Question

I have designed a class with sqlClient.SqlCommand wrappers to implement such functionality as automatic retries on timeout, Async (thread safety), error logging, and some sql server functions like WhoAmI.

I've used some strongly typed datasets mainly for display purposes only, but I'd like to have the same database functionality that I use with my class. Is there an interface I can implement or a way to hook my command/connection class into the dataset at design or runtime?

Or would I need to somehow write a wrapper for the dataset to implement these types of functions? if this is the only option could it be made generic to wrap anything that inherits from dataset?

Answer 1

Here is a way to extend transactional functionality to your typed dataset. You could alter this to include your special SQLCommand wrappers.

In this example, I have a dataset called "dsMain" and a few direct queries in a "QueriesTableAdapter". I extend the partial class for the TableAdapter with a function that will create a transaction based on the first (0) connection and then apply it to every connection in the table adapter.

Namespace dsMainTableAdapters
    Partial Public Class QueriesTableAdapter
        Public Function CreateTransaction() As Data.IDbTransaction   
            Dim oConnection = Me.CommandCollection(0).Connection
            oConnection.Open()

            Dim oTrans = oConnection.BeginTransaction()

            For Each cmd In Me.CommandCollection
                cmd.Connection = oConnection
                cmd.Transaction = oTrans
            Next

            Return oTrans
        End Function
    End Class
End Namespace

You begin the transaction by calling the new function

Dim qa As New dsMainTableAdapters.QueriesTableAdapter
Dim oTrans = qa.CreateTransaction()

Then you can call TableAdapter queries within your transaction

qa.Query1
qa.Query2

When you are done with your queries you commit the transaction

oTrans.Commit()

You can do the same thing for any TableAdapter that was created for your datasets. If you have multiple TableAdapters that need to use the same transaction, then in addition to a "CreateTransaction" you should make a "SetTransaction" and have the Transaction be a parameter.

Answer 2

Here is a more specific answer. This demontrastes how you can use a baseclass for your typed datasets for the purpose of swapping in your own connection or command classes.

Set this as the "BaseClass" for each of your typed TableAdapters, replacing "System.ComponentModel.Component". By using "MustInherit/MustOverride" ("Abstract" in C#) you can get to the properties you can't otherwise reach.

Public MustInherit Class SuperTableAdapter
    Inherits System.ComponentModel.Component

    Public MustOverride ReadOnly Property MyCommandCollection As Data.SqlClient.SqlCommand()

    Public Sub New()
        MyBase.New()
        'With the command collection exposed, you can replace it with your own.'

        For i = 0 To MyCommandCollection.Length - 1
            'Now you can put in your special command class here'
            Dim myspecialCommand As New Data.SqlClient.SqlCommand()
            MyCommandCollection(i) = myspecialCommand
        Next
    End Sub
End Class

For each of your table adapters that you set to inherit your BaseClass you must override the required "MustOverride" property. Without it, it won't compile. If you add the code but do not set the TableAdapter base class, it will not compile either. That is good thing; it ensures you do it right.

Namespace DataSet1TableAdapters
    Partial Public Class Table1TableAdapter
        Public Overrides ReadOnly Property MyCommandCollection As System.Data.SqlClient.SqlCommand()
            Get
                Return Me.CommandCollection
            End Get
        End Property
    End Class

    Partial Public Class Table2TableAdapter
        Public Overrides ReadOnly Property MyCommandCollection As System.Data.SqlClient.SqlCommand()
            Get
                Return Me.CommandCollection
            End Get
        End Property
    End Class
End Namespace

Now you can put all sorts of special code in your SuperTableAdapter. If you need access to something that wasn't exposed, just use "MustOverride" to guarantee that it is available.