/// <summary>
/// 传参数按主键更新数据（参数中必须包含全部主键列）
/// </summary>
/// <param name="parameters">全部参数</param>
/// <param name="changecolumns">需要更新的参数</param>
/// <param name="offsetParameters">需要偏移更新的参数（update xxx set a=a+@a...）</param>
/// <returns></returns>
public int Update(HashObject parameters, string[] changecolumns = null, string[] offsetParameters = null)
{
this.CheckForPrimary(parameters);//判断参数中是否包含主键列，如果不包含，抛出异常
string hostColName = DALConfig.GetHostColName(this._dbName);
HashObject whereParameters = new HashObject();
whereParameters.Add(this._primaryColumn, parameters[this._primaryColumn]);
whereParameters.Add(hostColName, parameters[hostColName]);
HashObject setParameters = new HashObject();
if (changecolumns != null)
{
foreach (string column in changecolumns)
{
setParameters[column] = parameters[column];
}
}
else
{
setParameters = parameters.Copy();
setParameters.Remove(this._primaryColumn); //移除主键列
setParameters.Remove(hostColName); //移除分区列
}
return(UpdateByWhere(setParameters, whereParameters, offsetParameters));
}
private static long GetHostID(string dbName, HashObject parameters)
{
string colName = DALConfig.GetHostColName(dbName); //从配置文件获取此数据库的分区列
if (!string.IsNullOrEmpty(colName)) //如果有分区列，则参数中必须包含,且值不能为0
{
if (!parameters.ContainsKey(colName))
{
throw new ArgumentNullException("parameters必须包含:" + colName);
}
long hostID = Convert.ToInt64(parameters[colName]);
if (hostID == 0)
{
throw new ArgumentNullException("分区id不能为0");
}
return(hostID);
}
else//单库
{
return(0);
}
}
/// <summary>
/// 保存，如果存在符合主键列的数据，则更新（默认）,否则，插入
/// 如果 updateIfExists = false ，存在则不做任何处理
/// </summary>
/// <param name="parameters">参数</param>
/// <returns></returns>
public long Save(HashObject parameters)
{
if (parameters == null || parameters.Count == 0)
{
throw new ArgumentNullException("parameters 不能为空!");
}
HashObject whereParameters = null;
if (parameters.ContainsKey(this._primaryColumn))
{
whereParameters = new HashObject();
whereParameters.Add(this._primaryColumn, parameters[this._primaryColumn]);
string colName = DALConfig.GetHostColName(this._dbName);
if (!string.IsNullOrEmpty(colName))
{
whereParameters.Add(colName, parameters[colName]);
}
}
return(this.Save(parameters, whereParameters, null));
}
/// <summary>
/// 组装update SQL 语句
/// </summary>
/// <param name="dbName">数据库名称</param>
/// <param name="tableName">表名称</param>
/// <param name="setParameters">set 参数</param>
/// <param name="whereParameters">条件参数</param>
/// <returns></returns>
private static string CreateUpdateSQL(string dbName, string tableName, HashObject setParameters, HashObject whereParameters, string[] offsetParameters, out HashObject newParameters)
{
StringBuilder Sql = new StringBuilder();
StringBuilder SqlFields = new StringBuilder();
StringBuilder SqlWhere = new StringBuilder();
newParameters = new HashObject();
SqlFields.Append("update ");
SqlFields.Append(tableName);
SqlFields.Append(" set ");
SqlWhere.Append(" where ");
Dictionary <string, Column> allColumns = GetAllColumns(dbName, tableName);
string primaryColumn = GetPrimaryColumn(dbName, tableName);
string hostColumn = DALConfig.GetHostColName(dbName);
foreach (string column in setParameters.Keys)
{
string key = column.ToLower().Trim();
if (key == hostColumn)//=分区列
{
newParameters[key] = setParameters[key];
}
else if (allColumns.ContainsKey(key) && key != primaryColumn)//包含在此表的列中,且不包含在主键中,不能是分区列
{
SqlFields.Append(key);
SqlFields.Append("=");
if (offsetParameters != null && offsetParameters.Contains(key))//包含在偏移量数组中，则更新时包含自己
{
SqlFields.Append(key);
SqlFields.Append("+"); //update table set a = a + @a,b=b+@b
}
SqlFields.Append("@");
SqlFields.Append(key);
SqlFields.Append(",");
object setvalue = setParameters[key];
//setvalue 为空时，取默认值
if (setvalue == null || setvalue.ToString() == string.Empty)
{
setvalue = allColumns[key].GetDefaultValue();
}
newParameters.Add(key.ToLower(), setvalue);
}
}
foreach (string column in whereParameters.Keys)
{
string key = column.ToLower().Trim();
if (allColumns.ContainsKey(key)) //包含在此表的列中
{
if (newParameters.ContainsKey(key)) //where 条件在 set 里面存在
{
SqlWhere.Append(key);
SqlWhere.Append("=@w_");
SqlWhere.Append(key);
SqlWhere.Append(" and ");
newParameters["w_" + key] = whereParameters[key];
}
else
{
SqlWhere.Append(key);
SqlWhere.Append("=@");
SqlWhere.Append(key);
SqlWhere.Append(" and ");
newParameters[key] = whereParameters[key];
}
}
}
Sql.Append(SqlFields.ToString(0, SqlFields.Length - 1));
Sql.Append(" ");
Sql.Append(SqlWhere.ToString(0, SqlWhere.Length - 4));
string strSql = Sql.ToString().ToLower();
return(strSql);
}