Click here to Skip to main content
15,867,568 members
Articles / Database Development

Visual Basic Using Reflection to Map DataTable in MySQL Database

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
5 Sep 2013CPOL14 min read 30.1K   308   10  
Easy way to map the table in the mysql database.

Contents

  • Introduction and background
  • How it works?
    • Part 1: The reflection basic knowledge
      1. Create a custom attribute
      2. Dynamic coding with reflection and custom attribute
        1. Doing reflection using keyword GetType
        2. Get the properties in our mapping schema
        3. Value assignment
      3. Create the schema class to map the table
      4. Testing
    • Part 2: The MySQL database mapping job
      1. Create custom attribute
      2. Create a helper class
      3. Create table class to store schema information
      4. The database mapping job
        1. The class components
        2. The mapping procedures
      5. Testing
  • Points of interest
  • Code improvements
    1. More general coding way
    2. Make it functional on multiple user situations
    3. The field attributes combination in MySQL
  • Additional

Introduction and background

To me personally, reading data records in a database is a horrible job as you may alter the schema of the table in your database sometimes. Every time you modify the structure of the data table in the database, you must alter the proceeding code of the data fetch function. Can we make this job easy in Visual Basic? Maybe we can using a dynamic code that uses Reflection to map the schema of your data table to make your database coding job more easy.

How it works?

Part 1: The reflection basic knowledge

1. Create a custom attribute

Creating a custom attribute is kind of a very simple job. You just need to create a class type that inherits from the [Attribute] class. When you have finished the properties and the constructor function of your class, you create a custom attribute class. Here is a custom attribute class that we must use in the database mapping job:

VB.NET
''' <summary> 
''' Custom attribute class to mapping the field in the data table.
''' (用于映射数据库中的表中的某一个字段的自定义属性类型)
''' </summary>
''' <remarks></remarks>
<AttributeUsage(AttributeTargets.Property, allowmultiple:=False, inherited:=True)>
Public Class DatabaseField : Inherits Attribute
 
    ''' <summary>
    ''' Get or set the name of the database field.
    ''' (获取或者设置数据库表中的字段的名称)
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns> 
    ''' <remarks></remarks>
    Public Property FieldName As String
 
    ''' <summary>
    ''' Construct a new database field attribute object.
    ''' (构造出一个新的数据库字段属性对象) 
    ''' </summary>
    ''' <param name="DbFiledName">Database field name.(数据库字段名称)</param>
    ''' <remarks></remarks>
    Sub New(DbFiledName As String)
        FieldName = DbFiledName
    End Sub
End Class     

2. Dynamic coding with reflection and custom attribute

Reflection is a powerful tool in .NET programming. Using Reflection lets you create dynamic code to meet the requirements of processing unknown object types. But as an old saying in Chinese: “鱼和熊掌不可兼得也”(English: You cannot sell the cow and drink the milk.), using reflection could make our coding job more easy but as a result of using reflection, we may lose a lot of the performance of our program.

To use reflection, we should follow these steps:

a. Doing reflection using keyword GetType

Doing a reflection operation is just as simple as easy using the keyword [GetType]. At first, we should create a Function object that we use to map the database table using Reflection. This function would look like this:

VB.NET
''' <summary>
''' Query a data table using Reflection.(使用反射机制来查询一个数据表)
''' </summary>
''' <typeparam name="ItemType">
''' Mapping schema to our data table.(对我们的数据表的映射类型)
''' </typeparam>
''' <param name="SQL">Sql 'SELECT' query statement.(Sql 'SELECT' 查询语句)</param>
''' <returns>The target data table.(目标数据表)</returns>
''' <remarks></remarks>
Public Function Query(Of ItemType)(SQL As String) As List(Of ItemType)   

The type parameter [ItemType] is our schema class that is used to map the database. And [SQL] is easily understood as a SELECT SQL query statement. Then we get the type information of our mapping schema class using the Reflection keyword: [GetType].

VB.NET
Dim Type As Type = GetType(ItemType)  

Now we get the type information of our mapping schema class. In the next step we get all of the properties in this mapping schema (as the term Field in the database).

b. Get the properties in our mapping schema

In this step, we define a variable to store the property information in our schema class, this variable looks like this:

VB.NET
Dim ItemTypeProperty = Type.GetProperties   

It’s just a simple task, isn’t it? And you also can get the methods and events information that is stored in this class type using different methods in the [Type] object like: Type.GetMethods, Type.GetEvents, and so on. In fact, you don’t need to define the type of the variable at its definition statement, because the Visual Basic compiler will do this job automatically when you compile your program, if you define a variable and assign the specific type value in the meantime. (You can’t define a variable as Object type at first because this operation will cause a lot of boxing that we can find in the IL disassembly code.)

c. Value assignment

Before we assign the value to our schema class, we should create an object instance of the type of our mapping schema. This is a dynamic proceeding that uses reflection:

VB.NET
'Create a instance of specific type: our record schema. 
Dim FillObject = Activator.CreateInstance(Type)  

As we have the properties information in our previous operation, we can use this information to work dynamically. Now we need a For loop to scan all of the properties in our schema class.

VB.NET
For i As Integer = 0 To ItemTypeProperty.Length - 1
[Property] = ItemTypeProperty(i)    

And then, we get the custom attribute from the meta data: [DatabaseField], and read from the custom attribute class that we created previously.

VB.NET
Attributes = [Property].GetCustomAttributes(GetType(DatabaseField), True) 

Next, we judge this property: whether it is our mapping field or not, by checking if this property has the custom attribute meta data or not.

VB.NET
If Not Attributes Is Nothing AndAlso Attributes.Length = 1 Then 

So this property is the property that we mapped the specific field in the database table, and then we assign the value read from the database to our mapping property.

VB.NET
CustomAttr = CType(Attributes(0), DatabaseField)

If Not CustomAttr Is Nothing AndAlso CustomAttr.FieldName.Length > 0 Then
   Ordinal = Reader.GetOrdinal(CustomAttr.FieldName)
   If Ordinal >= 0 Then              
       [Property].SetValue(FillObject, Reader.GetValue(Ordinal), Nothing)
   End If
End If

Now we have set the value of one of the properties in our mapping class object successfully, and we move to the next property to assign the value. It is a simply job: Next keyword statement.

Here, I post the whole code of this dynamic query function below:

''' <summary>
''' Query a data table using Reflection.(使用反射机制来查询一个数据表)
''' 
''' <typeparam name="ItemType">
''' Mapping schema to our data table.(对我们的数据表的映射类型)
''' 
''' <param name="SQL" />Sql 'SELECT' query statement.(Sql 'SELECT' 查询语句)
''' <returns>The target data table.(目标数据表)
''' <remarks>
Public Function Query(Of ItemType)(SQL As String) As List(Of ItemType)
    Dim Type As Type = GetType(ItemType)
   
    '[ConnectionString] is a compiled mysql connection string from our class constructor. 
    Dim MySql As MySqlConnection = New MySqlConnection(ConnectionString)
    Dim MySqlCommand As MySqlCommand = New MySqlCommand(SQL, MySql)
    Dim Reader As Global.MySql.Data.MySqlClient.MySqlDataReader = Nothing
    Dim NewList As New List(Of ItemType)
    Dim CustomAttr As DatabaseField
    Dim Ordinal As Integer = 0
    Dim Attributes As Object()
    Dim ItemTypeProperty = Type.GetProperties
    Dim [Property] As PropertyInfo
    Try
        MySql.Open()
        Reader = MySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
        While Reader.Read
        'When we call this function, the pointer will 
        'move to next line in the table automatically.   
          
            'Create a instance of specific type: our record schema. 
            Dim FillObject = Activator.CreateInstance(Type) 
            For i As Integer = 0 To ItemTypeProperty.Length - 1
                [Property] = ItemTypeProperty(i)
                Attributes = [Property].GetCustomAttributes(GetType(DatabaseField), True)
                If Not Attributes Is Nothing AndAlso Attributes.Length = 1 Then
                    CustomAttr = CType(Attributes(0), DatabaseField)
                    If Not CustomAttr Is Nothing AndAlso CustomAttr.FieldName.Length > 0 Then
                        Ordinal = Reader.GetOrdinal(CustomAttr.FieldName)
                        If Ordinal >= 0 Then
                            [Property].SetValue(FillObject, Reader.GetValue(Ordinal), Nothing)
                        End If
                    End If
                End If
            Next
            NewList.Add(FillObject)
        End While
        Return NewList 'Return the new table that we get
    Catch ex As Exception
    Finally
        If Not Reader Is Nothing Then Reader.Close()
        If Not MySqlCommand Is Nothing Then MySqlCommand.Dispose()
        If Not MySql Is Nothing Then MySql.Dispose()
    End Try
    Return Nothing
End Function

3. Create the schema class to map the table

Creating the schema class that we use to map the table is just a simple job using the custom attribute that we create!

VB.NET
Public Class TestRecord
   
    <DatabaseField("RegistryNumber")> Public Property ID As ULong
    <DatabaseField("GUID")> Public Property GUID As String
    <DatabaseField("DataModel")> Public Property Model As String
   
    Public Overrides Function ToString() As String
        Return String.Format("{0}, {1}, {2}", ID, GUID, Model)
    End Function
  
    Shared Narrowing Operator CType(e As TestRecord) As String
        Return String.Format("{0}, {1}, {2}", e.ID, e.GUID, e.Model)
    End Operator
End Class   

4. Testing

Now we can create a database server using MySQL and then test this code:

VB.NET
Dim MYSQL As Oracle.LinuxCompatibility.MySQL.Client.DbReflector =
    <MYSQL>http://localhost:1002/client?user=lab613%password=1234%database=gcmodeller</MYSQL>

Dim Stopwatch As New Stopwatch
Dim Table As List(Of TestRecord)

Call Stopwatch.Start()
Table = MYSQL.Query(Of TestRecord)("SELECT * FROM test;")
Call Stopwatch.Stop()
Call Console.WriteLine("Total time cost by query and reflection operation: {0} ms", Stopwatch.ElapsedMilliseconds)

For Each Record In Table
    Console.WriteLine(Record)
Next
Console.Read()          

OK, here we go. Click [Start] to run our test program. After a while, we fetch all of the data records from a data table in the MySQL database. But as you can see, the dynamic code that is created from reflection is too slow compared with ordinary coding. I hope you can resolve this performance problem.

Test output

Total time cost by query and reflection operation: 116 ms
1, {CED4E2AF-E63D-4E5E-A365-210740EB2964}, i have no idea
2, {3A43A713-5A0A-463D-95D0-618461140E11}, xie.guigang@gmail.com
3, {A6715C38-B559-4462-891D-4C700EC0D342}, 1234567890
4, {369C6AB8-1111-4578-8B12-53C6F8E7EE39}, 9876543210
5, {8EA2183B-416B-48BD-A837-B05A11448EFA}, abcd
6, {6D4C8D12-B6A1-4C6F-9EB7-622E68216035}, 1234
7, {5F633B6A-8111-4AE4-83A1-9F4BBA1F387F}, google
8, {E49DAAE6-314B-4649-8809-21FAC0457B13}, baidu
9, {C68D1744-6D60-44EE-BC03-65E0CE17CF85}, none sense
10, {A063370C-CA5F-43FB-B7C3-F45CFA6657BD}, yaaaaah 

Part 2: The MySQL database mapping job

Although reflection works slowly, the custom attributes used by reflection makes the definition of a class object more interesting. It looks like we can create a table directly from the class definition, here is a more complex example class definition:

VB.NET
<TableName("Test")>
Public Class TestRecord
    <DataType(MySqlDbType.BigInt)> <AutoIncrement> <NotNULL> _
         <Unsigned> <Unique> <PrimaryKey> <DatabaseField("RegistryNumber")>
    Public Property ID As ULong
    <NotNULL> <DataType(MySqlDbType.VarChar, "45")> _
      <Unique> <DatabaseField("GUID")> Public Property GUID As String
    <DataType(MySqlDbType.LongText)> <NotNULL> <DatabaseField("DataModel")>
    Public Property Model As String
    ''' <summary>
    ''' This property is not a field in the table because
    ''' it has no custom attribute of type DatabaseField
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ImNotAField As String
    Public Overrides Function ToString() As String
        Return String.Format("{0}, {1}, {2}", ID, GUID, Model)
    End Function
    Shared Narrowing Operator CType(e As TestRecord) As String
        Return String.Format("{0}, {1}, {2}", e.ID, e.GUID, e.Model)
    End Operator
End Class 

If we need to create a new table, we don’t need to write any of the ‘CREATE TABLE’ SQL text because this SQL text is created automatically through reflection using the custom attributes that we defined on the class object. This feature looks great, so how does it work? In order to introduce how this feature works, let’s see how to use this feature. It is a simple job:

VB.NET
Dim Schema As Oracle.LinuxCompatibility.MySQL.Client.Reflection.Schema.Table = GetType(TestRecord)
Dim SQL As String = Oracle.LinuxCompatibility.MySQL.Client.Reflection.SQL.CreateTableSQL.FromSchema(Schema)
Console.WriteLine(SQL)  

First we use a Table object to get the table schema defined on the custom attributes of the class, then generate the CREATE TABLE SQL text using a shared function in the CreateTableSQL helper object from the table schema that we got previously. And then we use this SQL text to create a table in the database. It is just a happy job, no more code needs to be written. But in order to realize this feature, it is not an easy coding job. So let’s see how it works:

1. Create a custom attribute

In order to work this way, we need more custom attributes, and it is a simple job. Please see the code using Object Browser in the namespace of Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes in my uploaded solution file. The custom attributes we need are define there. Please notice that I make all other custom attributes inherit from the class DbAttribute to makes the Class Diagram more friendly in Visual Studio, so except to organize the relationships between classes, the DbAttribute class has no more function, never mind. So as you can see, some classes defined in this namespace are just empty as DbAttribute, because some field attributes in MySQL are just Boolean type values, so no more information is needed to write the class of Unique, PrimaryKey, NotNULL, Binary, Unsigned, ZeroFill, and AutoIncrement. Just a flag attribute indicates that the field property has this MySQL field attribute or not. The attribute class TableName usage method is different from other attributes, it just works on the class definition. This difference comes from the attribute of this class definition: <AttributeUsage(AttributeTargets.Class, allowmultiple:=False, inherited:=True)>, and other attribute classes are declare as AttributeTargets.Property. So this database field attribute only works on the class definition, and other attribute classes just work on property definition. The usage definition of a custom attribute can be Method, Field (Class member), Module, and more, this depends on the problem you want the code to work in.

2. Create some helper class

In order to make work more easy and match the OO (object-oriented) feature of the Visual Basic language, I created another class object to store the filed attributes defined in this namespace. Here is part of the Field class definition:

VB.NET
Public Class Field
    Public FieldName As String
    Public Unique As Boolean
    Public PrimaryKey As Boolean
    Public DataType As _
        Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes.MySqlDbType
    
    Public Unsigned As Boolean
    Public NotNull As Boolean
    Public AutoIncrement As Boolean
    Public ZeroFill As Boolean
    Public Binary As Boolean
    Public [Default] As String = String.Empty
    ''' <summary>
    ''' The property information of this custom database field attribute. 
    ''' </summary>
    ''' <remarks></remarks>
    Public [PropertyInfo] As PropertyInfo   

This class includes all of the field attributes in the MySQL database that I could find. As I have noted some custom attribute classes are defined empty, just works as a Boolean flag and assigns the value in the corresponding member in this class.

In order to make the parsing job more easy and clean, I reconstructed the custom attribute parsing procedure as a new function written as the previous function (name: Function Query(Of ItemType)(SQL As String) As List(Of ItemType)) code that I posted above. Here it is the new function that I reconstructed:

VB.NET
''' <summary>
''' Get the specific type of custom attribute from a property.
''' (从一个属性对象中获取特定的自定义属性对象)
''' </summary> 
''' <typeparam name="T">The type of the custom attribute.(自定义属性的类型)</typeparam>
''' <param name="Property">Target property object.(目标属性对象)</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetAttribute(Of T As Attribute)([Property] As PropertyInfo) As T
    Dim Attributes As Object() = [Property].GetCustomAttributes(GetType(T), True)
    If Not Attributes Is Nothing AndAlso Attributes.Length = 1 Then
        Dim CustomAttr As T = CType(Attributes(0), T)
        If Not CustomAttr Is Nothing Then
            Return CustomAttr
        End If
    End If
    Return Nothing
End Function 

You can find out that a lot of value assignment statements will appear in this class’ construct method (name: Shared Widening Operator CType([Property] As PropertyInfo) As Field) like:

VB.NET
Field.Unique = Not GetAttribute(Of Unique)([Property]) Is Nothing 

Yes, many of the field attributes just work as a Boolean flag.

3. Create table class to store schema information

From the analysis of the CREATE TABLE SQL command text, we can find out that some basic property is necessary to create a table: TableName, Fields definition. So the Table class contains two members (TableName and Fields). And other members are not necessary but required from you to make the SQL more effective and execute faster, like indexing and so on. Here I post all of the members that are defined in the Table class:

VB.NET
''' <summary>
''' The table schema that we define on the custom attributes of a Class.
''' </summary>
''' <remarks></remarks>
Public Class Table
    Public Property TableName As String
    Public UniqueFields As New List(Of String)
    Public PrimaryField As New List(Of String)
    Public Fields As New List(Of Field)
    ''' <summary>
    ''' The index field when execute the update/delete sql.
    ''' </summary>
    ''' <remarks>
    ''' Long/Integer first, then the Text is second, the primary key is the last consideration.
    ''' </remarks>
    Friend Index As String, IndexProperty As PropertyInfo
    Public SchemaType As System.Type

In the constructor of this class, we use a method GetSchema to get the necessary information to create the table:

VB.NET
Private Sub GetSchema(Schema As Type)
    Dim ItemProperty = Schema.GetProperties
    Dim Field As Field
    Dim Index2 As String = String.Empty
    Dim IndexProperty2 As PropertyInfo = Nothing
    
    TableName = GetTableName(Schema)
    For i As Integer = 0 To ItemProperty.Length - 1
        'Parse the field attribute from the ctype operator, this property must have a 
        DatabaseField custom attribute to indicate that it is a database field. 
        Field = ItemProperty(i) 
        If Not Field Is Nothing Then
            Call Fields.Add(Field)
            If Field.PrimaryKey Then
                PrimaryField.Add(Field.FieldName)
            End If
            If Field.Unique Then
                UniqueFields.Add(Field.FieldName)
                If Commonly.Numerics.IndexOf(Field.DataType) > -1 AndAlso Field.PrimaryKey Then
                    Index = Field.FieldName
                    IndexProperty = ItemProperty(i)
                End If
                Index2 = Field.FieldName
                IndexProperty2 = ItemProperty(i)
            End If
        End If
    Next 
    Call Indexing(Index2, IndexProperty2, ItemProperty)
    'If we can not found a index from its unique field, then we indexing from its primary key.
End Sub   

The method is similar to the function Query(Of T) that I introduced in the previous section: GetType to do reflection on a specific type, then get all of its property information using Type.GetProperties, and then parse the property custom attribute to get the information we need, at last, we get the table schema information defined on the specific type object.

Finally, through the previous work, we get all necessary information to create a table. Now we can start to generate the CREATE TABLE SQL using the table schema that we have. Here is the SQL generation function, it is a simple job: this generation job is just a string formatting job:

VB.NET
''' <summary>
''' Generate the 'CREATE TABLE' sql command.
''' (生成'CREATE TABLE' sql命令)
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function FromSchema(Schema As Table) As String
    Dim sBuilder As StringBuilder = New StringBuilder(1024)
    Dim sBuilder2 As StringBuilder = New StringBuilder(128)
    sBuilder.AppendFormat(CREATE_TABLE & vbCrLf, Schema.TableName)
    
    Dim Fields = Schema.Fields
    For i As Integer = 0 To Fields.Count - 1
        sBuilder.AppendLine("  " & Fields(i).ToString & " ,")
    Next
    Dim PrimaryField = Schema.PrimaryField
    For Each PK As String In PrimaryField
        sBuilder2.AppendFormat("`{0}`, ", PK)
    Next
    sBuilder2.Remove(sBuilder2.Length - 2, 2)
    sBuilder.AppendFormat(PRIMARY_KEY & vbCrLf, sBuilder2.ToString)
    Dim UniqueFields = Schema.UniqueFields
    If UniqueFields.Count > 0 Then
        sBuilder.Append(" ,")
    End If
    For Each UniqueField As String In UniqueFields
        sBuilder.AppendLine(UNIQUE_INDEX.Replace("%s", UniqueField) & " ,")
    Next
    sBuilder.Remove(sBuilder.Length - 3, 3)
    sBuilder.Append(");") 'End of the sql statement
    Return sBuilder.ToString
End Function  

I also create a SQL text generator helper class to make the coding work more easy. You can find out the helper class in the namespace Oracle.LinuxCompatibility.MySQL.Client.Reflection.SQL using Object Browser in Visual Studio. The helper class includes INSERT, UPDATE, DELETE commands, and the helper class is functional and works as the CREATE TABLE helper class. This namespace only contains these commands but is enough for our database operation coding. 

4. The database mapping job

a. The class components

Now we have a helper class and function to map a table in a MySQL database, and we can start our mapping job now. In my opinion, the mapping job should contain the table creation job (DDL), data operation job (DML), so the mapping class contains four SQL helper classes (CREATE, INSERT, UPDATE, DELETE) that we have created previously (CREATE helper class appears as a shared method, so it does not exist as a class member):

VB.NET
''' <summary>
''' 'DELETE' sql text generator of a record that type of schema.
''' </summary>
''' <remarks></remarks>
Dim DeleteSQL As SQL.Delete(Of Schema)
''' <summary>
''' 'INSERT' sql text generator of a record that type of schema.
''' </summary>
''' <remarks></remarks>
Dim InsertSQL As SQL.Insert(Of Schema)
''' <summary>
''' 'UPDATE' sql text generator of a record that type of schema.
''' </summary>
''' <remarks></remarks>
Dim UpdateSQL As SQL.Update(Of Schema) 

And this class should have the ability to exchange data with the MySQL database server, so we have a MySQL client encapsulation class that provides the functions that we need.

VB.NET
Dim WithEvents MySQL As MySQL 

And then we need an object to store data in the database, so we have a list object:

VB.NET
''' <summary>
''' DataSet of the table in the database.
''' (数据库的表之中的数据集)
''' </summary>
''' <remarks></remarks>
Friend _ListData As New List(Of Schema)(capacity:=1024)
''' <summary>
''' DataSet of the table in the database. Do not edit the data directly from this property...
''' (数据库的表之中的数据集,请不要直接在这个属性之上修改数据)
''' </summary>
''' <remarks></remarks>
ReadOnly Property ListData As List(Of Schema)
    Get
        Return _ListData
    End Get
End Property

A property to output the error message returned from the MySQL server:

VB.NET
''' <summary>
''' The error information that come from MYSQL database server.
''' (来自于MYSQL数据库服务器的错误信息)
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
Public Property ErrorMessage As String  

Oh, and we also need a SQL transaction command text storage object to decrease the CPU and networking traffic usage of the server.

VB.NET
''' <summary>
''' The sql transaction that will be commit to the mysql database.
''' (将要被提交至MYSQL数据库之中的SQL事务集)
''' </summary>
''' <remarks></remarks>
Dim Transaction As StringBuilder = New StringBuilder(2048)

So now we have all we need, shall we start work? Yes, we are almost done but we don’t have a class constructor to get the connection information to the database server. So we add this constructor to it:

VB.NET
''' <summary>
''' Initialize the mapping from a connection object
''' </summary>
''' <param name="e"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Widening Operator CType(e As ConnectionHelper) As DataTable(Of Schema)
    Return New DataTable(Of Schema) With {.MySQL = e}
End Operator
''' <summary>
''' Initialize the mapping from a connection string
''' </summary>
''' <param name="e"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Widening Operator CType(e As String) As DataTable(Of Schema)
    Return New DataTable(Of Schema) With {.MySQL = e}
End Operator
''' <summary>
''' Initialize the mapping from a connection string
''' </summary>
''' <param name="e"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Widening Operator CType(e As Xml.Linq.XElement) As DataTable(Of Schema)
    Return New DataTable(Of Schema) With {.MySQL = CType(e, ConnectionHelper)}
End Operator 
b. The mapping procedures

First, we get the schema information in the constructor method of the class, this job seems fancy, a very simply job, and gives each SQL generation helper a copy of the table schema so that the helper classes can work properly on the object instance of the specific class type Schema:

VB.NET
Friend Sub New()
    'Start reflection and parsing the table structure information.
    TableSchema = GetType(Schema)
    'Initialize the sql generator using the table structure
    'information that parse from the custom attribut of a class object.
    DeleteSQL = TableSchema  
    InsertSQL = TableSchema
    UpdateSQL = TableSchema
End Sub   

And then to get data in the database, we use an improved version of the Query(Of T) function that we wrote above in part 1. It is no more than the table schema parsing job, and uses the table schema that is stored in the module variable directly. It is just a data fetch job from the dataset got from the query assigned to the user object.

VB.NET
''' <summary>
''' Load the data from database server. Please notice that: every time you 
''' call this function, the transaction will be commit to the database server in.
''' (从数据库服务器之中加载数据,请注意:每一次加载数据都会将先前的所积累下来的事务提交至数据库服务器之上)
''' </summary>
''' <param name="Count">
''' The count of the record that will be read from the server. Notice: Zero or negative is stands for 
''' load all records in the database.
''' (从数据库中读取的记录数目。请注意:值0和负数值都表示加载数据库的表中的所有数据)
''' </param>
''' <remarks></remarks>
Public Sub Fetch(Optional Count As Integer = 100)
    Call Me.Commit()  '
    If Count <= 0 Then  'Load all data when count is zero or negative.
        _ListData = Query(String.Format("SELECT * FROM {0};", TableSchema.TableName))
        p = _ListData.Count
    Else
        Dim NewData As List(Of Schema)
        NewData = Query(String.Format("SELECT * FROM {0} LIMIT {1},{2};", _
                         TableSchema.TableName, p, Count))
        _ListData.AddRange(NewData)
        p += Count  'pointer move next block.
    End If
End Sub  

In order to decrease the data traffic of the dataset, this function has two modes of querying data: query all lines or query a limited number of records. The Select SQL of the query is just simple: “select * from <table>;” and limit the number in MySQL using the limit keyword, the syntax is like:

SQL
Select * from <table> limit <start Line>,<record counts>; 

Next is the DML operation: Delete, Update, Insert of the row in the table. These three functions have a similar procedure: using the helper class to generate a SQL text and then store the SQL to the transaction, then do the specific operation in the ListData member. 

In order to get the data in the specific property in our input record object, we use reflection, it is just simple:

VB.NET
Dim value As String = Field.PropertyInfo.GetValue(Record, Nothing).ToString 

And then we create a value list, after the String.Format using this value list, finally we get a SQL command of the specific operation on a specific record object instance.

The last thing is to commit the transaction to the database to make the change on the database permanent using the Commit method:

VB.NET
''' <summary>
''' Commit the transaction to the database server to make the change permanently.
''' (将事务集提交至数据库服务器之上以永久的修改数据库) 
''' </summary>
''' <returns>The transaction commit is successfully or not.(事务集是否被成功提交)</returns>
''' <remarks></remarks>
Public Function Commit() As Boolean
    If Transaction.Length = 0 Then Return True 'No transaction will be commit to database server.
    If MySQL.CommitTransaction(Transaction.ToString) Then
        Call Transaction.Clear()  'Clean the previous transaction when the transaction commit is successfully. 
        Return True
    Else 'the transaction commit failure.
        ErrorMessage = MySQL.ErrMsg
        Return False
    End If
End Function 

5. Testing

All of my testing code can be found in the libarytest project:

VB.NET
Sub DataTableTest()
    Dim Table As Oracle.LinuxCompatibility.MySQL.Client.Reflection.DataTable(Of TestRecord) = 
        <url>http://localhost:1002/client?user=lab613%password=1234%database=gcmodeller</url>
    Call Table.Create()
    Table.Fetch()  'No data was fetched
    Dim Record = New TestRecord With {.GUID = "{1AEBD086-50F7-43E8-A6DC-8F4A9EA430ED}", .ID = 1, .Model = "1234567890"}
    Table.Insert(Record)
    Record = New TestRecord With {.GUID = "{3A43A713-5A0A-463D-95D0-618461140E11}", .ID = 2, .Model = "xie.guigang@gmail.com"}
    Table.Insert(Record)
    Record = Table.ListData.First
    Record.Model = "I have no idea!"
    Table.Update(Record)
    Table.Delete(Table.ListData.First)
    If Table.Commit() = False Then
        Console.WriteLine(Table.ErrorMessage)
    Else
        Console.WriteLine("Transaction commit to database successfully!")  'One record in the database
    End If
End Sub   

Console output:

SQL
CREATE  TABLE `Test` (
<pre>  `RegistryNumber` BigInt UNSIGNED NOT NULL AUTO_INCREMENT  ,
  `GUID` VarChar (45) NOT NULL  ,
  `DataModel` LongText NOT NULL  ,
PRIMARY KEY (`RegistryNumber`)
 ,UNIQUE INDEX `RegistryNumber_UNIQUE` (`RegistryNumber` ASC) ,
UNIQUE INDEX `GUID_UNIQUE` (`GUID` ASC) );
INSERT INTO `Test` (`RegistryNumber`, `GUID`, `DataModel`) VALUES ('1', '{1AEBD0
86-50F7-43E8-A6DC-8F4A9EA430ED}', '1234567890');
INSERT INTO `Test` (`RegistryNumber`, `GUID`, `DataModel`) VALUES ('2', '{3A43A7
13-5A0A-463D-95D0-618461140E11}', 'xie.guigang@gmail.com');
UPDATE `Test` SET `RegistryNumber`='1', `GUID`='{1AEBD086-50F7-43E8-A6DC-8F4A9EA
430ED}', `DataModel`='I have no idea!' WHERE `RegistryNumber`='1';
DELETE FROM `Test` WHERE `RegistryNumber`='1';
Transaction commit to database successfully!
Total time cost by query and reflection operation: 108 ms
2, {3A43A713-5A0A-463D-95D0-618461140E11}, xie.guigang@gmail.com   

Points of interest

As you can see in my coding job, a lot of CType operator override functions appear. In my opinion the CType operator is an inline function, so this attribute will make your type conversion job faster and your code more clean as we override the value assignment operator “=”. So from this point of view we can know that the value assignment operation has two ways in Visual Basic: value copy and type conversion. To override the operator = just override the Boolean judgment operator in Visual Basic, and if we want to override the value assignment operator we should override the operator CType. This is a normal method and not a boxing operation as we couldn’t find any boxing operator in its MSIL disassembly code, so this override does not decrease the efficiency of the code and it makes the code clean.

Code improvements

1. More general coding

The SQL command text syntax and the data type are little different between different DBMSs, the syntax defined in this module is only suited for the MySQL DBMS. And I believe that this module will work well on other DBMSs if we do some modifications to it. But it’s still not quite smart, and I am trying to work on it to make it work well on all known DBMSs without any modifications on the code.

2. Make it functional on multiple user situations

As you can see, the Delete, Insert, Update functions only make the change on the table in the memory and the modification does not happen immediately on the server. The change will be made when committing the transaction to the server. So if more than one users modifies the same table using this module, some changes will be lost. This situation is terrible. So this module does not work well on multiple user situations, and I am working hard on it and trying to solve the problem.

3. The field attributes combination in MySQL

Some data types and field attributes are not allowed to be combined with each other in MySQL like the “Text Unsigned” field attribute combination is illegal. This combination relationship is quite complex and I am working on my code to resolve this combination problem. So before the problem is solved, when you are using this module to create a schema, please read the MySQL syntax manual to make sure the combination of your field attribute in the schema is correct.

Additional

If you want to use Visual Basic .NET to connect to a MySQL database server, you should install the MySQL .NET Connector first. You can download the connector library installer (MSI package) from the MySQL official website (install on Ubuntu using apt-get command: sudo apt-get install libmysql6.4-cil or search "MySQL database connector for CLI" and install in Ubuntu software center). This library assembly was developed on Microsoft Visual Studio 2013 Preview, MySQL 5.6, and debugged on Windows 7 (.NET Framework 4.5)/Ubuntu 13.04(Novell mono 2.1) successfully.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead PANOMIX
China China
He is good and loves VisualBasic! Senior data scientist at PANOMIX


github: https://github.com/xieguigang

Comments and Discussions

 
-- There are no messages in this forum --