/// <summary>
/// 拼接 查询最大数 SQL语句
/// </summary>
/// <param name="propertyName">属性字段</param>
/// <returns></returns>
public static StringBuilder SelectMaxSql <T>(string propertyName) where T : new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
return(new StringBuilder("SELECT MAX(" + propertyName + ") FROM " + table + " WHERE 1=1 "));
}
/// <summary>
/// 拼接 查询 SQL语句，自定义条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where">条件</param>
/// <param name="allFieid">是否查询所有字段</param>
/// <returns></returns>
public static StringBuilder SelectSql <T>(string where, bool allFieid = false) where T : class, new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
PropertyInfo[] props = EntityAttributeHelper.GetProperties(typeof(T));
StringBuilder sbColumns = new StringBuilder();
if (allFieid)
{
sbColumns.Append(" * ");
}
else
{
foreach (PropertyInfo prop in props)
{
//string propertytype = prop.PropertyType.ToString();
sbColumns.Append("[" + prop.Name + "],");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
}
if (string.IsNullOrWhiteSpace(where))
{
where = " WHERE 1 = 1";
}
string strSql = "SELECT {0} FROM {1} {2}";
strSql = string.Format(strSql, sbColumns.ToString(), table + " ", where);
return(new StringBuilder(strSql));
}
/// <summary>
/// 拼接 查询条数 SQL语句
/// </summary>
/// <returns></returns>
public static StringBuilder SelectCountSql <T>() where T : new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
return(new StringBuilder("SELECT Count(1) FROM " + table + " WHERE 1=1 "));
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="where">删除条件</param>
/// <returns></returns>
public static StringBuilder DeleteSql <T>(string where)
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
if (string.IsNullOrWhiteSpace(where))
{
where = "1 = 1";
}
return(new StringBuilder("Delete From " + table + " " + where));
}
/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="pkName">主键</param>
/// <param name="where">自定义条件</param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity, string pkName = "", string where = "")
{
Type type = entity.GetType();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
//主键
string keyField = EntityAttributeHelper.GetEntityKey <T>();
//获取不做映射的字段
List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(table);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if (!notMappedField.Contains(prop.Name))
{
if (prop.GetValue(entity, null) != null && keyField != prop.Name)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append("[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
else
{
sb.Append(",[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
}
}
}
if (!string.IsNullOrEmpty(pkName))
{
sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
}
else if (!string.IsNullOrEmpty(where))
{
sb.Append(" " + where);
}
return(sb);
}
/// <summary>
/// 拼接 查询条数 SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
public static StringBuilder SelectCountSql <T>(string where) where T : new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
if (!string.IsNullOrEmpty(where))
{
if (!where.ToLower().Contains("where"))
{
where = " WHERE " + where;
}
}
return(new StringBuilder("SELECT Count(1) FROM " + table + " " + where));
}
/// <summary>
/// 泛型方法，反射生成InsertSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder InsertSql <T>(T entity)
{
Type type = entity.GetType();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
//获取不做映射的字段
List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(table);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo prop in props)
{
if (!notMappedField.Contains(prop.Name))
{
object value = prop.GetValue(entity, null);
if (value != null)
{
if (value.GetType() == typeof(DateTime))
{
var time = (DateTime)value;
if (time.Ticks != 0 && time.Kind != DateTimeKind.Unspecified)
{
sb_prame.Append(",[" + prop.Name + "]");
sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
}
}
else
{
sb_prame.Append(",[" + prop.Name + "]");
sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
}
}
}
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
return(sb);
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public static StringBuilder DeleteSql <T>(T entity)
{
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
StringBuilder sb = new StringBuilder("Delete From " + table + " Where 1=1");
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
sb.Append(" AND " + prop.Name + " = " + DbParameters.CreateDbParmCharacter() + "" + prop.Name + "");
}
}
return(sb);
}
/// <summary>
/// 拼接 查询 SQL语句
/// </summary>
/// <param name="top">显示条数</param>
/// <returns></returns>
public static StringBuilder SelectSql <T>(int top) where T : new()
{
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
PropertyInfo[] props = EntityAttributeHelper.GetProperties(typeof(T));
StringBuilder sbColumns = new StringBuilder();
foreach (PropertyInfo prop in props)
{
sbColumns.Append(prop.Name + ",");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
string strSql = "SELECT top {0} {1} FROM {2} WHERE 1=1 ";
strSql = string.Format(strSql, top, sbColumns.ToString(), table + " ");
return(new StringBuilder(strSql));
}
/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="where">更新条件</param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity, string where = "")
{
Type type = entity.GetType();
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
//主键
string keyField = EntityAttributeHelper.GetEntityKey <T>();
//获取不做映射的字段
List <string> notMappedField = EntityAttributeHelper.GetNotMappedFields <T>();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(table);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if (!notMappedField.Contains(prop.Name))
{
object value = prop.GetValue(entity, null);
if (value != null && keyField != prop.Name)
{
if (value.GetType() == typeof(DateTime))
{
var time = (DateTime)value;
if (time.Ticks != 0 && time.Kind != DateTimeKind.Unspecified)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append("[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
else
{
sb.Append(",[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
}
}
else
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append("[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
else
{
sb.Append(",[" + prop.Name + "]");
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
}
}
}
}
sb.Append(" Where ").Append(keyField).Append("=").Append(DbParameters.CreateDbParmCharacter() + keyField);
return(sb);
}