/// <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>
/// 为即将执行准备一个命令
/// </summary>
/// <param name="conn">SqlConnection对象</param>
/// <param name="cmd">SqlCommand对象</param>
/// <param name="isOpenTrans">DbTransaction对象</param>
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
/// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>
/// <param name="dbParameter">执行命令所需的sql语句对应参数</param>
private void PrepareCommand(DbConnection conn, IDbCommand cmd, DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] dbParameter)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (isOpenTrans != null)
{
cmd.Transaction = isOpenTrans;
}
cmd.CommandType = cmdType;
if (dbParameter != null)
{
dbParameter = DbParameters.ToDbParameter(dbParameter);
foreach (var parameter in dbParameter)
{
cmd.Parameters.Add(parameter);
}
}
}
/// <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.GetEntityTable <T>());
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(entity);
if (value == null || value == DBNull.Value)
{
continue;
}
var attributesIdentity = prop.GetCustomAttributes(typeof(IdentityAttribute), true) as IdentityAttribute[];
if (attributesIdentity != null && attributesIdentity.Length > 0)
{
continue;
}
var attributesInsert = prop.GetCustomAttributes(typeof(InsertIgnoreAttribute), true) as InsertIgnoreAttribute[];
if (attributesInsert != null && attributesInsert.Length > 0)
{
continue;
}
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 + ""));
}