/// <summary>
/// 填充新的insert 参数 (按照默认值)
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
private HashObject CreateNewInsertParameters(HashObject parameters)
{
HashObject newParameters = new HashObject();
foreach (KeyValuePair <string, Column> pair in this._allColumns) //循环所有列
{
if (pair.Value.Type == "timestamp") //timestamp不添加默认值
{
continue;
}
if (!parameters.ContainsKey(pair.Key) ||
(parameters.ContainsKey(pair.Key) && (parameters[pair.Key] == null || parameters[pair.Key].ToString() == string.Empty))
)//不包含或者包含此列单但数据为空则补充默认值
{
newParameters.Add(pair.Key, pair.Value.GetDefaultValue());
}
else
{
newParameters.Add(pair.Key, parameters[pair.Key]);
}
}
return(newParameters);
}
/// <summary>
/// 获取分区ID
/// </summary>
/// <param name="dbName"></param>
/// <param name="hostID"></param>
/// <returns></returns>
public static int GetShardID(string dbName, HashObject parameters)
{
if (parameters.ContainsKey("__shardid"))//显式指定分区id
{
return(Convert.ToInt32(parameters["__shardid"]));
}
long hostID = GetHostID(dbName, parameters);
if (hostID == 0)//单库返回1号分区
{
return(1);
}
else
{
//return 1;
string cacheKey = string.Format("{0}.{1}", _preCacheKey, hostID);
//1 先从web缓存获取
object result = CC.Caching.WebCache.GetCache(cacheKey);
if (result != null)
{
return(Convert.ToInt32(result));
}
else
{
//2.从数据库获取
int shardid = GetShardIDFromDB(hostID);
//3.放入缓存2分钟，滑动过期
CC.Caching.WebCache.SetCache(cacheKey, shardid, new TimeSpan(0, 2, 0));
return(shardid);
}
}
}
/// <summary>
/// 根据条件保存
/// </summary>
/// <param name="setParameters">set 参数</param>
/// <param name="whereParameters">条件参数</param>
/// <param name="offsetParameters">需要偏移更新的参数列update set a = a + @a</param>
/// <returns></returns>
public long Save(HashObject setParameters, HashObject whereParameters, string[] offsetParameters = null)
{
if (setParameters == null || setParameters.Count == 0)
{
throw new ArgumentNullException("setParameters 不能为空!");
}
long result = 0;
if (whereParameters != null && whereParameters.Count > 0)//如果存在条件
{
bool exists = Exists(whereParameters);
if (exists == true)//存在时更新
{
UpdateByWhere(setParameters, whereParameters, offsetParameters);
if (whereParameters.ContainsKey(this._primaryColumn))//如果条件中包含主键，则返回主键
{
result = Convert.ToInt64(whereParameters[this._primaryColumn]);
}
}
else//不存在时插入
{
result = Insert(setParameters);
}
}
else//无条件时插入
{
result = Insert(setParameters);
}
return(result);
}
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>
/// 判断是否需要创建新的ID
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
private bool NeedCreateNewId(HashObject parameters)
{
//参数不包含，或者为0，自动自增一个新的
return(!parameters.ContainsKey(_primaryColumn) || Convert.ToInt64(parameters[_primaryColumn]) == 0);
}
/// <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);
}