快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

通过SQL自动生成的Visual Basic源代码

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

介绍

最近,我为我的公司开发了一个域名拍卖平台服务器程序。数据库数据更新所需的部分代码,例如,我们有一个本地WHOIS数据库,用于验证用户提交的域名拍卖订单,并且当用户通过我们的电子邮件自动登顶服务器提交其订单时,则该订单中的域名商品将通过查询http://who.is/whois数据库API 自动进行验证,然后在用户验证认为发送到域的whois电子邮件的安全代码后,将生成订单,服务器将同时更新订单数据库和whois数据库

由于who.is/whois数据库的表中有很多数据库字段whois_registry,因此我必须在一个类中编写很多属性才能在服务器程序和MySQL数据库之间映射表。因此,我决定开发一个小型文件,以自动生成数据库映射操作的源代码,使我的编码工作愉快。:-) 🙂 🙂 🙂 🙂 🙂

 

这个怎么运作?

图片1

 

解析创建表定义

''' <summary>
''' Loading the table schema from a specific SQL doucment.
''' </summary>
''' <param name="Path"></param>
''' <returns></returns>
Public Function LoadSQLDoc(Path As String) As Reflection.Schema.Table()

从MySQL工作台数据导出工具中转出的SQL模式是格式正确的文档。每个表均以以下格式定义,例如:

--
-- Table structure for table `server`
--

DROP TABLE IF EXISTS `server`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `server` (
  `guid` int(11) NOT NULL COMMENT 'configuration entry value',
  `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
  PRIMARY KEY (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
/*!40101 SET character_set_client = @saved_cs_client */;

每个定义都从一行开始:CREATE TABLE `tbl_name`并以引擎类型和表注释结尾,因此我们可以使用regex表达式来解析每个表定义语句

''' <summary>
''' Parsing the create table statement in the SQL document.
''' </summary>
Const SQL_CREATE_TABLE As String = "CREATE TABLE `.+?` \(.+?PRIMARY KEY \(`.+?`\).+?ENGINE=.+?;"

此表达式在导出模式转储上正常工作。

有了CREATE TABLE定义语句后,就可以开始分析语句中字段的属性了:

在模式转储文档中,每个归档定义均以字段名称开头,然后跟随数据类型,然后是字段属性列表,最后是有关字段的注释。

`<field_name>` <data_type> [field_properties COMMENT 'comments',

字段定义可以如上所述,字段定义语句看起来很复杂,但是幸运的是,字段名称和字段数据类型在定义中不允许有空格,因此我们可以轻松地获取字段中每个字段的两个基本属性表只是使用String.Split功能。也可以使用regex表达式轻松解析有关每个字段的注释:

''' <summary>
''' Regex expression for parsing the comments of the field in a table definition.
''' </summary>
Const FIELD_COMMENTS As String = "COMMENT '.+?',"

 

创建模式映射

现在,在创建表架构之前,MySQL和VisualBasic之间的数据类型应进行映射。由于每个字段定义中的数据类型都易于解析,因此MySQL和VisualBasic之间的数据类型很容易在SQL源代码和VisualBasic源代码之间进行映射:

#Region "Mapping the MySQL database type and visual basic data type"
 
    ''' <summary>
    ''' Mapping the MySQL database type and visual basic data type
    ''' </summary>
    ''' <param name="TypeDef"></param>
    ''' <returns></returns>
    Private Function InternalCreateDataType(TypeDef As String) As Reflection.DbAttributes.DataType

        Dim Type As Reflection.DbAttributes.MySqlDbType
        Dim Parameter As String = ""

        If Regex.Match(TypeDef, "int\(\d+\)").Success Then

            Type = Reflection.DbAttributes.MySqlDbType.Int64
            Parameter = InternalGetNumberValue(TypeDef)

        ElseIf Regex.Match(TypeDef, "varchar\(\d+\)").Success Then

            Type = Reflection.DbAttributes.MySqlDbType.VarChar
            Parameter = InternalGetNumberValue(TypeDef)

        ElseIf Regex.Match(TypeDef, "double").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.Double

        ElseIf Regex.Match(TypeDef, "datetime").Success OrElse Regex.Match(TypeDef, "date").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.DateTime

        ElseIf Regex.Match(TypeDef, "text").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.Text

        Else

            'More complex type is not support yet, but you can easily extending the mapping code at here
            Throw New NotImplementedException($"Type define is not support yet for    {NameOf(TypeDef)}   >>> ""{TypeDef}""")

        End If

        Return New Reflection.DbAttributes.DataType(Type, Parameter)
    End Function

    Private Function InternalToDataType(TypeDef As Reflection.DbAttributes.DataType) As String

        Select Case TypeDef.MySQLType

            Case Reflection.DbAttributes.MySqlDbType.BigInt,
                 Reflection.DbAttributes.MySqlDbType.Int16,
                 Reflection.DbAttributes.MySqlDbType.Int24,
                 Reflection.DbAttributes.MySqlDbType.Int32,
                 Reflection.DbAttributes.MySqlDbType.MediumInt
                Return " As Integer"

            Case Reflection.DbAttributes.MySqlDbType.Bit,
                 Reflection.DbAttributes.MySqlDbType.Byte
                Return " As Byte"

            Case Reflection.DbAttributes.MySqlDbType.Date,
                 Reflection.DbAttributes.MySqlDbType.DateTime
                Return " As Date"

            Case Reflection.DbAttributes.MySqlDbType.Decimal
                Return " As Decimal"

            Case Reflection.DbAttributes.MySqlDbType.Double,
                 Reflection.DbAttributes.MySqlDbType.Float
                Return " As Double"

            Case Reflection.DbAttributes.MySqlDbType.Int64
                Return " As Long"

            Case Reflection.DbAttributes.MySqlDbType.UByte
                Return " As UByte"

            Case Reflection.DbAttributes.MySqlDbType.UInt16,
                 Reflection.DbAttributes.MySqlDbType.UInt24,
                 Reflection.DbAttributes.MySqlDbType.UInt32
                Return " As UInteger"

            Case Reflection.DbAttributes.MySqlDbType.UInt64
                Return " As ULong"

            Case Reflection.DbAttributes.MySqlDbType.LongText,
                 Reflection.DbAttributes.MySqlDbType.MediumText,
                 Reflection.DbAttributes.MySqlDbType.String,
                 Reflection.DbAttributes.MySqlDbType.Text,
                 Reflection.DbAttributes.MySqlDbType.TinyText,
                 Reflection.DbAttributes.MySqlDbType.VarChar,
                 Reflection.DbAttributes.MySqlDbType.VarString
                Return " As String"

            Case Else
                Throw New NotImplementedException($"{NameOf(TypeDef)}={TypeDef.ToString}")
        End Select
    End Function
#End Region

我只是在SQL源代码和VisualBasic源代码之间映射了几种最常用的数据类型,MySQL中的其他一些复杂数据类型也可以通过扩展上面显示的两个函数中的select语句来轻松映射。

生成VisualBasic源代码

每个表在Visual Basic中可以绝对是一个类,并且我已经在VisualBasic中为映射类定义了一个通用结构:

Public MustInherit Class SQLTable

    Public MustOverride Function GetInsertSQL() As String
    Public MustOverride Function GetUpdateSQL() As String
    Public MustOverride Function GetDeleteSQL() As String

    Public Overrides Function ToString() As String
        Return GetInsertSQL()
    End Function

End Class

由于创建表和选择查询是开发人员在数据库及其程序中手动创建的,因此该代码仅在MySQL中生成数据库更新方法:INSERT, UPDATE and DELETE

现在,我们可以从函数开始在visualbasic中创建一个类对象:

''' <summary>
''' Generate the class object definition to mapping a table in the mysql database.
''' </summary>
''' <param name="Table"></param>
''' <param name="DefSql"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GenerateTableClass(Table As Reflection.Schema.Table, DefSql As String) As String

一个。在VisualBasic中生成类对象的基本结构

引用有关定义类的visualbasic语法:

''' <summary>
''' Comments
''' </summary>
Public Class <class_name> : Inherits BaseClass

   ''' <summary>
   ''' Comments
   ''' </summary>
   <CustomAttributes> Public Property Name As DataType

   ''' <summary>
   ''' Comments
   ''' </summary>
   Public Const Name As DataType = <InitValue>

   ''' <summary>
   ''' Comments
   ''' </summary>
   Public Function Name() As DataType
   End Function

End Class

这样我们就可以使用以下代码轻松生成xml注释:

Call CodeGenerator.AppendLine("''' <summary>")
Call CodeGenerator.AppendLine("''' " & Field.Comment)
Call CodeGenerator.AppendLine("''' </summary>")
Call CodeGenerator.AppendLine("''' <value></value>")

使用以下代码生成类头:

Call CodeGenerator.AppendLine($"<Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes.TableName(""{Table.TableName}"")>")
Call CodeGenerator.AppendLine($"Public Class {Table.TableName}: Inherits Oracle.LinuxCompatibility.MySQL.Client.SQLTable")

使用代码生成属性:

Call CodeGenerator.Append(InternalCreateAttribute(Field, IsPrimaryKey:=Table.PrimaryField.Contains(Field.FieldName))) 'Apply the custom attribute on the property
Call CodeGenerator.Append("Public Property " & Field.FieldName)      'Generate the property name
Call CodeGenerator.Append(InternalToDataType(Field.DataType))        'Generate the property data type

 

Private Function InternalCreateAttribute(Field As Reflection.Schema.Field, IsPrimaryKey As Boolean) As String
    
    Dim Code As String = $"    <DatabaseField(""{Field.FieldName}"")"

    If IsPrimaryKey Then
        Code &= ", PrimaryKey"
    End If

    If Field.AutoIncrement Then
        Code &= ", AutoIncrement"
    End If

    Code &= $", DataType({DataTypeFullNamesapce}.{Field.DataType.MySQLType.ToString}{If(String.IsNullOrEmpty(Field.DataType.ParameterValue), "", ", """ & Field.DataType.ParameterValue & """")})"
    Code &= "> "

    Return Code
End Function

 

创建3个更新方法

Call CodeGenerator.AppendLine("#Region ""Public SQL Interface""")
Call CodeGenerator.AppendLine("#Region ""Interface SQL""")
Call CodeGenerator.AppendLine(Internal_INSERT_SQL(Table))
Call CodeGenerator.AppendLine(Internal_DELETE_SQL(Table))
Call CodeGenerator.AppendLine(Internal_UPDATE_SQL(Table))
Call CodeGenerator.AppendLine("#End Region")
Call CodeGenerator.AppendLine("    Public Overrides Function GetDeleteSQL() As String")
Call CodeGenerator.AppendLine(Internal_DELETE_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("    Public Overrides Function GetInsertSQL() As String")
Call CodeGenerator.AppendLine(Internal_INSERT_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("    Public Overrides Function GetUpdateSQL() As String")
Call CodeGenerator.AppendLine(Internal_UPDATE_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("#End Region")

我想在自动生成的代码中使结构更清楚,以便将SQL定义和函数调用分开:

为了更好的性能,将调用SQL的方法定义为共享的只读变量,例如:

Private Function Internal_DELETE_SQL(Schema As Reflection.Schema.Table) As String
    Dim SqlBuilder As StringBuilder = New StringBuilder("    Private Shared ReadOnly DELETE_SQL As String = <SQL>%s</SQL>")
    Call SqlBuilder.Replace("%s", Reflection.SQL.SqlGenerateMethods.GenerateDeleteSql(Schema))

    Return SqlBuilder.ToString
End Function

如何INSERT, UPDATE and DELETE SQL从MySQL表方案创建一个方法,该方法可以在我的早期代码项目文章(关于在VisualBasic和MySQL数据库之间映射类对象)上进行回顾::-)

引用:
Visual Basic使用反射来映射MySQL数据库中的数据表
http://www.codeproject.com/Articles/638976/Visual-Basic-Using-Reflection-to-Map-DataTable-in

 

为了在VisualBasic代码中调用SQL语句,我们仅使用String.Format函数使用类对象属性生成完整的SQL语句,示例如下:

Private Function Internal_INSERT_SQL_Invoke(Schema As Reflection.Schema.Table) As String

    Dim SqlBuilder As StringBuilder = New StringBuilder("        ")
    Call SqlBuilder.Append("Return String.Format(INSERT_SQL, ")
    Call SqlBuilder.Append(String.Join(", ", (From Field In Schema.Fields Select InternalGetFieldValueInvoke(Field)).ToArray))
    Call SqlBuilder.Append(")")

    Return SqlBuilder.ToString
End Function

使用代码

步骤1. MySQL数据库架构转储

图片2

我们可以使用MySQL工作台[数据导出]工具轻松地创建MySQL数据库架构转储。进入数据导出界面并选择目标数据库后,我们可以设置转储选项:

图片3

导出到独立文件,然后设置转储文件路径
在单个事务中创建转储
跳过表数据
 🙂

 

步骤2.将SQL语句转换为VisualBasic源代码

将SQL文件转换为VisualBasic源代码只需两个简单的步骤:

转换SQL文件并获取VisualBasic代码
' Convert the SQL file into a visualbasic source code
Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)

然后,当我们将代码保存到* .vb源文件中时,我们刚刚完成了一项艰巨的任务。

' Save the vb source code into a text file
doc.SaveTo(Output)

图片4

我已将此示例源代码用法放入上载文件的反射器项目中。

Imports Microsoft.VisualBasic.CommandLine.Reflection

Module CLIProgram

    Public Function Main() As Integer
        Return GetType(CLIProgram).RunCLI(arg:=Command)
    End Function

    <Command("--reflects", Info:="Automatically generates visualbasic source code from the MySQL database schema dump.",
                           Usage:="--reflects /sql <sql_path> [-o <output_path>]",
                           Example:="--reflects /sql ./test.sql")>
    <ParameterDescription("/sql", False, Description:="The file path of the MySQL database schema dump file."),
     ParameterDescription("-o", True, Description:="The output file path of the generated visual basic source code file from the SQL dump file ""/sql""")>
    Public Function Convert(argvs As Microsoft.VisualBasic.CommandLine.CommandLine) As Integer

        If Not argvs.CheckMissingRequiredParameters("/sql").IsNullOrEmpty Then
            Call Console.WriteLine("The required input parameter ""/sql"" is not specified!")
            Return -1
        End If

        Dim SQL As String = argvs("/sql"), Output As String = argvs("-o")

        If String.IsNullOrEmpty(Output) Then
            Output = FileIO.FileSystem.GetParentPath(SQL)
            Output = $"{Output}/{IO.Path.GetFileNameWithoutExtension(SQL)}.vb"
        End If

        If FileIO.FileSystem.FileExists(SQL) Then
<span style="font-size: 9pt;">            'Convert the SQL file into a visualbasic source code</span><span style="font-size: 9pt;">           
            Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)
</span><span style="font-size: 9pt;">            Return CInt(doc.SaveTo(Output))      'Save the vb source code into a text file</span><span style="font-size: 9pt;">       
        Else</span><span style="font-size: 9pt;">           
            Call Console.WriteLine($"The target schema sql dump file ""{SQL}"" is not exists on your file system!")</span><span style="font-size: 9pt;">          
            Return -2</span><span style="font-size: 9pt;">       
        End If</span><span style="font-size: 9pt;">        </span>

        Return 0<span style="font-size: 9pt;">   
    End Function
</span><span style="font-size: 9pt;">End Module</span>

SQL中的示例表

--
-- Table structure for table `server`
--

DROP TABLE IF EXISTS `server`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `server` (
  `guid` int(11) NOT NULL COMMENT 'configuration entry value',
  `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
  PRIMARY KEY (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
/*!40101 SET character_set_client = @saved_cs_client */;

visualbasic源代码输出示例

Imports Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes

''' <summary>
''' This table contains the server configuration data
'''
''' --
'''
''' DROP TABLE IF EXISTS `server`;
''' /*!40101 SET @saved_cs_client     = @@character_set_client */;
''' /*!40101 SET character_set_client = utf8 */;
''' CREATE TABLE `server` (
'''   `guid` int(11) NOT NULL COMMENT 'configuration entry value',
'''   `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
'''   PRIMARY KEY (`guid`)
''' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
''' /*!40101 SET character_set_client = @saved_cs_client */;
'''
''' --
'''
''' </summary>
''' <remarks></remarks>
<Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes.TableName("server")>
Public Class server: Inherits Oracle.LinuxCompatibility.MySQL.Client.SQLTable
#Region "Public Property Mapping To Database Fields"

''' <summary>
''' configuration entry value
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
    <DatabaseField("guid"), PrimaryKey, DataType(MySqlDbType.Int64, "11")> Public Property guid As Long

''' <summary>
''' the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
    <DatabaseField("value"), DataType(MySqlDbType.Text)> Public Property value As String

#End Region

#Region "Public SQL Interface"

#Region "Interface SQL"

    Private Shared ReadOnly INSERT_SQL As String = <SQL>INSERT INTO `server` (`guid`, `value`) VALUES ('{0}', '{1}');</SQL>
    Private Shared ReadOnly DELETE_SQL As String = <SQL>DELETE FROM `server` WHERE `guid`='{0}';</SQL>
    Private Shared ReadOnly UPDATE_SQL As String = <SQL>UPDATE `server` SET `guid`='{0}', `value`='{1}' WHERE `guid`='{2}';</SQL>

#End Region

    Public Overrides Function GetDeleteSQL() As String
        Return String.Format(DELETE_SQL, guid)
    End Function

    Public Overrides Function GetInsertSQL() As String
        Return String.Format(INSERT_SQL, guid, value)
    End Function

    Public Overrides Function GetUpdateSQL() As String
        Return String.Format(UPDATE_SQL, guid, value, guid)
    End Function

#End Region
End Class

使用SharpDevelop工具可以将自动生成的源代码轻松转换为c#代码

图片5

步骤3.编译程序

现在,事情变得很简单,打开服务器端程序,然后将输出源代码文件添加到您的程序中,或者打开将所选类对象粘贴到源代码中的vs编辑器。现在,不再需要MySQL数据库与您的程序之间的桥接编码。

 

您会发现,这个小型实用工具节省了您的大量时间,这是又一个快乐的日子!希望您喜欢此代码。:-)

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638