/// <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="where"></param>
/// <param name="cmd"></param>
/// <returns></returns>
public static DataTable GetData(string TableName, string where, OleDbCommand cmd)
{
Debug.Assert(!string.IsNullOrEmpty(TableName), "表 " + TableName + " 不存在！");
Debug.Assert(!string.IsNullOrEmpty(where), "Where语句为空！");
Debug.Assert(cmd != null, "DbCommand为空！");
string sql = string.Format("select * from {0} where 1=1 {1}", TableName, where);
return(DbHelperACE.Query(sql, cmd).Tables[0]);
}
/// <summary>
/// 得到通用的主键
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="KeyFileid">主键ID</param>
/// <param name="cmd">DbCommand</param>
/// <returns>最大ID</returns>
public static int GetMaxID(string TableName, string KeyFileid, OleDbCommand cmd)
{
Debug.Assert(!string.IsNullOrEmpty(TableName), "表 " + TableName + " 不存在！");
Debug.Assert(!string.IsNullOrEmpty(KeyFileid), "主键ID为空！");
Debug.Assert(cmd != null, "DbCommand为空！");
string sql = string.Format("select max({0}) FROM {1}", KeyFileid, TableName);
string ID = DbHelperACE.GetSingle(sql, cmd).ToString();
return(ID == "" ? 1 : int.Parse(ID) + 1);
}
/// <summary>
/// 获得最大ID
/// </summary>
/// <param name="FieldName">字段名</param>
/// <param name="TableName">表名</param>
/// <returns>最大ID</returns>
public static int GetMaxID(string FieldName, string TableName)
{
Debug.Assert(!string.IsNullOrEmpty(FieldName), "字段 " + FieldName + " 不存在！");
Debug.Assert(!string.IsNullOrEmpty(TableName), "表 " + TableName + " 不存在！");
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = DbHelperACE.GetSingle(strsql);
if (obj == null)
{
return(1);
}
else
{
return(int.Parse(obj.ToString()));
}
}
/// <summary>
/// 检查查询结果是否存在
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回是否存在结果</returns>
public static bool Exists(string strSql)
{
Debug.Assert(!string.IsNullOrEmpty(strSql), "要执行的SQL为空！");
object obj = DbHelperACE.GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <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));
}