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