public class Update
{
private static string UPDATE_SQL = @"UPDATE {0} SET {1} WHERE {2}";//更新语句
private DbHelper _DbHelper = null; //数据操作类
private string _TableName = string.Empty; //表名
private string _Where = string.Empty; //条件
private Hashtable _Columns = new Hashtable(); //字段集合
private DynamicParameters _Parameters=new DynamicParameters(); //参数
/// <summary>
/// 初始化
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="tableName"></param>
public Update(DbHelper dbHelper, string tableName)
{
_DbHelper = dbHelper;
_TableName = tableName;
}
/// <summary>
/// 条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public Update Where(string where)
{
_Where = where;
return this;
}
/// <summary>
/// 初始化列名称/值 集合
/// </summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <returns></returns>
public Update Column(string name, object value)
{
_Columns.Add(name, value);
return this;
}
/// <summary>
/// 初始化列名称/值 集合
/// </summary>
/// <param name="columns">列名称/值 集合</param>
/// <returns></returns>
public Update Column(Hashtable columns)
{
_Columns = columns;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters">参数</param>
/// <returns></returns>
public Update Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Count(); i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 执行更新
/// </summary>
/// <returns></returns>
public int Execute()
{
if (_Columns.Count <= 0)
return -1;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
string columnNames = "";
foreach (string key in _Columns.Keys)
{
columnNames += key + "=@" + key + ",";
_Parameters.Add("@" + key, _Columns[key]);
}
string sql = string.Format(UPDATE_SQL, _TableName, columnNames.TrimEnd(','), _Where);
return _DbHelper.Execute(sql, _Parameters);
}
}
/// <summary>
/// 实体类型更新
/// </summary>
/// <typeparam name="T"></typeparam>
public class Update_T<T>
{
private static string UPDATE_SQL = @"UPDATE {0} SET {1} WHERE {2}";
private DbHelper _DbHelper = null; //数据操作类
private T _Model; //实体模型
private string _TableName = string.Empty; //表名
private Hashtable _Columns = new Hashtable(); //字段集合
private DynamicParameters _Parameters=new DynamicParameters(); //参数
private string _Where = string.Empty; //条件
private object[] _PrimaryKey; //过滤的主外键集合
/// <summary>
/// 初始化
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="tableName"></param>
/// <param name="model"></param>
public Update_T(DbHelper dbHelper, string tableName, T model)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Model = model;
}
/// <summary>
/// 更新条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public Update_T<T> Where(string where)
{
_Where = where;
return this;
}
/// <summary>
/// 过滤的主外键参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Update_T<T> PrimaryKeys(params object[] primarykeys)
{
if (primarykeys != null)
{
_PrimaryKey = primarykeys;
}
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Update_T<T> Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Count(); i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
private string UpdateSQL(T model)
{
StringBuilder sbColumns = new StringBuilder();
PropertyInfo[] _PropertyInfos = model.GetType().GetProperties();
foreach (PropertyInfo _PropertyInfo in _PropertyInfos)
{
if (null != _PropertyInfo.GetValue(model, null))
{
if (!_PrimaryKey.Contains(_PropertyInfo.Name))
{
sbColumns.Append(_PropertyInfo.Name + "=@" + _PropertyInfo.Name + ",");
_Parameters.Add("@" + _PropertyInfo.Name, _PropertyInfo.GetValue(model));
}
}
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
string strSql = string.Format(UPDATE_SQL, _TableName, sbColumns.ToString(),_Where);
return strSql;
}
/// <summary>
/// 执行修改
/// </summary>
/// <returns></returns>
public int Execute()
{
string sql = UpdateSQL(_Model);
return _DbHelper.Execute(sql, _Parameters);
}
}
{
private static string UPDATE_SQL = @"UPDATE {0} SET {1} WHERE {2}";//更新语句
private DbHelper _DbHelper = null; //数据操作类
private string _TableName = string.Empty; //表名
private string _Where = string.Empty; //条件
private Hashtable _Columns = new Hashtable(); //字段集合
private DynamicParameters _Parameters=new DynamicParameters(); //参数
/// <summary>
/// 初始化
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="tableName"></param>
public Update(DbHelper dbHelper, string tableName)
{
_DbHelper = dbHelper;
_TableName = tableName;
}
/// <summary>
/// 条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public Update Where(string where)
{
_Where = where;
return this;
}
/// <summary>
/// 初始化列名称/值 集合
/// </summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <returns></returns>
public Update Column(string name, object value)
{
_Columns.Add(name, value);
return this;
}
/// <summary>
/// 初始化列名称/值 集合
/// </summary>
/// <param name="columns">列名称/值 集合</param>
/// <returns></returns>
public Update Column(Hashtable columns)
{
_Columns = columns;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters">参数</param>
/// <returns></returns>
public Update Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Count(); i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 执行更新
/// </summary>
/// <returns></returns>
public int Execute()
{
if (_Columns.Count <= 0)
return -1;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
string columnNames = "";
foreach (string key in _Columns.Keys)
{
columnNames += key + "=@" + key + ",";
_Parameters.Add("@" + key, _Columns[key]);
}
string sql = string.Format(UPDATE_SQL, _TableName, columnNames.TrimEnd(','), _Where);
return _DbHelper.Execute(sql, _Parameters);
}
}
/// <summary>
/// 实体类型更新
/// </summary>
/// <typeparam name="T"></typeparam>
public class Update_T<T>
{
private static string UPDATE_SQL = @"UPDATE {0} SET {1} WHERE {2}";
private DbHelper _DbHelper = null; //数据操作类
private T _Model; //实体模型
private string _TableName = string.Empty; //表名
private Hashtable _Columns = new Hashtable(); //字段集合
private DynamicParameters _Parameters=new DynamicParameters(); //参数
private string _Where = string.Empty; //条件
private object[] _PrimaryKey; //过滤的主外键集合
/// <summary>
/// 初始化
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="tableName"></param>
/// <param name="model"></param>
public Update_T(DbHelper dbHelper, string tableName, T model)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Model = model;
}
/// <summary>
/// 更新条件
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public Update_T<T> Where(string where)
{
_Where = where;
return this;
}
/// <summary>
/// 过滤的主外键参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Update_T<T> PrimaryKeys(params object[] primarykeys)
{
if (primarykeys != null)
{
_PrimaryKey = primarykeys;
}
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Update_T<T> Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Count(); i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
private string UpdateSQL(T model)
{
StringBuilder sbColumns = new StringBuilder();
PropertyInfo[] _PropertyInfos = model.GetType().GetProperties();
foreach (PropertyInfo _PropertyInfo in _PropertyInfos)
{
if (null != _PropertyInfo.GetValue(model, null))
{
if (!_PrimaryKey.Contains(_PropertyInfo.Name))
{
sbColumns.Append(_PropertyInfo.Name + "=@" + _PropertyInfo.Name + ",");
_Parameters.Add("@" + _PropertyInfo.Name, _PropertyInfo.GetValue(model));
}
}
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
string strSql = string.Format(UPDATE_SQL, _TableName, sbColumns.ToString(),_Where);
return strSql;
}
/// <summary>
/// 执行修改
/// </summary>
/// <returns></returns>
public int Execute()
{
string sql = UpdateSQL(_Model);
return _DbHelper.Execute(sql, _Parameters);
}
}