/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity)
{
string pkName = GetKeyField <T>().ToString();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(EntityAttribute.GetTableName <T>());
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null && pkName != 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);
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
return(sb);
}
/// <summary>
/// 哈希表生成UpdateSql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <param name="pkName">主键</param>
/// <returns></returns>
public static StringBuilder UpdateSql(string tableName, Hashtable ht, string pkName)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(tableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (ht[key] != null && pkName != key)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + key);
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
return(sb);
}
/// <summary>
/// 对象参数转换DbParameter
/// </summary>
/// <returns></returns>
public static DbParameter[] GetParameter <T>(T entity)
{
IList <DbParameter> parameter = new List <DbParameter>();
DbType dbtype = new DbType();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo pi in props)
{
var obj = pi.GetValue(entity, null);
if (obj != null)
{
switch (pi.PropertyType.ToString())
{
case "System.Nullable`1[System.Int32]":
dbtype = DbType.Int32;
break;
case "System.Nullable`1[System.Decimal]":
dbtype = DbType.Decimal;
break;
case "System.Nullable`1[System.DateTime]":
dbtype = DbType.DateTime;
break;
default:
dbtype = DbType.String;
break;
}
parameter.Add(DbParameters.CreateDbParameter(DbParameters.CreateDbParmCharacter() + pi.Name, obj, dbtype));
}
}
return(parameter.ToArray());
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">多参数</param>
/// <returns></returns>
public static StringBuilder DeleteSql(string tableName, Hashtable ht)
{
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where 1=1");
foreach (string key in ht.Keys)
{
sb.Append(" AND " + key + " = " + DbParameters.CreateDbParmCharacter() + "" + key + "");
}
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();
StringBuilder sb = new StringBuilder("Delete From " + EntityAttribute.GetTableName <T>() + " 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>
/// 对象参数转换DbParameter
/// </summary>
/// <returns></returns>
public static DbParameter[] GetParameter(Hashtable ht)
{
IList <DbParameter> parameter = new List <DbParameter>();
DbType dbtype = new DbType();
foreach (string key in ht.Keys)
{
if (ht[key] is DateTime)
{
dbtype = DbType.DateTime;
}
else
{
dbtype = DbType.String;
}
parameter.Add(DbParameters.CreateDbParameter(DbParameters.CreateDbParmCharacter() + key, ht[key], dbtype));
}
return(parameter.ToArray());
}
/// <summary>
/// 哈希表生成Insert语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static StringBuilder InsertSql(string tableName, Hashtable ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(tableName);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (string key in ht.Keys)
{
if (ht[key] != null)
{
sb_prame.Append("," + key);
sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + key);
}
}
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>
/// 泛型方法，反射生成InsertSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder InsertSql <T>(T entity)
{
Type type = entity.GetType();
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(EntityAttribute.GetTableName <T>());
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
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="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <returns></returns>
public static StringBuilder DeleteSql(string tableName, string pkName)
{
return(new StringBuilder("Delete From " + tableName + " Where " + pkName + " = " + DbParameters.CreateDbParmCharacter() + pkName + ""));
}