internal SqlConnectionManager(IProvider provider, DbConnection con, int maxUsers, bool disposeConnection) {
this.provider = provider;
this.connection = con;
this.maxUsers = maxUsers;
this.infoMessagehandler = new SqlInfoMessageEventHandler(this.OnInfoMessage);
this.users = new List<IConnectionUser>(maxUsers);
this.disposeConnection = disposeConnection;
}
/// <summary>
/// Opens a connection to the database, executes the <paramref name="commandText"/> against the connection, and returns the resulting value.
/// Throws if there are no rows, multiple rows, no columns, or multiple columns.
/// This is a more restrictive version of <see cref="SqlCommand.ExecuteScalar"/>.
/// </summary>
/// <remarks>
/// Sets CommandBehavior = CommandBehavior.CloseConnection so that the created connection is closed when the data reader is closed.
/// </remarks>
/// <param name="connectionString">String used to open a connection to the database.</param>
/// <param name="commandText">The SQL statement, table name, or stored procedure to execute at the data source.</param>
/// <param name="commandTimeoutInSeconds">OPTIONAL value with the wait time, in seconds, before terminating an attempt to execute the command and generating an error. DEFAULT is 30 seconds. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).</param>
/// <param name="commandParameters">OPTIONAL set of parameters to associate with the command. DEFAULT is null (no parameters).</param>
/// <param name="commandType">OPTIONAL value that determines how the command text is to be interpreted. DEFAULT is <see cref="CommandType.Text"/>; a SQL text command.</param>
/// <param name="commandBehavior">OPTIONAL value providing a description of the results of the query and its effect on the database. DEFAULT is <see cref="CommandBehavior.Default"/>; the query may return multiple result sets and execution of the query may affect the database state. This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values.</param>
/// <param name="prepareCommand">OPTIONAL value indicating whether to prepared (or compile) the command on the data source.</param>
/// <param name="sqlInfoMessageEventHandler">OPTIONAL method that will handle the <see cref="SqlConnection.InfoMessage"/> event.</param>
/// <returns>
/// The resulting value.
/// </returns>
public static object ReadSingleValue(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList <SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.CloseConnection,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
using (var reader = connectionString.ExecuteReader(commandText, commandTimeoutInSeconds, commandParameters, commandType, commandBehavior, prepareCommand, sqlInfoMessageEventHandler))
{
var result = reader.ReadSingleValueInternal();
return(result);
}
}
/// <summary>
/// 执行一个 SQL 语句，返回第一行第一列的内容
/// </summary>
public static object ExecuteScalar(string s, SqlInfoMessageEventHandler imHandler = null)
{
var se = CurrentSqlElements;
object r = null;
var conn = se.First;
int ocs = -1;
if (conn == null)
{
conn = new SqlConnection(ConnectString);
}
else
{
ocs = (int)conn.State;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Open();
}
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
try {
using (var cmd = conn.CreateCommand()) {
cmd.Transaction = se.Second;
cmd.CommandType = CommandType.Text;
cmd.CommandText = s;
r = cmd.ExecuteScalar();
}
} finally {
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Close();
}
if (ocs == -1)
{
conn.Dispose();
}
}
return(r);
}
/// <summary>
/// Opens a connection to the database, executes the <paramref name="commandText"/> against the connection, and returns a single row of values.
/// Throws if there are multiple rows or multiple columns with the same case-insensitive name.
/// </summary>
/// <remarks>
/// Sets CommandBehavior = CommandBehavior.CloseConnection so that the created connection is closed when the data reader is closed.
/// </remarks>
/// <param name="connectionString">String used to open a connection to the database.</param>
/// <param name="commandText">The SQL statement, table name, or stored procedure to execute at the data source.</param>
/// <param name="commandTimeoutInSeconds">OPTIONAL value with the wait time, in seconds, before terminating an attempt to execute the command and generating an error. DEFAULT is 30 seconds. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).</param>
/// <param name="commandParameters">OPTIONAL set of parameters to associate with the command. DEFAULT is null (no parameters).</param>
/// <param name="commandType">OPTIONAL value that determines how the command text is to be interpreted. DEFAULT is <see cref="CommandType.Text"/>; a SQL text command.</param>
/// <param name="commandBehavior">OPTIONAL value providing a description of the results of the query and its effect on the database. DEFAULT is <see cref="CommandBehavior.Default"/>; the query may return multiple result sets and execution of the query may affect the database state. This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values.</param>
/// <param name="prepareCommand">OPTIONAL value indicating whether to prepared (or compile) the command on the data source.</param>
/// <param name="sqlInfoMessageEventHandler">OPTIONAL method that will handle the <see cref="SqlConnection.InfoMessage"/> event.</param>
/// <returns>
/// null if there are no rows, otherwise a dictionary where the keys are column names (case insensitive) and values are the values of the single row returned by the query.
/// </returns>
public static async Task <IReadOnlyDictionary <string, object> > ReadSingleRowWithNamedColumnsOrDefaultAsync(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList <SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.CloseConnection,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
using (var reader = await connectionString.ExecuteReaderAsync(commandText, commandTimeoutInSeconds, commandParameters, commandType, commandBehavior, prepareCommand, sqlInfoMessageEventHandler))
{
var result = await reader.ReadSingleRowWithNamedColumnsInternalAsync(throwIfNoRows : false);
return(result);
}
}
/// <summary>
/// 执行一个 SQL 语句，填充一个数据表，返加受影响行数
/// </summary>
public static int ExecuteDataTable(DataTable dt, string s, SqlInfoMessageEventHandler imHandler = null)
{
var se = CurrentSqlElements;
var conn = se.First;
int ocs = -1;
if (conn == null)
{
conn = new SqlConnection(ConnectString);
}
else
{
ocs = (int)conn.State;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Open();
}
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
try {
using (var cmd = conn.CreateCommand()) {
cmd.Transaction = se.Second;
cmd.CommandType = CommandType.Text;
cmd.CommandText = s;
using (var sda = new SqlDataAdapter(cmd)) {
return(sda.Fill(dt));
}
}
} finally {
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Close();
}
if (ocs == -1)
{
conn.Dispose();
}
}
}
/// <summary>
/// Opens a connection to the database, executes the <paramref name="commandText"/> against the connection, and returns a single row of values.
/// Throws if there are no rows, multiple rows, or multiple columns with the same case-insensitive name.
/// </summary>
/// <remarks>
/// Sets CommandBehavior = CommandBehavior.CloseConnection so that the created connection is closed when the data reader is closed.
/// </remarks>
/// <param name="connectionString">String used to open a connection to the database.</param>
/// <param name="commandText">The SQL statement, table name, or stored procedure to execute at the data source.</param>
/// <param name="commandTimeoutInSeconds">OPTIONAL value with the wait time, in seconds, before terminating an attempt to execute the command and generating an error. DEFAULT is 30 seconds. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).</param>
/// <param name="commandParameters">OPTIONAL set of parameters to associate with the command. DEFAULT is null (no parameters).</param>
/// <param name="commandType">OPTIONAL value that determines how the command text is to be interpreted. DEFAULT is <see cref="CommandType.Text"/>; a SQL text command.</param>
/// <param name="commandBehavior">OPTIONAL value providing a description of the results of the query and its effect on the database. DEFAULT is <see cref="CommandBehavior.Default"/>; the query may return multiple result sets and execution of the query may affect the database state. This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values.</param>
/// <param name="prepareCommand">OPTIONAL value indicating whether to prepared (or compile) the command on the data source.</param>
/// <param name="sqlInfoMessageEventHandler">OPTIONAL method that will handle the <see cref="SqlConnection.InfoMessage"/> event.</param>
/// <returns>
/// A dictionary where the keys are the index (0-based) of the column in the result set and values are the values of the single row returned by the query.
/// </returns>
public static IReadOnlyDictionary <int, object> ReadSingleRowWithOrdinalColumns(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList <SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.CloseConnection,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
using (var reader = connectionString.ExecuteReader(commandText, commandTimeoutInSeconds, commandParameters, commandType, commandBehavior, prepareCommand, sqlInfoMessageEventHandler))
{
var result = reader.ReadSingleRowWithOrdinalColumnsInternal(throwIfNoRows: true);
return(result);
}
}
public static int ExecuteNonQuery(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList <SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
using (var connection = connectionString.OpenSqlConnection(sqlInfoMessageEventHandler))
{
var result = connection.ExecuteNonQuery(commandText, commandTimeoutInSeconds, commandParameters, commandType, null, prepareCommand);
connection.Close();
return(result);
}
}
/// <summary>
/// Opens a connection to the database, executes the <paramref name="commandText"/>,
/// and determines if reading from the <see cref="SqlDataReader"/> results in at least one row of data.
/// </summary>
/// <remarks>
/// Sets CommandBehavior = CommandBehavior.CloseConnection so that the created connection is closed when the data reader is closed.
/// </remarks>
/// <param name="connectionString">String used to open a connection to the database.</param>
/// <param name="commandText">The SQL statement, table name, or stored procedure to execute at the data source.</param>
/// <param name="commandTimeoutInSeconds">OPTIONAL value with the wait time, in seconds, before terminating an attempt to execute the command and generating an error. DEFAULT is 30 seconds. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).</param>
/// <param name="commandParameters">OPTIONAL set of parameters to associate with the command. DEFAULT is null (no parameters).</param>
/// <param name="commandType">OPTIONAL value that determines how the command text is to be interpreted. DEFAULT is <see cref="CommandType.Text"/>; a SQL text command.</param>
/// <param name="commandBehavior">OPTIONAL value providing a description of the results of the query and its effect on the database. DEFAULT is <see cref="CommandBehavior.Default"/>; the query may return multiple result sets and execution of the query may affect the database state. This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values.</param>
/// <param name="prepareCommand">OPTIONAL value indicating whether to prepared (or compile) the command on the data source.</param>
/// <param name="sqlInfoMessageEventHandler">OPTIONAL method that will handle the <see cref="SqlConnection.InfoMessage"/> event.</param>
/// <returns>
/// true if executing the command results in at least one row of data; otherwise false.
/// </returns>
public static async Task <bool> HasAtLeastOneRowWhenReadingAsync(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList <SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.CloseConnection,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
using (var reader = await connectionString.ExecuteReaderAsync(commandText, commandTimeoutInSeconds, commandParameters, commandType, commandBehavior, prepareCommand, sqlInfoMessageEventHandler))
{
var result = await reader.ReadAsync();
reader.Close();
return(result);
}
}
public ACS320_Process CalculateData(int year, int period, string byUser, SqlInfoMessageEventHandler infoMessageHandler = null)
{
SqlConnection conn = new SqlConnection(AppConfig.ConnectionString);
conn.FireInfoMessageEventOnUserErrors = true; // This flag allow receive message realtime.
SqlData db = new SqlData(conn);
try
{
db.BeginTrans();
//#####################
//# Process Calcualte
//# While processing, reponse progress periodically.
//#####################
conn.InfoMessage += infoMessageHandler;
ACS320_Process result = StoreProcedure.sp_ACS320_Process_CalculateData(db, year, period, byUser);
//#####################
//# Update Calculation Status
//#####################
conn.InfoMessage -= infoMessageHandler;
StoreProcedure.sp_ACS320_Update_ProcessControl(db, year, period, byUser);
db.CommitTrans();
return(result);
}
catch (Exception ex)
{
if (db.HasTransaction)
{
db.RollbackTrans();
}
throw;
}
finally
{
db.Close();
}
}
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);
}
public static SqlDataReader ExecuteReader(
this string connectionString,
string commandText,
int commandTimeoutInSeconds = 30,
IReadOnlyList<SqlParameter> commandParameters = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.CloseConnection,
bool prepareCommand = false,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
if (!commandBehavior.HasFlag(CommandBehavior.CloseConnection))
{
throw new ArgumentException(Invariant($"{nameof(commandBehavior)} does not set the flag {CommandBehavior.CloseConnection}. This will result in an open connection with the caller having no means of closing it."));
}
var connection = OpenSqlConnection(connectionString, sqlInfoMessageEventHandler);
var result = connection.ExecuteReader(commandText, commandTimeoutInSeconds, commandParameters, commandType, null, commandBehavior, prepareCommand);
return result;
}
/// <summary>
/// Execute command
/// </summary>
/// <param name="script"> Query text </param>
public string ExecuteNonQueryWithPrintMessage(string script)
{
SqlConnection conn = null;
var retMessage = new StringBuilder();
SqlInfoMessageEventHandler infoHandler = delegate(object sender, SqlInfoMessageEventArgs e) { retMessage.Append(e.Message); };
try
{
conn = GetConnection();
conn.InfoMessage += infoHandler;
var cmd = GetCommand(script, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.InfoMessage -= infoHandler;
}
CloseConnection(conn);
}
return(retMessage.ToString());
}
internal void OnInfoMessage(SqlInfoMessageEventArgs imevent)
{
if (Bid.TraceOn)
{
Bid.Trace("<sc.SqlConnection.OnInfoMessage|API|INFO> %d#, Message='%ls'\n", this.ObjectID, (imevent != null) ? imevent.Message : "");
}
SqlInfoMessageEventHandler handler = (SqlInfoMessageEventHandler) base.Events[EventInfoMessage];
if (handler != null)
{
try
{
handler(this, imevent);
}
catch (Exception exception)
{
if (!ADP.IsCatchableOrSecurityExceptionType(exception))
{
throw;
}
ADP.TraceExceptionWithoutRethrow(exception);
}
}
}
public void ProcessRetrieveData(SqlInfoMessageEventHandler infoMessageHandler)
{
using (SqlConnection conn = new SqlConnection(AppConfig.ConnectionString))
{
conn.InfoMessage += infoMessageHandler;
conn.FireInfoMessageEventOnUserErrors = true; // This flag allow receive message realtime.
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
using (SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable))
{
cmd.CommandText = "sp_Test_Progress";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = AppConfig.SqlDefaultTimeout;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw;
}
}
}
conn.Close();
}
}
internal void OnInfoMessage(SqlInfoMessageEventArgs imevent, out bool notified)
{
SqlInfoMessageEventHandler handler = InfoMessage;
if (null != handler)
{
notified = true;
try
{
handler(this, imevent);
}
catch (Exception e)
{
if (!ADP.IsCatchableOrSecurityExceptionType(e))
{
throw;
}
}
}
else
{
notified = false;
}
}
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user, AsyncQueryRunner.AsyncResult result)
{
var remoteIP = OData.GetRemoteIP();
var key = "total-" + remoteIP;
var currentCount = (int?)Current.GetCachedObject(key) ?? 0;
currentCount++;
Current.SetCachedObjectSliding(key, currentCount, 60 * 60);
if (currentCount > 130)
{
// clearly a robot, auto black list
Current.DB.BlackList.Insert(new { CreationDate = DateTime.UtcNow, IPAddress = remoteIP });
}
if (currentCount > 100)
{
throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!");
}
if (Current.DB.Query<int>("select count(*) from BlackList where IPAddress = @remoteIP", new { remoteIP }).First() > 0)
{
System.Threading.Thread.Sleep(2000);
throw new Exception("You have been blacklisted due to abuse!");
}
var results = new QueryResults();
using (SqlConnection cnn = site.GetOpenConnection())
{
// well we do not want to risk blocking, if somebody needs to change this we will need to add a setting
cnn.Execute("set transaction isolation level read uncommitted");
var timer = new Stopwatch();
timer.Start();
var messages = new StringBuilder();
var infoHandler = new SqlInfoMessageEventHandler((sender, args) =>
{
// todo handle errors as well
messages.AppendLine(args.Message);
});
try
{
cnn.InfoMessage += infoHandler;
if (query.IncludeExecutionPlan)
{
using (var command = new SqlCommand("SET STATISTICS XML ON", cnn))
{
command.ExecuteNonQuery();
}
}
var plan = new QueryPlan();
foreach (string batch in query.ExecutionSqlBatches)
{
using (var command = new SqlCommand(batch, cnn))
{
if (result != null)
{
result.Command = command;
if (result.Cancelled)
{
continue;
}
}
command.CommandTimeout = AppSettings.QueryTimeout;
try
{
PopulateResults(results, command, result, messages, query.IncludeExecutionPlan);
}
catch (Exception ex)
{
// Ugh. So, if we cancel the query in-process, we get an exception...
// But we have no good way of knowing that the exception here is actually
// *that* exception...so we'll just assume it was if the state is Cancelled
if (result == null || result.State != AsyncQueryRunner.AsyncState.Cancelled)
{
throw ex;
}
}
}
if (query.IncludeExecutionPlan)
{
plan.AppendBatchPlan(results.ExecutionPlan);
results.ExecutionPlan = null;
}
}
results.ExecutionPlan = plan.PlanXml;
}
finally
{
cnn.InfoMessage -= infoHandler;
results.Messages = messages.ToString();
}
timer.Stop();
results.ExecutionTime = timer.ElapsedMilliseconds;
ProcessMagicColumns(results, cnn);
}
return results;
}
public bool Excute_Sql_Node(string strQuery, CommandType cmdtype, string[] para, object[] values, SqlInfoMessageEventHandler result)
{
SqlCommand sqlCommand = new SqlCommand(strQuery, Connection);
sqlCommand.CommandType = cmdtype;
SqlParameter sqlpara;
try
{
for (int i = 0; i < para.Length; i++)
{
sqlpara = new SqlParameter();
sqlpara.ParameterName = para[i];
sqlpara.SqlValue = values[i];
sqlCommand.Parameters.Add(sqlpara);
}
Connection.Open();
Connection.InfoMessage += result;
sqlCommand.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi xảy ra");
return(false);
}
finally
{
Connection.InfoMessage -= result;
Connection.Close();
sqlCommand.Dispose();
}
}
public static SqlExecutionResult[] ExecuteTest(ConnectionContext ctx, string testSql, params DbParameter[] tsqlParameters)
{
if (ctx == null)
{
throw new AssertFailedException("The ConnectionContext cannot be null");
}
if (ctx.Connection == null)
{
throw new AssertFailedException("The connection cannot be null");
}
if (ctx.Provider == null)
{
throw new AssertFailedException("The provider factory cannot be null");
}
if (testSql == null)
{
throw new AssertFailedException("The T-SQL string cannot be null");
}
AssertConnectionStateValid(ConnectionState.Open, ctx.Connection.State);
DbCommand command = ctx.Connection.CreateCommand();
SqlExecutionResult result = new SqlExecutionResult();
int num = 0;
int num2 = 0;
int rowsAffectedCount = 0;
List <int> rowsAffected = new List <int>();
SqlCommand command2 = command as SqlCommand;
SqlConnection connection = ctx.Connection as SqlConnection;
SqlInfoMessageEventHandler handler = null;
StatementCompletedEventHandler handler2 = null;
if (connection != null)
{
handler = delegate(object sender, SqlInfoMessageEventArgs e) {
ProcessErrors(e.Errors);
};
connection.InfoMessage += handler;
}
if (command2 != null)
{
handler2 = delegate(object sender, StatementCompletedEventArgs e) {
rowsAffectedCount += e.RecordCount;
rowsAffected.Add(e.RecordCount);
};
command2.StatementCompleted += handler2;
}
try
{
DataSet dataSet = new DataSet
{
Locale = CultureInfo.CurrentCulture
};
command.CommandText = testSql;
command.CommandType = CommandType.Text;
command.Transaction = ctx.Transaction;
command.CommandTimeout = ctx.CommandTimeout;
if (tsqlParameters != null)
{
int length = tsqlParameters.Length;
for (int i = 0; i < length; i++)
{
DbParameter parameter = tsqlParameters[i];
command.Parameters.Add(parameter);
}
}
DbDataAdapter adapter1 = ctx.Provider.CreateDataAdapter();
adapter1.SelectCommand = command;
DateTime now = DateTime.Now;
adapter1.Fill(dataSet);
DateTime time2 = DateTime.Now;
result.DataSet = dataSet;
result.ExecutionTime = time2.Subtract(now);
result.RowsAffected = rowsAffected.ToArray();
num++;
num2 += dataSet.Tables.Count;
}
catch (SqlException exception1)
{
ProcessErrors(exception1.Errors);
throw;
}
finally
{
if (connection != null)
{
connection.InfoMessage -= handler;
}
if (command2 != null)
{
command2.StatementCompleted -= handler2;
}
}
PrintMessage("{0} batches, {1} ResultSets, {2} rows affected", num, num2, rowsAffectedCount);
return(new[] { result });
}
/// <summary>
/// 执行一个 SQL 语句，返回第一行第一列的强类型内容
/// </summary>
public static T ExecuteScalar <T>(string s, SqlInfoMessageEventHandler imHandler = null)
{
return((T)ExecuteScalar(s, imHandler));
}
public void Exec()
{
EmitSuffix();
Console.Error.WriteLine("Executing SQL Buffer: {0} Bytes, {1} Writes Queued", buf.Length, writeQueue.Count);
if(options.SQLDump != null) {
options.SQLDump.WriteLine(buf.ToString());
options.SQLDump.WriteLine();
options.SQLDump.WriteLine("GO");
options.SQLDump.WriteLine(new string('-', 80));
}
SqlInfoMessageEventHandler infoMessage = new SqlInfoMessageEventHandler(conn_InfoMessage);
writeCount = writeQueue.Count;
if(options.ProgressHandler != null)
options.ProgressHandler(this, 0, writeQueue.Count);
lastWriteReport = 0;
conn.InfoMessage += infoMessage;
try {
using(SqlCommand cmd = new SqlCommand(buf.ToString(), conn)) {
cmd.CommandTimeout = 12 * 60 * 60;
using(SqlDataReader reader = cmd.ExecuteReader())
if(reader.Read())
for(int i = 0; i < reader.FieldCount; i++) {
Var var = null;
if(declaredVars.TryGetValue(reader.GetName(i), out var))
var.Value = reader.GetValue(i);
}
}
}
catch(SqlException ex) {
ThrowSQLError(ex.Number, ex.LineNumber, ex.Message);
}
finally {
conn.InfoMessage -= infoMessage;
if(options.ProgressHandler != null)
options.ProgressHandler(this, 0, 0);
}
if(errors.Count > 0)
ThrowSQLError(errors[0].Number, errors[0].LineNumber, errors[0].Message);
if(writeQueue.Count != 0)
throw new ApplicationException("WriteStack mismatch, items left: " + writeQueue.Count);
writeCount = 0;
foreach(Var var in declaredVars.Values)
var.IsDeclared = false;
buf.Length = 0;
}
internal SqlConnectionManager(IProvider provider, DbConnection con, int maxUsers, bool disposeConnection)
: base (provider, con, maxUsers, disposeConnection)
{
this.infoMessagehandler = new SqlInfoMessageEventHandler(this.OnInfoMessage);
}
/// <summary>
/// Parse the sql.
/// </summary>
/// <param name="args">Expects:
/// [0] The connection string
/// [1] The sql to execute
/// [2] A batch id.
/// </param>
/// <remarks>
/// Emits event: sql-parse-complete
/// Event params:
/// [0] <see cref="Exception"/> if any.
/// [1] The sql execute batch id
/// [2] A list of parse errors if any.
/// </remarks>
private async void ParseSql(object[] args)
{
var replyMsgName = "sql-parse-complete";
if (args == null || args.Length != 3)
{
OsEvent.Emit(replyMsgName, new ArgumentException("Invalid arguments"));
return;
}
var connStr = args[0] as string;
var sql = args[1] as string;
var id = args[2] as string;
try
{
var errors = new List <SqlError>();
var handler = new SqlInfoMessageEventHandler((object sender, SqlInfoMessageEventArgs evt) =>
{
//ensure that all errors are caught
var errs = new SqlError[evt.Errors.Count];
evt.Errors.CopyTo(errs, 0);
errors.AddRange(errs);
});
if (!string.IsNullOrWhiteSpace(connStr) &&
!string.IsNullOrWhiteSpace(sql) &&
!string.IsNullOrWhiteSpace(id))
{
OsEvent.Emit("sql-parse-begin", null, id);
var batches = GetSqlBatches(sql);
var builder = new SqlConnectionStringBuilder(connStr);
if (string.IsNullOrWhiteSpace(builder.InitialCatalog))
{
builder.InitialCatalog = "master";
}
if (!string.IsNullOrWhiteSpace(builder.Password) && Crypto.TryDecrypt(builder.Password, out var pass))
{
builder.Password = pass;
}
connStr = builder.ToString();
using (var conn = new SqlConnection(connStr))
{
try
{
conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += handler;
OsEvent.Emit("sql-parse-connecting", null, id);
_app.Logger.Debug($"Connecting to {connStr}");
await conn.OpenAsync().ConfigureAwait(false);
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SET PARSEONLY ON";
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
OsEvent.Emit("sql-parse-parsing", null, id);
foreach (var batch in batches)
{
if (!string.IsNullOrWhiteSpace(batch))
{
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
cmd.CommandText = batch;
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
var result = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
cmd.CommandText = "SET PARSEONLY OFF";
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
finally
{
conn.FireInfoMessageEventOnUserErrors = false;
conn.InfoMessage -= handler;
}
}
}
OsEvent.Emit(replyMsgName, null, id, errors);
}
catch (Exception e)
{
OsEvent.Emit(replyMsgName, e, id, null);
}
}
/// <summary>
/// 执行一个 SQL 命令对象，返回第一行第一列的强类型内容
/// </summary>
public static T ExecuteScalar <T>(SqlCommand cmd, SqlInfoMessageEventHandler imHandler = null)
{
return((T)ExecuteScalar(cmd, imHandler));
}
/// <summary>
/// 执行一个 SQL 命令对象，返回一个 DbSet 数据集
/// </summary>
public static DbSet ExecuteDbSet(SqlCommand cmd, bool isGetInfoMessage = true, SqlInfoMessageEventHandler imHandler = null)
{
var se = CurrentSqlElements;
var ds = new DbSet();
var h = new SqlInfoMessageEventHandler((sender, ea) => {
foreach (System.Data.SqlClient.SqlError err in ea.Errors)
{
if (err.Class == 0)
{
ds.Messages.Add(err.Message);
continue;
}
ds.Errors.Add(new SqlLib.SqlError {
Class = err.Class,
LineNumber = err.LineNumber,
Message = err.Message,
Number = err.Number,
Procedure = err.Procedure,
Server = err.Server,
Source = err.Source,
State = err.State
});
}
});
SqlConnection conn = null;
if (cmd.Connection == null)
{
conn = se.First;
}
else
{
conn = cmd.Connection;
}
int ocs = -1;
if (conn == null)
{
conn = new SqlConnection(ConnectString);
}
else
{
ocs = (int)conn.State;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Open();
}
if (isGetInfoMessage)
{
conn.InfoMessage += h;
}
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
SqlParameter returnParameter = null;
if (cmd.CommandType == CommandType.StoredProcedure)
{
if (!cmd.Parameters.Contains("RETURN_VALUE"))
{
returnParameter = new SqlParameter("RETURN_VALUE", System.Data.SqlDbType.Int, 0, ParameterDirection.ReturnValue, false, 0, 0, null, DataRowVersion.Current, null);
cmd.Parameters.Add(returnParameter);
}
}
try {
cmd.Connection = conn;
if (cmd.Transaction == null)
{
cmd.Transaction = se.Second;
}
using (var r = cmd.ExecuteReader()) {
do
{
using (var st = r.GetSchemaTable()) {
if (st != null)
{
var t = new DbTable();
for (var i = 0; i < st.Rows.Count; i++)
{
t.NewColumn((string)st.Rows[i]["ColumnName"]
, (Type)st.Rows[i]["DataType"]
, (bool)st.Rows[i]["AllowDBNull"]);
}
ds.Tables.Add(t);
if (r.HasRows)
{
while (r.Read())
{
var buffer = new object[t.Columns.Count];
r.GetValues(buffer);
t.NewRow(buffer);
}
}
}
}
} while(r.NextResult());
ds.RecordsAffected = r.RecordsAffected;
}
if (cmd.CommandType == CommandType.StoredProcedure)
{
var o = cmd.Parameters["RETURN_VALUE"].Value;
if (o == null || o == DBNull.Value)
{
ds.ReturnValue = 0;
}
ds.ReturnValue = (int)o;
}
} finally {
if (cmd.CommandType == CommandType.StoredProcedure)
{
if (returnParameter != null)
{
cmd.Parameters.Remove(returnParameter);
}
}
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
if (isGetInfoMessage)
{
conn.InfoMessage -= h;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Close();
}
if (ocs == -1)
{
conn.Dispose();
}
}
return(ds);
}
/// <summary>
/// 执行一段 SQL 脚本( for MS SQL200X )
/// </summary>
public static void ExecuteSqlScript(string sScript, ScriptExecuteHandler handler, SqlInfoMessageEventHandler imHandler = null)
{
string[] statements = System.Text.RegularExpressions.Regex.Split(sScript, "\\sGO\\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
using (var conn = new SqlConnection(ConnectString)) {
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
conn.Open();
try {
using (var trans = conn.BeginTransaction()) {
foreach (var sql0 in statements)
{
var sql = sql0.Trim();
try {
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length > 0)
{
using (SqlCommand cmd = new SqlCommand()) {
cmd.Transaction = trans;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
var o = cmd.ExecuteScalar();
if (handler != null)
{
handler(new EventSQLScriptArgs(cmd.CommandText, o));
}
}
}
} catch (Exception x) {
trans.Rollback();
throw new Exception(String.Format("ERROR:\n{1}\n\nSTATEMENT:\n{0}", sql, x.Message));
}
}
trans.Commit();
}
} finally {
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
conn.Close();
conn.Dispose();
}
}
}
/// <summary>
/// 执行一个 SQL 脚本文件( for MS SQL200X )
/// </summary>
public static void ExecuteSqlScriptFile(string fileName, ScriptExecuteHandler handler, SqlInfoMessageEventHandler imHandler = null)
{
string sScript = null;
try {
using (var file = new System.IO.StreamReader(fileName)) {
sScript = file.ReadToEnd() + Environment.NewLine;
file.Close();
}
} catch (System.IO.FileNotFoundException) {
return;
} catch (Exception x) {
throw new Exception("Failed to read " + fileName, x);
}
ExecuteSqlScript(sScript, handler, imHandler);
}
ExecuteResultsAsAsyncEnumerable
(
TDbConnection connection
, string storeProcedureName
, JToken inputsParameters = null //string.Empty
, int commandTimeoutInSeconds = 90
)
{
var extensionInfo = new ExtensionInfo()
{
resultSetID = 0
, messageID = 0
, recordCounts = null
, messages = null
};
TDbCommand command = null;
List <TDbParameter> dbParameters;
bool statisticsEnabled;
StatementCompletedEventHandler
onStatementCompletedEventHandlerProcessAction = null;
SqlInfoMessageEventHandler
onSqlInfoMessageEventHandlerProcessAction = null;
JObject result;
try
{
(
command
, dbParameters
, statisticsEnabled
, onStatementCompletedEventHandlerProcessAction
, onSqlInfoMessageEventHandlerProcessAction
, result
) = ResultPreprocess
(
connection
, storeProcedureName
, inputsParameters
, commandTimeoutInSeconds
, extensionInfo
);
await
connection
.OpenAsync();
var dataReader = command
.ExecuteReader
(
CommandBehavior
.CloseConnection
);
var entries = dataReader
.AsMultipleResultsIAsyncEnumerable();
//(
// (resultSetIndex, rowIndex, columns, dataRecord) =>
// {
// return dataRecord;
// }
//);
await
foreach //(var entry in entries)
(
var
(
resultSetIndex
, rowIndex
, columns
, dataRecord
)
in
entries
)
{
extensionInfo
.resultSetID = resultSetIndex;
yield
return
(
resultSetIndex
, rowIndex
, columns
, dataRecord
);
}
await
dataReader
.CloseAsync();
}
finally
{
extensionInfo.Clear();
if (onStatementCompletedEventHandlerProcessAction != null)
{
if (command is SqlCommand sqlCommand)
{
sqlCommand
.StatementCompleted -=
onStatementCompletedEventHandlerProcessAction;
}
}
if (onSqlInfoMessageEventHandlerProcessAction != null)
{
if (connection is SqlConnection sqlConnection)
{
sqlConnection
.InfoMessage -=
onSqlInfoMessageEventHandlerProcessAction;
if (sqlConnection.StatisticsEnabled)
{
sqlConnection.StatisticsEnabled = false;
}
}
}
if (_needAutoRefreshExecutedTimeForSlideExpire)
{
RefreshCachedExecuted
(
connection
, storeProcedureName
);
}
if (connection.State != ConnectionState.Closed)
{
await
connection
.CloseAsync();
}
if (command != null)
{
command
.Dispose();
}
}
}
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user)
{
var remoteIP = OData.GetRemoteIP();
var key = "total-" + remoteIP;
var currentCount = (int?)Current.GetCachedObject(key) ?? 0;
currentCount++;
Current.SetCachedObjectSliding(key, currentCount, 60 * 60);
if (currentCount > 130)
{
// clearly a robot, auto black list
var b = new BlackList { CreationDate = DateTime.UtcNow, IPAddress = remoteIP };
}
if (currentCount > 100)
{
throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!");
}
if (Current.DB.ExecuteQuery<int>("select count(*) from BlackList where IPAddress = {0}", remoteIP).First() > 0)
{
System.Threading.Thread.Sleep(2000);
throw new Exception("You have been blacklisted due to abuse!");
}
var results = new QueryResults();
using (SqlConnection cnn = site.GetConnection())
{
cnn.Open();
// well we do not want to risk blocking, if somebody needs to change this we will need to add a setting
cnn.Execute("set transaction isolation level read uncommitted");
var timer = new Stopwatch();
timer.Start();
var messages = new StringBuilder();
var infoHandler = new SqlInfoMessageEventHandler((sender, args) =>
{
// todo handle errors as well
messages.AppendLine(args.Message);
});
try
{
cnn.InfoMessage += infoHandler;
if (query.IncludeExecutionPlan)
{
using (var command = new SqlCommand("SET STATISTICS XML ON", cnn))
{
command.ExecuteNonQuery();
}
}
var plan = new QueryPlan();
foreach (string batch in query.ExecutionSqlBatches)
{
using (var command = new SqlCommand(batch, cnn))
{
command.CommandTimeout = QUERY_TIMEOUT;
PopulateResults(results, command, messages, query.IncludeExecutionPlan);
}
if (query.IncludeExecutionPlan)
{
plan.AppendBatchPlan(results.ExecutionPlan);
results.ExecutionPlan = null;
}
}
results.ExecutionPlan = plan.PlanXml;
}
finally
{
cnn.InfoMessage -= infoHandler;
results.Messages = messages.ToString();
}
timer.Stop();
results.ExecutionTime = timer.ElapsedMilliseconds;
ProcessMagicColumns(results, cnn);
}
return results;
}
public void StartLoadThread()
{
try
{
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations; i++)
{
if (_runCancelled)
{
throw new Exception();
}
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
var x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception e)
{
if (_runCancelled)
{
throw;
}
else
{
outException = e;
}
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (_statsComm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
Monitor.Pulse(QueryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
}
}
}
catch
{
if (_runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
}
}
else
{
throw;
}
}
}
/// <summary>
/// Adds an info message event Listener.
/// </summary>
/// <param name="handler">An info message event Listener.</param>
public void AddInfoMessageHandler(SqlInfoMessageEventHandler handler)
{
_underlyingConnection.InfoMessage += handler;
}
/// <summary>
/// 执行一个 SQL 命令对象，返回一个 DbSet 数据集
/// </summary>
public static DbTable ExecuteDbTable(SqlCommand cmd, SqlInfoMessageEventHandler imHandler = null)
{
var se = CurrentSqlElements;
var t = new DbTable();
SqlConnection conn = null;
if (cmd.Connection == null)
{
conn = se.First;
}
else
{
conn = cmd.Connection;
}
int ocs = -1;
if (conn == null)
{
conn = new SqlConnection(ConnectString);
}
else
{
ocs = (int)conn.State;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Open();
}
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
try {
cmd.Connection = conn;
if (cmd.Transaction == null)
{
cmd.Transaction = se.Second;
}
using (var r = cmd.ExecuteReader()) {
using (var st = r.GetSchemaTable()) {
if (st != null)
{
for (var i = 0; i < st.Rows.Count; i++)
{
t.NewColumn((string)st.Rows[i]["ColumnName"]
, (Type)st.Rows[i]["DataType"]
, (bool)st.Rows[i]["AllowDBNull"]);
}
if (r.HasRows)
{
while (r.Read())
{
var buffer = new object[t.Columns.Count];
r.GetValues(buffer);
t.NewRow(buffer);
}
}
}
}
}
} finally {
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
if (ocs == -1 || ocs == (int)ConnectionState.Closed)
{
conn.Close();
}
if (ocs == -1)
{
conn.Dispose();
}
}
return(t);
}
/// <summary>
/// Removes an info message event Listener.
/// </summary>
/// <param name="handler">An info message event Listener.</param>
public void RemoveInfoMessageHandler(SqlInfoMessageEventHandler handler)
{
_underlyingConnection.InfoMessage -= handler;
}
public void startLoadThread()
{
try
{
//do the work
using (SqlConnection conn = query_comm.Connection)
{
int connectionHashCode = conn.GetHashCode();
SqlInfoMessageEventHandler handler = new SqlInfoMessageEventHandler(queryInput.GetInfoMessages);
for (int i = 0; i < iterations; i++)
{
if (runCancelled)
throw new Exception();
Exception outException = null;
try
{
//initialize the outInfo structure
queryInput.outInfo = new queryOutput();
conn.Open();
//set up the statistics gathering
if (stats_comm != null)
{
stats_comm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (query_comm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(query_comm.Parameters);
}
sw.Start();
if (forceDataRetrieval)
{
SqlDataReader reader = query_comm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
object x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
query_comm.ExecuteNonQuery();
Thread.Sleep(0);
}
sw.Stop();
}
catch (Exception e)
{
if (runCancelled)
throw;
else
outException = e;
if (sw.IsRunning)
{
sw.Stop();
}
}
finally
{
//Clean up the connection
if (stats_comm != null)
conn.InfoMessage -= handler;
conn.Close();
}
bool finished = (i == (iterations - 1)) ? true : false;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
queryOutput theout = new queryOutput(
outException,
sw.Elapsed,
finished,
(infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
outInfo.e = outException;
outInfo.time = sw.Elapsed;
outInfo.finished = finished;
lock (LoadEngine.queryOutInfo)
{
LoadEngine.queryOutInfo.Enqueue(outInfo);
Monitor.Pulse(LoadEngine.queryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
sw.Reset();
}
}
}
catch
{
if (runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
outInfo.time = new TimeSpan(0);
outInfo.finished = true;
lock (LoadEngine.queryOutInfo)
{
LoadEngine.queryOutInfo.Enqueue(outInfo);
}
}
else
throw;
}
}
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user, AsyncQueryRunner.AsyncResult result)
{
var remoteIP = OData.GetRemoteIP();
var key = "total-" + remoteIP;
var currentCount = (int?)Current.GetCachedObject(key) ?? 0;
currentCount++;
Current.SetCachedObjectSliding(key, currentCount, 60 * 60);
if (currentCount > 130)
{
// clearly a robot, auto black list
Current.DB.BlackList.Insert(new { CreationDate = DateTime.UtcNow, IPAddress = remoteIP });
}
if (currentCount > 100)
{
throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!");
}
if (Current.DB.Query <int>("select count(*) from BlackList where IPAddress = @remoteIP", new { remoteIP }).First() > 0)
{
System.Threading.Thread.Sleep(2000);
throw new Exception("You have been blacklisted due to abuse!");
}
var results = new QueryResults();
using (SqlConnection cnn = site.GetOpenConnection())
{
// well we do not want to risk blocking, if somebody needs to change this we will need to add a setting
cnn.Execute("set transaction isolation level read uncommitted");
var timer = new Stopwatch();
timer.Start();
var messages = new StringBuilder();
var infoHandler = new SqlInfoMessageEventHandler((sender, args) =>
{
// todo handle errors as well
messages.AppendLine(args.Message);
});
try
{
cnn.InfoMessage += infoHandler;
if (query.IncludeExecutionPlan)
{
using (var command = new SqlCommand("SET STATISTICS XML ON", cnn))
{
command.ExecuteNonQuery();
}
}
var plan = new QueryPlan();
foreach (string batch in query.ExecutionSqlBatches)
{
using (var command = new SqlCommand(batch, cnn))
{
if (result != null)
{
result.Command = command;
if (result.Cancelled)
{
continue;
}
}
command.CommandTimeout = AppSettings.QueryTimeout;
try
{
PopulateResults(results, command, result, messages, query.IncludeExecutionPlan);
}
catch (Exception ex)
{
// Ugh. So, if we cancel the query in-process, we get an exception...
// But we have no good way of knowing that the exception here is actually
// *that* exception...so we'll just assume it was if the state is Cancelled
if (result == null || result.State != AsyncQueryRunner.AsyncState.Cancelled)
{
throw ex;
}
}
}
if (query.IncludeExecutionPlan)
{
plan.AppendBatchPlan(results.ExecutionPlan);
results.ExecutionPlan = null;
}
}
results.ExecutionPlan = plan.PlanXml;
}
finally
{
cnn.InfoMessage -= infoHandler;
results.Messages = messages.ToString();
}
timer.Stop();
results.ExecutionTime = timer.ElapsedMilliseconds;
ProcessMagicColumns(results, cnn);
}
return(results);
}
public DBUtil(string connectionString, SqlInfoMessageEventHandler sqlInfoMessageEventHandler)
{
this.ConnectionString = connectionString;
this.SqlInfoMessageEventHandler = sqlInfoMessageEventHandler;
}
public static void WarningsBeforeRowsTest()
{
bool hitWarnings = false;
int iteration = 0;
Action <object, SqlInfoMessageEventArgs> warningCallback =
(object sender, SqlInfoMessageEventArgs imevent) =>
{
for (int i = 0; i < imevent.Errors.Count; i++)
{
Assert.True(imevent.Errors[i].Message.Contains(warningNoiseMessage), "FAILED: WarningsBeforeRowsTest Callback did not contain correct message. Failed in loop iteration: " + iteration);
}
hitWarnings = true;
};
SqlInfoMessageEventHandler handler = new SqlInfoMessageEventHandler(warningCallback);
SqlConnection sqlConnection = new SqlConnection(DataTestUtility.TCPConnectionString);
sqlConnection.InfoMessage += handler;
sqlConnection.Open();
foreach (string orderClause in new string[] { "", " order by FirstName" })
{
foreach (bool messagesOnErrors in new bool[] { true, false })
{
iteration++;
sqlConnection.FireInfoMessageEventOnUserErrors = messagesOnErrors;
// These queries should return warnings because AND here is a noise word.
SqlCommand cmd = new SqlCommand("select FirstName from Northwind.dbo.Employees where contains(FirstName, '\"Anne AND\"')" + orderClause, sqlConnection);
using (SqlDataReader reader = cmd.ExecuteReader())
{
Assert.True(reader.HasRows, "FAILED: SqlDataReader.HasRows is not correct (should be TRUE)");
bool receivedRows = false;
while (reader.Read())
{
receivedRows = true;
}
Assert.True(receivedRows, "FAILED: Should have received rows from this query.");
Assert.True(hitWarnings, "FAILED: Should have received warnings from this query");
}
hitWarnings = false;
cmd.CommandText = "select FirstName from Northwind.dbo.Employees where contains(FirstName, '\"NotARealPerson AND\"')" + orderClause;
using (SqlDataReader reader = cmd.ExecuteReader())
{
Assert.False(reader.HasRows, "FAILED: SqlDataReader.HasRows is not correct (should be FALSE)");
bool receivedRows = false;
while (reader.Read())
{
receivedRows = true;
}
Assert.False(receivedRows, "FAILED: Should have NOT received rows from this query.");
Assert.True(hitWarnings, "FAILED: Should have received warnings from this query");
}
}
}
sqlConnection.Close();
}
public virtual void LoadItems(BulkLoadContext context, IEnumerable <BulkLoadItem> items)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (items == null)
{
throw new ArgumentNullException(nameof(items));
}
items = OnItemProcessing.Execute(items, (p, itms) => p.Process(context, itms));
var db = Factory.GetDatabase(context.Database, true);
var connectionString = ConfigurationManager.ConnectionStrings[db.ConnectionStringName].ConnectionString;
var infoMessageHandler = new SqlInfoMessageEventHandler((s, e) => OnSqlInfoMessage(context, s, e));
using (var conn = new SqlConnection(connectionString))
{
var sqlContext = new BulkLoadSqlContext(conn, typeof(BulkLoader));
SqlTransaction transaction = null;
try
{
conn.InfoMessage += infoMessageHandler;
conn.Open();
BulkItemsAndFieldsReader itemAndFieldReader;
if (!StageData(context, sqlContext, items, out itemAndFieldReader))
{
return;
}
if (context.StageDataWithoutProcessing)
{
context.Log.Info("Data to import is staged in database, no processing done.");
context.StageSucceeded(Stage.Load);
return;
}
if (itemAndFieldReader.ReadItemCount > 0)
{
LookupIds(context, sqlContext, itemAndFieldReader);
if (!ValidateAndPrepareData(context, sqlContext))
{
return;
}
sqlContext.Transaction = transaction = conn.BeginTransaction();
MergeData(context, sqlContext, itemAndFieldReader);
}
OnTransactionCommitting.Execute(p => p.Process(context, sqlContext));
// After this point, there's no use in keeping the transaction arround,
// because we cannot sync everything transactionally (e.g. indexes, publshing, ...)
// Be aware that after this point the process may halt and not everything is consistent.
// We mitigate this inconsistency with crash recovery, see below.
transaction?.Commit();
transaction = null;
// Allow clearing caches before raising event so that reading the item API in event will result in fresh reads.
OnItemsLoading.Execute(p => p.Process(context, sqlContext, context.ItemChanges));
// Databases are now entirely in sync.
context.OnDataLoaded?.Invoke(context);
Event.RaiseEvent("bulkloader:dataloaded", context);
// Execute post processors.y.
var itemChanges = GetChanges(context, sqlContext);
OnItemsLoaded.Execute(p => p.Process(context, sqlContext, itemChanges));
context.StageSucceeded(Stage.Load);
}
catch (Exception ex)
{
transaction?.Rollback();
context.StageFailed(Stage.Load, ex.Message);
}
finally
{
conn.InfoMessage -= infoMessageHandler;
}
}
}