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