/// <summary>
/// 单表或多表 进行 单条或多条Insert时调用
/// </summary>
/// <param name="option">SQLOption</param>
/// <returns>SQL语句</returns>
public static string GetInsertSQL(SQLOption option)
{
string tableName = "";
StringBuilder sqlBuilder = new StringBuilder();
if (option.InsertModelList.Count > 0)
{
for (int i = 0; i < option.InsertModelList.Count; i++)
{
string colunmPart = "";
string valuePart = "";
foreach (var column in option.InsertModelList[i].GetType().GetProperties())
{
if (column.Name.ToLower() != "id")
{
colunmPart += column.Name + ",";
if (option.ParentIdColum != "" && column.Name.ToLower() == option.ParentIdColum.ToLower() && i > 0)
{
valuePart += "@ParentID ,";
}
else
{
valuePart += "'" + column.GetValue(option.InsertModelList[i], null) + "' ,";
}
}
}
if (tableName != option.InsertModelList[i].GetType().Name)
{
if (i > 0)
{
sqlBuilder.Append("; ");
if (option.ParentIdColum != "" && i == 1)
{
sqlBuilder.Append(" declare @ParentID int; ");
sqlBuilder.Append(" set @ParentID=SCOPE_IDENTITY(); ");
}
}
sqlBuilder.Append("INSERT INTO ");
sqlBuilder.Append(" [" + option.InsertModelList[i].GetType().Name + "] ");
sqlBuilder.Append(" (" + colunmPart.TrimEnd(',') + ") VALUES ");
sqlBuilder.Append(" ( " + valuePart.TrimEnd(',') + " ) ");
}
else
{
sqlBuilder.Append(" ,( " + valuePart.TrimEnd(',') + " ) ");
}
tableName = option.InsertModelList[i].GetType().Name;
}
sqlBuilder.Append(";");
}
return(sqlBuilder.ToString());
}
/// <summary>
/// 指定更新字段或指定where条件或需要 批处理时调用
/// </summary>
/// <param name="option">SQLOption</param>
/// <returns>SQL语句</returns>
public static string GetUpdateSQL(SQLOption option)
{
string sql = "";
if (option.UpdateModelList.Count > 0)
{
foreach (var updateModel in option.UpdateModelList)
{
if (updateModel.TableName != "" && updateModel.UpdateColumnDic.Count > 0 && updateModel.WhereColumnDic.Count > 0 || updateModel.UpdateColumnList.Count > 0)
{
string valuePart = "";
string optionPart = "";
string wherePart = "";
foreach (var item in updateModel.UpdateColumnDic)
{
valuePart += string.Format(" {0}='{1}',", item.Key, item.Value);
}
valuePart = valuePart.TrimEnd(",".ToCharArray());
foreach (var item in updateModel.WhereColumnDic)
{
optionPart += string.Format(" {0}='{1}' and", item.Key, item.Value);
}
optionPart = optionPart.TrimEnd("and".ToCharArray());
if (updateModel.UpdateColumnList.Count > 0)
{
foreach (var item in updateModel.UpdateColumnList)
{
if (item.Symbol == "replace")
{
wherePart += string.Format(", {0} = {1} ('{2}') and", item.Field, item.Symbol, item.Val);
}
}
wherePart = wherePart.TrimEnd("and".ToCharArray());
}
sql += string.Format(@"Update [{0}] set {1}{3} where {2} ;", updateModel.TableName, valuePart, optionPart, wherePart);
}
}
}
return(sql);
}
/// <summary>
/// 多条件 批量删除
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
public static string GetDeleteSQL(SQLOption option)
{
string sql = "";
if (option.DeleteModelList.Count > 0)
{
foreach (var item in option.DeleteModelList)
{
if (item.TableName != "" && item.WhereColumnDic.Count > 0)
{
string wherePart = "";
foreach (var whereDic in item.WhereColumnDic)
{
wherePart += string.Format(" {0}='{1}' and", whereDic.Key, whereDic.Value);
}
wherePart = wherePart.TrimEnd("and".ToCharArray());
sql += string.Format(@"DELETE FROM [{0}] WHERE {1} ;", item.TableName, wherePart);
}
}
}
return(sql);
}
/// <summary>
/// 指定更新字段或指定where条件或需要 批处理时调用
/// </summary>
/// <param name="option">SQLOption</param>
/// <returns>SQL语句</returns>
public static string GetSelectSQL(SQLOption option)
{
string sql = "";
if (option.SelectModelList.Count > 0)
{
foreach (var selectModel in option.SelectModelList)
{
if (selectModel.TableName != "")
{
string selectPart = "";
string wherePart = "";
string groupPart = "";
string orderPart = "";
string descPart = "";
if (selectModel.IsAllColumn || selectModel.SelectColumnList.Count == 0)
{
selectModel.SelectColumnList.Add("*");
}
selectPart = String.Join(",", selectModel.SelectColumnList.ToArray());
if (selectModel.WhereColumnDic.Count > 0)
{
wherePart = " WHERE ";
foreach (var item in selectModel.WhereColumnDic)
{
wherePart += string.Format(" {0}='{1}' and", item.Key, item.Value);
}
wherePart = wherePart.TrimEnd("and".ToCharArray());
}
if (selectModel.WhereColumnList.Count > 0)
{
wherePart = " WHERE ";
foreach (var item in selectModel.WhereColumnList)
{
if (item.Symbol == "=" || item.Symbol == ">" || item.Symbol == ">=" || item.Symbol == "<" || item.Symbol == "<=")
{
wherePart += string.Format(" {0}{1} '{2}' and", item.Field, item.Symbol, item.Val);
}
else if (item.Symbol == "like")
{
wherePart += string.Format(" {0}{1}'%{2}%' and", item.Field, item.Symbol, item.Val);
}
else if (item.Symbol == "llike")
{
wherePart += string.Format(" {0}{1}'%{2}' and", item.Field, item.Symbol, item.Val);
}
else if (item.Symbol == "rlike")
{
wherePart += string.Format(" {0}{1}'{2}%' and", item.Field, item.Symbol, item.Val);
}
else if (item.Symbol == "in")
{
wherePart += string.Format(" {0}{1} ('{2}') and", item.Field, item.Symbol, item.Val);
}
}
wherePart = wherePart.TrimEnd("and".ToCharArray());
}
if (selectModel.GroupByColumnList.Count > 0)
{
groupPart = " GROUP BY ";
groupPart += String.Join(",", selectModel.GroupByColumnList.ToArray());
}
if (selectModel.OrderByColumnList.Count > 0)
{
orderPart = " ORDER BY ";
orderPart += String.Join(",", selectModel.OrderByColumnList.ToArray());
if (selectModel.IsDesc == false)
{
descPart = " ASC ";
}
else
{
descPart = " DESC ";
}
}
sql += string.Format(@"SELECT {0} FROM [{1}] {2} {3} {4} {5} ;", selectPart, selectModel.TableName, wherePart, groupPart, orderPart, descPart);
}
}
}
return(sql);
}
