[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)]/*用于给扩展类的属性增加特性标识;如果实体类属性有此标识,则不参与脚本运算*/
public class IsExtendFieldAttribute:Attribute
{
}
public class RepUtility//说明sys_是数据库表名的前缀;HashSet是集合属性;本例只支持单表操作
{
public string Sql = "select * from {0} {1} order by {2} {3} ;";
/// <summary>
/// P0:表名,P1:查询条件,P2:排序字段名,P3:排序方式,P4:第几页,P5:每页显示记录数
/// </summary>
public string PageSql = "select * from {0} {1} order by {2} {3} limit {4},{5};";
/*
* Mysql分页:
select * from tableName where 条件 limit (当前页码-1)*页面容量 , 页面容量
*/
#region 注意此方法不通用,可根据相应场景进行调整
/// <summary>
/// 不通用,还在整理中;根据实体类对象生成查询条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <returns></returns>
public string GetCondition<T>(T instance) where T : class
{
if (instance.IsNull())
return string.Empty;
StringBuilder builder = new StringBuilder(" where 1=1");
Type objType = typeof(T);
var properties = objType.GetProperties();
foreach (var property in properties)
{
if (property.IsNotNull() && property.Name.Contains("sys_").IsFalse())
{
object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);
if (objAttrs.IsNotNull() && objAttrs.Length > 0)
{ }
else
{
var value = property.GetValue(instance);
if (value.IsNotNull())
{
if (value.ToString().Trim() == string.Empty || value.ToString().Trim() == "0")
continue;
if (value.GetType().Name.Contains("HashSet"))
continue;
builder.Append(string.Format(" and {0}='{1}'", property.Name, value));
}
}
}
}
return builder.ToString();
}
/// <summary>
/// 根据实体类对象生成更新脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <param name="key">主键,这里单独把可以作为参数是为了更方便扩展</param>
/// <returns></returns>
public string GetUpdateSql<T>(T instance, string key) where T : class
{
if (instance.IsNull())
return string.Empty;
StringBuilder builder = new StringBuilder();
Type objType = typeof(T);
builder.AppendLine("update " + objType.Name + " set");
var properties = objType.GetProperties();
foreach (var property in properties)
{
if (property.Name.Contains("sys_").IsFalse())
{
object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);
if (objAttrs.IsNotNull() && objAttrs.Length > 0)
{ }
else
{
if (property.IsNotNull() && property.Name.ToLower() != key.ToLower())
{
var value = property.GetValue(instance);
//如果是int Not null类型的需要过滤默认值是0的字段
if (value.IsNotNull() && value.ConverToString().IsNullOrEmpty().IsFalse() && value.ToString().Trim() != "0")
{
builder.AppendLine(string.Format("{0}='{1}',", property.Name, value));
}
}
}
}
}
builder.Remove(builder.Length - 3, 3);//移除最有一个逗号和换行符,\r\n占两位;"\r\n".Length=2;
builder.AppendLine(string.Format("where {0}='{1}';", key, objType.GetProperty(key).GetValue(instance)));
return builder.ToString();
}
/// <summary>
/// 根据实体类对象生成插入脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <param name="key"></param>
/// <returns></returns>
public string GetAddSql<T>(T instance, string key) where T : class
{
if (instance.IsNull())
return string.Empty;
Type objType = typeof(T);
var properties = objType.GetProperties();
var tmpProperties = properties.Where(t => t.Name.ToLower() != key.ToLower()).ToList();
//1.获得不为NULL的字段、字段值
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (var property in tmpProperties)
{
if (property.Name.Contains("sys_").IsFalse())
{
object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);
if (objAttrs.IsNotNull() && objAttrs.Length > 0)/*有特性标识则不参与脚本运算*/
{ }
else
{
var value = property.GetValue(instance);
if (value.IsNull())
continue;
dict.Add(property.Name, value);
}
}
}
//2.生成列
StringBuilder builder = new StringBuilder();
builder.AppendLine("insert into " + objType.Name + "(");
string tmp = string.Empty;
foreach (var item in dict)
{
tmp += item.Key + ",";
}
tmp = tmp.TrimEnd(new char[] { ',' }) + ") values(";
builder.AppendLine(tmp);
//3.生成值
tmp = string.Empty;
foreach (var item in dict)
{
tmp += "'" + item.Value.ToString() + "',";
}
tmp = tmp.TrimEnd(new char[] { ',' }) + ");";
builder.AppendLine(tmp);
//4.返回
return builder.ToString();
}
/// <summary>
/// 拼接查询脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="orderby"></param>
/// <param name="sort"></param>
/// <param name="where"></param>
/// <returns></returns>
public string GetSql<T>(string orderby = "id", string sort = "asc", string where = " where 1=1 ") where T : class
{
return string.Format(Sql, typeof(T).Name, where, orderby, sort);
}
#endregion
/// <summary>
/// 拼接查询数量的脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
public string GetCountSql<T>(string where = "where 1=1") where T : class
{
string tbName = typeof(T).Name;
string safeSql = string.Format("select count(*) as count from {0} {1} ;", tbName, where);
return safeSql;
}
/// <summary>
/// 拼接分页的脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="orderby"></param>
/// <param name="sort"></param>
/// <param name="pageCurrentIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public string GetPageSql<T>(string where = "where 1=1", string orderby = "id", string sort = "asc", int pageCurrentIndex = 0, int pageSize = 10) where T : class
{
string tbName = typeof(T).Name;
string safeSql = string.Format(PageSql, tbName, where, orderby, sort, (pageCurrentIndex - 1) * pageSize, pageSize);
return safeSql;
}
#region 可用列表
/// <summary>
/// 根据参数生成查询条件脚本
/// </summary>
/// <param name="list">List<Tuple<字段, 操作符, 参数名,参数值>></param>
/// <returns></returns>
public string GetConditionWithParam(List<Tuple<string, string, string, string>> listPara, char flag = '?')
{
if (listPara.IsNull() || listPara.Count == 0)
return " where 1=1";
StringBuilder builder = new StringBuilder();
builder.AppendLine(" where 1=1");
foreach (var item in listPara)
{
// and id=?id//and id=@id
builder.AppendLine(string.Format(" and {0}{1}{2}{3}", item.Item1, item.Item2, flag, item.Item3));
}
return builder.ToString();
}
/// <summary>
/// 根据实体类对象生成参数化的更新脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <param name="key"></param>
/// <param name="listPara"></param>
/// <param name="flag"></param>
/// <returns></returns>
public string GetUpdateSql<T>(T instance, string key, ref List<Tuple<string, string, string, object>> listPara, char flag = '?') where T : class
{
if (instance.IsNull())
return string.Empty;
StringBuilder builder = new StringBuilder();
Type objType = typeof(T);
builder.AppendLine("update " + objType.Name + " set");
var properties = objType.GetProperties().Where(p => p.Name.ToLower() != key.ToLower() && p.Name.ToLower().Contains("sys_") == false).ToList();
foreach (var property in properties)
{
if (property.Name.Contains("sys_").IsFalse())
{
object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);
if (objAttrs.IsNotNull() && objAttrs.Length > 0)
{ }
else
{
if (property.IsNotNull() && property.Name.ToLower() != key.ToLower())
{
var value = property.GetValue(instance);
//如果是int Not null类型的需要过滤默认值是0的字段
if (value.IsNotNull() && value.ConverToString().IsNullOrEmpty().IsFalse() && value.ToString().Trim() != "0")
{
builder.AppendLine(string.Format("{0}={1}{2},", property.Name, flag, property.Name));
listPara.Add(new Tuple<string, string, string, object>(property.Name, "=", property.Name, value));
}
}
}
}
}
builder.Remove(builder.Length - 3, 1);//移除最有一个逗号,\r\n占两位;"\r\n".Length=2;
builder.AppendLine(string.Format("where {0}={1}{2};", key, flag, key));
listPara.Add(new Tuple<string, string, string, object>(key, "=", flag + key, objType.GetProperty(key).GetValue(instance)));
return builder.ToString();
}
/// <summary>
/// 根据实体类对象生成参数化的插入脚本
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <param name="key"></param>
/// <param name="listPara"></param>
/// <param name="flag"></param>
/// <returns></returns>
public string GetAddSql<T>(T instance, string key, ref List<Tuple<string, string, string, object>> listPara, char flag = '?') where T : class
{
if (instance.IsNull())
return string.Empty;
Type objType = typeof(T);
var properties = objType.GetProperties();
var tmpProperties = properties.Where(p => p.Name.ToLower() != key.ToLower() && p.Name.Contains("sys_") == false).ToList();
//1.获得不为NULL的字段、字段值
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (var property in tmpProperties)
{
if (property.Name.Contains("sys_").IsFalse())
{
object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);
if (objAttrs.IsNotNull() && objAttrs.Length > 0)
{ }
else
{
var value = property.GetValue(instance);
if (value.IsNull())
continue;
dict.Add(property.Name, value);
}
}
}
//2.生成列
StringBuilder builder = new StringBuilder();
builder.AppendLine("insert into " + objType.Name + "(");
string tmp = string.Empty;
foreach (var item in dict)
{
tmp += item.Key + ",";
}
tmp = tmp.TrimEnd(new char[] { ',' }) + ") values(";
builder.AppendLine(tmp);
//3.生成值
tmp = string.Empty;
foreach (var item in dict)
{
tmp = tmp + string.Format("{0}{1},", flag, item.Key);
listPara.Add(new Tuple<string, string, string, object>(item.Key, "=", flag + item.Key, item.Value));
}
tmp = tmp.TrimEnd(new char[] { ',' }) + ");";
builder.AppendLine(tmp);
//4.返回
return builder.ToString();
}
#endregion
}