VB.net 连接 MySql,Use MySql.Data.dll

下载一个叫 mysql-connector-net 的库文件
安装后，会得到一下东西
MySql.Data.dll ，.net 与MySql 连接的桥梁，很好用
MySql.Data.dll ，源码，C# 的，你可以试着完善它
C#和VB.net 的示例程序。
在项目中引用 MySql.Data.dll
再来个 Imports MySql.Data.MySqlClient 就可以使用了

里面有一些对MySql 数据库的一般操作，很容易上手。

下面是我总结出的MySql命令语句，留个档~~

连接：
server={0};user id={1}; password={2}; database=mysql; pooling=false;port={3}

获取：
SELECT * FROM login where UserName='{0}' and Password='{1}'
这里值得注意，文本型的在 Sql 2000 中要使用 like 比较
select ID from login order by ID desc limit 1
可以获得最后一个ID

更新：
UPDATE login SET logintimes='{0}', LastDateTime='{1}' where UserName='{2}'

插入：
INSERT INTO login (id,username,password) VALUES ('{0}','{1}','{2}')

删除：
delete FROM med_entry where ID='{0}'


下面几个自己写的函数，很有用处

    '执行 MySql 语句，支持多行。没有返回值
    Function ExecuteMySqlCMD(ByRef iconn As MySqlConnection, ByRef MySqlCMD As String, Optional ByRef Err As String = "") As Boolean
        MySqlCMD = Strings.Replace(MySqlCMD, Chr(13), "")
        MySqlCMD = Strings.Replace(MySqlCMD, Chr(10), "")
        Err = ""
        Dim reader As MySqlDataReader = Nothing
        Dim cmdList() As String
        Dim cmd As MySqlCommand = Nothing
        Dim i As Integer
        cmdList = Strings.Split(MySqlCMD, ";")

        If cmdList IsNot Nothing Then
            Try
                For i = 0 To cmdList.Length - 1
                    If cmdList(i) = "" Then Continue For
                    cmd = New MySqlCommand(cmdList(i), iconn)
                    reader = cmd.ExecuteReader()
                    reader.Close()
                Next
                Return True
            Catch ex As MySqlException
                Err = ex.Message
            End Try
        End If
        Return False
    End Function

    '执行 MySql 语句，只能一行。返回 DataTable,MySqlDataAdapter,MySqlCommandBuilder
    Function ExecuteMySqlCMD(ByRef iconn As MySqlConnection, ByRef MySqlCMD As String, ByRef iDataTable As DataTable, Optional ByRef iMySqlDataAdapter As MySqlDataAdapter = Nothing, Optional ByRef iMySqlCommandBuilder As MySqlCommandBuilder = Nothing, Optional ByRef Err As String = "") As Boolean
        MySqlCMD = Strings.Replace(MySqlCMD, Chr(13), "")
        MySqlCMD = Strings.Replace(MySqlCMD, Chr(10), "")
        Err = ""

        Dim cmdList() As String
        cmdList = Strings.Split(MySqlCMD, ";")
        If cmdList IsNot Nothing Then
            Try
                If cmdList(0) = "" Then Exit Try
                iDataTable = New DataTable
                iMySqlDataAdapter = New MySqlDataAdapter(cmdList(0), iconn)
                iMySqlCommandBuilder = New MySqlCommandBuilder(iMySqlDataAdapter)
                iMySqlDataAdapter.Fill(iDataTable)
                Return True
            Catch ex As MySqlException
                Err = ex.Message
            End Try
        End If
        Return False
    End Function

    '检查数据库是否存在
    Function CheckDataBase(ByRef iconn As MySqlConnection, ByRef DataBaseName As String, Optional ByRef Err As String = "") As Boolean
        Dim reader As MySqlDataReader = Nothing
        DataBaseName = DataBaseName.ToLower
        Err = ""
        Dim cmd As New MySqlCommand("SHOW DATABASES", iconn)
        Try
            reader = cmd.ExecuteReader()

            While (reader.Read())
                If reader.GetString(0).ToLower = DataBaseName Then
                    Return True
                End If
            End While

        Catch ex As MySqlException
            Err = ex.Message
        Finally
            If Not reader Is Nothing Then reader.Close()
        End Try
        Return False
    End Function

    '检查数据表是否存在
    Function CheckTable(ByRef iconn As MySqlConnection, ByRef DataBaseName As String, ByVal TableName As String, Optional ByRef Err As String = "") As Boolean
        Dim reader As MySqlDataReader = Nothing
        DataBaseName = DataBaseName.ToLower
        TableName = TableName.ToLower
        Err = ""
        iconn.ChangeDatabase(DataBaseName)
        Dim cmd As New MySqlCommand("SHOW TABLES", iconn)
        Try
            reader = cmd.ExecuteReader()

            While (reader.Read())
                If reader.GetString(0).ToLower = TableName Then
                    Return True
                End If
            End While

        Catch ex As MySqlException
            Err = ex.Message
        Finally
            If Not reader Is Nothing Then reader.Close()
        End Try
        Return False
    End Function