Imports System.Text


''' <summary>
''' 数据库语句助手
''' 时间：2020-12-21
''' 作者：ML
''' 版本：1.0
''' 
''' 注意：添加一条数据库帮助语句时,需要考虑Mysql/Sqlite/Mssql等数据库是否支持命令,不支持则需要在对应帮助类中重写该帮助语句
''' 注意：Sqlite数据库与大多数据库不相同,DB开头数据库语句大多不适用
'''
''' </summary>
Public MustInherit Class DbCmdHelper
        Protected  FiledSuffix As Char
        Protected  FiledPrefix As Char

        Public Shared Function CreateCmdHelper(type As DbExecutor.DbTypeEnum) As DbCmdHelper
            Select Case type
                Case DbExecutor.DbTypeEnum.Mysql
                    Return New MysqlCmdHelper()
                Case DbExecutor.DbTypeEnum.Mssql
                    Return New MssqlCmdHelper()
                Case DbExecutor.DbTypeEnum.Sqlite
                    Return New SqliteCmdHelper()
                Case Else
                    Throw New Exception($"CreateCmdHelper :Unknown Type {type}")
            End Select
        End Function


#Region "访问单数据库连接"
        ''' <summary>
        ''' 查询指定数据表符合条件的所有数据
        ''' </summary>
        ''' <param name="tableName">指定表名</param>
        ''' <param name="condition">查询条件,</param>
        ''' <returns></returns>
        Public Overridable Function SearchAll(tableName As String, Optional condition As String = "") As String
            If String.IsNullOrWhiteSpace(condition) Then
                Return $"Select * FROM {FiledSuffix}{tableName}{FiledPrefix};"
            Else
                Return $"Select * FROM {FiledSuffix}{tableName}{FiledPrefix} WHERE {condition};"
            End If
        End Function


        ''' <summary>
        ''' 查询表符合条件的所有指定列的数据
        ''' </summary>
        ''' <param name="columnName">列名集合,需要返回多列时用','符号分隔列名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="condition">条件</param>
        ''' <returns></returns>
        Public Overridable Function Search(columnName As String, tableName As String, Optional condition As String = "") As String
            If String.IsNullOrWhiteSpace(condition) Then
                Return $"Select {columnName} FROM {FiledSuffix}{tableName}{FiledPrefix};"
            Else
                Return $"Select {columnName} FROM {FiledSuffix}{tableName}{FiledPrefix} WHERE {condition};"
            End If
        End Function

        ''' <summary>
        '''  查询表符合条件的所有指定列的数据
        ''' </summary>
        ''' <param name="columnName">表名</param>
        ''' <param name="tableName">条件</param>
        ''' <returns></returns>
        Public Overridable Function Search(columnName As List(Of String), tableName As String, Optional condition As String = "") As String
            Dim colNameString As New StringBuilder
            For i As Integer = 0 To columnName.Count - 1
                If i = 0 Then
                    colNameString.Append($"{FiledSuffix}{columnName(i)}{FiledPrefix}")
                Else
                    colNameString.Append($",{FiledSuffix}{columnName(i)}{FiledPrefix}")
                End If
            Next

            If String.IsNullOrWhiteSpace(condition) Then
                Return $"Select {colNameString} FROM {FiledSuffix}{tableName}{FiledPrefix};"
            Else
                Return $"Select {colNameString} FROM {FiledSuffix}{tableName}{FiledPrefix} Where {condition};"
            End If
        End Function

        Public Overridable Function SearchOrder(columnName As List(Of String), tableName As String, Optional orderString As String = "") As String
            Dim colNameString As New StringBuilder
            For i As Integer = 0 To columnName.Count - 1
                If i = 0 Then
                    colNameString.Append($"{FiledSuffix}{columnName(i)}{FiledPrefix}")
                Else
                    colNameString.Append($",{FiledSuffix}{columnName(i)}{FiledPrefix}")
                End If
            Next

            If String.IsNullOrWhiteSpace(orderString) Then
                Return $"Select {colNameString} FROM {FiledSuffix}{tableName}{FiledPrefix};"
            Else
                Return $"Select {colNameString} FROM {FiledSuffix}{tableName}{FiledPrefix} {orderString};"
            End If
        End Function


        ''' <summary>
        ''' 查询指定表包含的内容行数
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <param name="condition">查询条件</param>
        ''' <returns></returns>
        Public Overridable Function SearchCount(tableName As String, Optional condition As String = "") As String
            Return Search("count(*)", tableName, condition)
        End Function

        ''' <summary>
        ''' 查询指定数据表的信息
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <returns></returns>
        Public Overridable Function SearchTableInfo(tableName As String) As String
            Return $"Select * from information_schema.tables where table_name = '{tableName}';"
        End Function


        ''' <summary>
        ''' 查询指定数据表是否存在的信息,返回查询当前表在数据库中存在的数量
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <returns></returns>
        Public Overridable Function SearchTableExists(tableName As String) As String
            Return $"Select count(*) from information_schema.tables where table_name = '{tableName}';"
        End Function


    ''' <summary>
    ''' 数据表插入一行数据
    ''' </summary>
    ''' <param name="tableName">表名</param>
    ''' <param name="colNames">列名字符串</param>
    ''' <param name="values">列值字符串</param>
    ''' <returns></returns>
    Public Overridable Function Insert(tableName As String, colNames As String, values As String) As String
        '将返回输出到C盘 log.txt中
        '判断文件是否存在，如果不存在则创建

        Return $"Insert into {FiledSuffix}{tableName}{FiledPrefix} ({colNames}) Values ( {values} );"
    End Function



    ''' <summary>
    ''' 数据表插入一行数据
    ''' </summary>
    ''' <param name="tableName">表名</param>
    ''' <param name="dicNameValues">列名与列值键值对</param>
    ''' <returns></returns>
    Public Overridable Function Insert(tableName As String, dicNameValues As Dictionary(Of String, String)) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If colNames.Length = 0 Then
                    colNames.Append($"{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($"'{keyValuePair.Value}'")
                Else
                    colNames.Append($",{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($",'{keyValuePair.Value}'")
                End If
            Next

        Return Insert(tableName, colNames.ToString(), values.ToString())

    End Function
           Public Overridable Function Insert2(tableName As String, dicNameValues As Dictionary(Of String, Object )) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, Object) In dicNameValues
                
                If colNames.Length = 0 Then
                    colNames.Append($"{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($"'{keyValuePair.Value}'")
                Else
                    colNames.Append($",{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($",'{keyValuePair.Value}'")

                End If
            Next
            Return Insert(tableName, colNames.ToString(), values.ToString())
        End Function

        ''' <summary>
        ''' 数据表插入一行,通过命令参数方式执行时使用
        ''' </summary>
        ''' <param name="tableName"></param>
        ''' <param name="dicNameValues"></param>
        ''' <returns></returns>
        Public Overridable Function InsertParam(tableName As String, dicNameValues As Dictionary(Of String, String)) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If colNames.Length = 0 Then
                    colNames.Append($"{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($"{keyValuePair.Value}")
                Else
                    colNames.Append($",{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($",{keyValuePair.Value}")
                End If
            Next
            Return Insert(tableName, colNames.ToString(), values.ToString())
        End Function

        ''' <summary>
        ''' 数据表插入一行,通过命令参数方式执行时使用,参数名由@{ColName}
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="colNames">字段列表</param>
        ''' <returns></returns>
        Public Overridable Function InsertParam(tableName As String, colNames As List(Of String)) As String
            Dim colNameString As New StringBuilder
            Dim values As New StringBuilder
            For Each colName As String In colNames
                If colNameString.Length = 0 Then
                    colNameString.Append($"{FiledSuffix}{colName}{FiledPrefix}")
                    values.Append($"@{colName}")
                Else
                    colNameString.Append($",{FiledSuffix}{colName}{FiledPrefix}")
                    values.Append($",@{colName}")
                End If
            Next
            Return Insert(tableName, colNameString.ToString(), values.ToString())
        End Function


        ''' <summary>
        ''' 更新指定表数据
        ''' </summary>
        ''' <param name="tableName">指定表名</param>
        ''' <param name="destStr">更新字符串</param>
        ''' <param name="condition"></param>
        ''' <returns></returns>
        Public Overridable Function Update(tableName As String, destStr As String, condition As String) As String
            Return $"Update {FiledSuffix}{tableName}{FiledPrefix} Set {destStr} Where {condition};"
        End Function

        ''' <summary>
        ''' 更新指定表数据
        ''' </summary>
        ''' <param name="tableName">指定表名</param>
        ''' <param name="dicNameValues">更新列名与列值键值对</param>
        ''' <param name="condition">更新列索引条件</param>
        ''' <returns></returns>
        Public Overridable Function Update(tableName As String, dicNameValues As Dictionary(Of String, String), condition As String) As String
            Dim destStr As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If destStr.Length = 0 Then
                    destStr.Append($"{FiledSuffix}{keyValuePair.Key}{FiledPrefix} = '{keyValuePair.Value}'")
                Else
                    destStr.Append($",{FiledSuffix}{keyValuePair.Key}{FiledPrefix} = '{keyValuePair.Value}'")
                End If
            Next
            Return Update(tableName, destStr.ToString(), condition)
        End Function

        ''' <summary>
        ''' 更新指定数据库中指定表数据,参数名由@{ColName}
        ''' </summary>
        ''' <param name="tableName">指定表名</param>
        ''' <param name="colNames">更新列名的集合</param>
        ''' <param name="condition">更新列索引条件</param>
        ''' <returns></returns>
        Public Overridable Function UpdateParam(tableName As String, colNames As List(Of String), condition As String) As String
            Dim destStr As New StringBuilder
            For Each colName As String In colNames
                If destStr.Length = 0 Then
                    destStr.Append($"{FiledSuffix}{colName}{FiledPrefix} = @{colName}")
                Else
                    destStr.Append($",{FiledSuffix}{colName}{FiledPrefix} = @{colName}")
                End If
            Next
            Return Update(tableName, destStr.ToString(), condition)
        End Function

        ''' <summary>
        ''' 指定数据表增加一列数据
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="colName">列名</param>
        ''' <param name="colType">列类型</param>
        ''' <param name="isNull">是否允许为空</param>
        ''' <returns></returns>
        Public Overridable Function AddCol(tableName As String, colName As String, colType As String, Optional isNull As Boolean = True) As String
            Return $"Alter Table {FiledSuffix}{tableName}{FiledPrefix} Add {FiledSuffix}{colName}{FiledPrefix} {colType} {IIf(isNull, "Default Null", "Not Null")};"
        End Function

        ''' <summary>
        ''' 数据表删除一列数据
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="colName">需要删除的列名,仅一列</param>
        ''' <returns></returns>
        Public Overridable Function DropCol(tableName As String, colName As String) As String
            Return $"Alter Table {FiledSuffix}{tableName}{FiledPrefix} Drop Column {FiledSuffix}{colName}{FiledPrefix};"
        End Function

        ''' <summary>
        ''' 删除指定表多行数据
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="condition">条件</param>
        ''' <returns></returns>
        Public Overridable Function DeleteRows(tableName As String, condition As String) As String
            Return $"Delete From {FiledSuffix}{tableName}{FiledPrefix} Where {condition};"
        End Function

        ''' <summary>
        ''' 创建数据表
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="createStr">创建表的列信息字符串</param>
        ''' <returns></returns>
        Public Overridable Function CreateTable(tableName As String, createStr As String) As String
            Return $"Create Table {FiledSuffix}{tableName}{FiledPrefix} ( {createStr} );"
        End Function

        ''' <summary>
        ''' 创建数据表，如果存在则不创建
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="createStr">创建表的列信息字符串</param>
        ''' <returns></returns>
        Public Overridable Function CreateTableWhenNotExists(tableName As String, createStr As String) As String
            Return $"Create Table if not exists {FiledSuffix}{tableName}{FiledPrefix} ( {createStr} );"
        End Function

        ''' <summary>
        ''' 清空数据表,表依旧存在
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DeleteTable(tableName As String) As String
            Return $"Delete From {FiledSuffix}{tableName}{FiledPrefix};"
        End Function


        ''' <summary>
        ''' 删除数据表
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DropTable(tableName As String) As String
            Return $"Drop Table {FiledSuffix}{tableName}{FiledPrefix};"
        End Function

        ''' <summary>
        ''' 删除数据表
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DropTableWhenExists(tableName As String) As String
            Return $"Drop Table If Exists {FiledSuffix}{tableName}{FiledPrefix};"
        End Function
#End Region


#Region "访问多数据库连接"



        ''' <summary>
        ''' 查询指定数据库中指定数据表符合条件的所有指定列的数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="ColsName">列名集合,需要返回多列时用','符号分隔列名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="condition">条件</param>
        ''' <returns></returns>
        Public Overridable Function DbSearch(dbName As String, colsName As String, tableName As String, Optional condition As String = "") As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Select {colsName} From ")

            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If

            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")

            If String.IsNullOrWhiteSpace(condition) = False Then
                cmdText.Append($" WHERE {condition}")
            End If

            cmdText.Append($";")
            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 查询指定数据库中指定数据表符合条件的所有指定列的去重数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="ColsName">列名集合,需要返回多列时用','符号分隔列名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="condition">条件</param>
        ''' <returns></returns>
        Public Overridable Function DbDistinctSearch(dbName As String, colsName As String, tableName As String, Optional condition As String = "") As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Select Distinct {colsName} From ")

            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If

            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")

            If String.IsNullOrWhiteSpace(condition) = False Then
                cmdText.Append($" WHERE {condition}")
            End If

            cmdText.Append($";")
            Return cmdText.ToString()
        End Function


        ''' <summary>
        '''  查询指定数据库中指定数据表符合条件的所有指定列的数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="columnName">表名</param>
        ''' <param name="tableName">条件</param>
        ''' <returns></returns>
        Public Overridable Function DbSearch(dbName As String, columnName As List(Of String), tableName As String, Optional condition As String = "") As String
            Dim colNameString As New StringBuilder
            For i As Integer = 0 To columnName.Count - 1
                If i = 0 Then
                    colNameString.Append($"{FiledSuffix}{columnName(i)}{FiledPrefix}")
                Else
                    colNameString.Append($",{FiledSuffix}{columnName(i)}{FiledPrefix}")
                End If
            Next

            Return DbSearch(dbName, colNameString.ToString(), tableName, condition)
        End Function

        ''' <summary>
        ''' 查询指定表包含的内容行数
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">数据表名</param>
        ''' <param name="condition">查询条件</param>
        ''' <returns></returns>
        Public Overridable Function DbSearchCount(dbName As String, tableName As String, Optional condition As String = "") As String
            Return DbSearch(dbName, "count(*)", tableName, condition)
        End Function

        ''' <summary>
        ''' 查询指定数据库中指定数据表符合条件的所有数据
        ''' 
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">数据表名</param>
        ''' <param name="condition">查询条件(可选)</param>
        ''' <returns></returns>
        Public Overridable Function DbSearchAll(dbName As String, tableName As String, Optional condition As String = "") As String
            Return DbSearch(dbName, "*", tableName, condition)
        End Function

        ''' <summary>
        ''' 查询指定数据库中指定数据表的信息
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <returns></returns>
        Public Overridable Function DbSearchTableInfo(dbName As String, tableName As String) As String
            Return DbSearch("", "*", "information_schema.tables", "table_schema = '{dbName}' and table_name = '{tableName}'")
        End Function

        ''' <summary>
        ''' 查询指定数据表是否存在的信息,返回查询当前表在指定数据库中存在的数量
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <returns></returns>
        Public Overridable Function DbSearchTableExists(dbName As String, tableName As String) As String
            Return DbSearch("", "count(*)", "information_schema.tables", "table_schema = '{dbName}' and table_name = '{tableName}'")
        End Function



        ''' <summary>
        ''' 指定数据库中数据表插入一行数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="colNames">列名字符串</param>
        ''' <param name="values">列值字符串</param>
        ''' <returns></returns>
        Public Overridable Function DbInsert(dbName As String, tableName As String, colNames As String, values As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Insert into ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" ({colNames}) Values ( {values} );")
            Return cmdText.ToString()
        End Function


        ''' <summary>
        ''' 指定数据库中数据表插入一行数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="dicNameValues">列名与列值键值对</param>
        ''' <returns></returns>
        Public Overridable Function DbInsert(dbName As String, tableName As String, dicNameValues As Dictionary(Of String, String)) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If colNames.Length = 0 Then
                    colNames.Append($"{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($"'{keyValuePair.Value}'")
                Else
                    colNames.Append($",{FiledSuffix}{keyValuePair.Key}{FiledPrefix}")
                    values.Append($",'{keyValuePair.Value}'")
                End If
            Next

            Return DbInsert(dbName, tableName, colNames.ToString(), values.ToString())
        End Function

        ''' <summary>
        ''' 指定数据库中数据表插入一行,通过命令参数方式执行时使用,参数名由@{ColName}
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName"></param>
        ''' <param name="colNames">需要插入列名的集合</param>
        ''' <returns></returns>
        Public Overridable Function DbInsertParam(dbName As String, tableName As String, colNames As List(Of String)) As String
            Dim colNameBuilder As New StringBuilder
            Dim valueBuilder As New StringBuilder
            For Each colName As String In colNames
                If colNameBuilder.Length = 0 Then
                    colNameBuilder.Append($"{FiledSuffix}{colName}{FiledPrefix}")
                    valueBuilder.Append($"@{colName}")
                Else
                    colNameBuilder.Append($",{FiledSuffix}{colName}{FiledPrefix}")
                    valueBuilder.Append($",@{colName}")
                End If
            Next
            'insert into dbName.tablename (1,2,3) value (@1,@2,@3)
            Return DbInsert(dbName, tableName, colNameBuilder.ToString(), valueBuilder.ToString())
        End Function


    ''' <summary>
    ''' 更新指定数据库中指定表数据
    ''' </summary>
    ''' <param name="dbName">数据库名</param>
    ''' <param name="tableName">指定表名</param>
    ''' <param name="destStr">更新字符串</param>
    ''' <param name="condition"></param>
    ''' <returns></returns>
    Public Overridable Function DbUpdate(dbName As String, tableName As String, destStr As String, condition As String) As String
        Dim cmdText As New StringBuilder
        Dim tmpStrCmdText As String = ""

        cmdText.Append($"Update ")
        If String.IsNullOrEmpty(dbName) = False Then
            cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
        End If
        cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
        cmdText.Append($"  Set {destStr} Where {condition};")
        tmpStrCmdText = cmdText.ToString()
        'Console.WriteLine("SQL_CMD = " & tmpStrCmdText)
        'If Not System.IO.File.Exists("C:\log.txt") Then
        '    System.IO.File.Create("C:\log.txt").Dispose()
        'End If
        ''将返回写入  Log.txt中
        'System.IO.File.AppendAllText("C:\log.txt", $"{tmpStrCmdText}")
        Return tmpStrCmdText
    End Function

    ''' <summary>
    ''' 更新指定数据库中指定表数据,参数名由@{ColName}
    ''' </summary>
    ''' <param name="dbName">数据库名</param>
    ''' <param name="tableName">指定表名</param>
    ''' <param name="colNames">更新列名的集合</param>
    ''' <param name="condition">更新列索引条件</param>
    ''' <returns></returns>
    Public Overridable Function DbUpdateParam(dbName As String, tableName As String, colNames As List(Of String), condition As String) As String
            Dim destStr As New StringBuilder
            For Each colName As String In colNames
                If destStr.Length = 0 Then
                    destStr.Append($"{FiledSuffix}{colName}{FiledPrefix} = @{colName}")
                Else
                    destStr.Append($",{FiledSuffix}{colName}{FiledPrefix} = @{colName}")
                End If
            Next
            Return DbUpdate(dbName, tableName, destStr.ToString(), condition)
        End Function


        ''' <summary>
        ''' 更新指定数据库中指定表数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">指定表名</param>
        ''' <param name="filedDictionary">更新列名与列值键值对</param>
        ''' <param name="condition">更新列索引条件</param>
        ''' <returns></returns>
        Public Overridable Function DbUpdate(dbName As String, tableName As String, filedDictionary As Dictionary(Of String, String), condition As String) As String
            Dim destStr As New StringBuilder
            For Each filed As KeyValuePair(Of String, String) In filedDictionary
                If destStr.Length = 0 Then
                    destStr.Append($"{FiledSuffix}{filed.Key}{FiledPrefix} = '{filed.Value}'")
                Else
                    destStr.Append($",{FiledSuffix}{filed.Key}{FiledPrefix} = '{filed.Value}'")
                End If
            Next
            Return DbUpdate(dbName, tableName, destStr.ToString(), condition)
        End Function


        ''' <summary>
        ''' 指定数据库中指定数据表增加一列数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="colName">列名</param>
        ''' <param name="colType">列类型</param>
        ''' <param name="isNull">是否允许为空</param>
        ''' <returns></returns>
        Public Overridable Function DbAddCol(dbName As String, tableName As String, colName As String, colType As String, Optional isNull As Boolean = True) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Alter Table ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" Add {FiledSuffix}{colName}{FiledPrefix} {colType} {IIf(isNull, "Default Null", "Not Null")};")

            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 指定数据库中数据表删除一列数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="colName">需要删除的列名,仅一列</param>
        ''' <returns></returns>
        Public Overridable Function DbDropCol(dbName As String, tableName As String, colName As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Alter Table ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" Drop Column {FiledSuffix}{colName}{FiledPrefix};")

            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 指定数据库中指定表删除多行数据
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="condition">条件</param>
        ''' <returns></returns>
        Public Overridable Function DbDeleteRows(dbName As String, tableName As String, condition As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Delete From ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" Where {condition};")

            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 指定数据库中创建数据表
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="createStr">创建表的列信息字符串</param>
        ''' <returns></returns>
        Public Overridable Function DbCreateTable(dbName As String, tableName As String, createStr As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Create Table ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" ( {createStr} );")

            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 指定数据库中创建数据表，如果存在则不创建
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">表名</param>
        ''' <param name="createStr">创建表的列信息字符串</param>
        ''' <returns></returns>
        Public Overridable Function DbCreateTableWhenNotExists(dbName As String, tableName As String, createStr As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Create Table if not exists ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($" ( {createStr} );")

            Return cmdText.ToString()
        End Function

        ''' <summary>
        ''' 清空指定数据库中数据表,表依旧存在
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DbDeleteTable(dbName As String, tableName As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Delete From ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($";")

            Return cmdText.ToString()
        End Function


        ''' <summary>
        ''' 删除指定数据库中数据表
        ''' </summary>
        ''' <param name="dbName">数据库名</param>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DbDropTable(dbName As String, tableName As String) As String
            Dim cmdText As New StringBuilder
            cmdText.Append($"Drop Table ")
            If String.IsNullOrEmpty(dbName) = False Then
                cmdText.Append($"{FiledSuffix}{dbName}{FiledPrefix}.")
            End If
            cmdText.Append($"{FiledSuffix}{tableName}{FiledPrefix}")
            cmdText.Append($";")

            Return cmdText.ToString()
        End Function
#End Region
End Class