/// <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); }