/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="pkName">主键</param>
/// <param name="ParamKey">
/// 参数化
/// Oracle :
/// SQLServer @
/// MySql ?
/// </param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity, string pkName)
{
SoftRegHelper Verify = new SoftRegHelper();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(type.Name);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(prop.Name);
sb.Append("=");
sb.Append(ParamKey + prop.Name);
}
else
{
sb.Append("," + prop.Name);
sb.Append("=");
sb.Append(ParamKey + prop.Name);
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append(ParamKey + pkName);
return(sb);
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <returns></returns>
public static StringBuilder DeleteSql(string tableName, string pkName)
{
SoftRegHelper Verify = new SoftRegHelper();
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where " + pkName + " = " + ParamKey + pkName + "");
return(sb);
}
/// <summary>
/// 泛型方法，反射生成InsertSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <param name="ParamKey">
/// 参数化
/// Oracle :
/// SQLServer @
/// MySql ?
/// </param>
/// <returns>int</returns>
public static StringBuilder InsertSql <T>(T entity)
{
SoftRegHelper Verify = new SoftRegHelper();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(type.Name);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
sb_prame.Append("," + (prop.Name));
sp.Append("," + ParamKey + "" + (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>
/// 哈希表生成UpdateSql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">主键</param>
/// <param name="ht">Hashtable</param>
/// <param name="ParamKey">
/// 参数化
/// Oracle :
/// SQLServer @
/// MySql ?
/// </param>
/// <returns></returns>
public static StringBuilder UpdateSql(string tableName, string pkName, Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
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)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append(ParamKey + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append(ParamKey + key);
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append(ParamKey + pkName);
return(sb);
}
/// <summary>
/// 实体类对象参数转换
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static SqlParam[] GetParameter <T>(T entity)
{
SoftRegHelper Verify = new SoftRegHelper();
DbType dbtype = new DbType();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
IList <SqlParam> sqlparam = new List <SqlParam>();
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
if (prop.PropertyType.ToString() == "System.Nullable`1[System.DateTime]")
{
dbtype = DbType.DateTime;
}
else
{
dbtype = DbType.AnsiString;
}
sqlparam.Add(new SqlParam(ParamKey + prop.Name, dbtype, prop.GetValue(entity, null)));
}
}
return(sqlparam.ToArray());
}
/// <summary>
/// 将IDataReader转换为 集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns></returns>
public static List <T> ReaderToList <T>(IDataReader dr)
{
SoftRegHelper Verify = new SoftRegHelper();
using (dr)
{
List <string> field = new List <string>(dr.FieldCount);
for (int i = 0; i < dr.FieldCount; i++)
{
field.Add(dr.GetName(i).ToLower());
}
List <T> list = new List <T>();
while (dr.Read())
{
T model = Activator.CreateInstance <T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
if (!IsNullOrDBNull(dr[property.Name]))
{
property.SetValue(model, HackType(dr[property.Name], property.PropertyType), null);
}
}
}
list.Add(model);
}
return(list);
}
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">多参数</param>
/// <returns></returns>
public static StringBuilder DeleteSql(string tableName, Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where 1=1");
foreach (string key in ht.Keys)
{
sb.Append(" AND " + key + " = " + DbCommon.ParamKey + "" + key + "");
}
return(sb);
}
/// <summary>
/// 将DataRow转换为 实体类
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns></returns>
public static T ReaderToModel <T>(DataRow dr)
{
SoftRegHelper Verify = new SoftRegHelper();
T model = Activator.CreateInstance <T>();
foreach (PropertyInfo pi in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (!IsNullOrDBNull(dr[pi.Name]))
{
pi.SetValue(model, HackType(dr[pi.Name], pi.PropertyType), null);
}
}
return(model);
}
/// <summary>
/// 通过Hashtable对象构造键值
/// </summary>
/// <param name="database">数据库</param>
/// <param name="cmd">SQL命令</param>
/// <param name="_params">参数化</param>
public static void AddInParameter(Database database, DbCommand cmd, Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
if (ht == null)
{
return;
}
foreach (string key in ht.Keys)
{
if (key == "Msg")
{
database.AddOutParameter(cmd, ParamKey + key, DbType.AnsiString, 1000);
}
else
{
database.AddInParameter(cmd, ParamKey + key, DbType.AnsiString, ht[key]);
}
}
}
/// <summary>
/// 通过Hashtable对象构造键值
/// </summary>
/// <param name="database">数据库</param>
/// <param name="cmd">SQL命令</param>
/// <param name="_params">参数化</param>
public static void AddMoreParameter(Database database, DbCommand cmd, Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
if (ht == null)
{
return;
}
foreach (string key in ht.Keys)
{
if (key.StartsWith("OUT_"))
{
string tmp = key.Remove(0, 4);
database.AddOutParameter(cmd, ParamKey + tmp, DbType.AnsiString, 1000);
}
else
{
database.AddInParameter(cmd, ParamKey + key, DbType.AnsiString, ht[key]);
}
}
}
/// <summary>
/// 将IDataReader转换为DataTable
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static DataTable ReaderToDataTable(IDataReader reader)
{
SoftRegHelper Verify = new SoftRegHelper();
DataTable objDataTable = new DataTable("Table");
int intFieldCount = reader.FieldCount;
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return(objDataTable);
}
/// <summary>
/// 哈希表生成InsertSql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <param name="ParamKey">
/// 参数化
/// Oracle :
/// SQLServer @
/// MySql ?
/// </param>
/// <returns>int</returns>
public static StringBuilder InsertSql(string tableName, Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
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("," + ParamKey + "" + 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>
/// 通过参数类构造键值
/// </summary>
/// <param name="database">数据库</param>
/// <param name="cmd">SQL命令</param>
/// <param name="_params">参数化</param>
public static void AddInParameter(Database database, DbCommand cmd, SqlParam[] _params)
{
SoftRegHelper Verify = new SoftRegHelper();
if (_params == null)
{
return;
}
DbType dbtype = new DbType();
foreach (SqlParam _param in _params)
{
if (_param.FiledValue is DateTime)
{
dbtype = DbType.DateTime;
}
else
{
dbtype = DbType.AnsiString;
}
database.AddInParameter(cmd, _param.FieldName, dbtype, _param.FiledValue);
}
}
/// <summary>
/// Hashtable对象参数转换
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public static SqlParam[] GetParameter(Hashtable ht)
{
SoftRegHelper Verify = new SoftRegHelper();
DbType dbtype = new DbType();
IList <SqlParam> sqlparam = new List <SqlParam>();
foreach (string key in ht.Keys)
{
if (ht[key] != null)
{
if (ht[key] is DateTime)
{
dbtype = DbType.DateTime;
}
else
{
dbtype = DbType.AnsiString;
}
sqlparam.Add(new SqlParam(ParamKey + key, dbtype, ht[key]));
}
}
return(sqlparam.ToArray());
}
/// <summary>
/// 构造方法
/// </summary>
public DbHelperExpand()
{
SoftRegHelper Verify = new SoftRegHelper();
}