297 lines
13 KiB
VB.net
297 lines
13 KiB
VB.net
Imports System.Text
|
|
|
|
Namespace Database.Base
|
|
|
|
Public MustInherit Class CommandHelpers
|
|
Public Overridable Function Search(param As SearchParams) As String
|
|
Dim searchString As New StringBuilder
|
|
|
|
'基础查询
|
|
searchString.Append("Select")
|
|
searchString.Append(" ")
|
|
searchString.Append($"{String.Join(",", param.SearchColNames)}")
|
|
searchString.Append(" ")
|
|
searchString.Append("From")
|
|
searchString.Append(" ")
|
|
searchString.Append($"`{param.TableName}`")
|
|
|
|
'筛选条件
|
|
If param.SearchCondition IsNot Nothing Then
|
|
If param.SearchCondition.Count > 0 Then
|
|
searchString.Append(" ")
|
|
searchString.Append("Where")
|
|
For i As Integer = 0 To param.SearchCondition.Count - 1
|
|
If i > 0 Then
|
|
searchString.Append(" ")
|
|
searchString.Append(param.SearchCondition(i).LogicPrevious.ToString())
|
|
End If
|
|
searchString.Append(param.SearchCondition(i).ToString())
|
|
Next
|
|
End If
|
|
End If
|
|
|
|
'排序与排序方式
|
|
If param.OrderType <> SearchParams.OrderTypeEnum.None Then
|
|
searchString.Append($" Order By {param.OrderColName} {param.OrderType}")
|
|
End If
|
|
|
|
'返回结果行数
|
|
If param.Limit > -1 Then
|
|
searchString.Append($" Limit {param.Limit}")
|
|
End If
|
|
|
|
searchString.Append(";")
|
|
Return searchString.ToString()
|
|
End Function
|
|
|
|
|
|
Public Overridable Function SearchAll(tableName As String) As String
|
|
Return $"Select * FROM `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function SearchAll(tableName As String, condition As String) As String
|
|
Return $"Select * FROM `{tableName}` WHERE {condition};"
|
|
End Function
|
|
|
|
Public Overridable Function Search(columnName As List(Of String), tableName As String) As String
|
|
Dim colNameString As New StringBuilder
|
|
For i As Integer = 0 To columnName.Count - 1
|
|
If i = 0 Then
|
|
colNameString.Append($"`{columnName(i)}`")
|
|
Else
|
|
colNameString.Append($",`{columnName(i)}`")
|
|
End If
|
|
Next
|
|
|
|
Return $"Select {colNameString} FROM `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function Search(columnName As String, tableName As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function Search(columnName As String, tableName As String, condition As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}` WHERE {condition};"
|
|
End Function
|
|
|
|
Public Overridable Function Search(columnName As String, tableName As String, condition As String, limit As Integer) As String
|
|
Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Limit {limit};"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDistinct(columnName As String, tableName As String) As String
|
|
Return $"Select Distinct {columnName} FROM `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDistinct(columnName As String, tableName As String, condition As String) As String
|
|
Return $"Select Distinct {columnName} FROM `{tableName}` WHERE {condition};"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDescOrder(columnName As String, tableName As String, orderCol As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Desc;"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDescOrder(columnName As String, tableName As String, orderCol As String, limit As Integer) As String
|
|
Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Desc Limit {limit};"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDescOrder(columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By `{orderCol}` Desc;"
|
|
End Function
|
|
|
|
Public Overridable Function SearchDescOrder(columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String, limit As Integer) As String
|
|
Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By `{orderCol}` Desc Limit {limit};"
|
|
End Function
|
|
|
|
Public Overridable Function SearchAscOrder(ByVal columnName As String, ByVal tableName As String, ByVal orderCol As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Asc;"
|
|
End Function
|
|
|
|
|
|
Public Overridable Function SearchAscOrder(ByVal columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String) As String
|
|
Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By {orderCol} Asc;"
|
|
End Function
|
|
|
|
Public Overridable Function SearchNullTable(tableName As String) As String
|
|
Return $"Select * FROM `{tableName}` Where Limit 0;"
|
|
End Function
|
|
|
|
|
|
Public Overridable Function Insert(ByVal tableName As String, ByVal values As String) As String
|
|
Return $"Insert into `{tableName}` Values ( {values} );"
|
|
End Function
|
|
|
|
Public Overridable Function Insert(ByVal tableName As String, ByVal colNames As String, ByVal values As String) As String
|
|
Return $"Insert into `{tableName}` ({colNames}) Values ( {values} );"
|
|
End Function
|
|
|
|
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($"`{keyValuePair.Key}`")
|
|
values.Append($"'{keyValuePair.Value}'")
|
|
Else
|
|
colNames.Append($",`{keyValuePair.Key}`")
|
|
values.Append($",'{keyValuePair.Value}'")
|
|
End If
|
|
Next
|
|
Return Insert(tableName, colNames.ToString(), values.ToString())
|
|
End Function
|
|
|
|
Public Overridable Function InsertByParameters(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($"`{keyValuePair.Key}`")
|
|
values.Append($"{keyValuePair.Value}")
|
|
Else
|
|
colNames.Append($",`{keyValuePair.Key}`")
|
|
values.Append($",{keyValuePair.Value}")
|
|
End If
|
|
Next
|
|
Return Insert(tableName, colNames.ToString(), values.ToString())
|
|
End Function
|
|
|
|
|
|
Public Overridable Function AddCol(ByVal tableName As String, ByVal colName As String, ByVal colType As String, Optional isNull As Boolean = True) As String
|
|
Return $"Alter Table `{tableName}` Add `{colName}` {colType} {IIf(isNull, "Default Null", "Not Null")};"
|
|
End Function
|
|
|
|
Public Overridable Function AddCol(ByVal tableName As String, colParam As DatabaseData) As String
|
|
Dim sb As New StringBuilder
|
|
sb.Append($"Alter Table `{tableName}` ")
|
|
sb.Append("Add ")
|
|
sb.Append(colParam.ToAddColString())
|
|
Return sb.ToString()
|
|
End Function
|
|
|
|
|
|
Public Overridable Function AddCols(tableName As String, colList As List(Of DatabaseData)) As String
|
|
Dim sb As New StringBuilder
|
|
sb.Append($"Alter Table `{tableName}` ")
|
|
sb.Append("Add ")
|
|
sb.Append("( ")
|
|
|
|
sb.Append(colList(0).ToAddColString())
|
|
For i As Integer = 1 To colList.Count - 1
|
|
sb.Append($",{colList(i).ToAddColString()}")
|
|
Next
|
|
|
|
sb.Append(");")
|
|
|
|
Return sb.ToString()
|
|
End Function
|
|
|
|
|
|
Public Overridable Function Update(ByVal tableName As String, ByVal destStr As String, ByVal condition As String) As String
|
|
Return $"Update `{tableName}` Set {destStr} Where {condition};"
|
|
End Function
|
|
|
|
Public Overridable Function Update(ByVal tableName As String, dicNameValues As Dictionary(Of String, String), ByVal 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($"`{keyValuePair.Key}` = '{keyValuePair.Value}'")
|
|
Else
|
|
destStr.Append($",`{keyValuePair.Key}` = '{keyValuePair.Value}'")
|
|
End If
|
|
Next
|
|
Return Update(tableName, destStr.ToString(), condition)
|
|
End Function
|
|
|
|
Public Overridable Function Update(ByVal tableName As String, names() As String, values() As String, condition As String) As String
|
|
Dim destStr As New StringBuilder
|
|
If names.Length <> values.Length Then
|
|
Throw New Exception("DBHelpers_Update:names.Length <> values.Length")
|
|
End If
|
|
|
|
For i As Integer = 0 To names.Length - 1
|
|
If i = 0 Then
|
|
destStr.Append($"{names(i)} = '{values(i)}'")
|
|
Else
|
|
destStr.Append($",{names(i)} = '{values(i)}'")
|
|
End If
|
|
Next
|
|
|
|
Return Update(tableName, destStr.ToString(), condition)
|
|
End Function
|
|
|
|
Public Overridable Function DeleteRows(ByVal tableName As String, ByVal condition As String) As String
|
|
Return $"Delete From `{tableName}` Where {condition};"
|
|
End Function
|
|
|
|
''' <summary>
|
|
''' 清空数据表
|
|
''' </summary>
|
|
''' <param name="tableName">数据表名</param>
|
|
''' <returns></returns>
|
|
Public Overridable Function DeleteTable(ByVal tableName As String) As String
|
|
Return $"Delete From `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function DropCol(ByVal tableName As String, ByVal colName As String) As String
|
|
Return $"Alter Table `{tableName}` Drop Column `{colName}`;"
|
|
End Function
|
|
|
|
''' <summary>
|
|
''' 删除数据表
|
|
''' </summary>
|
|
''' <param name="tableName">数据表名</param>
|
|
''' <returns></returns>
|
|
Public Overridable Function DropTable(ByVal tableName As String) As String
|
|
Return $"Drop Table `{tableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function CreateTable(ByVal tableName As String, ByVal createStr As String) As String
|
|
Return $"Create Table `{tableName}` ( {createStr} );"
|
|
End Function
|
|
|
|
Public Overridable Function CreateTableWhenNotExists(tableName As String, createStr As String) As String
|
|
Return $"Create Table if not exists `{tableName}` ( {createStr} );"
|
|
End Function
|
|
|
|
|
|
Public Overridable Function CreateLikeTable(tableName As String, baseTableName As String) As String
|
|
Return $"create table `{tableName}` like `{baseTableName}`;"
|
|
End Function
|
|
|
|
Public Overridable Function CreateLikeTableNotExists(tableName As String, baseTableName As String) As String
|
|
Return $"create table if not exists `{tableName}` like `{baseTableName}`;"
|
|
End Function
|
|
|
|
''' <summary>
|
|
''' 创建表,同时复制基础表数据(不包含原表索引与主键)
|
|
''' 若想复制表结构加数据,则先复制表结构创建表，再拷贝数据
|
|
''' </summary>
|
|
''' <param name="tableName">表名</param>
|
|
''' <param name="baseTableName">基础表名</param>
|
|
''' <returns></returns>
|
|
Public Overridable Function CreateCopyTable(tableName As String, baseTableName As String) As String
|
|
Return $"create table `{tableName}` as select * from `{baseTableName}`;"
|
|
End Function
|
|
|
|
''' <summary>
|
|
''' 不存在表时即创建表,同时复制基础表数据(不包含原表索引与主键)
|
|
''' 若想复制表结构加数据,则先复制表结构创建表，再拷贝数据
|
|
''' </summary>
|
|
''' <param name="tableName">表名</param>
|
|
''' <param name="baseTableName">基础表名</param>
|
|
''' <returns></returns>
|
|
Public Overridable Function CreateCopyTableNotExists(tableName As String, baseTableName As String) As String
|
|
Return $"create table if not exists `{tableName}` as select * from `{baseTableName}`;"
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
''' 复制基础表数据到新表中
|
|
''' </summary>
|
|
''' <param name="tableName">表名</param>
|
|
''' <param name="baseTableName">基础表名</param>
|
|
''' <returns></returns>
|
|
Public Overridable Function InsertCopyTable(tableName As String, baseTableName As String) As String
|
|
Return $"insert into `{tableName}` select * from `{baseTableName}`;"
|
|
End Function
|
|
End Class
|
|
End Namespace |