private void PrepareParametersByQuery(DbCommandWrapper info)
{
if (Regex.IsMatch(info.Query, @"(?i)create\s+((proc)|(func))"))
{
info.Parameters = new DbParameter[] {};
return;
}
if (info.Dialect != DbDialect.SqlServer)
{
throw new Exception("cannot setup parameters for non-TSql query");
}
var references = Regex.Matches(info.Query, @"@[\w\d]+").OfType <Match>().Select(_ => _.Value.Substring(1))
.Distinct().ToList();
foreach (var r in references.ToArray())
{
if (Regex.IsMatch(info.Query, @"(?i)declare\s+@" + r + @"\s+"))
{
references.Remove(r);
}
}
info.Parameters = references.Select(_ => new DbParameter {
Name = _
}).ToArray();
}
private static void DetectTypeByOwnConnection(DbCommandWrapper info, string query)
{
var connecitonIsOpened = info.Connection.State == ConnectionState.Open;
try {
if (!connecitonIsOpened)
{
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database))
{
info.Connection.ChangeDatabase(info.Database);
}
var cmd = info.Connection.CreateCommand();
cmd.CommandText = query;
var result = cmd.ExecuteScalar();
SetupObjectTypeValue(info, result);
}
finally {
if (!connecitonIsOpened)
{
info.Connection.Close();
}
}
}
private void DetectObjectType(DbCommandWrapper info)
{
var schema = "dbo";
var name = info.ObjectName;
if (name.Contains("."))
{
schema = name.Split('.')[0];
name = name.Split('.')[1];
}
var query = string.Format(GetObjectTypeInfoQueryTemplate, schema, name);
if (null == ProxyExecutor)
{
DetectTypeByOwnConnection(info, query);
}
else
{
var callProxy = info.CloneNoQuery();
callProxy.Query = query;
callProxy.Notation = DbCallNotation.Scalar;
ProxyExecutor.Execute(callProxy).Wait();
SetupObjectTypeValue(info, callProxy.Result);
}
}
private void PrepareParametersByObject(DbCommandWrapper info)
{
if (info.Dialect != DbDialect.SqlServer)
{
throw new Exception("cannot setup parameters for non-TSql query");
}
var schema = "dbo";
var name = info.ObjectName;
if (name.Contains("."))
{
schema = name.Split('.')[0];
name = name.Split('.')[1];
}
var query = string.Format(PrepareParametersQueryTemplate, schema, name);
if (null == ProxyExecutor)
{
SetupByOwnConnection(info, query);
}
else
{
SetupByProxy(info, query);
}
}
private void PrepareParameters(DbCommandWrapper info)
{
if (info.Trace)
{
Logger.Info("begin prepare parameters: " + info);
}
//если набор парамтеров определен при вызове - не требуется подготавливать параметры
if (null != info.Parameters)
{
return;
}
if (!string.IsNullOrWhiteSpace(info.Query))
{
PrepareParametersByQuery(info);
}
else if (!string.IsNullOrWhiteSpace(info.ObjectName))
{
PrepareParametersByObject(info);
}
StoreParametersFromSource(info);
if (info.Trace)
{
Logger.Info("end prepare parameters: " + info);
}
}
private static void SetupByOwnConnection(DbCommandWrapper info, string query)
{
var connecitonIsOpened = info.Connection.State == ConnectionState.Open;
try {
if (!connecitonIsOpened)
{
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database))
{
info.Connection.ChangeDatabase(info.Database);
}
var cmd = info.Connection.CreateCommand();
cmd.CommandText = query;
var parameters = new List <DbParameter>();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
var name = reader.GetString(0);
var type = reader.GetString(1);
parameters.Add(SetupParameter(name, type));
}
}
info.Parameters = parameters.ToArray();
}
finally {
if (!connecitonIsOpened)
{
info.Connection.Close();
}
}
}
private void StoreParametersFromSource(DbCommandWrapper info)
{
if (info.ParametersBinded)
{
return;
}
if (null == info.ParametersSoruce)
{
return;
}
var dictionary = info.ParametersSoruce.ToDict();
foreach (var o in dictionary.ToArray())
{
dictionary[o.Key.ToLowerInvariant()] = o.Value;
}
foreach (var pd in info.Parameters)
{
pd.Value = null;
if (dictionary.ContainsKey(pd.Name.ToLowerInvariant()))
{
pd.Value = dictionary[pd.Name.ToLowerInvariant()];
}
}
info.ParametersBinded = true;
}
private void PrepareQuery(DbCommandWrapper info)
{
if (info.Trace)
{
Logger.Info("begin prepare query: " + info);
}
if (string.IsNullOrWhiteSpace(info.Query))
{
if (string.IsNullOrWhiteSpace(info.ObjectName))
{
throw new Exception("cannot setup query - no query or objectname");
}
if (info.Dialect != DbDialect.SqlServer)
{
throw new Exception("cannot setup query for non-TSql");
}
PrepareTSqlObjectQuery(info);
}
var cmd = info.PreparedCommand = info.Connection.CreateCommand();
cmd.CommandText = info.Query;
StoreParametersFromSource(info);
foreach (var parameter in info.Parameters)
{
var p = cmd.CreateParameter();
var name = parameter.Name;
if (p is SqlParameter)
{
name = "@" + parameter.Name;
}
p.ParameterName = name;
if (parameter.DbType != default(DbType))
{
p.DbType = parameter.DbType;
}
if (p.DbType == DbType.AnsiString || p.DbType == DbType.String)
{
p.Size = -1;
}
if (p.DbType == DbType.Decimal)
{
p.Size = 18;
p.Precision = 6;
}
p.Value = parameter.Value ?? DBNull.Value;
cmd.Parameters.Add(p);
}
if (info.Trace)
{
Logger.Info("end prepare query: " + info);
}
}
private object SetupSingleResult(DbCommandWrapper info, IDataReader reader, int resultNumber = 0)
{
var result = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var value = reader.GetValue(i);
if (DBNull.Value == value)
{
value = null;
}
result[name] = value;
}
if (info.Notation == DbCallNotation.ObjectReader || info.Notation == DbCallNotation.SingleObject ||
info.Notation == DbCallNotation.MultipleObject)
{
var type = info.TargetType;
if (info.Notation == DbCallNotation.MultipleObject)
{
if (null != info.TargetTypes && 0 != info.TargetTypes.Length)
{
if (resultNumber < info.TargetTypes.Length)
{
type = info.TargetTypes[resultNumber];
}
else
{
throw new Exception("invalid types count for multiple ORM");
}
}
}
if (null == type)
{
throw new Exception("no type was given for ORM call notation");
}
foreach (var source in result.ToArray())
{
result[source.Key.ToLowerInvariant()] = source.Value;
}
var ormresult = Activator.CreateInstance(type);
var properties = type.GetProperties();
foreach (var propertyInfo in properties)
{
if (propertyInfo.CanWrite)
{
if (result.ContainsKey(propertyInfo.Name.ToLowerInvariant()))
{
var srcvalue = result[propertyInfo.Name.ToLowerInvariant()];
var resultvalue = srcvalue.ToTargetType(propertyInfo.PropertyType, true);
propertyInfo.SetValue(ormresult, resultvalue);
}
}
}
return(ormresult);
}
return(result);
}
private void ExecuteCommand(DbCommandWrapper info)
{
if (info.Notation == DbCallNotation.None)
{
info.PreparedCommand.ExecuteNonQuery();
}
else if (info.Notation == DbCallNotation.Scalar)
{
var result = info.PreparedCommand.ExecuteScalar();
info.Result = result;
}
else if (info.Notation.HasFlag(DbCallNotation.Reader))
{
using (var reader = info.PreparedCommand.ExecuteReader()) {
if (info.Notation.HasFlag(DbCallNotation.Single))
{
if (reader.Read())
{
info.Result = SetupSingleResult(info, reader);
}
}
else if (info.Notation.HasFlag(DbCallNotation.Multiple))
{
var resultNumber = 0;
var resultAdvanced = false;
var result = new List <object>();
var current = new List <object>();
while ((reader.Read()) || (resultAdvanced = reader.NextResult()))
{
if (resultAdvanced)
{
resultAdvanced = false;
resultNumber++;
result.Add(current.ToArray());
current = new List <object>();
continue;
}
current.Add(SetupSingleResult(info, reader, resultNumber));
}
result.Add(current.ToArray());
info.Result = result.ToArray();
}
else
{
var result = new List <object>();
while (reader.Read())
{
result.Add(SetupSingleResult(info, reader));
}
info.Result = result.ToArray();
}
}
}
}
private void PrepareConnection(DbCommandWrapper info)
{
if (info.Trace)
{
Logger.Info("begin prepare connection: " + info);
}
if (null == info.Connection)
{
if (string.IsNullOrWhiteSpace(info.ConnectionString))
{
info.ConnectionString = "default";
}
if (null == ConnectionProvider)
{
if (info.ConnectionString == "default")
{
info.ConnectionString = "Server=(local);Trusted_Connection=true";
}
if (info.ConnectionString.Contains(";"))
{
info.Connection = new SqlConnection(info.ConnectionString);
}
else
{
throw new Exception("no connection provider setup");
}
}
else
{
info.Connection = ConnectionProvider.GetConnection(info.ConnectionString);
}
if (null == info.Connection)
{
throw new Exception("cannot retrieve connection for " + info.ConnectionString);
}
}
if (info.Dialect == DbDialect.None)
{
if (info.Connection is SqlConnection)
{
info.Dialect = DbDialect.SqlServer;
}
else if (info.Connection.GetType().Name.ToLowerInvariant().Contains("pg"))
{
info.Dialect = DbDialect.PostGres;
}
else
{
info.Dialect = DbDialect.Ansi;
}
}
if (info.Trace)
{
Logger.Info("end prepare connection: " + info);
}
}
private void PrepareTSqlObjectQuery(DbCommandWrapper info)
{
if (info.ObjectType == SqlObjectType.None)
{
DetectObjectType(info);
}
var q = new StringBuilder();
if (info.ObjectType == SqlObjectType.Function || info.ObjectType == SqlObjectType.TableFunction)
{
if (info.ObjectType == SqlObjectType.TableFunction)
{
q.Append("SELECT * FROM ");
}
else
{
q.Append("SELECT ");
}
q.Append(info.ObjectName);
q.Append("(");
var first = true;
foreach (var parameter in info.Parameters)
{
if (!first)
{
q.Append(", ");
}
first = false;
q.Append("@");
q.Append(parameter.Name);
}
q.Append(")");
}
else
{
q.Append("EXEC ");
q.Append(info.ObjectName);
q.Append(" ");
var first = true;
foreach (var parameter in info.Parameters)
{
if (!first)
{
q.Append(", ");
}
first = false;
q.Append("@");
q.Append(parameter.Name);
q.Append("=@");
q.Append(parameter.Name);
}
}
info.Query = q.ToString();
}
private Task <DbCommandWrapper> InternalExecute(DbCommandWrapper info)
{
if (null != ProxyExecutor)
{
if (info.Trace)
{
Logger.Info("redirect execution to proxy: " + info);
}
return(ProxyExecutor.Execute(info));
}
return(Task.Run(() => InternalExecuteSync(info)));
}
/// <summary>
/// Копия исходного контекста за исключением самого запроса и его результата
/// </summary>
/// <returns></returns>
public DbCommandWrapper CloneNoQuery()
{
var result = new DbCommandWrapper {
Connection = Connection,
ConnectionString = ConnectionString,
Database = Database,
Trace = Trace,
Dialect = Dialect,
OnError = OnError,
OnMessage = OnMessage
};
return(result);
}
/// <summary>
/// Производит асинхронный вызов Sql
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public async Task <DbCommandWrapper> Execute(DbCommandWrapper info)
{
try {
if (null == info.PreparedCommand)
{
PrepareConnection(info);
PrepareParameters(info);
PrepareQuery(info);
}
else if (info.Trace)
{
Logger.Info("not required for prepare: ");
}
if (info.NoExecute)
{
if (info.Trace)
{
Logger.Info("no execution required: ");
}
return(info);
}
return(await InternalExecute(info));
}
catch (Exception ex) {
if (info.Trace)
{
var str = ex.ToString();
var len = str.Length;
if (len > 500)
{
str = str.Substring(500);
}
Logger.Error("error in " + info + ": " + str, ex);
}
info.Error = ex;
if (null != info.OnError)
{
info.OnError(info, ex);
}
}
return(info);
}
private DbCommandWrapper InternalExecuteSync(DbCommandWrapper info)
{
if (info.Trace)
{
Logger.Info("begin execution: " + info);
}
var wasOpened = info.Connection.State == ConnectionState.Open;
SqlInfoMessageEventHandler onMessage = null;
try {
if (null != info.OnMessage && info.Connection is SqlConnection)
{
onMessage = (o, a) => { info.OnMessage(info, a.Message); };
((SqlConnection)info.Connection).InfoMessage += onMessage;
}
if (!wasOpened)
{
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database))
{
info.Connection.ChangeDatabase(info.Database);
}
ExecuteCommand(info);
}
finally {
if (!wasOpened)
{
info.Connection.Close();
}
if (null != onMessage)
{
((SqlConnection)info.Connection).InfoMessage -= onMessage;
}
}
if (info.Trace)
{
Logger.Info("end execution: " + info);
}
return(info);
}
private static void SetupObjectTypeValue(DbCommandWrapper info, object result)
{
if (null == result || DBNull.Value == result)
{
throw new Exception("cannot detect object type for " + info.ObjectName);
}
var type = result.ToStr().Trim();
if (type == "P")
{
info.ObjectType = SqlObjectType.Procedure;
}
else if (type == "F")
{
info.ObjectType = SqlObjectType.Function;
}
else if (type == "TF")
{
info.ObjectType = SqlObjectType.TableFunction;
}
}
private void SetupByProxy(DbCommandWrapper info, string query)
{
var proxyCall = info.CloneNoQuery();
proxyCall.Query = query;
proxyCall.Notation = DbCallNotation.Reader;
ProxyExecutor.Execute(proxyCall).Wait();
if (proxyCall.Ok)
{
var result = (object[])proxyCall.Result;
var parameters = new List <DbParameter>();
foreach (IDictionary <string, object> record in result)
{
parameters.Add(SetupParameter(record["name"].ToStr(), record["type"].ToStr()));
}
info.Parameters = parameters.ToArray();
}
else
{
throw proxyCall.Error;
}
}