/// <summary>
/// 更新数据
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="htValues">更新的键值对</param>
/// <param name="htWhere">条件</param>
/// <returns>返回更新执行的结果</returns>
public static int UpdateByWhere(string TableName, Hashtable htValues, Hashtable htWhere)
{
Debug.Assert(!string.IsNullOrEmpty(TableName), "表 " + TableName + " 不存在！");
Debug.Assert(htValues != null, "更新的键值对为空！");
Debug.Assert(htWhere != null, "更新的条件对为空！");
string ValuesList = string.Empty;
string WhereList = string.Empty;
StringBuilder sqlString = new StringBuilder();
OleDbParameter[] prams;
int Count = 0;
sqlString.Append("update " + TableName + " set ");
prams = new OleDbParameter[htValues.Count];
foreach (DictionaryEntry item in htValues)
{
if (!string.IsNullOrEmpty(item.Key.ToString()))
{
ValuesList += item.Key.ToString() + "=@" + item.Key.ToString() + ",";
}
prams[Count] = new OleDbParameter("@" + item.Key.ToString(), item.Value);
Count++;
}
foreach (DictionaryEntry d in htWhere)
{
WhereList += d.Key.ToString() + "=" + d.Value.ToString() + " AND ";
}
sqlString.Append(ValuesList.Substring(0, ValuesList.Length - 1) + " where " + WhereList.Substring(0, WhereList.Length - 5));
return(DbHelperACE.ExecuteSql(sqlString.ToString(), prams));
}
/// <summary>
/// 根据条件操作数据 查询 删除
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="ht">参数键值对</param>
/// <param name="Orderby">排序字段</param>
/// <param name="type">操作方式</param>
/// <returns>结果表</returns>
public static DataTable DataByWhere(string TableName, Hashtable ht, string Orderby, OperateType type)
{
Debug.Assert(!string.IsNullOrEmpty(TableName), "表 " + TableName + " 不存在！");
Debug.Assert(ht != null, "参数键值对为空！");
DataTable dt = new DataTable();
if (ht.Count > 0)
{
string ValuesList = string.Empty;
string FieldsList = string.Empty;
OleDbParameter[] prams = new OleDbParameter[ht.Count];
int Count = 0;
StringBuilder sqlString = new StringBuilder();
foreach (DictionaryEntry d in ht)
{
ValuesList += d.Key.ToString() + "=@" + d.Key.ToString() + " AND ";
prams[Count] = new OleDbParameter("@" + d.Key.ToString(), d.Value);
Count++;
}
switch (type)
{
case OperateType.Delete:
sqlString.Append("delete from " + TableName + " where " + ValuesList.Substring(0, ValuesList.Length - 5));
DbHelperACE.ExecuteSql(sqlString.ToString(), prams);
break;
case OperateType.Select:
sqlString.Append("select * from " + TableName + " where " + ValuesList.Substring(0, ValuesList.Length - 5) + (Orderby == "" ? "" : " order by " + Orderby));
dt = DbHelperACE.Query(sqlString.ToString(), prams).Tables[0];
break;
default:
break;
}
}
return(dt);
}
/// <summary>
/// 执行sql语句操作
/// </summary>
/// <param name="SQLString">sql语句</param>
/// <param name="cmd">传入的OleDbCommand 对象</param>
/// <returns>返回sql执行影响的记录数</returns>
public static int ExecuteSql(string SQLString, OleDbCommand cmd)
{
Debug.Assert(!string.IsNullOrEmpty(SQLString), "要执行的SQL为空！");
Debug.Assert(cmd != null, "DbCommand为空！");
return(DbHelperACE.ExecuteSql(SQLString, cmd));
}
