Custom Typed DataTable and DataSet

Published on
11,321 Points
Last Modified:
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code.

First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable.
Also, it can inherit Typed TableBase(Of T)  where T is DataRow or a Typed DataRow.  I don't use Typed DataRow. It has worse performance (by about 30%) on Table Fill  than DataRow, and also needs some tricks to build it.

Next, I create a class for each DataTable that inherits from the base class that contains the common code.   In this class, I declare and create each DataColumn for the DataTable.

Now I create the class constructor (New) where I add the columns to Table.Columns and optionally, assign PrimaryKey Columns.

When I have created each Table, I can create a Class for DataSet (Inherits DataSet).   Here, I declare and create an instance for each DataTable.   Optionally, I declare and create DataRelations.   Then in the Constructor,  add each table to DataSet.Tables and relations to DataSet.Relations.


When you load a DataTable, DataAdapter or DataReader don't query the database to get column information.
You can access Fields by Table.Column declared on Typed DataTable.  This is faster than by ColumnName, avoids errors in typing the ColumnName, and has the best support for Refactoring.
Declare DataTypes for each DataColumn, then you can use DirectCast or TryCast.  When you use auto-generated tables based on database information, you need to use Ctype for numeric Columns, a numeric column can use Short, Integer, Long, Double, Decimal...
You can add functions and overload base methods for each custom Typed DataTable or DataSet.

In the following example code, I wrote a complex function for updating tables on a DataSet with Relations.  If the Tables on the database have Relations, then:

    I can't Insert a Child Row until Master Row is inserted.
    I can't Delete a Master Row until Childs Rows are Deleted.

So, I use a function:  
    Update( ByVal ParamArray Tables() As RetDataTable )

Here, I put first the Master Table, then the Child Table, then the Child of the Child ...
This is valid for Inserts, but for Deletions, I need do it in reverse order.

Then I put a deleted flag on each Table and do deletions in Tables Reverse Order for each table before sending any other change to database.

'Base Clase For Empleados, Grupos, Puestos, TelPuesto
Friend Class RetDataTable
    Inherits DataTable

    Public Da As OleDbDataAdapter, Modified, Deleted As Boolean
    Public Cb As OleDbCommandBuilder

    Sub New(ByVal Sql As String)
        Me.TableName = Me.GetType.Name
        If CnDb Is Nothing Then Return
        Da = New OleDbDataAdapter(Sql, CnDb)
    End Sub

'Lazzy Command Creation for DataBAse Update.
    Public Sub InitCb()
        If Cb Is Nothing AndAlso (Modified OrElse Deleted) Then
           Cb = New OleDbCommandBuilder(Da)
           Da.InsertCommand = Cb.GetInsertCommand
           Da.UpdateCommand = Cb.GetUpdateCommand
           Da.DeleteCommand = Cb.GetDeleteCommand
        End If
    End Sub

    Public Sub Refresh()
        Catch ex As OleDbException
        End Try
    End Sub
End Class

Friend Class Empleados
    Inherits RetDataTable

    Public Emp As New DataColumn("Emp", GetType(String)) With {.MaxLength = 10, .AllowDBNull = False}
    Public Nombre As New DataColumn("Nombre", GetType(String)) With {.MaxLength = 40, .AllowDBNull = False}
    Public TelLargo As New DataColumn("TelLargo", GetType(String)) With {.MaxLength = 12} ', .AllowDBNull = False}
    Public TelCorto As New DataColumn("TelCorto", GetType(String)) With {.MaxLength = 12} ', .AllowDBNull = False}
    Public TP As New DataColumn("CodTelPuesto", GetType(String)) With {.MaxLength = 1}
    Public IdPuesto As New DataColumn("IdPuesto", GetType(Short))
    Public CA As New DataColumn("CA", GetType(String))

    Sub New()
        MyBase.New("SELECT  Emp, Nombre, TelLargo, TelCorto, CodTelPuesto, IdPuesto, Ca from Empleados")
        MinimumCapacity = 1024
        Columns.AddRange(New DataColumn() {Emp, Nombre, TelLargo, TelCorto, TP, IdPuesto, CA})
        PrimaryKey = New DataColumn() {Emp}
    End Sub
End Class

Friend Class RetenDataset
    Inherits DataSet

    'Tables are Delclared Shared on Application Main Module for easy access
    'Public TTelPuesto As New TelPuesto
    'Public TEmpleados As New Empleados
    'Public TPuestos As New Puestos
    'Public TGrupos As New Grupos

    Public REmpleados, RTelPuesto, RPuestos As DataRelation

    Sub New()
        Tables.AddRange(New DataTable() {TTelPuesto, TEmpleados, TPuestos, TGrupos})

        RPuestos = New DataRelation("GrupoPuesto", TGrupos.PrimaryKey, New DataColumn() {TPuestos.IdGrupo}, False)
        REmpleados = New DataRelation("PuestoEmpleado", TPuestos.PrimaryKey, New DataColumn() {TEmpleados.IdPuesto}, False)
        RTelPuesto = New DataRelation("PuestoTelefono", TPuestos.PrimaryKey, New DataColumn() {TTelPuesto.Id}, False)

        Relations.AddRange(New DataRelation() {RPuestos, REmpleados, RTelPuesto})
    End Sub

    Sub Update(ByVal ParamArray Tables() As RetDataTable)
        Dim t As RetDataTable, L As List(Of DataRow) = Nothing, Da As OleDbDataAdapter
        For Each t In Tables : t.InitCb() : Next
        Dim Tr = CnDb.BeginTransaction
           'En Primer los registros eliminados
           For N = Tables.Length - 1 To 0 Step -1
               t = Tables(N) : If t.Deleted = False Then Continue For
               If L Is Nothing Then L = New List(Of DataRow)
               For Each r As DataRow In t.Rows
                   If r.RowState = DataRowState.Deleted Then L.Add(r)
               Da = t.Da : Da.DeleteCommand.Transaction = Tr
               Da.Update(L.ToArray) : L.Clear()

           For Each t In Tables
               If t.Modified Then
                  Da = t.Da
                  Da.InsertCommand.Transaction = Tr
                  Da.UpdateCommand.Transaction = Tr
               End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Actualizando datos a Database")
        End Try
        For Each t In Tables
           t.Deleted = False
           t.Modified = False
    End Sub

    Public Overrides Sub RejectChanges()
       For Each t As RetDataTable In DsReten.Tables
           t.Modified = False
           t.Deleted = False
    End Sub

    Sub Refresh()
            Me.EnforceConstraints = False
            For Each T As RetDataTable In Me.Tables
            EnforceConstraints = True
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Refresh data from Database")
        End Try
    End Sub
End Class

Open in new window


Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month