/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string Select(SqlModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
foreach (var item in model.Members)
{
if (model.ColFunction != null)
{
string sourceName = model.ColFunction(item);
if (sourceName != item)
{
sql.Append(model.Left);
sql.Append(model.ColFunction(item)).Append(" AS ");
sql.Append(model.Right);
}
}
sql.Append(model.Left);
sql.Append(item);
sql.Append(model.Right);
sql.Append(",");
}
sql.Length -= 1;
sql.Append(" FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>更新字符串结果</returns>
public string UpdateWhere(SqlModel model)
{
StringBuilder sql = new StringBuilder(Update(model));
sql.Append(" WHERE ");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有委托处理的Model</param>
/// <param name="conditions">需要匹配的成员集合</param>
/// <returns>条件字符串结果</returns>
public string Condition(SqlModel model, params string[] conditions)
{
if (conditions == null)
{
return(string.Empty);
}
StringBuilder sql = new StringBuilder(" WHERE ");
for (int i = 0; i < conditions.Length; i += 1)
{
sql.Append(model.Left);
if (model.ColFunction != null)
{
sql.Append(model.ColFunction(conditions[i]));
}
else
{
sql.Append(conditions[i]);
}
sql.Append(model.Right);
sql.Append("=@");
if (model.FilterFunction != null)
{
sql.Append(model.FilterFunction(conditions[i]));
}
else
{
sql.Append(conditions[i]);
}
sql.Append(" AND ");
}
sql.Length -= 5;
return(sql.ToString());
}
public string Condition(SqlModel model, IEnumerable <string> conditions)
{
StringBuilder sql = new StringBuilder(" WHERE ");
foreach (var item in conditions)
{
sql.Append(model.Left);
if (model.ColFunction != null)
{
sql.Append(model.ColFunction(item));
}
else
{
sql.Append(item);
}
sql.Append(model.Right);
sql.Append("=@");
if (model.FilterFunction != null)
{
sql.Append(model.FilterFunction(item));
}
else
{
sql.Append(item);
}
sql.Append(" AND ");
}
if (sql.Length > 0)
{
sql.Length -= 5;
}
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT * FROM [TableName] WHERE
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectAllWhere(SqlModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append(SelectAll(model));
sql.Append(" WHERE ");
return(sql.ToString());
}
/// <summary>
/// 生成 DELETE FROM [TableName] WHERE [condition1]=@condition1 AND [condition2]=@condition2
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="conditions">需要匹配的成员集合</param>
/// <returns>删除字符串结果</returns>
public string DeleteWithCondition(SqlModel model, params string[] conditions)
{
StringBuilder sql = new StringBuilder(Delete(model));
ConditionTemplate template = new ConditionTemplate();
sql.Append(template.Condition(model, conditions));
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 INSERT INTO [TableName] ([member1],[member2]...) VALUES (@member1,@member2...)
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>插入字符串的结果</returns>
public string Insert(SqlModel parameter_model)
{
var model = parameter_model;
if (!parameter_model.PrimaryManually)
{
model = parameter_model.ModelWithoutPrimary();
model.FilterFunction = parameter_model.FilterFunction;
}
StringBuilder pre_str = new StringBuilder(20);
StringBuilder aft_str = new StringBuilder(20);
pre_str.Append(" (");
aft_str.Append('(');
foreach (var item in model.Members)
{
pre_str.Append(model.Left);
if (model.ColFunction != null)
{
pre_str.Append(model.ColFunction(item));
}
else
{
pre_str.Append(item);
}
pre_str.Append(model.Right);
pre_str.Append(',');
aft_str.Append('@');
if (model.FilterFunction != null)
{
aft_str.Append(model.FilterFunction(item));
}
else
{
aft_str.Append(item);
}
aft_str.Append(',');
}
pre_str.Length -= 1;
aft_str.Length -= 1;
pre_str.Append(')');
aft_str.Append(')');
StringBuilder sql = new StringBuilder(40);
sql.Append("INSERT INTO ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
sql.Append(pre_str);
sql.Append("VALUES");
sql.Append(aft_str);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName] WHERE [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition_models">需要匹配的成员集合</param>
/// <returns>查询字符串结果</returns>
public string SelectWithCondition(SqlModel model, params string[] conditions)
{
var select = Select(model);
StringBuilder sql = new StringBuilder(select);
ConditionTemplate template = new ConditionTemplate();
sql.Append(template.Condition(model, conditions));
return(sql.ToString());
}
/// <summary>
/// 静态创建一个Link对象
/// </summary>
/// <param name="handler">DapperWrapper对象</param>
/// <returns></returns>
public static SqlLink <T> Load(DapperWrapper <T> handler)
{
SqlLink <T> instance = new SqlLink <T>();
instance._handler = handler;
instance._model = SqlModel <T> .CopyInstance();
return(instance);
}
/// <summary>
/// 根据model信息生成 SELECT COUNT(*) FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectCount(SqlModel model)
{
StringBuilder sql = new StringBuilder(23 + model.TableName.Length);
sql.Append("SELECT COUNT(*) FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT * FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectAll(SqlModel model)
{
StringBuilder sql = new StringBuilder(16 + model.TableName.Length);
sql.Append("SELECT * FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 DELETE FROM [TableName]
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>删除字符串结果</returns>
public string Delete(SqlModel model)
{
StringBuilder sql = new StringBuilder(21 + model.TableName.Length);
sql.Append("DELETE FROM ");
sql.Append(model.Left);
sql.Append(model.TableName);
sql.Append(model.Right);
//sql.Append(" WHERE ");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 SELECT * FROM [TableName] WHERE [PrimaryKey] IN @keys
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectAllIn(SqlModel model)
{
StringBuilder sql = new StringBuilder();
sql.Append(SelectAllWhere(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append(" IN @keys");
return(sql.ToString());
}
/// <summary>
/// 根据model信息生成 DELETE FROM [TableName] WHERE [PrimaryKey] =@PrimaryKey
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>删除字符串结果</returns>
public string DeleteByPrimary(SqlModel model)
{
if (model.PrimaryKey != null)
{
StringBuilder sql = new StringBuilder(23 + model.TableName.Length + model.PrimaryKey.Length * 2);
sql.Append(DeleteWhere(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append("=@");
sql.Append(model.PrimaryKey);
return(sql.ToString());
}
return(null);
}
/// <summary>
/// 根据model信息生成 SELECT * FROM [TableName] WHERE [PrimaryKey] = @PrimaryKey
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public string SelectAllByPrimary(SqlModel model)
{
if (model.PrimaryKey != null)
{
StringBuilder sql = new StringBuilder();
sql.Append(SelectAllWhere(model));
sql.Append(model.Left);
sql.Append(model.PrimaryKey);
sql.Append(model.Right);
sql.Append("=@");
sql.Append(model.PrimaryKey);
return(sql.ToString());
}
return(null);
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...)
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>更新字符串结果</returns>
public string Update(SqlModel model)
{
var temp_model = model.ModelWithoutPrimary();
StringBuilder update = new StringBuilder(40);
foreach (var item in temp_model.Members)
{
update.Append(temp_model.Left);
if (temp_model.ColFunction != null)
{
update.Append(temp_model.ColFunction(item));
}
else
{
update.Append(item);
}
update.Append(temp_model.Right);
update.Append("=@");
if (temp_model.FilterFunction != null)
{
update.Append(temp_model.FilterFunction(item));
}
else
{
update.Append(item);
}
update.Append(',');
}
StringBuilder sql = new StringBuilder(60);
if (update.Length > 0)
{
update.Length -= 1;
sql.Append("UPDATE ");
sql.Append(temp_model.Left);
sql.Append(temp_model.TableName);
sql.Append(temp_model.Right);
sql.Append(" SET ");
sql.Append(update);
//sql.Append(" WHERE ");
}
return(sql.ToString());
}
/// <summary>
/// 生成 DELETE FROM [TableName] WHERE [condition1]=@condition1 AND [condition2]=@condition2
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="conditions">需要匹配的成员集合</param>
/// <returns>删除字符串结果</returns>
public static string DeleteWithCondition(SqlModel model, params string[] conditions)
{
return(Delete.DeleteWithCondition(model, conditions));
}
/// <summary>
/// 根据model信息生成 SELECT COUNT(*) FROM [TableName] WHERE [Member1]=@Member1 AND [Member2]=@Member2 ....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查重字符串结果</returns>
public static string RepeateCount(SqlModel model)
{
return(Repeate.RepeateCount(model));
}
/// <summary>
/// 生成 DELETE FROM [TableName] WHERE [condition1]=@condition1 AND [condition2]=@condition2
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition">查询字符串</param>
/// <returns>删除字符串结果</returns>
public static string CustomerInsert(SqlModel model)
{
return(Insert.Insert(model));
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName] WHERE + condition
/// </summary>
/// <param name="model"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static string CustomerSelect(SqlModel model, string condition)
{
return($"{Select.Select(model)} WHERE {condition}");
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName] WHERE [PrimaryKey] IN @keys
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <returns>查询字符串结果</returns>
public static string SelectIn(SqlModel model)
{
return(Select.SelectIn(model));
}
/// <summary>
/// 根据model信息生成 SELECT [member1],[member2]... FROM [TableName] WHERE [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition_models">需要匹配的成员集合</param>
/// <returns>查询字符串结果</returns>
public static string SelectWithCondition(SqlModel model, params string[] conditions)
{
return(Select.SelectWithCondition(model, conditions));
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition">查询字符串</param>
/// <returns>更新字符串结果</returns>
public static string CustomerUpdate(SqlModel model, string condition)
{
return($"{Update.Update(model)} WHERE {condition}");
}
/// <summary>
/// 生成 DELETE FROM [TableName] WHERE [condition1]=@condition1 AND [condition2]=@condition2
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition">查询字符串</param>
/// <returns>删除字符串结果</returns>
public static string CustomerDelete(SqlModel model, string condition)
{
return($"{Delete.Delete(model)} WHERE {condition}");
}
/// <summary>
/// 根据model信息生成 UPDATE [TableName] SET([member1]=@member1,[member2]...=@member2...) WHERE [condition1]=@condition,[condition2]=@condition2.....
/// </summary>
/// <param name="model">载有生成信息的Model</param>
/// <param name="condition_models">需要匹配的成员集合</param>
/// <returns>更新字符串结果</returns>
public static string UpdateWithCondition(SqlModel model, params string[] conditions)
{
return(Update.UpdateWithCondition(model, conditions));
}