R – How to use the own connection class with a strongly typed dataset

datasetnetstrongly-typed-datasetvisual studio

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?

Best Answer

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.

Related Topic