/// <summary>
/// 批量插入
/// </summary>
/// <param name="cURDModelSave"></param>
/// <param name="tableInfos"></param>
/// <returns></returns>
public string Insert(CURDModelSave cURDModelSave, List <TableInfo> tableInfos)
{
StringBuilder sb = new StringBuilder();
string tableName = cURDModelSave.TableName;
string columnName = string.Empty;
string columnValue = string.Empty;
foreach (var columnsList in cURDModelSave.Columns)
{
int i = 0;
foreach (var column in columnsList)
{
i++;
int iColumnProperty = tableInfos.Where(t => t.ColumnName == column.Key.ToString()).FirstOrDefault().ColumnProperty;
if (iColumnProperty == 0) //string
{
if (i == columnsList.Count)
{
columnName += column.Key;
columnValue += "'" + column.Value + "'";
}
else
{
columnName += column.Key + ",";
columnValue += "'" + column.Value + "'" + ",";
}
}
if (iColumnProperty == 1) //int
{
if (i == columnsList.Count)
{
columnName += column.Key;
columnValue += column.Value;
}
else
{
columnName += column.Key + ",";
columnValue += column.Value + ",";
}
}
if (iColumnProperty == 2) //bool
{
if (i == columnsList.Count)
{
columnName += column.Key;
columnValue += (column.Value.ToString().ToLower() == "true" ? "1" : "0");
}
else
{
columnName += column.Key + ",";
columnValue += (column.Value.ToString().ToLower() == "true" ? "1" : "0") + ",";
}
}
}
}
sb.Append($" insert into {tableName}({columnName}) values({columnValue}) ;");
return(sb.ToString());
}
/// <summary>
/// 批量修改
/// </summary>
/// <param name="cURDModelSave"></param>
/// <param name="tableInfos"></param>
/// <returns></returns>
public string Modify(CURDModelSave cURDModelSave, List <TableInfo> tableInfos)
{
StringBuilder sb = new StringBuilder();
string tableName = cURDModelSave.TableName;
List <string> columnNameAndValue = new List <string>();
string id = string.Empty;
foreach (var columnsList in cURDModelSave.Columns)
{
foreach (var column in columnsList)
{
if (column.Key.ToString().ToLower() == "id")
{
id = column.Value.ToString();
}
else
{
TableInfo tableInfo = tableInfos.Where(t => t.ColumnName == column.Key.ToString()).FirstOrDefault();
int iColumnProperty = tableInfo.ColumnProperty;
bool IsModify = tableInfo.IsSupportModify;
if (IsModify) //当前字段是否支持修改
{
if (iColumnProperty == 0) //string
{
columnNameAndValue.Add($" {column.Key}='{column.Value }'");
}
if (iColumnProperty == 1) //int
{
columnNameAndValue.Add($" {column.Key}={column.Value }");
}
if (iColumnProperty == 2) //bool
{
columnNameAndValue.Add($" {column.Key}={(column.Value.ToString().ToLower() == "true" ? "1" : "0")}");
}
}
}
}
}
sb.Append($" update {tableName} set {string.Join(",", columnNameAndValue)} where id={id};");
return(sb.ToString());
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="cURDModelSave"></param>
/// <returns></returns>
public string Delete(CURDModelSave cURDModelSave)
{
StringBuilder sb = new StringBuilder();
string tableName = cURDModelSave.TableName;
string columnName = string.Empty;
string columnValue = string.Empty;
List <string> strWhereList = new List <string>();
foreach (var columnsList in cURDModelSave.Columns)
{
foreach (var column in columnsList)
{
if (column.Key.ToLower() == "id")
{
strWhereList.Add($" id={column.Value} ");
}
}
}
sb.Append($" delete from {tableName} where {string.Join(" or ", strWhereList)}");
return(sb.ToString());
}
/// <summary>
/// 读取
/// </summary>
/// <param name="cURDModelSave"></param>
/// <param name="tableInfos"></param>
/// <returns></returns>
public string Read(CURDModelSave cURDModelSave, List <TableInfo> tableInfos)
{
StringBuilder sb = new StringBuilder();
string tableName = cURDModelSave.TableName;
List <string> strWhereAnd = new List <string>();
List <string> strWhereOr = new List <string>();
string id = string.Empty;
foreach (var columnsList in cURDModelSave.Columns)
{
foreach (var column in columnsList)
{
if (column.Key.ToString().ToLower() == "id")
{
//直接返回
return($" select * from {tableName} where id={column.Value.ToString()}");
}
else
{
TableInfo tableInfo = tableInfos.Where(t => t.ColumnName == column.Key.ToString()).FirstOrDefault();
int iColumnProperty = tableInfo.ColumnProperty;
bool IsSupportQuery = tableInfo.IsSupportQuery;
bool IsSupporLikeQuery = tableInfo.IsSupporLikeQuery;
bool IsAnd = tableInfo.IsAnd;
if (IsSupportQuery) //当前字段是否支持查询
{
if (iColumnProperty == 2) //bit
{
if (IsAnd)
{
strWhereAnd.Add($" {column.Key}={(column.Value.ToString().ToLower() == "true" ? "1" : "0")}");
}
else
{
strWhereOr.Add($" {column.Key}={(column.Value.ToString().ToLower() == "true" ? "1" : "0")}");
}
}
if (IsSupporLikeQuery) //是否支持模糊查询
{
if (iColumnProperty == 0) //string
{
if (IsAnd)
{
strWhereAnd.Add($" { column.Key } like '%{ column.Value }%'");
}
else
{
strWhereOr.Add($" { column.Key } like '%{ column.Value }%'");
}
}
if (iColumnProperty == 1) //int
{
if (IsAnd)
{
strWhereAnd.Add($" { column.Key } like '%{column.Value }%'");
}
else
{
strWhereOr.Add($" { column.Key } like '%{column.Value }%'");
}
}
}
else
{
if (iColumnProperty == 0) //string
{
if (IsAnd)
{
strWhereAnd.Add($" { column.Key } = '{ column.Value }' ");
}
else
{
strWhereOr.Add($" { column.Key } = '{ column.Value }' ");
}
}
if (iColumnProperty == 1) //int
{
if (IsAnd)
{
strWhereAnd.Add($" { column.Key } = {column.Value } ");
}
else
{
strWhereOr.Add($" { column.Key } = {column.Value } ");
}
}
if (iColumnProperty == 2) //bool
{
if (IsAnd)
{
strWhereAnd.Add($" { column.Key } = {(column.Value.ToString().ToLower() == "true" ? "1" : "0")}");
}
else
{
strWhereOr.Add($" { column.Key } = {(column.Value.ToString().ToLower() == "true" ? "1" : "0")}");
}
}
}
}
}
}
}
sb.Append($" select * from {tableName} where 1=1 ");
if (strWhereAnd.Count > 0)
{
sb.Append($" {" and" +"(" + string.Join("and", strWhereAnd) + ")"};");
}
if (strWhereOr.Count > 0)
{
sb.Append($" {" and" + "(" + string.Join("or", strWhereOr) + ")"};");
}
if (!string.IsNullOrEmpty(cURDModelSave.SqlWhere))
{
sb.Append(" " + cURDModelSave.SqlWhere);
}
return(sb.ToString());
}
