// ctor for non-mapped stored procedure internal PassChainer(IStoredProc prev, ParameterArgument[] arguments) : this((Chainer)prev) { var root = GetRoot(); if (arguments == null) { arguments = new ParameterArgument[] { null }; } Array.ForEach(arguments, argument => { if (argument == null) { argument = Designer.Null; } TryThrow(argument.Exception); // infer data type from value var param = Variable.InferParam(root, argument, out chainException); TryThrow(); root.TryAddParamOrThrow(param, true); }); Executable = new Executable((Compilable)prev, arguments); }
public async Task <int> ExecuteNonQueryAsync <TRequest>( IStoredProc <TRequest> sp, TRequest parameters) { // Copy-paste cannot be avoided due to ExecuteAsync must be completed inside of SqlConnection. var connectionStr = _dbResources.ChooseDb(sp.DbName).SelectRandomly(); var stopwatch = Stopwatch.StartNew(); Exception error = null; try { using (var connection = _generateConnection(connectionStr)) { return(await connection.ExecuteAsync( sp.StoredProcedureName, parameters, commandTimeout : sp.CommandTimeoutSecs, commandType : CommandType.StoredProcedure)); } } catch (Exception e) { RaiseOnError(e, 1); error = e; throw; } finally { stopwatch.Stop(); RaiseOnQueryComplete(sp, stopwatch.ElapsedMilliseconds, error); } }
/// <summary> /// Fill Stored Proc Parameters /// </summary> /// <param name="proc">Stored Procedure</param> /// <param name="values">Values</param> public static void Fill(this IStoredProc proc, IDictionary <string, object> values) { if (null != values) { var properties = proc.GetProperties(); foreach (var property in properties) { if (null != property && property.CanWrite && values.ContainsKey(property.Name)) { var value = values[property.Name]; if (null == value) { property.SetValue(proc, null, null); } else if (value.GetType() == property.PropertyType) { property.SetValue(proc, value, null); } else { var t = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; var safeValue = Convert.ChangeType(value, t); property.SetValue(proc, safeValue, null); } } } } }
/// <summary> /// User Permissions /// </summary> /// <param name="identifier">Identifier</param> /// <param name="type">Type</param> /// <returns>User Identifiers</returns> private Guid[] UserPermissions(Guid identifier, Reference type) { IStoredProc proc = null; switch (type) { case Reference.Item: proc = new AdminGetItemUsers() { Identifier = identifier, }; break; case Reference.User: proc = new AdminGetProfileUsers() { UserIdentifier = identifier, }; break; case Reference.ItemRequest: proc = new AdminGetItemRequestUsers() { Identifier = identifier, }; break; } var permissions = proc.CallObjects <SearchPermissions>(); return(permissions.Select(p => p.UserIdentifier).ToArray()); }
public Task <IEnumerable <TResult> > QueryAsync <TRequest, TResult>( IStoredProc <TRequest, TResult> sp, TRequest parameters, string cacheKey = "") { return(ExecuteCacheOrGetAsync(sp, parameters, () => QueryAsyncImpl(sp, parameters), cacheKey)); }
public int ExecuteNonQuery <TRequest>( IStoredProc <TRequest> sp, TRequest parameters) { var connectionStr = _dbResources.ChooseDb(sp.DbName).SelectRandomly(); var stopwatch = Stopwatch.StartNew(); Exception error = null; try { using (var connection = _generateConnection(connectionStr)) { return(connection.Execute( sp.StoredProcedureName, parameters, commandTimeout: sp.CommandTimeoutSecs, commandType: CommandType.StoredProcedure)); } } catch (Exception e) { RaiseOnError(e, 1); error = e; throw; } finally { stopwatch.Stop(); RaiseOnQueryComplete(sp, stopwatch.ElapsedMilliseconds, error); } }
private Task <TResult> QueryAsyncSharedBlock <TResult>( IStoredProc sp, Func <IDbConnection, Task <TResult> > queryFunc) { return(_dbResources.ChooseDb(sp.DbName).ExecuteAsync(async(connectionStr, _) => { var stopwatch = Stopwatch.StartNew(); Exception error = null; try { using (var connection = _generateConnection(connectionStr)) { return await queryFunc(connection); } } catch (Exception e) { error = e; throw; } finally { stopwatch.Stop(); RaiseOnQueryComplete(sp, stopwatch.ElapsedMilliseconds, error); } }, ShouldRetry(sp.MaxAttemptCount), RaiseOnError)); }
private Task <TFuncResult> ExecuteCacheOrGetAsync <TRequest, TResult, TFuncResult>( IStoredProc <TRequest, TResult> sp, TRequest parameters, Func <Task <TFuncResult> > getResultFunc) { return(EnableCache(sp) ? _cache.GetOrCreateAsync( sp.GetParameters(parameters).CreateCacheKey(sp.StoredProcedureName), sp.CacheLifetime, getResultFunc) : getResultFunc()); }
private Task <IEnumerable <TResult> > QueryAsyncImpl <TRequest, TResult>( IStoredProc <TRequest, TResult> sp, TRequest parameters) { return(QueryAsyncSharedBlock(sp, connection => connection.QueryAsync <TResult>( sp.StoredProcedureName, parameters, commandTimeout: sp.CommandTimeoutSecs, commandType: CommandType.StoredProcedure) )); }
/// <summary> /// Execute Non-Query /// </summary> /// <param name="proc">Procedure To Execute</param> /// <returns>rows affected</returns> public static int ExecuteNonQuery(this IStoredProc proc) { var rowsAffected = 0; var database = DatabaseFactory.CreateDatabase(); using (var command = proc.BuildCommand(database)) { rowsAffected = database.ExecuteNonQuery(command); } return(rowsAffected); }
public static IList <T> CallObjects <T>(this IStoredProc proc) where T : new() { IList <T> obj = null; using (var ds = proc.Execute()) { obj = ds.LoadObjects <T>(); } return(obj); }
/// <summary> /// Execute IStored Proc /// </summary> /// <param name="proc">Procedure</param> /// <returns>Data Set</returns> public static DataSet Execute(this IStoredProc proc) { DataSet dataSet = null; var database = DatabaseFactory.CreateDatabase(); using (var command = proc.BuildCommand(database)) { dataSet = database.ExecuteDataSet(command); } return(dataSet); }
public static T CallObject <T>(this IStoredProc proc) where T : new() { var obj = default(T); using (var ds = proc.Execute()) { obj = ds.LoadObject <T>(); } return(obj); }
private TFuncResult ExecuteCacheOrGet <TRequest, TResult, TFuncResult>( IStoredProc <TRequest, TResult> sp, TRequest parameters, Func <TFuncResult> getResultFunc, string cacheKey = "") { return(EnableCache(sp) ? _cache.GetOrCreate(string.IsNullOrEmpty(cacheKey) ? sp.GetParameters(parameters).CreateCacheKey(sp.StoredProcedureName) : cacheKey, sp.CacheLifetime, getResultFunc) : getResultFunc()); }
internal static List <IStoredProc> GetAllStoredProcs() { List <IStoredProc> result = new List <IStoredProc>(); foreach (Type type in _StoredProcTable.Values) { IStoredProc storedProc = Hubble.Framework.Reflection.Instance.CreateInstance(type) as Hubble.Core.StoredProcedure.IStoredProc; if (storedProc != null) { result.Add(storedProc); } } return(result); }
/// <summary> /// Build Command /// </summary> /// <param name="proc">Procedure</param> /// <param name="database">Database</param> /// <returns>Database Command</returns> public static DbCommand BuildCommand(this IStoredProc proc, Database database) { if (null == database) { throw new ArgumentNullException("database"); } var command = database.GetStoredProcCommand(proc.StoredProc); foreach (var prop in proc.GetProperties()) { var mapper = prop.GetAttribute <DataMapperAttribute>(); if (mapper != null) { var value = prop.GetValue(proc, null); database.AddInParameter(command, mapper.ParameterName, mapper.DatabaseType, value); } } return(command); }
// ctor for SQL batch internal PassChainer(IStoredProc prev, ParameterArgument[] arguments, List <string> batchParameters) : this((Chainer)prev) { var root = GetRoot(); // check arguments-params count int argumentsCount = arguments == null ? 0 : arguments.Count(); int parametersCount = batchParameters.Count; if (argumentsCount != parametersCount) { ThrowArgumentCountMismatch(parametersCount, argumentsCount); } if (arguments == null) { arguments = new ParameterArgument[] { null }; } int i = 0; Array.ForEach(arguments, argument => { if (argument == null) { argument = Designer.Null; } TryThrow(argument.Exception); // infer data type from value var param = Variable.InferParam(root, argument, out chainException, batchParameters[i++]); TryThrow(); root.TryAddParamOrThrow(param, true); }); Executable = new Executable((Compilable)prev, arguments); }
public bool CallProc(IStoredProc objProc) { return(CallProc(objProc, null)); }
//private Transaction CreateTransaction() //{ // Transaction tr=mConn.BeginTransaction(); // tr.Commit(); //} #endregion #endregion #region 公开方法:存贮过程相关 #region CallProc public bool CallProc(IStoredProc objProc, Transaction tr) { #region 创建命令 DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = objProc.GetProcName(); dbCommand.Transaction = tr; #endregion #region 参数赋值 #if DT_ORACLE DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), false); #elif DT_MSSQL DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), true); #elif DT_OLEDB DbParameter[] dbparams = new DbParameter[0]; this.WriteLogInfo(this.GetType().Name + "OLEDB方式不支持取存贮过程参数"); return(false); #endif Hashtable htParam = objProc.GetFields(); foreach (DbParameter dbparam in dbparams) { if (dbparam.Direction == ParameterDirection.Input || dbparam.Direction == ParameterDirection.InputOutput) { dbparam.Value = htParam[dbparam.ParameterName.ToUpper()]; } else { dbparam.Value = DBNull.Value; } dbCommand.Parameters.Add(dbparam); } #endregion #region 执行 DataSet mDataSet = new DataSet(); using (DataAdapter mDataAdapter = new DataAdapter(dbCommand)) { try { mDataAdapter.Fill(mDataSet); } catch (Exception e) { WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n" + e.Message); return(false); } } #endregion #region 读取参数 foreach (DbParameter dbparam in dbparams) { if (dbparam.Direction == ParameterDirection.Output || dbparam.Direction == ParameterDirection.InputOutput) { htParam[dbparam.ParameterName.ToUpper()] = dbparam.Value; } } if (objProc.GetCursorFieldList() != "") { string[] cursorFields = objProc.GetCursorFieldList().Split(new char[] { ',' }); if (mDataSet.Tables.Count != cursorFields.Length) { WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n 返回的游标数量与参数不匹配:" + "\r\n\t游标参数:" + objProc.GetCursorFieldList() + "\r\n\t游标返回数:" + mDataSet.Tables.Count); return(false); } int idx = 0; foreach (string cursorField in cursorFields) { htParam[cursorField.ToUpper()] = mDataSet.Tables[idx++]; } } #endregion return(true); }
protected virtual void RaiseOnQueryComplete(IStoredProc sp, long time, Exception error) => OnQueryComplete?.Invoke(this, new QueryCompleteEventArgs(sp, time, error));
private static bool EnableCache <TRequest, TResult>(IStoredProc <TRequest, TResult> sp) { return(sp.CacheLifetime.HasValue && sp.CacheLifetime.Value > TimeSpan.Zero); }
public IEnumerable <TResult> Query <TRequest, TResult>( IStoredProc <TRequest, TResult> sp, TRequest parameters) { return(ExecuteCacheOrGet(sp, parameters, () => QueryImpl(sp, parameters))); }
public QueryCompleteEventArgs(IStoredProc storedProc, long executionTime, Exception error) { StoredProc = storedProc; ExecutionTime = executionTime; Error = error; }
public Query Chain(IStoredProc other) { return new Query() { procs = this.procs.Add(other) }; }