/// <summary>
/// 根据自增主键删除数据库中 Order 实体
/// </summary>
/// <param name="primaryKey">自增主键</param>
public void DeleteOrderByPk(long primaryKey)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@OrderId", DbType.Int64, primaryKey);
try
{
DB.ExecSp("spA_Order_d", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 DeleteOrderByPk 时出错", ex);
}
}
/// <summary>
/// 更新Product 实体
/// </summary>
/// <param name="entity">ProductModel对象</param>
public void UpdateProduct(ProductEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@ProductId", DbType.Int64, entity.ProductId);
parameters.AddInParameter("@ProductName", DbType.String, entity.ProductName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Product_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 UpdateProduct 时出错", ex);
}
}
/// <summary>
/// 更新User 实体
/// </summary>
/// <param name="entity">UserModel对象</param>
public void UpdateUser(UserEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@UserId", DbType.Int64, entity.UserId);
parameters.AddInParameter("@UserName", DbType.String, entity.UserName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_User_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 UserDal 时，访问 UpdateUser 时出错", ex);
}
}
/// <summary>
/// 向数据库中插入 User 实体
/// </summary>
/// <param name="entity">UserModel对象</param>
/// <returns>自增主键</returns>
public long InsertUser(UserEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddOutParameter("@UserId", DbType.Int64, 8);
parameters.AddInParameter("@UserName", DbType.String, entity.UserName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_User_i", parameters);
return Convert.ToInt64(parameters["@UserId"].Value);
}
catch (Exception ex)
{
throw new DalException("调用 UserDal 时，访问 InsertUser 时出错", ex);
}
}
/// <summary>
/// 向数据库中插入 Order 实体
/// </summary>
/// <param name="entity">OrderModel对象</param>
/// <returns>自增主键</returns>
public long InsertOrder(OrderEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddOutParameter("@OrderId", DbType.Int64, 8);
parameters.AddInParameter("@UserId", DbType.Int64, entity.UserId);
parameters.AddInParameter("@Name", DbType.String, entity.Name);
parameters.AddInParameter("@Price", DbType.Decimal, entity.Price);
parameters.AddInParameter("@ProductId", DbType.Int64, entity.ProductId);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Order_i", parameters);
return(Convert.ToInt64(parameters["@OrderId"].Value));
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 InsertOrder 时出错", ex);
}
}
/// <summary>
/// 执行非查询指令
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="isWrite">读写配置</param>
/// <returns>影响行数</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public Int32 ExecNonQuery(String sql, StatementParameterCollection parameters, bool isWrite = true)
{
return(ExecNonQuery(sql, parameters, null, isWrite));
}
/// <summary>
/// 执行单返回值聚集查询指令
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="isWrite">读写配置</param>
/// <returns>object</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public Object ExecScalar(String sql, StatementParameterCollection parameters, bool isWrite = false)
{
return(ExecScalar(sql, parameters, null, isWrite));
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="isWrite">读写配置</param>
/// <returns>DataTable</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public DataTable SelectDataTable(String sql, StatementParameterCollection parameters, bool isWrite = false)
{
return(SelectDataTable(sql, parameters, null, isWrite ? OperationType.Write : OperationType.Read));
}
/// <summary>
/// 执行查询语句，并返回指定的结果（连接会确认被释放，安全）
/// </summary>
/// <typeparam name="T">返回值的类型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="callback">回调，接受IDataReader作为参数，返回T类型的结果</param>
/// <param name="isWrite">读写配置</param>
/// <returns>T</returns>
public T VisitDataReader <T>(String sql, StatementParameterCollection parameters, Func <IDataReader, T> callback, bool isWrite = false)
{
return(VisitDataReader(sql, parameters, null, callback, isWrite));
}
public IComparable GetShardColumnValue <T>(String logicDbName, T entity, ICollection <SqlColumn> columns, StatementParameterCollection parameters, IDictionary hints)
{
if (String.IsNullOrEmpty(logicDbName))
{
return(null);
}
if (shardColumns == null || shardColumns.Count == 0)
{
return(null);
}
//Verify by shard column value in hints
var shardColumnValue = GetShardColumnValueByValue(hints);
//Verify by map in hints
if (shardColumnValue == null)
{
shardColumnValue = GetShardColumnValueByMap(shardColumns, hints);
}
//Verify by entity(columns)
if (shardColumnValue == null)
{
shardColumnValue = GetShardColumnValueByEntity(shardColumns, entity, columns);
}
//Verify by parameters
if (shardColumnValue == null)
{
shardColumnValue = GetShardColumnValueByParameters(logicDbName, shardColumns, parameters);
}
return(shardColumnValue);
}
private static IComparable GetShardColumnValueByParameters(String logicDbName, IList <String> shardColumns, StatementParameterCollection parameters, IDictionary hints)
{
IComparable shardColumnValue = null;
if (shardColumns == null || shardColumns.Count == 0)
{
return(null);
}
if (parameters == null || parameters.Count == 0)
{
return(null);
}
var dict = new Dictionary <String, StatementParameter>();
foreach (var item in parameters)
{
String parameterName = item.Name;
if (String.IsNullOrEmpty(parameterName))
{
continue;
}
parameterName = parameterName.ToLower();
if (!dict.ContainsKey(parameterName))
{
dict.Add(parameterName, item);
}
parameterName = item.ColumnName;
if (String.IsNullOrEmpty(parameterName))
{
continue;
}
parameterName = parameterName.ToLower();
if (!dict.ContainsKey(parameterName))
{
dict.Add(parameterName, item);
}
}
var quote = string.Empty;
if (hints != null && hints.Contains(DALExtStatementConstant.PARAMETER_SYMBOL))
{
quote = hints[DALExtStatementConstant.PARAMETER_SYMBOL] as string;
}
foreach (var item in shardColumns)
{
String name = quote + item.ToLower();
if (dict.ContainsKey(name))
{
shardColumnValue = dict[name].Value as IComparable;
break;
}
if (dict.ContainsKey(item.ToLower()))
{
shardColumnValue = dict[item.ToLower()].Value as IComparable;
break;
}
}
return(shardColumnValue);
}
public static List <Statement> GetQueryStatement(String logicDbName, string sql, IShardingStrategy shardingStrategy, StatementParameterCollection parameters, IDictionary hints, OperationType?operationType = null)
{
var result = new List <Statement>();
var tupleList = ShardingUtil.GetShardInfo(logicDbName, shardingStrategy, parameters, hints);
foreach (var tuple in tupleList)
{
Statement statement = new Statement
{
DatabaseSet = logicDbName,
StatementType = StatementType.Sql,
OperationType = operationType ?? OperationType.Default,
Hints = hints,
ShardID = tuple.Item1,
TableName = "",
SqlOperationType = SqlStatementType.SELECT,
Parameters = parameters
};
statement.StatementText = string.IsNullOrEmpty(tuple.Item2) ? sql : string.Format(sql, tuple.Item2);
result.Add(statement);
}
return(result);
}
//private static IDictionary<String, IList<T>> ShuffledByDb<T>(String logicDbName, IShardingStrategy shardingStrategy, StatementParameterCollection parameters,
// IList<T> list, IDictionary hints)
//{
// if (String.IsNullOrEmpty(logicDbName))
// return null;
// if (list == null || list.Count == 0)
// return null;
// var dict = new Dictionary<String, IList<T>>();
// foreach (var item in list)
// {
// String shardId = GetShardId(logicDbName, shardingStrategy, parameters, hints);
// if (String.IsNullOrEmpty(shardId))
// continue;
// if (!dict.ContainsKey(shardId))
// dict.Add(shardId, new List<T>());
// dict[shardId].Add(item);
// }
// return dict;
//}
//private static IDictionary<String, IList<T>> ShuffledByTable<T>(String logicDbName, IShardingStrategy shardingStrategy, StatementParameterCollection parameters,
// IList<T> list,IDictionary hints)
//{
// if (String.IsNullOrEmpty(logicDbName))
// return null;
// if (list == null || list.Count == 0)
// return null;
// var dict = new Dictionary<String, IList<T>>();
// foreach (var item in list)
// {
// String tableId = GetTableId(logicDbName, shardingStrategy, parameters, hints);
// if (String.IsNullOrEmpty(tableId))
// continue;
// if (!dict.ContainsKey(tableId))
// dict.Add(tableId, new List<T>());
// dict[tableId].Add(item);
// }
// return dict;
//}
//private static IDictionary<String, IDictionary<String, IList<T>>> ShuffledByDbTable<T>(String logicDbName, IShardingStrategy shardingStrategy, StatementParameterCollection parameters,
// IList<T> list, IDictionary hints)
//{
// if (String.IsNullOrEmpty(logicDbName))
// return null;
// if (list == null || list.Count == 0)
// return null;
// var dict = new Dictionary<String, IDictionary<String, IList<T>>>();
// foreach (var item in list)
// {
// String shardId = GetShardId(logicDbName, shardingStrategy, parameters, hints);
// String tableId = GetTableId(logicDbName, shardingStrategy , parameters,hints);
// if (!dict.ContainsKey(shardId))
// dict.Add(shardId, new Dictionary<String, IList<T>>());
// if (!dict[shardId].ContainsKey(tableId))
// dict[shardId].Add(tableId, new List<T>());
// dict[shardId][tableId].Add(item);
// }
// return dict;
//}
#endregion Shuffled Items
public static IList <Statement> GetShardStatement(String logicDbName, IShardingStrategy shardingStrategy,
StatementParameterCollection parameters, IDictionary hints, Func <IDictionary, List <Statement> > func, SqlStatementType sqlStatementType)
{
IList <Statement> statements;
if (shardingStrategy == null)
{
return(null);
}
var shardingType = GetShardingType(shardingStrategy);
if (shardingType != ShardingType.ShardByDBAndTable)
{
IList <String> shards = null;
//Get shards from hints
if (hints != null)
{
IList <String> temp = null;
if (shardingType == ShardingType.ShardByDB) //是分库的情况
{
if (hints.Contains(DALExtStatementConstant.SHARD_IDS)) //看下hints里面有没有指定分配id(一组)
{
temp = hints[DALExtStatementConstant.SHARD_IDS] as List <String>;
}
else if (hints.Contains(DALExtStatementConstant.SHARDID))//单个的分配id
{
temp = new List <String> {
hints[DALExtStatementConstant.SHARDID] as String
};
}
}
else if (shardingType == ShardingType.ShardByTable)//是分表的情况
{
if (hints.Contains(DALExtStatementConstant.TABLE_IDS))
{
temp = hints[DALExtStatementConstant.TABLE_IDS] as List <String>;
}
else if (hints.Contains(DALExtStatementConstant.TABLEID))
{
temp = new List <String> {
hints[DALExtStatementConstant.TABLEID] as String
};
}
}
if (temp != null)
{
shards = temp;
}
}
var bulkCopy = false;
if (hints != null && hints.Contains(DALExtStatementConstant.BULK_COPY))//查看是否是批量插入的case
{
bulkCopy = Convert.ToBoolean(hints[DALExtStatementConstant.BULK_COPY]);
}
if (bulkCopy)
{
return(func.Invoke(hints));
}
//Get shards from parameters 这里会根据 查询参数得出分配的信息
if (shards == null)
{
if (shardingType == ShardingType.ShardByDB)
{
shards = GetShardId(logicDbName, shardingStrategy, parameters, hints);
}
else if (shardingType == ShardingType.ShardByTable)
{
shards = GetTableId(logicDbName, shardingStrategy, parameters, hints);
}
}
//对于不带条件的查询 都默认查询所有的
if ((shards == null || shards.Count == 0) && sqlStatementType.Equals(SqlStatementType.SELECT))
{
shards = shardingStrategy.AllShards;
}
if (shards == null || shards.Count == 0)
{
throw new DalException("Please provide shard information.");
}
//Build statements
statements = new List <Statement>();
foreach (var item in shards.Distinct())
{
var newHints = HintsUtil.CloneHints(hints);
switch (shardingType)
{
case ShardingType.ShardByDB:
newHints[DALExtStatementConstant.SHARDID] = item;
break;
case ShardingType.ShardByTable:
newHints[DALExtStatementConstant.TABLEID] = item;
break;
}
var statement = func.Invoke(newHints);
foreach (var ss in statement)
{
statements.Add(ss);
}
}
}
else
{
statements = new List <Statement>();
IDictionary <String, IList <String> > shardDict = null;
if (hints != null && hints.Contains(DALExtStatementConstant.SHARD_TABLE_DICT))
{
shardDict = hints[DALExtStatementConstant.SHARD_TABLE_DICT] as IDictionary <String, IList <String> >;
}
if (shardDict == null)
{
var newHints = HintsUtil.CloneHints(hints);
newHints[DALExtStatementConstant.SHARDID] = GetShardIdByHints(hints);
newHints[DALExtStatementConstant.TABLEID] = GetTableIdByHints(hints);
var statement = func.Invoke(newHints);
foreach (var ss in statement)
{
statements.Add(ss);
}
}
else
{
foreach (var shard in shardDict)
{
foreach (var table in shard.Value)
{
var newHints = HintsUtil.CloneHints(hints);
newHints[DALExtStatementConstant.SHARDID] = shard.Key;
newHints[DALExtStatementConstant.TABLEID] = table;
var statement = func.Invoke(newHints);
foreach (var ss in statement)
{
statements.Add(ss);
}
}
}
}
}
return(statements);
}
private static List <String> GetTableId(String logicDbName, IShardingStrategy shardingStrategy, StatementParameterCollection parameters, IDictionary hints)
{
List <String> tableIdList = new List <string>();
Boolean shardByTable = IsShardByTable(shardingStrategy);
if (shardByTable)
{
var tableId = GetTableIdByHints(hints);
if (String.IsNullOrEmpty(tableId))
{
List <IComparable> shardColumnValue = shardingStrategy.GetShardColumnValueList(logicDbName, parameters, hints);
foreach (var comparable in shardColumnValue)
{
tableId = CalculateShardId(shardingStrategy, comparable);
tableIdList.Add(tableId);
}
}
else
{
tableIdList.Add(tableId);
}
}
return(tableIdList);
}
public IDataReader SelectDataReader(String sql, StatementParameterCollection parameters, bool isWrite = false)
{
return(SelectDataReader(sql, parameters, null, isWrite));
}
private void SetOutDbParameter(DbCommand dbCmd, StatementParameterCollection parms)
{
var outList = parms.Where(s => s.Direction == ParameterDirection.Output || s.Direction == ParameterDirection.InputOutput);
if (outList.Count() == 0) //若没有Output参数，则退出
return;
var dbParmList = dbCmd.Parameters.Cast<DbParameter>();
outList.ToList().ForEach(delegate(StatementParameter parm)
{
DbParameter dbParm = dbParmList.FirstOrDefault(s => s.ParameterName == parm.Name);
dbParm.Value = parm.Value;
});
}
/// <summary>
/// 获取分片信息
/// </summary>
/// <param name="logicDbName"></param>
/// <param name="shardingStrategy"></param>
/// <param name="hints"></param>
/// <returns></returns>
public static List <Tuple <String, String> > GetShardInfo(String logicDbName, IShardingStrategy shardingStrategy, StatementParameterCollection parameters, IDictionary hints)
{
List <String> shardId = null;
List <String> tableId = null;
var result = new List <Tuple <string, string> >();
Boolean shardEnabled = IsShardEnabled(shardingStrategy);
if (!shardEnabled)
{
return(result);
}
shardId = GetShardId(logicDbName, shardingStrategy, parameters, hints); //是否有分库
tableId = GetTableId(logicDbName, shardingStrategy, parameters, hints); //分表
if (shardId.Count < 1 && tableId.Count < 1)
{
throw new DalException("Please provide shard information.");
}
if (shardId.Count > 0 && tableId.Count == 0)
{
foreach (var d in shardId)
{
result.Add(Tuple.Create <String, String>(d, null));
}
}
else if (shardId.Count == 0 && tableId.Count > 0)
{
foreach (var d in tableId)
{
result.Add(Tuple.Create <String, String>(null, d));
}
}
else
{
foreach (var d in shardId)
{
foreach (var t in tableId)
{
result.Add(Tuple.Create <String, String>(d, t));
}
}
}
return(result);
}
private StatementParameterCollection ChangeStateParm(DbCommand dbCmd)
{
DbParameterCollection parmsList = dbCmd.Parameters;
StatementParameterCollection sparmList = new StatementParameterCollection();
foreach (DbParameter dbParm in parmsList)
{
StatementParameter stateParm = new StatementParameter();
stateParm.Name = dbParm.ParameterName;
stateParm.DbType = dbParm.DbType;
stateParm.Direction = dbParm.Direction;
stateParm.Size = dbParm.Size;
stateParm.Value = dbParm.Value;
sparmList.Add(stateParm);
// ParmsList.Add(stateParm);
}
return sparmList;
}
public List <IComparable> GetShardColumnValueList(string logicDbName, StatementParameterCollection parameters, IDictionary hints)
{
List <IComparable> shardColumnValueList = new List <IComparable>();
if (shardColumns == null || shardColumns.Count == 0)
{
return(shardColumnValueList);
}
if (parameters == null || parameters.Count == 0)
{
return(shardColumnValueList);
}
var dict = new List <Tuple <String, StatementParameter> >();
foreach (var item in parameters)
{
String parameterName = item.Name;
if (String.IsNullOrEmpty(parameterName))
{
continue;
}
parameterName = parameterName.ToLower();
dict.Add(Tuple.Create <string, StatementParameter>(parameterName, item));
parameterName = item.ColumnName;
if (String.IsNullOrEmpty(parameterName))
{
continue;
}
parameterName = parameterName.ToLower();
dict.Add(Tuple.Create <string, StatementParameter>(parameterName, item));
}
var quote = string.Empty;
if (hints != null && hints.Contains(DALExtStatementConstant.PARAMETER_SYMBOL))
{
quote = hints[DALExtStatementConstant.PARAMETER_SYMBOL] as string;
}
foreach (var item in shardColumns)
{
String name = quote + item.ToLower();
foreach (var itemKv in dict)
{
IComparable shardColumnValue = null;
if (itemKv.Item1.Equals(name))
{
shardColumnValue = itemKv.Item2.Value as IComparable;
itemKv.Item2.IsShardingColumn = true;
itemKv.Item2.ShardingValue = ComputeShardId(shardColumnValue);
}
else if (itemKv.Item1.Equals(item.ToLower()))
{
shardColumnValue = itemKv.Item2.Value as IComparable;
itemKv.Item2.IsShardingColumn = true;
itemKv.Item2.ShardingValue = ComputeShardId(shardColumnValue);
}
if (shardColumnValue != null)
{
shardColumnValueList.Add(shardColumnValue);
}
}
}
return(shardColumnValueList);
}
/// <summary>
/// 执行单返回值聚集查询指令
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="hints">指令参数：如timeout</param>
/// <param name="operationType">操作类型，读写分离，默认从master库读取</param>
/// <returns>object</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public Object ExecScalar(String sql, StatementParameterCollection parameters, IDictionary hints, OperationType operationType)
{
Object result = null;
if (!IsShardEnabled)
{
Statement statement = SqlBuilder.GetScalarStatement(LogicDbName, ShardingStrategy, sql, parameters, hints, operationType).Single();
AddSqlToExtendParams(statement, hints);
try
{
result = DatabaseBridge.Instance.ExecuteScalar(statement);
return(result);
}
finally
{
RunTimeDetail runTimeDetail = new RunTimeDetail
{
DbName = statement.DbName,
Duration = statement.Duration,
Server = statement.HostName
};
if (hints != null)
{
hints.Add(DALExtStatementConstant.EXCUTE_TIME, new List <RunTimeDetail> {
runTimeDetail
});
}
}
}
else
{
var statements = ShardingUtil.GetShardStatement(LogicDbName, ShardingStrategy, parameters, hints,
newHints => SqlBuilder.GetScalarStatement(LogicDbName, ShardingStrategy, sql, parameters, newHints, operationType), SqlStatementType.SELECT);
try
{
var temp = ShardingExecutor.ExecuteShardingScalar(statements);
if (temp.Count == 1)
{
result = temp[0];
return(result);
}
else
{
throw new DalException("ExecScalar exception:more than one shard.");
}
}
finally
{
var runTimeList = new List <RunTimeDetail>();
foreach (var statement in statements)
{
RunTimeDetail runTimeDetail = new RunTimeDetail
{
DbName = statement.DbName,
Duration = statement.Duration,
Server = statement.HostName
};
runTimeList.Add(runTimeDetail);
}
if (hints != null)
{
hints.Add(DALExtStatementConstant.EXCUTE_TIME, runTimeList);
}
}
}
}
/// <summary>
/// 更新时间戳
/// </summary>
public void UpdateDataChangeLastTime(long id)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@OrderId", DbType.Int64, id);
parameters.AddInParameter("@DataChange_LastTime", DbType.DateTime, DBNull.Value);
try
{
DB.ExecSp("spA_Order_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 UpdateDataChangeLastTime 时出错", ex);
}
}
/// <summary>
/// 批量插入分片规则： 解析sqlstring 按照分片进行分组 在重新组合 如果没有找到分片的默认第一个分片
/// </summary>
/// <param name="logicDbName"></param>
/// <param name="shardingStrategy"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="hints"></param>
/// <param name="tupleList"></param>
/// <param name="sqlType"></param>
/// <param name="operationType"></param>
/// <returns></returns>
private static List <Statement> BulkCopyCase(String logicDbName, IShardingStrategy shardingStrategy,
String sql, StatementParameterCollection parameters, IDictionary hints, List <Tuple <String, String> > tupleList, SqlStatementType sqlType, OperationType?operationType = null)
{
var result = new List <Statement>();
//var shardingDB = tuple.Item1;
//var sharingTable = tuple.Item2;
if (shardingStrategy.ShardByDB && shardingStrategy.ShardByTable)
{
//又分表又分库的情况
}
else if (shardingStrategy.ShardByDB || shardingStrategy.ShardByTable)
{
var dicValues = new Dictionary <string, Tuple <List <string>, StatementParameterCollection> >();
foreach (var tuple in tupleList)
{
dicValues.Add(shardingStrategy.ShardByDB?tuple.Item1: tuple.Item2, Tuple.Create(new List <string>(), new StatementParameterCollection()));
}
var defaultSharding = dicValues.Keys.First();
//分库的情况
var arr = sql.Split(new string[] { "VALUES" }, StringSplitOptions.None);
if (arr.Length != 2)
{
throw new DalException("sharding db for bulkInsert sql string err.");
}
var title = arr[0] + " VALUES ";
var body = arr[1].Replace("\r\n", "");
var values = body.Split(')').Where(r => !string.IsNullOrEmpty(r)).Select(r => r.StartsWith(",")?r.Substring(1):r).ToArray(); //总共有多少行
var first = values.First();
var cloumnCount = first.Split(',').Length; //每行总共有多少列
if (parameters.Count != (cloumnCount * values.Length))
{
throw new DalException("sharding db for bulkInsert sql parameters counts err.");
}
var ii = 0;
//将parameters 按 分库进行分组
for (int i = 0; i < values.Length; i++)
{
var columns = values[i].Split(',');
var haveShardingC = false;
var shardingValue = string.Empty;
List <StatementParameter> newC = new List <StatementParameter>();
for (int j = 0; j < columns.Length; j++)
{
var p = parameters.ElementAt(ii);
if (p.IsShardingColumn)
{
haveShardingC = true;
shardingValue = p.ShardingValue;
}
newC.Add(p);
ii++;
}
if (haveShardingC)
{
if (!string.IsNullOrEmpty(shardingValue))
{
if (dicValues.ContainsKey(shardingValue))
{
dicValues[shardingValue].Item1.Add(values[i] + ")");
foreach (var pp in newC)
{
dicValues[shardingValue].Item2.Add(pp);
}
}
}
else
{
//添加到第一个分片
dicValues[defaultSharding].Item1.Add(values[i] + ")");
foreach (var pp in newC)
{
dicValues[defaultSharding].Item2.Add(pp);
}
}
}
}
foreach (var dic in dicValues)
{
if (dic.Value.Item1.Count == 0)
{
continue;
}
var newHints = HintsUtil.CloneHints(hints);
if (shardingStrategy.ShardByDB)
{
newHints[DALExtStatementConstant.SHARDID] = dic.Key;
}
else
{
newHints[DALExtStatementConstant.TABLEID] = dic.Key;
}
var statement = GetStatement(logicDbName, StatementType.Sql, operationType ?? OperationType.Default, sqlType, newHints, shardingStrategy.ShardByDB? dic.Key:null);
statement.StatementText = shardingStrategy.ShardByDB ? (title + string.Join(",", dic.Value.Item1)): GetSql(title + string.Join(",", dic.Value.Item1), dic.Key);
statement.Parameters = dic.Value.Item2;
#if !NETSTANDARD
CurrentStackCustomizedLog(statement);
#endif
result.Add(statement);
}
}
return(result);
}
private static IComparable GetShardColumnValueByParameters(String logicDbName, IList <String> shardColumns, StatementParameterCollection parameters, Boolean quote = true)
{
IComparable shardColumnValue = null;
if (shardColumns == null || shardColumns.Count == 0)
{
return(null);
}
if (parameters == null || parameters.Count == 0)
{
return(null);
}
DbDialect dbDialect = null;
if (quote)
{
dbDialect = DbDialectFactory.Build(DALBootstrap.GetProviderType(logicDbName));
}
var dict = new Dictionary <String, StatementParameter>();
foreach (var item in parameters)
{
String parameterName = item.Name;
if (String.IsNullOrEmpty(parameterName))
{
continue;
}
parameterName = parameterName.ToLower();
if (!dict.ContainsKey(parameterName))
{
dict.Add(parameterName, item);
}
}
foreach (var item in shardColumns)
{
String name = quote ? dbDialect.QuoteParameter(item.ToLower()) : item.ToLower();
if (dict.ContainsKey(name))
{
shardColumnValue = dict[name].Value as IComparable;
break;
}
}
return(shardColumnValue);
}
/// <summary>
/// GetScalarStatement
/// </summary>
/// <param name="logicDbName"></param>
/// <param name="shardingStrategy"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="extendedParameters"></param>
/// <param name="operationType"></param>
/// <returns></returns>
public static List <Statement> GetScalarStatement(String logicDbName, IShardingStrategy shardingStrategy,
String sql, StatementParameterCollection parameters, IDictionary extendedParameters, OperationType?operationType = null)
{
return(GetDefaultSqlStatement(logicDbName, shardingStrategy, sql, parameters, extendedParameters, SqlStatementType.SELECT, operationType ?? OperationType.Default));
}
/// <summary>
/// 执行查询语句，并返回指定的结果（连接会确认被释放，安全）
/// </summary>
/// <typeparam name="T">返回值的类型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="hints">指令扩展属性</param>
/// <param name="callback">回调，接受IDataReader作为参数，返回T类型的结果</param>
/// <param name="isWrite">读写配置</param>
/// <returns>T</returns>
public T VisitDataReader <T>(String sql, StatementParameterCollection parameters, IDictionary hints, Func <IDataReader, T> callback, bool isWrite = false)
{
return(VisitDataReader(sql, parameters, hints, isWrite ? OperationType.Write : OperationType.Read, callback));
}
/// <summary>
/// </summary>
/// <param name="logicDbName"></param>
/// <param name="shardingStrategy"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="extendedParameters"></param>
/// <param name="operationType"></param>
/// <param name="sqlStatementType"></param>
/// <returns></returns>
public static List <Statement> GetNonQueryStatement(String logicDbName, IShardingStrategy shardingStrategy,
String sql, StatementParameterCollection parameters, IDictionary extendedParameters, OperationType?operationType = null, SqlStatementType?sqlStatementType = null)
{
return(GetDefaultSqlStatement(logicDbName, shardingStrategy, sql, parameters, extendedParameters, sqlStatementType ?? SqlStatementType.UNKNOWN, operationType ?? OperationType.Write));
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="isWrite">读写配置</param>
/// <returns>DataSet</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public DataSet SelectDataSet(String sql, StatementParameterCollection parameters, bool isWrite = false)
{
return(SelectDataSet(sql, parameters, null, isWrite));
}
public static Tuple <String, String> GetShardInfo <T>(String logicDbName, IShardingStrategy shardingStrategy, T entity, ICollection <SqlColumn> columns, StatementParameterCollection parameters, IDictionary hints)
{
String shardId = null;
String tableId = null;
Boolean shardEnabled = IsShardEnabled(shardingStrategy);
if (!shardEnabled)
{
return(Tuple.Create <String, String>(shardId, tableId));
}
shardId = GetShardId(logicDbName, shardingStrategy, entity, columns, parameters, hints);
tableId = GetTableId(logicDbName, shardingStrategy, entity, columns, parameters, hints);
if (String.IsNullOrEmpty(shardId) && String.IsNullOrEmpty(tableId))
{
throw new DalException("Please provide shard information.");
}
return(Tuple.Create <String, String>(shardId, tableId));
}
/// <summary>
/// 执行单返回值聚集查询指令
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="hints">指令参数：如timeout</param>
/// <param name="isWrite">读写配置</param>
/// <returns>object</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public Object ExecScalar(String sql, StatementParameterCollection parameters, IDictionary hints, bool isWrite = false)
{
return(ExecScalar(sql, parameters, hints, isWrite ? OperationType.Write : OperationType.Read));
}
private static String GetTableId <T>(String logicDbName, IShardingStrategy shardingStrategy,
T entity, ICollection <SqlColumn> columns, StatementParameterCollection parameters, IDictionary hints)
{
String tableId = null;
Boolean shardByTable = IsShardByTable(shardingStrategy);
if (shardByTable)
{
tableId = GetTableIdByHints(hints);
if (String.IsNullOrEmpty(tableId))
{
IComparable shardColumnValue = shardingStrategy.GetShardColumnValue(logicDbName, entity, columns, parameters, hints);
tableId = CalculateShardId(shardingStrategy, shardColumnValue);
}
}
return(tableId);
}
/// <summary>
/// 执行非查询指令
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">查询参数</param>
/// <param name="hints">指令参数：如timeout</param>
/// <param name="isWrite">读写配置</param>
/// <returns>影响行数</returns>
/// <exception cref="DalException">数据访问框架异常</exception>
public Int32 ExecNonQuery(String sql, StatementParameterCollection parameters, IDictionary hints, bool isWrite = true)
{
return(ExecNonQuery(sql, parameters, hints, isWrite ? OperationType.Write : OperationType.Read));
}
public static IList <Statement> GetShardStatement(String logicDbName, IShardingStrategy shardingStrategy,
StatementParameterCollection parameters, IDictionary hints, Func <IDictionary, Statement> func)
{
IList <Statement> statements;
if (shardingStrategy == null)
{
return(null);
}
var shardingType = GetShardingType(shardingStrategy);
if (shardingType != ShardingType.ShardByDBAndTable)
{
IList <String> shards = null;
//Get shards from hints
if (hints != null)
{
IList <String> temp = null;
if (shardingType == ShardingType.ShardByDB)
{
if (hints.Contains(DALExtStatementConstant.SHARD_IDS))
{
temp = hints[DALExtStatementConstant.SHARD_IDS] as List <String>;
}
else if (hints.Contains(DALExtStatementConstant.SHARDID))
{
temp = new List <String> {
hints[DALExtStatementConstant.SHARDID] as String
};
}
}
else if (shardingType == ShardingType.ShardByTable)
{
if (hints.Contains(DALExtStatementConstant.TABLE_IDS))
{
temp = hints[DALExtStatementConstant.TABLE_IDS] as List <String>;
}
else if (hints.Contains(DALExtStatementConstant.TABLEID))
{
temp = new List <String> {
hints[DALExtStatementConstant.TABLEID] as String
};
}
}
if (temp != null)
{
shards = temp;
}
}
//Get shards from parameters
if (shards == null)
{
if (parameters != null)
{
if (shardingType == ShardingType.ShardByDB)
{
String shardId = GetShardId <Object>(logicDbName, shardingStrategy, null, null, parameters, hints);
if (!String.IsNullOrEmpty(shardId))
{
shards = new List <String> {
shardId
}
}
;
}
else if (shardingType == ShardingType.ShardByTable)
{
String tableId = GetTableId <Object>(logicDbName, shardingStrategy, null, null, parameters, hints);
if (!String.IsNullOrEmpty(tableId))
{
shards = new List <String> {
tableId
}
}
;
}
}
}
if (shards == null)
{
throw new DalException("Please provide shard information.");
}
//Build statements
statements = new List <Statement>();
foreach (var item in shards)
{
var newHints = HintsUtil.CloneHints(hints);
switch (shardingType)
{
case ShardingType.ShardByDB:
newHints[DALExtStatementConstant.SHARDID] = item;
break;
case ShardingType.ShardByTable:
newHints[DALExtStatementConstant.TABLEID] = item;
break;
}
Statement statement = func.Invoke(newHints);
statements.Add(statement);
}
}
else
{
statements = new List <Statement>();
IDictionary <String, IList <String> > shardDict = null;
if (hints != null && hints.Contains(DALExtStatementConstant.SHARD_TABLE_DICT))
{
shardDict = hints[DALExtStatementConstant.SHARD_TABLE_DICT] as IDictionary <String, IList <String> >;
}
if (shardDict == null)
{
var newHints = HintsUtil.CloneHints(hints);
newHints[DALExtStatementConstant.SHARDID] = GetShardIdByHints(hints);
newHints[DALExtStatementConstant.TABLEID] = GetTableIdByHints(hints);
Statement statement = func.Invoke(newHints);
statements.Add(statement);
}
else
{
foreach (var shard in shardDict)
{
foreach (var table in shard.Value)
{
var newHints = HintsUtil.CloneHints(hints);
newHints[DALExtStatementConstant.SHARDID] = shard.Key;
newHints[DALExtStatementConstant.TABLEID] = table;
Statement statement = func.Invoke(newHints);
statements.Add(statement);
}
}
}
}
return(statements);
}
public IDataReader SelectDataReader(String sql, StatementParameterCollection parameters, IDictionary hints, bool isWrite = false)
{
return(SelectDataReader(sql, parameters, hints, isWrite? OperationType.Write : OperationType.Read));
}
/// <summary>
/// GetSqlStatement
/// </summary>
/// <param name="logicDbName"></param>
/// <param name="shardingStrategy"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="extendedParameters"></param>
/// <param name="operationType"></param>
/// <returns></returns>
public static Statement GetSqlStatement(String logicDbName,
String sql, StatementParameterCollection parameters, IDictionary extendedParameters, OperationType?operationType = null)
{
return(GetDefaultSqlStatement(logicDbName, sql, parameters, extendedParameters, SqlStatementType.SELECT, operationType ?? OperationType.Default));
}
