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