数据类型有自定义类型吗_自定义类型的数据表和数据集

最新推荐文章于 2023-02-15 11:51:38 发布

cunchi8090

最新推荐文章于 2023-02-15 11:51:38 发布

阅读量369

点赞数

文章标签：数据库 java python sql mysql

原文链接：https://www.experts-exchange.com/articles/3292/Custom-Typed-DataTable-and-DataSet.html

版权

数据类型有自定义类型吗

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.

首先，我为DataTables通用代码创建一个Abstract类。此类从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.

而且，它可以继承Typed TableBase（Of T），其中T是DataRow或Typed DataRow。我不使用Typed DataRow。它在表填充方面的性能比DataRow差（大约30％），并且还需要一些技巧来构建它。

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.

接下来，我为每个DataTable创建一个类，该类从包含通用代码的基类继承。在此类中，我声明并创建DataTable的每个DataColumn。

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

现在，我创建类构造函数（新建），将列添加到Table.Columns中，并可选地分配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.

创建每个表后，可以为数据集创建一个类（继承数据集）。在这里，我为每个DataTable声明并创建一个实例。（可选）我声明并创建DataRelations。然后在构造函数中，将每个表添加到DataSet.Tables并将关系添加到DataSet.Relations。

Benefits:

优点：

When you load a DataTable, DataAdapter or DataReader don't query the database to get column information.

加载DataTable时，DataAdapter或DataReader不会查询数据库以获取列信息。

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.

您可以通过在类型化数据表上声明的Table.Column访问Fields。这比使用ColumnName更快，可避免在键入ColumnName时出错，并且对重构具有最佳支持。

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...

为每个DataColumn声明DataType，然后可以使用DirectCast或TryCast。当使用基于数据库信息的自动生成的表时，需要对数字列使用Ctype，数字列可以使用Short，Integer，Long，Double，Decimal ...

You can add functions and overload base methods for each custom Typed DataTable or DataSet.

您可以为每个自定义的Typed DataTable或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 )

更新（ByVal ParamArray Tables（）作为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