protected DbCommand CreateCommand(string sqlQuery, Dictionary <string, object> parameters)
{
DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = sqlQuery;
dbCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", match => SqlDialect.CreateParameterExpression(match.Value.Substring(1)));
if (parameters != null)
{
foreach (var parameter in parameters)
{
IDbDataParameter dataParameter = dbCommand.CreateParameter();
dataParameter.ParameterName = SqlDialect.CreateParameterExpression(parameter.Key);
dataParameter.Direction = ParameterDirection.Input;
dataParameter.Value = ConvertParameter(parameter.Value);
dbCommand.Parameters.Add(dataParameter);
}
}
return(dbCommand);
}
protected DbCommand CreateCommand(string sqlQuery, QueryParameterCollection parameters, CommandType commandType)
{
DbCommand dbCommand = Connection.CreateCommand();
dbCommand.CommandType = commandType;
dbCommand.CommandText = sqlQuery;
dbCommand.Transaction = CurrentTransaction;
dbCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", match => SqlDialect.CreateParameterExpression(match.Value.Substring(1)));
if (parameters != null)
{
foreach (var parameter in parameters)
{
IDbDataParameter dataParameter = dbCommand.CreateParameter();
dataParameter.ParameterName = SqlDialect.CreateParameterExpression(parameter.Name);
dataParameter.Direction = ParameterDirection.Input;
dataParameter.DbType = DbType(parameter.Type);
dataParameter.Value = ConvertParameter(parameter.Value);
dbCommand.Parameters.Add(dataParameter);
}
}
return(dbCommand);
}
public override IEnumerable <Dictionary <string, object> > ExecuteSqlReader(string sql, QueryParameterCollection parameters)
{
var stopwatch = SqlLogger != null?Stopwatch.StartNew() : null;
try
{
BeginTransaction(IsolationLevel.None);
List <Dictionary <string, object> > records = new List <Dictionary <string, object> >();
using (var cmd = CreateCommand(sql, parameters, CommandType.Text))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> rec = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (reader.IsDBNull(i))
{
rec[fieldName] = null;
}
else
{
rec[fieldName] = reader.GetValue(i);
}
}
records.Add(rec);
}
}
}
return(records);
}
finally
{
CommitTransaction();
SqlLogger?.LogSql(sql, parameters?.ToDictionary(p => SqlDialect.CreateParameterExpression(p.Name), p => p.Value), stopwatch?.Elapsed ?? TimeSpan.Zero);
}
}
public override int ExecuteProcedure(string procName, QueryParameterCollection parameters = null)
{
var stopwatch = SqlLogger != null?Stopwatch.StartNew() : null;
try
{
BeginTransaction(IsolationLevel.None);
using (var cmd = CreateCommand(procName, parameters, CommandType.StoredProcedure))
{
return(cmd.ExecuteNonQuery());
}
}
finally
{
CommitTransaction();
SqlLogger?.LogSql("EXEC " + procName, parameters?.ToDictionary(p => SqlDialect.CreateParameterExpression(p.Name), p => p.Value), stopwatch?.Elapsed ?? TimeSpan.Zero);
}
}
