Contents
- Introduction and background
- How it works?
- Part 1: The reflection basic knowledge
- Create a custom attribute
- Dynamic coding with reflection and custom attribute
- Doing reflection using keyword GetType
- Get the properties in our mapping schema
- Value assignment
- Create the schema class to map the table
- Testing
- Part 2: The MySQL database mapping job
- Create custom attribute
- Create a helper class
- Create
table class to store schema information
- The database mapping job
- The class components
- The mapping procedures
- Testing
- Points of interest
- Code improvements
- More general coding way
- Make it functional on multiple user situations
- 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:
<AttributeUsage(AttributeTargets.Property, allowmultiple:=False, inherited:=True)>
Public Class DatabaseField : Inherits Attribute
Public Property FieldName As String
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:
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].
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:
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:
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.
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.
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.
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.
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!
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:
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:
<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
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:
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:
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
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:
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:
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:
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)
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:
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
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)
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:
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(");")
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):
Dim DeleteSQL As SQL.Delete(Of Schema)
Dim InsertSQL As SQL.Insert(Of Schema)
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.
Dim WithEvents MySQL As MySQL
And then we need an object to store data in the database, so we have a list object:
Friend _ListData As New List(Of Schema)(capacity:=1024)
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:
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.
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:
Public Shared Widening Operator CType(e As ConnectionHelper) As DataTable(Of Schema)
Return New DataTable(Of Schema) With {.MySQL = e}
End Operator
Public Shared Widening Operator CType(e As String) As DataTable(Of Schema)
Return New DataTable(Of Schema) With {.MySQL = e}
End Operator
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:
Friend Sub New()
TableSchema = GetType(Schema)
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.
Public Sub Fetch(Optional Count As Integer = 100)
Call Me.Commit()
If Count <= 0 Then
_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
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:
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:
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:
Public Function Commit() As Boolean
If Transaction.Length = 0 Then Return True
If MySQL.CommitTransaction(Transaction.ToString) Then
Call Transaction.Clear()
Return True
Else
ErrorMessage = MySQL.ErrMsg
Return False
End If
End Function 5. Testing
All of my testing code can be found in the libarytest project:
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()
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!")
End If
End Sub Console output:
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.
