Turning a DataTable into a generic collection method under VB.net environment

Background

when doing computer room charge system, encountered such a problem, the data in the database table is taken out of a row, the contents of each cell is populated into the form

of the the text box. Given the complexity of getting results with DataReader, I used a generic collection. But there's a way to ask .

Problem.

Question one:

This problem occurs because the field type of the table and the field type of the entity are inconsistent when the DataTable is turned to an entity type. This is also possible.

so to speak, Chinese children all follow the father's name, which is a tradition. But the child with the mother's surname, there is something wrong.

Issue two: The field names in the database tables are inconsistent with the Entity attribute field names to be converted, causing the depart in the converted entity to be null.

Conversion principle

Analysis: The reason for these problems is that I don't quite understand the principle of the DataTable to entity type. after looking at the information, coupled with their own understanding, and finally will this

Conversions method mastered, in the code to save a lot of Kung Fu. First look at the conversion process schematic diagram.

Principle: Each row of data in a database table is first transformed into an entity class, and then the transformed entity class is put into a generic collection

Generic collection Application Implementation functionality: Load data from a database table into the corresponding form text box

Code implementation (1) Build an entity transformation process at the entity level

' *************************************************** ' Description: Generic collection, Complete conversion of DataTable type to entity class ' Wang Zu ' Date created: 2014-04-24 11:08:02 ' version number: V1.00 ' ***************************************************imports System.Collections.GenericImports System.reflectionpublic Class Entitymoudule ' converts a DataTable to a generic collection public Shared Function convertolist (for T as {New}) (by Val DT as DataTable) as IList (Of T) Dim mylist As New List (Of T) ' defines the final returned collection Dim mytype as Type = Get Type (T) ' Get entity class name Dim dr As DataRow ' define rowset Dim tmpname As String = String.Empty ' definition One A temporary variable ' iterates through all the data rows of a DataTable for every Dr in Dt. Rows Dim MyT As New T ' defines an entity class object Dim Propertys () as PropertyInfo = Myt.gettype ().                GetProperties () ' Defines the property collection Dim PR as PropertyInfo ' to traverse all properties of the object for every PR in propertys Tmpname = pr. Name ' assigns the property name to the global variable ' checks if the DataTable contains this column, and the column name = = Property name of the object if (dt. Columns.contains (Tmpname)) Then ' compare this property to the column name of the DataTable to see if the DataTable contains this property ' to determine if this property has a setter class if (pr.                    CanWrite = False) Then ' to determine if this property is writable and if not writable, jump out of this loop Continue for End if Dim Value As Object = DR (Tmpname) ' defines an object's columns to hold the value of the column If (value. ToString <> DBNull.Value.ToString () Then ' determines whether it is empty, or if not NULL, assigns the property PR to the object.            SetValue (MyT, value, nothing) ' dynamically accesses a dynamic property through reflection during runtime End If End if Next MyList. Add (MyT) ' adds MyT object to collection Next return mylist ' return entity collection End FunctionEnd Class

(2) Implement the code in the D layer

Public Class BASICDATABLL Public        Function basicdatalist () as IList (of entity.basicdataentity)        Dim Ibasicdata as Idal. Ibasicdata        Dim Factory as New factory.basicdatafac        ibasicdata = Factory. Createibasicdata ()        Dim mylist as IList (of entity.basicdataentity)        mylist = ibasicdata.setdatalist ()        Return mylist    End functionend Class

(3) interface layer, defining an interface

Public Interface ibasicdata    Function setdatalist () as IList (of entity.basicdataentity) End Interface

(4) Factory layer, create an interface

Public Class Basicdatafac    Dim assemblyname As String = "DAL"    Dim db As String = ConfigurationSettings.AppSettings ( "DB")    Function Createibasicdata () as Ibasicdata        Dim ibasicdata as ibasicdata        ibasicdata = CType ( Assembly.Load (AssemblyName). CreateInstance (AssemblyName + "." + db + "Basicdatadal"), Ibasicdata)        Return ibasicdata    End functionend Class

(5) Accessing the D-Layer interface method in layer B

Public Class BASICDATABLL Public    Function basicdatalist () as IList (of entity.basicdataentity)        Dim Ibasicdata as Idal. Ibasicdata        Dim Factory as New factory.basicdatafac        ibasicdata = Factory. Createibasicdata ()        Dim mylist as IList (of entity.basicdataentity)        mylist = ibasicdata.setdatalist ()        Return mylist    End functionend Class

(6) Appearance layer, receive a generic set in B-layer

Public Class basicdatafacade Public    Function basicdatalist () as IList (of entity.basicdataentity)        Dim MyList as IList (of entity.basicdataentity)        Dim SetData as New BLL. BASICDATABLL        mylist = SetData. Basicdatalist ()        Return mylist    End functionend Class

(7) U-layer, populating the data in a generic collection into a form

    Private Sub frmsetbasic_load (sender as Object, e as EventArgs) Handles mybase.load        cmdcommit.enabled = False   ' Make sure Button cannot be used        groupbox1.enabled = False   ' data not modifiable        cmdcancle.enabled = False   ' Cancel unavailable        ' Dim mylist as IList (of entity.basicdataentity)        Dim loaddata as New facade.basicdatafacade        mylist = LoadData. Basicdatalist ()        Dim num as Integer        num = mylist. Count-1     ' num represents the last row in the table        txtfixed.text = mylist (num). Fixeduser        txtcasual.text = mylist (num). Casualuser        txtincrease.text = mylist (num). Incrementoftime        txtatleast.text = mylist (num). Atleasttime        txtprepare.text = mylist (num). Preparetime        txtleast.text = mylist (num). Mincharge    End Sub

(8) Entity type properties

Public Class basicdataentity ' general user Properties Private _userid As String public property userId As String Get    Return _userid End Get Set (value as String) _userid = value End Set End Property         ' Minimum Amount attribute Private _mincharge as decimal public property Mincharge as decimal Get Return _mincharge End Get Set (value as Decimal) _mincharge = value End Set End Property ' Prepare time attribute Pr Ivate _preparetime As Integer public property preparetime As Integer Get Return _preparetime E  nd Get Set (value as Integer) _preparetime = value End Set End Property ' minimum on machine time attribute Private _atleasttime As Integer public property atleasttime As Integer Get Return _atleasttime End Ge T set (value as Integer) _atleasttime = value End Set End Property ' unit increment time attribute Private _inc Rementoftime as Integer   Public property Incrementoftime as Integer get Return _incrementoftime End get Set (valu e as Integer) _incrementoftime = value End Set End Property ' Fixed user attribute Private _fixeduser as Decim Al Public property Fixeduser as Decimal get Return _fixeduser End get Set (value as Deci MAL) _fixeduser = value End Set End Property ' Temporary user attribute Private _casualuser as Decimal public P            Roperty casualuser as decimal get Return _casualuser End get Set (value as decimal) _casualuser = value End Set End Propertyend Class

Summarize

The conversion of generics to DataReader processing data is simplified, the data is operable, that is, after the D-layer conversion, regardless of the layer is

Can read data, optimize the code structure, make business logic simple.

Turning a DataTable into a generic collection method under VB.net environment