[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Custom Typed DataTable and DataSet

Published on
11,207 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

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month