/// <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()); }