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