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);
            }
        }
예제 #3
0
        /// <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);
        }
예제 #4
0
        /// <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);
            }
        }
예제 #5
0
파일: SqlHelper.cs 프로젝트: denghe/dbset
        /// <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();
                }
            }
        }
예제 #6
0
        /// <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);
            }
        }
예제 #9
0
        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();
            }
        }
예제 #10
0
        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;
        }
예제 #12
0
        /// <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());
        }
예제 #13
0
 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);
         }
     }
 }
예제 #14
0
        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();
            }
        }
예제 #15
0
        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;
        }
예제 #17
0
        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 });
        }
예제 #19
0
 /// <summary>
 /// 执行一个 SQL 语句,返回第一行第一列的强类型内容
 /// </summary>
 public static T ExecuteScalar <T>(string s, SqlInfoMessageEventHandler imHandler = null)
 {
     return((T)ExecuteScalar(s, imHandler));
 }
예제 #20
0
        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;
        }
예제 #21
0
        internal SqlConnectionManager(IProvider provider, DbConnection con, int maxUsers, bool disposeConnection) 
			: base (provider, con, maxUsers, disposeConnection)
		{
            this.infoMessagehandler = new SqlInfoMessageEventHandler(this.OnInfoMessage);
        }
예제 #22
0
        /// <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);
            }
        }
예제 #23
0
 /// <summary>
 /// 执行一个 SQL 命令对象,返回第一行第一列的强类型内容
 /// </summary>
 public static T ExecuteScalar <T>(SqlCommand cmd, SqlInfoMessageEventHandler imHandler = null)
 {
     return((T)ExecuteScalar(cmd, imHandler));
 }
예제 #24
0
        /// <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);
        }
예제 #25
0
        /// <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();
                }
            }
        }
예제 #26
0
        /// <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);
        }
예제 #27
0
        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;
        }
예제 #29
0
            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;
                    }
                }
            }
예제 #30
0
 /// <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;
 }
예제 #31
0
        /// <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);
        }
예제 #32
0
 /// <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;
 }
예제 #33
0
            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;
                }
            }
예제 #34
0
        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);
        }
예제 #35
0
 public DBUtil(string connectionString, SqlInfoMessageEventHandler sqlInfoMessageEventHandler)
 {
     this.ConnectionString = connectionString;
     this.SqlInfoMessageEventHandler = sqlInfoMessageEventHandler;
 }
예제 #36
0
        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;
                }
            }
        }