Custom Typed DataTable and DataSet: VB.NET, DataTables, Typed DataSet

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.

Benefits:

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()
        Try
            Da.Fill(Me)
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        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
        Try
           '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)
               Next
               Da = t.Da : Da.DeleteCommand.Transaction = Tr
               Da.Update(L.ToArray) : L.Clear()
           Next

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

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

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:

Select all Open in new window

Custom Typed DataTable and DataSet

VB.NET

DataTables

Typed DataSet

Microsoft Visual Basic.Net

About The Author

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Visual Basic.NET Experts