Custom Typed DataTable and DataSet

x77
Published:
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
                      

Open in new window

0
5,878 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.