System.Data.SqlClient.SqlConnection.CreateCommand()

public static bool Delete(int postalCode)
        {
            Int32 rowsAffected = 0;
            SqlConnection cn = GetConnection();
            ZipCode zipCode = Get(cn, postalCode);
            if (zipCode != null)
            {
                using (SqlCommand sqlCmd = cn.CreateCommand())
                {
                    sqlCmd.CommandText = "DELETE FROM ZipCode WHERE ZipCodePostalCode=" + postalCode;
                    rowsAffected = sqlCmd.ExecuteNonQuery();
                }
            }
            FinishedWithConnection(cn);
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, ZipCode zipCode)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand sqlCmd = cn.CreateCommand())
            {
                sqlCmd.CommandText = "UPDATE ZipCode SET ZipCodeZipCodeCityId=@ZipCodeZipCodeCityId WHERE ZipCodePostalCode=@ZipCodePostalCode";

                BuildSqlParameter(sqlCmd, "@ZipCodePostalCode", SqlDbType.Int, zipCode.PostalCode);
                BuildSqlParameter(sqlCmd, "@ZipCodeZipCodeCityId", SqlDbType.Int, zipCode.CityId);

                rowsAffected = sqlCmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, VendorOrderItem vendorOrderItem)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand sqlCmd = cn.CreateCommand())
            {
                sqlCmd.CommandText = "UPDATE VendorOrderItem SET VendorOrderItemVendorOrderId=@VendorOrderItemVendorOrderId,VendorOrderItemVendorItemId=@VendorOrderItemVendorItemId,VendorOrderItemQuanreplacedy=@VendorOrderItemQuanreplacedy WHERE VendorOrderItemId=@VendorOrderItemId";

                BuildSqlParameter(sqlCmd, "@VendorOrderItemId", SqlDbType.Int, vendorOrderItem.Id);
                BuildSqlParameter(sqlCmd, "@VendorOrderItemVendorOrderId", SqlDbType.Int, vendorOrderItem.VendorOrderId);
                BuildSqlParameter(sqlCmd, "@VendorOrderItemVendorItemId", SqlDbType.Int, vendorOrderItem.VendorItemId);
                BuildSqlParameter(sqlCmd, "@VendorOrderItemQuanreplacedy", SqlDbType.Int, vendorOrderItem.Quanreplacedy);

                rowsAffected = sqlCmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

public static ZipCode Add(int postalCode, int cityId)
        {
            ZipCode result = null;

            SqlConnection cn = GetConnection();
            using (SqlCommand sqlCmd = cn.CreateCommand())
            {
                sqlCmd.CommandText = "INSERT INTO ZipCode (ZipCodePostalCode, ZipCodeZipCodeCityId) VALUES (@ZipCodePostalCode, @ZipCodeZipCodeCityId)";
                BuildSqlParameter(sqlCmd, "@ZipCodePostalCode", SqlDbType.Int, postalCode);
                BuildSqlParameter(sqlCmd, "@ZipCodeZipCodeCityId", SqlDbType.Int, cityId);
                if (sqlCmd.ExecuteNonQuery() > 0)
                {
                    result = new ZipCode(postalCode, cityId);
                }
            }
            FinishedWithConnection(cn);

            return result;
        }

public static bool Delete(int id)
        {
            Int32 rowsAffected = 0;
            SqlConnection cn = GetConnection();
            ZipCodeCity zipCodeCity = Get(cn, id);
            if (zipCodeCity != null)
            {
                using (SqlCommand sqlCmd = cn.CreateCommand())
                {
                    sqlCmd.CommandText = "DELETE FROM ZipCodeCity WHERE ZipCodeCityId=" + id;
                    rowsAffected = sqlCmd.ExecuteNonQuery();
                }
            }
            FinishedWithConnection(cn);
            return (rowsAffected != 0);
        }

private void DoSqlNonQueryCommand(string sqlCommand)
        {
            if (sqlCommand == null)
            {
                return;
            }
            Int32 rowsAffected;
            SqlConnection cn = DataModelBase.GetConnection();
            using (SqlCommand sqlCmd = cn.CreateCommand())
            {
                sqlCmd.CommandText = sqlCommand;
                rowsAffected = sqlCmd.ExecuteNonQuery();
            }
            DataModelBase.FinishedWithConnection(cn);
            OutputSqlNonQuery(rowsAffected != 0);
        }

private static bool Delete(int id)
        {
            Int32 rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(DatabaseHelper.ConnectionString))
            {
                cn.Open();
                using (SqlCommand sqlCmd = cn.CreateCommand())
                {
                    sqlCmd.CommandText = "DELETE FROM CrashIncident WHERE CrashIncidentId=" + id;
                    rowsAffected = sqlCmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, CrashIncident entry)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "UPDATE CrashIncident SET CrashIncidentCustomerId=@CrashIncidentCustomerId,CrashIncidentWhen=@CrashIncidentWhen,CrashIncidentInitialCrashReportId=@CrashIncidentInitialCrashReportId WHERE CrashIncidentId=@CrashIncidentId";

                DatabaseHelper.BuildSqlParameter(cmd, "@CrashIncidentId", SqlDbType.Int, entry.Id);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashIncidentCustomerId", SqlDbType.Int, entry.CustomerId);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashIncidentWhen", SqlDbType.DateTime, entry.When);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashIncidentInitialCrashReportId", SqlDbType.Int, entry.TopLevelCrashReportId);

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

public static bool DeleteAll(int crashIncidentId)
        {
            Int32 rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(DatabaseHelper.ConnectionString))
            {
                cn.Open();
                using (SqlCommand sqlCmd = cn.CreateCommand())
                {
                    sqlCmd.CommandText = "DELETE FROM CrashReport WHERE CrashReportCrashIncidentId=" + crashIncidentId;
                    rowsAffected = sqlCmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, CrashReport entry)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "UPDATE CrashReport SET CrashReportCrashIncidentId=@CrashReportCrashIncidentId,CrashReportExceptionName=@CrashReportExceptionName,CrashReportExceptionMessage=@CrashReportExceptionMessage,CrashReportExceptionStackTrace=@CrashReportExceptionStackTrace,CrashReportInnerExceptionCrashReportId=@CrashReportInnerExceptionCrashReportId WHERE CrashReportId=@CrashReportId";

                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportId", SqlDbType.Int, entry.Id);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportCrashIncidentId", SqlDbType.Int, entry.CrashIncidentId);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportExceptionName", SqlDbType.Text, entry.ExceptionName);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportExceptionMessage", SqlDbType.Text, entry.ExceptionMessage);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportExceptionStackTrace", SqlDbType.Text, entry.StackTrace);
                DatabaseHelper.BuildSqlParameter(cmd, "@CrashReportInnerExceptionCrashReportId", SqlDbType.Int, entry.InnerExceptionCrashReportId);

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, Customer entry)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "UPDATE Customer SET CustomerBusinessName=@CustomerBusinessName,CustomerName=@CustomerName,CustomerAddress=@CustomerAddress,CustomerCity=@CustomerCity,CustomerState=@CustomerState,CustomerZip=@CustomerZip,CustomerPhone1=@CustomerPhone1,CustomerPhone2=@CustomerPhone2 WHERE CustomerId=@CustomerId";

                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerId", SqlDbType.Int, entry.Id);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerBusinessName", SqlDbType.Text, entry.BusinessName);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerName", SqlDbType.Text, entry.ContactsName);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerAddress", SqlDbType.Text, entry.Address);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerCity", SqlDbType.Text, entry.City);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerState", SqlDbType.Text, entry.State);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerZip", SqlDbType.Text, entry.ZipCode);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerPhone1", SqlDbType.Text, entry.Phone1);
                DatabaseHelper.BuildSqlParameter(cmd, "@CustomerPhone2", SqlDbType.Text, entry.Phone2);

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

public static bool SqlExecute(string sqlCommandText)
        {
            Int32 rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();
                using (SqlCommand sqlCmd = cn.CreateCommand())
                {
                    sqlCmd.CommandText = sqlCommandText;
                    sqlCmd.CommandType = CommandType.Text;
                    rowsAffected = sqlCmd.ExecuteNonQuery();
                }
                cn.Close();
            }
            return (rowsAffected != 0);
        }

private static bool Update(SqlConnection cn, License entry)
        {
            Int32 rowsAffected = 0;
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "UPDATE License SET LicenseCustomerId=@LicenseCustomerId,LicenseSerialNumber=@LicenseSerialNumber,LicenseIdenreplacedyHash=@LicenseIdenreplacedyHash,LicenseIsValid=@LicenseIsValid WHERE LicenseId=@LicenseId";

                DatabaseHelper.BuildSqlParameter(cmd, "@LicenseId", SqlDbType.Int, entry.Id);
                DatabaseHelper.BuildSqlParameter(cmd, "@LicenseCustomerId", SqlDbType.Int, entry.CustomerId);
                DatabaseHelper.BuildSqlParameter(cmd, "@LicenseSerialNumber", SqlDbType.Text, entry.SerialNumber);
                DatabaseHelper.BuildSqlParameter(cmd, "@LicenseIdenreplacedyHash", SqlDbType.Binary, entry.IdenreplacedyHash);
                DatabaseHelper.BuildSqlParameter(cmd, "@LicenseIsValid", SqlDbType.Bit, entry.IsValid);

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return (rowsAffected != 0);
        }

public static void ConnectionInformation()
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                connection.InfoMessage += (sender, e) =>
                {
                    Console.WriteLine($"warning or info: {e.Message}");
                };
                connection.StateChange += (sender, e) =>
                {
                    Console.WriteLine($"current state: {e.CurrentState}, before: {e.OriginalState}");
                };

                try
                {
                    connection.StatisticsEnabled = true;
                    connection.FireInfoMessageEventOnUserErrors = true;
                    connection.Open();

                    Console.WriteLine("connection opened");

                    // Do something useful
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT replacedl, Publisher FROM [ProCSharp].[Books]";
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader.GetString(0)} {reader.GetString(1)}");
                    }
                    IDictionary statistics = connection.RetrieveStatistics();
                    ShowStatistics(statistics);
                    connection.ResetStatistics();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

public async Task AddBookAsync(Book book, Transaction tx)
        {
            using (SqlConnection connection = new SqlConnection(GetConnectionString()))
            {
                string sql = "INSERT INTO [ProCSharp].[Books] ([replacedle], [Publisher], [Isbn], [ReleaseDate]) " +
                    "VALUES (@replacedle, @Publisher, @Isbn, @ReleaseDate)";

                await connection.OpenAsync();
                if (tx != null)
                {
                    connection.EnlistTransaction(tx);
                }
                var command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddWithValue("replacedle", book.replacedle);
                command.Parameters.AddWithValue("Publisher", book.Publisher);
                command.Parameters.AddWithValue("Isbn", book.Isbn);
                command.Parameters.AddWithValue("ReleaseDate", book.ReleaseDate);

                await command.ExecuteNonQueryAsync();
            }
        }

public void AddBook(Book book)
        {
            using (SqlConnection connection = new SqlConnection(GetConnectionString()))
            {
                string sql = "INSERT INTO [ProCSharp].[Books] ([replacedle], [Publisher], [Isbn], [ReleaseDate]) " +
                    "VALUES (@replacedle, @Publisher, @Isbn, @ReleaseDate)";

                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddWithValue("replacedle", book.replacedle);
                command.Parameters.AddWithValue("Publisher", book.Publisher);
                command.Parameters.AddWithValue("Isbn", book.Isbn);
                command.Parameters.AddWithValue("ReleaseDate", book.ReleaseDate);

                command.ExecuteNonQuery();
            }
        }

private static void StoredProcedure(string publisher)
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "[ProCSharp].[GetBooksByPublisher]";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = command.CreateParameter();
                p1.SqlDbType = SqlDbType.NVarChar;
                p1.ParameterName = "@Publisher";
                p1.Value = publisher;
                command.Parameters.Add(p1);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = (int)reader["Id"];
                        string replacedle = (string)reader["replacedle"];
                        string pub = (string)reader["Publisher"];
                        DateTime releaseDate = (DateTime)reader["ReleaseDate"];
                        Console.WriteLine($"{replacedle} - {pub}; {releaseDate:d}");
                    }
                }
            }
        }

private static void ExecuteScalar()
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                string sql = "SELECT COUNT(*) FROM [ProCSharp].[Books]";
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                object count = command.ExecuteScalar();
                Console.WriteLine($"counted {count} book records");
            }
        }

private static void EnsureDBSetup()
        {
            using (var cnn = new SqlConnection(BenchmarksBase.ConnectionString))
            {
                cnn.Open();
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = @"
                        If (Object_Id('Post') Is Null)
                        Begin
	                        Create Table Post
	                        (
		                        Id int idenreplacedy primary key, 
		                        [Text] varchar(max) not null, 
		                        CreationDate datetime not null, 
		                        LastChangeDate datetime not null,
		                        Counter1 int,
		                        Counter2 int,
		                        Counter3 int,
		                        Counter4 int,
		                        Counter5 int,
		                        Counter6 int,
		                        Counter7 int,
		                        Counter8 int,
		                        Counter9 int
	                        );
	   
	                        Set NoCount On;
	                        Declare @i int = 0;
	                        While @i <= 5001
	                        Begin
		                        Insert Post ([Text],CreationDate, LastChangeDate) values (replicate('x', 2000), GETDATE(), GETDATE());
		                        Set @i = @i + 1;
	                        End
                        End
                    ";
                    cmd.Connection = cnn;
                    cmd.ExecuteNonQuery();
                }
            }
        }

private static List<EnWord> GetListTestObject()
        {
            string wordFilePath = string.Format(@"{0}\ListWord.data", AppDomain.CurrentDomain.BaseDirectory.Trim('\\', '/'));

            if (File.Exists(wordFilePath))
            {
                //从 磁盘序列化文件获取 单词集合
                List<EnWord> listWord = (List<EnWord>)FileDeserialize(wordFilePath, true);
                return listWord;
            }
            else
            {
                #region  从 数据库获取 单词集合

                List<EnWord> listWord = new List<EnWord>();
                using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=MyWord;User Id=sa; Pwd=123.com;"))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT * FROM [cetsix];";
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                EnWord item = new EnWord();
                                item.Word = (Convert.ToString(reader["words"]) ?? string.Empty).Trim();
                                item.Mean = (Convert.ToString(reader["meaning"]) ?? string.Empty).Trim();
                                item.Demo = (Convert.ToString(reader["lx"]) ?? string.Empty).Trim();
                                listWord.Add(item);
                            }
                        }
                    }
                }

                FileSerialize(wordFilePath, listWord, true);
                return listWord;

                #endregion
            }
        }

public static DataTable MssqlQuery(string connStr, string sql, SqlParameter[] sqlParameter = null)
        {
            var dt = new DataTable();
            using (var conn = new SqlConnection(connStr))
            {
                conn.Open();

                DataBase = conn.Database;

                var cmd = conn.CreateCommand();

                cmd.CommandText = sql;

                if (sqlParameter != null)
                    cmd.Parameters.AddRange(sqlParameter);

                var dr = cmd.ExecuteReader();

                dt.Load(dr);
            }

            return dt;
        }

private List<Expensive_Query> fetch_TopCustom_Expensive_Queries(string SQLConnectionString, int Num_Query_Returned = 40)
        {

            List<Expensive_Query> fetched_Query_list = new List<Expensive_Query>();



            SqlConnection conn = new SqlConnection(SQLConnectionString);

            DataTable dt = new DataTable();

            SqlCommand comm = conn.CreateCommand();

            try
            {

                string SPROC = " SELECT TOP " + Num_Query_Returned.ToString().Trim() + " " +

                                       " qs.sql_handle," +

                                       " qs.execution_count," +

                                       " qs.total_worker_time AS Total_CPU," +

                                       " total_CPU_inSeconds = " +

                                       " qs.total_worker_time/1000000," +

                                       " average_CPU_inSeconds = " +

                                       " (qs.total_worker_time/1000000) / qs.execution_count," +

                                       " qs.total_elapsed_time," +

                                       " total_elapsed_time_inSeconds = " +

                                       " qs.total_elapsed_time/1000000," +

                                       " st.text," +

                                       " qp.query_plan" +

                                   " FROM" +

                                       " sys.dm_exec_query_stats AS qs" +

                                           " CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st" +

                                           " CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp" +

                                   " ORDER BY qs.total_worker_time DESC";

                comm.CommandType = CommandType.Text;

                comm.CommandText = SPROC;

                comm.CommandTimeout = 500;

                conn.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(comm);

                adapter.Fill(dt);



                if (dt.Rows.Count > 0)
                {

                    fetched_Query_list.Clear();



                    foreach (DataRow dr in dt.Rows)
                    {

                        Expensive_Query fetched_query = new Expensive_Query

                        {

                            Sql_Handle = Encoding.ASCII.GetBytes(dr["sql_handle"].ToString()),

                            Execution_Count = long.Parse(dr["execution_count"].ToString().Trim()),

                            Total_CPU_inMicroSeconds = long.Parse(dr["Total_CPU"].ToString().Trim()),

                            Total_CPU_inSeconds = long.Parse(dr["total_CPU_inSeconds"].ToString().Trim()),

                            Average_CPU_inSeconds = float.Parse(dr["average_CPU_inSeconds"].ToString().Trim()),

                            Total_Elapsed_Time_inMicroSeconds = long.Parse(dr["total_elapsed_time"].ToString().Trim()),

                            Total_Elapsed_Time_inSeconds = long.Parse(dr["total_elapsed_time_inSeconds"].ToString().Trim()),

                            Sql_Text = dr["text"].ToString(),

                            Query_Plan = dr["query_plan"].ToString()

                        };

                        fetched_Query_list.Add(fetched_query);

                    }

                }

            }

            catch (Exception ex) { }



            return fetched_Query_list;

        }

private List<SQL_Individual_Performance_Counter> fetch_Performance_Counters(string SQLConnectionString)
        {

            List<SQL_Individual_Performance_Counter> fetched_Counters_List = new List<SQL_Individual_Performance_Counter>();



            SqlConnection conn = new SqlConnection(SQLConnectionString);

            DataTable dt = new DataTable();

            SqlCommand comm = conn.CreateCommand();

            try
            {

                string SPROC = " SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters ";

                comm.CommandType = CommandType.Text;

                comm.CommandText = SPROC;

                comm.CommandTimeout = 500;

                conn.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(comm);

                adapter.Fill(dt);



                if (dt.Rows.Count > 0)
                {

                    fetched_Counters_List.Clear();

                    foreach (DataRow dr in dt.Rows)
                    {

                        SQL_Individual_Performance_Counter fetched_query = new SQL_Individual_Performance_Counter

                        {

                            object_name = dr["object_name"].ToString(),

                            counter_name = dr["counter_name"].ToString(),

                            instance_name = dr["instance_name"].ToString(),

                            cntr_value = long.Parse(dr["cntr_value"].ToString()),

                            cntr_type = int.Parse(dr["cntr_type"].ToString())

                        };

                        fetched_Counters_List.Add(fetched_query);

                    }

                }

            }

            catch (Exception ex) { }



            return fetched_Counters_List;

        }

private List<Expensive_Query> fetch_Top20_Expensive_Queries(string SQLConnectionString)
        {

            List<Expensive_Query> fetched_Query_list = new List<Expensive_Query>();



            SqlConnection conn = new SqlConnection(SQLConnectionString);

            DataTable dt = new DataTable();

            SqlCommand comm = conn.CreateCommand();

            try
            {

                string SPROC = " SELECT TOP 20 " +

                                       " qs.sql_handle," +

                                       " qs.execution_count," +

                                       " qs.total_worker_time AS Total_CPU," +

                                       " total_CPU_inSeconds = " +

                                       " qs.total_worker_time/1000000," +

                                       " average_CPU_inSeconds = " +

                                       " (qs.total_worker_time/1000000) / qs.execution_count," +

                                       " qs.total_elapsed_time," +

                                       " total_elapsed_time_inSeconds = " +

                                       " qs.total_elapsed_time/1000000," +

                                       " st.text," +

                                       " qp.query_plan" +

                                   " FROM" +

                                       " sys.dm_exec_query_stats AS qs" +

                                           " CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st" +

                                           " CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp" +

                                   " ORDER BY qs.total_worker_time DESC";

                comm.CommandType = CommandType.Text;

                comm.CommandText = SPROC;

                comm.CommandTimeout = 500;

                conn.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(comm);

                adapter.Fill(dt);



                if (dt.Rows.Count > 0)
                {

                    fetched_Query_list.Clear();



                    foreach (DataRow dr in dt.Rows)
                    {

                        Expensive_Query fetched_query = new Expensive_Query

                        {

                            Sql_Handle = Encoding.ASCII.GetBytes(dr["sql_handle"].ToString()),

                            Execution_Count = long.Parse(dr["execution_count"].ToString().Trim()),

                            Total_CPU_inMicroSeconds = long.Parse(dr["Total_CPU"].ToString().Trim()),

                            Total_CPU_inSeconds = long.Parse(dr["total_CPU_inSeconds"].ToString().Trim()),

                            Average_CPU_inSeconds = float.Parse(dr["average_CPU_inSeconds"].ToString().Trim()),

                            Total_Elapsed_Time_inMicroSeconds = long.Parse(dr["total_elapsed_time"].ToString().Trim()),

                            Total_Elapsed_Time_inSeconds = long.Parse(dr["total_elapsed_time_inSeconds"].ToString().Trim()),

                            Sql_Text = dr["text"].ToString(),

                            Query_Plan = dr["query_plan"].ToString()

                        };

                        fetched_Query_list.Add(fetched_query);

                    }

                }

            }

            catch (Exception ex) { }



            return fetched_Query_list;

        }

private List<SQLServer_Buffer_Usage> fetch_SQLServer_Buffer_Usage(string SQLConnectionString)
        {

            List<SQLServer_Buffer_Usage> fetched_list = new List<SQLServer_Buffer_Usage>();



            SqlConnection conn = new SqlConnection(SQLConnectionString);

            DataTable dt = new DataTable();

            SqlCommand comm = conn.CreateCommand();

            try
            {

                string SPROC = " DECLARE @total_buffer INT; " +



                               " SELECT @total_buffer = cntr_value " +

                               " FROM sys.dm_os_performance_counters  " +

                               " WHERE RTRIM([object_name]) LIKE '%Buffer Manager' " +

                               " AND counter_name = 'Database Pages'; " +



                               " ;WITH src AS " +

                               " ( " +

                               " SELECT  " +

                               " database_id, db_buffer_pages = COUNT_BIG(*) " +

                               " FROM sys.dm_os_buffer_descriptors " +

                               " GROUP BY database_id " +

                               " ) " +

                               " SELECT " +

                               " [db_name] = CASE [database_id] WHEN 32767  " +

                               " THEN 'Resource DB'  " +

                               " ELSE DB_NAME([database_id]) END, " +

                               " db_buffer_pages, " +

                               " db_buffer_MB = db_buffer_pages / 128, " +

                               " db_buffer_percent = CONVERT(DECIMAL(6,3),  " +

                               " db_buffer_pages * 100.0 / @total_buffer) " +

                               " FROM src " +

                               " ORDER BY db_buffer_MB DESC;  ";

                comm.CommandType = CommandType.Text;

                comm.CommandText = SPROC;

                comm.CommandTimeout = 500;

                conn.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(comm);

                adapter.Fill(dt);



                if (dt.Rows.Count > 0)
                {

                    fetched_list.Clear();



                    foreach (DataRow dr in dt.Rows)
                    {

                        SQLServer_Buffer_Usage fetched_item = new SQLServer_Buffer_Usage

                        {

                            db_name = dr.Field<string>("db_name"),

                            db_buffer_pages = long.Parse(dr["db_buffer_pages"].ToString().Trim()),

                            db_buffer_MB = long.Parse(dr["db_buffer_MB"].ToString().Trim()),

                            db_buffer_percent = float.Parse(dr["db_buffer_percent"].ToString().Trim())

                        };

                        fetched_list.Add(fetched_item);

                    }

                }

            }

            catch (Exception ex) { }



            return fetched_list;

        }

private List<SQLServer_Memory_Clerks> fetch_All_SQLServer_Memory_Clerks(string SQLConnectionString)
        {

            List<SQLServer_Memory_Clerks> fetched_list = new List<SQLServer_Memory_Clerks>();



            SqlConnection conn = new SqlConnection(SQLConnectionString);

            DataTable dt = new DataTable();

            SqlCommand comm = conn.CreateCommand();

            try
            {

                string SPROC = " SELECT * FROM sys.dm_os_memory_clerks ORDER BY (pages_kb + virtual_memory_committed_kb + virtual_memory_reserved_kb +  awe_allocated_kb) desc ";

                comm.CommandType = CommandType.Text;

                comm.CommandText = SPROC;

                comm.CommandTimeout = 500;

                conn.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(comm);

                adapter.Fill(dt);



                if (dt.Rows.Count > 0)
                {

                    fetched_list.Clear();



                    foreach (DataRow dr in dt.Rows)
                    {

                        SQLServer_Memory_Clerks fetched_item = new SQLServer_Memory_Clerks

                        {

                            memory_clerk_address = dr.Field<byte[]>("memory_clerk_address"),

                            type = dr.Field<string>("type"),

                            name = dr.Field<string>("name"),

                            memory_node_id = int.Parse(dr["memory_node_id"].ToString().Trim()),

                            pages_kb = dr.Field<long>("pages_kb"),

                            virtual_memory_reserved_kb = dr.Field<long>("virtual_memory_reserved_kb"),

                            virtual_memory_committed_kb = dr.Field<long>("virtual_memory_committed_kb"),

                            awe_allocated_kb = dr.Field<long>("awe_allocated_kb"),

                            shared_memory_reserved_kb = dr.Field<long>("shared_memory_reserved_kb"),

                            shared_memory_committed_kb = dr.Field<long>("shared_memory_committed_kb"),

                            page_size_in_bytes = dr.Field<long>("page_size_in_bytes"),

                            page_allocator_address = dr.Field<byte[]>("page_allocator_address"),

                            host_address = dr.Field<byte[]>("host_address")

                        };

                        fetched_list.Add(fetched_item);

                    }

                }

            }

            catch (Exception ex) { }



            return fetched_list;

        }

private int CreateInterval(SqlConnection conn, SqlTransaction tran, DateTime intervalTime)
        {
            string sql = @"INSERT INTO [{0}].[Intervals] (interval_id, end_time, duration_minutes) VALUES (@interval_id, @end_time, @duration_minutes); ";
            sql = String.Format(sql, ConnectionInfo.SchemaName);

            // interval id is the number of seconds since 01/01/2000
            int interval_id = (int)intervalTime.Subtract(DateTime.MinValue.AddYears(1999)).TotalSeconds;

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@interval_id", interval_id);
                cmd.Parameters.AddWithValue("@end_time", intervalTime);
                cmd.Parameters.AddWithValue("@duration_minutes", Interval);
                cmd.ExecuteNonQuery();
            }

            // If this the first interval of the replacedysis, write
            // a marker interval with duration = 0 
            if (!FirstIntervalWritten)
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.Transaction = tran;
                    cmd.CommandText = sql;
                    cmd.Parameters.AddWithValue("@interval_id", interval_id - 1);
                    cmd.Parameters.AddWithValue("@end_time", intervalTime.AddSeconds(-1));
                    cmd.Parameters.AddWithValue("@duration_minutes", 0);
                    cmd.ExecuteNonQuery();
                    FirstIntervalWritten = true;
                }
            }

            return interval_id;
        }

protected void CreateTargetTables()
        {
			CreateTargetDatabase();

			string sql = File.ReadAllText(WorkloadController.BaseLocation + "\\Consumer\\replacedysis\\DatabaseSchema.sql");

            sql = sql.Replace("{DatabaseName}", ConnectionInfo.DatabaseName);
            sql = sql.Replace("{SchemaName}", ConnectionInfo.SchemaName);

            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConnectionInfo.ConnectionString;
                conn.Open();
                conn.ChangeDatabase(ConnectionInfo.DatabaseName);

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }

                sql = "IF OBJECT_ID('dbo.createreplacedysisView') IS NOT NULL EXEC('DROP PROCEDURE dbo.createreplacedysisView')";
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }

                sql = File.ReadAllText(WorkloadController.BaseLocation + "\\Consumer\\replacedysis\\createreplacedysisView.sql");
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }

                // Invoke the stored procedure to create the workload comparison view
                sql = @"
                    DECLARE @name1 sysname, @name2 sysname;

                    SELECT @name1 = [1], @name2 = [2]
                    FROM (
                        SELECT TOP(2) OBJECT_SCHEMA_NAME(object_id) AS schema_name, ROW_NUMBER() OVER (ORDER BY create_date DESC) AS RN
                        FROM sys.tables
                        WHERE name = 'WorkloadDetails'
                        ORDER BY create_date DESC
                    ) AS src
                    PIVOT( MIN(schema_name) FOR RN IN ([1], [2])) AS p;

                    SELECT @name1 ,@name2

                    IF OBJECT_ID(@name1 + '.WorkloadDetails') IS NOT NULL OR OBJECT_ID(@name2 + '.WorkloadDetails') IS NOT NULL
                    BEGIN
                        EXEC createreplacedysisView @name1, @name2;
                    END
                ";
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
        }

private void ReadEventsFromFile()
        {
            try
            {
                SqlConnectionInfo info = new SqlConnectionInfo();
                info.ServerName = "(localdb)\\MSSQLLocalDB";

                string sqlCreateTable = @"
                    IF OBJECT_ID('tempdb.dbo.trace_reader_queue') IS NULL
                    BEGIN
                        CREATE TABLE tempdb.dbo.trace_reader_queue (
                            ts datetime DEFAULT GETDATE(),
                            path nvarchar(4000)
                        )
                    END
                    TRUNCATE TABLE tempdb.dbo.trace_reader_queue;
                    INSERT INTO tempdb.dbo.trace_reader_queue (path) VALUES(@path);
                ";
                
                using(SqlConnection conn = new SqlConnection())
                {
                    conn.ConnectionString = info.ConnectionString;
                    conn.Open();
                    using(SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sqlCreateTable;
                        SqlParameter prm = new SqlParameter()
                        {
                            ParameterName = "@path",
                            DbType = System.Data.DbType.String,
                            Size = 4000,
                            Value = tracePath
                        };
                        cmd.Parameters.Add(prm);
                        cmd.ExecuteNonQuery();
                    }
                }

                reader = new FileTraceEventDataReader(info.ConnectionString, Filter, Events);
                reader.ReadEvents();
                finished = true;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);

                if (ex.InnerException != null)
                    logger.Error(ex.InnerException.Message);

                Dispose();
            }

        }

private void ReadXEData(SqlConnection conn, ReadIteration currenreplacederation)
        {

            string sqlXE = @"
                SELECT event_data, file_name, file_offset
                FROM sys.fn_xe_file_target_read_file(
                    @filename, 
                    NULL, 
                    @initial_file_name, 
                    @initial_offset
                )
            ";

            logger.Debug("Reading XE data...");

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sqlXE;

                var paramPath = cmd.Parameters.Add("@filename", System.Data.SqlDbType.NVarChar, 260);
                if (ServerType != ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase)
                {
                    paramPath.Value = currenreplacederation.GetXEFilePattern();
                }
                else
                {
                    // Azure SqlDatabase does not support wildcards in file names
                    // Specify an exact file name
                    paramPath.Value = currenreplacederation.StartFileName;
                } 


                var paramInitialFile = cmd.Parameters.Add("@initial_file_name", System.Data.SqlDbType.NVarChar, 260);
                paramInitialFile.Value = currenreplacederation.StartFileName;

                var paramInitialOffset = cmd.Parameters.Add("@initial_offset", System.Data.SqlDbType.BigInt);
                paramInitialOffset.Value = currenreplacederation.GetInitialOffset();

                // don't preplaced initial file name and offset
                // read directly from the initial file
                // until we have some rows read already
                if (
                       EventCount == 0 
                    || currenreplacederation.StartOffset <=0 
                    || currenreplacederation.StartOffset == currenreplacederation.MinOffset
                )
                {
                    if (ServerType != ExtendedEventsWorkloadListener.ServerType.LocalDB)
                    {
                        paramPath.Value = currenreplacederation.StartFileName;
                    }
                    paramInitialFile.Value = DBNull.Value;
                    paramInitialOffset.Value = DBNull.Value;
                }

                retryWithNULLS:

                logger.Debug($"paramPath         : {paramPath.Value}");
                logger.Debug($"paramInitialFile  : {paramInitialFile.Value}");
                logger.Debug($"paramInitialOffset: {paramInitialOffset.Value}");

                // in case we don't have any data in the xe file
                // GetInitialOffset returns -1 and we need to wait a bit 
                // to let events flow to the file target
                if (currenreplacederation.GetInitialOffset() > 0)
                {

                    SqlTransformer transformer = new SqlTransformer();

                    using (var reader = cmd.ExecuteReader())
                    {
                        try
                        {
                            int skippedRows = 0;
                            while (reader.Read())
                            {
                                if (reader["file_name"] != DBNull.Value)
                                    currenreplacederation.EndFileName = (string)reader["file_name"];

                                if (reader["file_offset"] != DBNull.Value)
                                    currenreplacederation.EndOffset = (long)reader["file_offset"];

                                string xmldata = (string)reader["event_data"];
                                XmlDoreplacedent doc = new XmlDoreplacedent();
                                doc.LoadXml(xmldata);
                                var evt = parseEvent(doc);

                                // skip to the correct event in case we're reading again
                                // from the same file and we have a reference sequence
                                if ((currenreplacederation.RowsRead == 0) && (currenreplacederation.StartSequence > 0))
                                {
                                    // skip rows until we encounter the reference event_sequence
                                    if (evt.EventSequence != currenreplacederation.StartSequence)
                                    {
                                        skippedRows++;
                                        continue;
                                    }
                                    else
                                    {
                                        // skip one more row...
                                        skippedRows++;
                                        currenreplacederation.RowsRead++;
                                        continue;
                                    }
                                }


                                // this is only to print out a message, so consider
                                // getting rid of it
                                if (skippedRows > 0)
                                {
                                    logger.Debug($"Skipped rows: {skippedRows}");
                                    skippedRows = 0;
                                }

                                // now we have an event, no matter if good or bad => increment rows read
                                currenreplacederation.RowsRead++;
                                if (evt.EventSequence != null)
                                {
                                    currenreplacederation.EndSequence = (long)evt.EventSequence;
                                }

                                if (evt.Type == WorkloadEvent.EventType.Unknown)
                                    continue;

                                if (evt.Type <= WorkloadEvent.EventType.BatchCompleted)
                                {
                                    if (transformer.Skip(evt.Text))
                                        continue;

                                    evt.Text = transformer.Transform(evt.Text);
                                }

                                // it's a "good" event: add it to the queue                       
                                Events.Enqueue(evt);

                                EventCount++;

                            }
                            logger.Debug($"currenreplacederation.EndSequence : {currenreplacederation.EndSequence}");


                        }
                        catch (Exception xx)
                        {
                            if (xx.Message.Contains("Specify an offset that exists in the log file"))
                            {
                                // retry the query without specifying the offset / file pair
                                paramInitialFile.Value = DBNull.Value;
                                paramInitialOffset.Value = DBNull.Value;
                                goto retryWithNULLS;
                            }
                            else
                            {
                                throw;
                            }
                        }

                    }
                }

                // Wait before querying the events file again
                if (currenreplacederation.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD)
                    Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000);

            }

        }

private ReadIteration InitializeReadIteration(SqlConnection conn, ReadIteration previous)
        {

            string sqlPath = @"
                SELECT value AS path
                FROM ::fn_trace_getinfo(default)
                WHERE traceid = @traceId
                    AND property = 2;
            ";

            string sqlPathLocaldb = @"
                IF OBJECT_ID('tempdb.dbo.trace_reader_queue') IS NOT NULL
                BEGIN
                    SELECT TOP(1) path
                    FROM tempdb.dbo.trace_reader_queue
                    ORDER BY ts DESC
                END
                ELSE
                BEGIN
                    SELECT '' AS path
                END
            ";
            
            ReadIteration currenreplacederation = null;
            using (SqlCommand cmdPath = conn.CreateCommand())
            {
                if (conn.DataSource.StartsWith("(localdb)", StringComparison.InvariantCultureIgnoreCase))
                {
                    _checkedFormat = false;
                    cmdPath.CommandText = sqlPathLocaldb;
                }
                else
                {
                    _checkedFormat = true;
                    cmdPath.CommandText = sqlPath;

                    // Get trace id
                    if (traceId == -1)
                    {
                        string tracePath = utils.GetSqlDefaultLogPath(conn);
                        traceId = utils.GetTraceId(conn, Path.Combine(tracePath, "sqlworkload"));
                        if (traceId == -1)
                        {
                            throw new InvalidOperationException("The SqlWorkload capture trace is not running.");
                        }
                    }
                    var paramTraceId = cmdPath.Parameters.Add("@traceId", System.Data.SqlDbType.Int);
                    paramTraceId.Value = traceId;
                }   


                try
                {
                    logger.Debug("Initializing read iteration");

                    using (SqlDataReader reader = cmdPath.ExecuteReader())
                    {
                        // should return only one row
                        if (reader.Read())
                        {
                            currenreplacederation = new ReadIteration()
                            {
                                StartFileName = reader.GetString(0),
                                Files = 1
                            };
                            currenreplacederation.EndFileName = currenreplacederation.StartFileName;
                            if (previous != null)
                            {
                                //if we have a previous iteration, keep reading from that file first
                                currenreplacederation.StartFileName = previous.EndFileName;
                                // if the file has changed from the previous iteration
                                // read the default number of files ( = 0 )
                                if(currenreplacederation.StartFileName != currenreplacederation.EndFileName)
                                {
                                    currenreplacederation.Files = 0;
                                }

                                // we will use the previous event sequence as the boundary to where 
                                // we need to start reading events again
                                currenreplacederation.StartSequence = previous.EndSequence;
                                currenreplacederation.EndSequence = previous.EndSequence;

                                // trace files do not have an offset like xe files but
                                // the offset can be used to go back and read events
                                // from the previous sequence minus a safety offset
                                currenreplacederation.StartOffset = previous.EndSequence - ReadIteration.TRACE_DEFAULT_OFFSET;

                                // if reading from localdb we don't need to wait for more data
                                if (conn.DataSource.StartsWith("(localdb)", StringComparison.InvariantCultureIgnoreCase))
                                {
                                    if (
                                        (currenreplacederation.StartFileName == previous.StartFileName) && 
                                        (currenreplacederation.StartSequence == previous.StartSequence)
                                    )
                                    {
                                        return null;
                                    }
                                }

                            }

                            logger.Debug($"currenreplacederation.StartFileName: {currenreplacederation.StartFileName}");
                            logger.Debug($"currenreplacederation.MinOffset    : {currenreplacederation.MinOffset}");
                            logger.Debug($"currenreplacederation.EndFileName  : {currenreplacederation.EndFileName}");
                            logger.Debug($"currenreplacederation.StartOffset  : {currenreplacederation.StartOffset}");
                            logger.Debug($"currenreplacederation.StartSequence: {currenreplacederation.StartSequence}");
                        }
                    }
                }
                catch (Exception e)
                {
                    logger.Error(e.StackTrace);
                    throw;
                }
            }

            // check columns in the source file (if localdb)
            // before returning the read iteration
            if (!_checkedFormat)
            {
                if (!utils.CheckTraceFormat(conn, currenreplacederation.StartFileName))
                {
                    throw new InvalidDataException($"The trace file {currenreplacederation.StartFileName} lacks critical column information. See the doreplacedentation for required trace columns.");
                }
            }
            
            return currenreplacederation;
        }

private void ReadTraceData(SqlConnection conn, ReadIteration currenreplacederation)
        {
            string sqlReadTrace = @"
                SELECT EventSequence
	                ,Error
	                ,TextData
	                ,BinaryData
	                ,DatabaseID
	                ,HostName
	                ,ApplicationName
	                ,LoginName
	                ,SPID
	                ,Duration
	                ,StartTime
	                ,EndTime
	                ,Reads
	                ,Writes
	                ,CPU
	                ,EventClreplaced
	                ,DatabaseName
                FROM fn_trace_gettable(@path, @number_files)
            ";

            if (currenreplacederation.StartSequence > 0)
            {
                sqlReadTrace += "WHERE EventSequence > @event_offset";
            }

            logger.Debug("Reading Trace data...");

            TraceEventParser parser = new TraceEventParser();

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sqlReadTrace;

                var paramPath = cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar, 260);
                paramPath.Value = currenreplacederation.StartFileName;

                var paramNumberFiles = cmd.Parameters.Add("@number_files", System.Data.SqlDbType.Int);
                paramNumberFiles.Value = currenreplacederation.Files;

                var paramInitialSequence = cmd.Parameters.Add("@event_offset", System.Data.SqlDbType.BigInt);
                paramInitialSequence.Value = currenreplacederation.StartOffset;

                // don't preplaced initial file name and offset
                // read directly from the initial file
                // until we have some rows read already
                if (
                       EventCount == 0
                    || currenreplacederation.StartOffset <= 0
                    || currenreplacederation.StartOffset == currenreplacederation.MinOffset
                )
                {
                    paramPath.Value = currenreplacederation.StartFileName;
                    paramNumberFiles.Value = 0;
                    paramInitialSequence.Value = 0;
                }

                logger.Debug($"paramPath           : {paramPath.Value}");
                logger.Debug($"paramNumberFiles    : {paramNumberFiles.Value}");
                logger.Debug($"paramInitialSequence: {paramInitialSequence.Value}");

            
                SqlTransformer transformer = new SqlTransformer();

                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int skippedRows = 0;
                        while (reader.Read())
                        {
                            if (reader["EventSequence"] != DBNull.Value)
                                currenreplacederation.EndSequence = (long)reader["EventSequence"];

                            // read the event from the sqldatareader
                            var evt = parser.ParseEvent(reader);

                            // skip invalid events
                            if (evt.Type == WorkloadEvent.EventType.Unknown)
                                continue;

                            // skip to the correct event in case we're reading again
                            // from the same file and we have a reference sequence
                            if ((currenreplacederation.RowsRead == 0) && (currenreplacederation.StartSequence > 0))
                            {
                                // skip rows until we encounter the reference event_sequence
                                if (evt.EventSequence != currenreplacederation.StartSequence)
                                {
                                    skippedRows++;
                                    continue;
                                }
                                else
                                {
                                    // skip one more row...
                                    skippedRows++;
                                    currenreplacederation.RowsRead++;
                                    continue;
                                }
                            }


                            // this is only to print out a message, so consider
                            // getting rid of it
                            if (skippedRows > 0)
                            {
                                logger.Debug($"Skipped rows: {skippedRows}");
                                skippedRows = 0;
                            }

                            // now we have an event, no matter if good or bad => increment rows read
                            currenreplacederation.RowsRead++;
                            if (evt.EventSequence != null)
                            {
                                currenreplacederation.EndSequence = (long)evt.EventSequence;
                            }


                            if (evt.Type <= WorkloadEvent.EventType.BatchCompleted)
                            {
                                if (transformer.Skip(evt.Text))
                                    continue;

                                if (!Filter.Evaluate(evt))
                                    continue;

                                evt.Text = transformer.Transform(evt.Text);
                            }

                            // it's a "good" event: add it to the queue                       
                            Events.Enqueue(evt);

                            EventCount++;

                        }
                        logger.Debug($"currenreplacederation.EndSequence : {currenreplacederation.EndSequence}");
                    }

                }
                catch (Exception)
                {
                    throw;
                }

                // Wait before querying the events file again
                if (currenreplacederation.RowsRead < ReadIteration.DEFAULT_TRACE_ROWS_SLEEP_THRESHOLD 
                    && currenreplacederation.StartFileName == currenreplacederation.EndFileName)
                    Thread.Sleep(ReadIteration.DEFAULT_TRACE_INTERVAL_SECONDS * 1000);

            }

        }

private void StopTrace(SqlConnection conn, int id)
        {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = String.Format(@"
                    IF EXISTS (
                        SELECT *
                        FROM sys.traces
                        WHERE id = {0}
                    )
                    BEGIN
                        EXEC sp_trace_setstatus {0}, 0;
                        EXEC sp_trace_setstatus {0}, 2;
                    END
                ", id);
                cmd.ExecuteNonQuery();
        }

protected void CreateTargetDatabase()
		{
			string databaseName = ConnectionInfo.DatabaseName;
			ConnectionInfo.DatabaseName = "master";

			try
			{
				using (SqlConnection conn = new SqlConnection())
				{
					conn.ConnectionString = ConnectionInfo.ConnectionString;
					conn.Open();
					conn.ChangeDatabase(ConnectionInfo.DatabaseName);
					using (SqlCommand cmd = conn.CreateCommand())
					{
						string createDb = @"
						IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @name)
						BEGIN
						    DECLARE @sql nvarchar(max); 
							SET @sql = N'CREATE DATABASE ' + QUOTENAME(@name);
							EXEC sp_executesql @sql;
						END
					";
						cmd.CommandText = createDb;
						cmd.Parameters.AddWithValue("@name", databaseName);
						cmd.ExecuteNonQuery();
					}
				}
			}
			finally
			{
				// restore original database name
				ConnectionInfo.DatabaseName = databaseName;
			}

		}

public string GetSqlDefaultLogPath(SqlConnection conn)
        {
            string sql = @"
            DECLARE @defaultLog nvarchar(4000);

            EXEC master.dbo.xp_instance_regread
	            N'HKEY_LOCAL_MACHINE',
	            N'Software\Microsoft\MSSQLServer\MSSQLServer',
	            N'DefaultLog',
	            @defaultLog OUTPUT;

            IF @defaultLog IS NULL
            BEGIN
	            SELECT @defaultLog = REPLACE(physical_name,'mastlog.ldf','') 
	            FROM sys.master_files
                WHERE file_id = 2
					AND database_id = 1;
            END

            SELECT @defaultLog AS DefaultLog;
        ";
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                return (string)cmd.ExecuteScalar();
            }
        }

private ReadIteration InitializeReadIteration(SqlConnection conn, ReadIteration previous)
        {

            string sqlPath = @"
                SELECT file_name, ISNULL(file_offset,-1) AS file_offset
                FROM (
                    SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]','nvarchar(1000)') AS file_name
                    FROM sys.dm_xe_{0}session_targets AS t
                    INNER JOIN sys.dm_xe_{0}sessions AS s
                        ON t.event_session_address = s.address
                    WHERE s.name = @sessionName
                        AND target_name = 'event_file'
                ) AS fileName
                OUTER APPLY (
                    SELECT TOP(1) file_offset
                    FROM fn_xe_file_target_read_file(file_name,NULL,NULL,NULL)
                ) AS fileOffset;
            ";

            string sqlPathLocaldb = @"
                IF OBJECT_ID('tempdb.dbo.trace_reader_queue') IS NOT NULL
                BEGIN
                    SELECT TOP(1) path, CAST(1 AS bigint) AS file_offset
                    FROM tempdb.dbo.trace_reader_queue
                    ORDER BY ts DESC
                END
                ELSE
                BEGIN
                    SELECT '' AS path, CAST(-1 AS bigint) AS file_offset
                END
            ";

            string databaseSuffix = ServerType == ExtendedEventsWorkloadListener.ServerType.AzureSqlDatabase ? "database_" : "";
            ReadIteration currenreplacederation = null;
            using (SqlCommand cmdPath = conn.CreateCommand())
            {
                if (ServerType == ExtendedEventsWorkloadListener.ServerType.LocalDB)
                {
                    cmdPath.CommandText = sqlPathLocaldb;
                }
                else
                {
                    cmdPath.CommandText = String.Format(sqlPath, databaseSuffix);
                    var paramSessionName = cmdPath.Parameters.Add("@sessionName", System.Data.SqlDbType.NVarChar, 260);
                    paramSessionName.Value = SessionName;
                }

                try
                {
                    logger.Debug("Initializing read iteration");

                    using (SqlDataReader reader = cmdPath.ExecuteReader())
                    {
                        // should return only one row
                        if (reader.Read())
                        {
                            currenreplacederation = new ReadIteration()
                            {
                                StartFileName = reader.GetString(0),
                                MinOffset = reader.GetInt64(1)
                            };
                            currenreplacederation.EndFileName = currenreplacederation.StartFileName;
                            if (previous != null)
                            {
                                //if we have a previous iteration, keep reading from that file first
                                currenreplacederation.StartFileName = previous.EndFileName;

                                // we need to read the file from the previous distinct offset
                                // to avoid skipping events. The function fn_xe_file_target_read_file
                                // will skip all events up to the @initial_offset INCLUDED,
                                // so we need to start from the previous offset and skip some rows
                                currenreplacederation.StartOffset = ReadIteration.GetSecondLastOffset(currenreplacederation.StartFileName);

                                // we will use the previous event sequence as the boundary to where 
                                // we need to start reading events again
                                currenreplacederation.StartSequence = previous.EndSequence;
                                currenreplacederation.EndSequence = previous.EndSequence;

                                // if reading from localdb we don't need to wait for more data
                                if (ServerType == ExtendedEventsWorkloadListener.ServerType.LocalDB)
                                {
                                    if (
                                        (currenreplacederation.StartFileName == previous.StartFileName) &&
                                        (currenreplacederation.StartSequence == previous.StartSequence)
                                    )
                                    {
                                        return null;
                                    }
                                }
                            }

                            logger.Debug($"currenreplacederation.StartFileName: {currenreplacederation.StartFileName}");
                            logger.Debug($"currenreplacederation.MinOffset    : {currenreplacederation.MinOffset}");
                            logger.Debug($"currenreplacederation.EndFileName  : {currenreplacederation.EndFileName}");
                            logger.Debug($"currenreplacederation.StartOffset  : {currenreplacederation.StartOffset}");
                            logger.Debug($"currenreplacederation.StartSequence: {currenreplacederation.StartSequence}");
                        }
                    }
                }
                catch (Exception e)
                {
                    logger.Error(e.StackTrace);
                    throw;
                }
            }
            return currenreplacederation;
        }

public int GetTraceId(SqlConnection conn, string path)
        {
            string sql = @"
                SELECT TOP(1) id
                FROM (
	                SELECT id FROM sys.traces WHERE path LIKE '{0}%'
	                UNION ALL
	                SELECT -1
                ) AS i
                ORDER BY id DESC
            ";

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = String.Format(sql, path);
            return (int)cmd.ExecuteScalar();
        }

public void Load()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConnectionInfo.ConnectionString;
                conn.Open();

                Dictionary<long, NormalizedQuery> NormalizedQueries = new Dictionary<long, NormalizedQuery>();

                int numIntervals = 0;
                int preaggregation = 1;
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM " + ConnectionInfo.SchemaName + ".Intervals WHERE duration_minutes > 0;";
                    cmd.CommandTimeout = 0;
                    numIntervals = (int)cmd.ExecuteScalar();
                }
                if (numIntervals > 500) // around 8 hours
                    preaggregation = 15;
                if (numIntervals > 1000) // around 16 hours
                    preaggregation = 30;
                if (numIntervals > 2000) // around 32 hours
                    preaggregation = 60;

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT TOP(1) end_time FROM " + ConnectionInfo.SchemaName + ".Intervals ORDER BY interval_id ASC ";
                    cmd.CommandTimeout = 0;
                    StartDate = (DateTime)cmd.ExecuteScalar();
                }

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM " + ConnectionInfo.SchemaName + ".NormalizedQueries";
                    cmd.CommandTimeout = 0;
                    using (var rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            NormalizedQueries.Add(rdr.GetInt64(rdr.GetOrdinal("sql_hash")), new NormalizedQuery()
                            {
                                Hash = rdr.GetInt64(rdr.GetOrdinal("sql_hash")),
                                NormalizedText = rdr.GetString(rdr.GetOrdinal("normalized_text")),
                                ExampleText = rdr.GetString(rdr.GetOrdinal("example_text"))
                            });
                        }
                    }
                }

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandTimeout = 0;
                    string sqlText = WorkloadViewer.Properties.Resources.Workloadreplacedysis;
                    cmd.CommandText = sqlText.Replace("capture", ConnectionInfo.SchemaName);
                    cmd.CommandText = cmd.CommandText.Replace("preaggregation", preaggregation.ToString());
                    cmd.CommandTimeout = 0;
                    using (var rdr = cmd.ExecuteReader())
                    {
                        Points = new ObservableCollection<WorkloadreplacedysisPoint>();
                        while (rdr.Read())
                        {
                            try
                            {
                                WorkloadreplacedysisPoint point = new WorkloadreplacedysisPoint()
                                {
                                    OffsetMinutes = rdr.GetInt32(rdr.GetOrdinal("offset_minutes")),
                                    DurationMinutes = rdr.GetInt32(rdr.GetOrdinal("duration_minutes")),
                                    NormalizedQuery = NormalizedQueries[rdr.GetInt64(rdr.GetOrdinal("sql_hash"))],
                                    ApplicationName = rdr.GetString(rdr.GetOrdinal("application_name")),
                                    DatabaseName = rdr.GetString(rdr.GetOrdinal("database_name")),
                                    LoginName = rdr.GetString(rdr.GetOrdinal("login_name")),
                                    HostName = rdr.GetString(rdr.GetOrdinal("host_name")),
                                    AvgCpuUs = rdr.GetInt64(rdr.GetOrdinal("avg_cpu_us")),
                                    MinCpuUs = rdr.GetInt64(rdr.GetOrdinal("min_cpu_us")),
                                    MaxCpuUs = rdr.GetInt64(rdr.GetOrdinal("max_cpu_us")),
                                    SumCpuUs = rdr.GetInt64(rdr.GetOrdinal("sum_cpu_us")),
                                    AvgReads = rdr.GetInt64(rdr.GetOrdinal("avg_reads")),
                                    MinReads = rdr.GetInt64(rdr.GetOrdinal("min_reads")),
                                    MaxReads = rdr.GetInt64(rdr.GetOrdinal("max_reads")),
                                    SumReads = rdr.GetInt64(rdr.GetOrdinal("sum_reads")),
                                    AvgWrites = rdr.GetInt64(rdr.GetOrdinal("avg_writes")),
                                    MinWrites = rdr.GetInt64(rdr.GetOrdinal("min_writes")),
                                    MaxWrites = rdr.GetInt64(rdr.GetOrdinal("max_writes")),
                                    SumWrites = rdr.GetInt64(rdr.GetOrdinal("sum_writes")),
                                    AvgDurationUs = rdr.GetInt64(rdr.GetOrdinal("avg_duration_us")),
                                    MinDurationUs = rdr.GetInt64(rdr.GetOrdinal("min_duration_us")),
                                    MaxDurationUs = rdr.GetInt64(rdr.GetOrdinal("max_duration_us")),
                                    SumDurationUs = rdr.GetInt64(rdr.GetOrdinal("sum_duration_us")),
                                    ExecutionCount = rdr.GetInt64(rdr.GetOrdinal("execution_count"))
                                };
                                Points.Add(point);
                            }
                            catch(Exception e)
                            {
                                logger.Warn($"Skipping invalid datapoint at {rdr.GetInt32(rdr.GetOrdinal("offset_minutes"))} because of Exception: {e.StackTrace}");
                            }
                        }
                    }
                }
            }
        }

public override void Initialize()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                if (ConnectionInfo == null)
                {
                    throw new ArgumentNullException("You need to provide ConnectionInfo to inizialize an ExtendedEventsWorkloadListener");
                }
                conn.ConnectionString = ConnectionInfo.ConnectionString;
                conn.Open();

                LoadServerType(conn);

                if (serverType == ServerType.AzureSqlDatabase)
                {
                    if (FileTargetPath == null)
                    {
                        throw new ArgumentException("Azure SqlDatabase does not support Extended Events streaming. Please specify a path for the FileTarget");
                    }
                    if (ConnectionInfo.DatabaseName == null)
                    {
                        throw new ArgumentException("Azure SqlDatabase does not support starting Extended Events sessions on the master database. Please specify a database name.");
                    }

                    ((ExtendedEventsEventFilter)Filter).IsSqlAzure = true;
                }
                else
                {
                    ConnectionInfo.DatabaseName = "master";
                }

                logger.Info($"Reading Extended Events session definition from {Source}");

                string sessionSql = null;
                try
                {
                    sessionSql = System.IO.File.ReadAllText(Source);

                    // Push Down EventFilters
                    string filters = String.Empty;

                    string appFilter = Filter.ApplicationFilter.PushDown();
                    string dbFilter = Filter.DatabaseFilter.PushDown();
                    string hostFilter = Filter.HostFilter.PushDown();
                    string loginFilter = Filter.LoginFilter.PushDown();

                    if (appFilter != String.Empty)
                    {
                        filters += ((filters == String.Empty) ? String.Empty : " AND ") + appFilter;
                    }
                    if (dbFilter != String.Empty)
                    {
                        filters += ((filters == String.Empty) ? String.Empty : " AND ") + dbFilter;
                    }
                    if (hostFilter != String.Empty)
                    {
                        filters += ((filters == String.Empty) ? String.Empty : " AND ") + hostFilter;
                    }
                    if (loginFilter != String.Empty)
                    {
                        filters += ((filters == String.Empty) ? String.Empty : " AND ") + loginFilter;
                    }

                    if (filters != String.Empty)
                    {
                        filters = "WHERE " + filters;
                    }

                    string sessionType = serverType == ServerType.AzureSqlDatabase ? "DATABASE" : "SERVER";
                    string principalName = serverType == ServerType.AzureSqlDatabase ? "username" : "server_principal_name";

                    sessionSql = String.Format(sessionSql, filters, sessionType, principalName);

                }
                catch (Exception e)
                {
                    throw new ArgumentException("Cannot open the source script to start the extended events session", e);
                }

                StopSession(conn);

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sessionSql;
                    cmd.ExecuteNonQuery();
                }
                if (FileTargetPath != null)
                {
                    string sql = @"
                        ALTER EVENT SESSION [{2}] ON {0}
                        ADD TARGET package0.event_file(SET filename=N'{1}',max_file_size=(100))
                    ";

                    sql = String.Format(sql, serverType == ServerType.FullInstance ? "SERVER" : "DATABASE", FileTargetPath, SessionName);

                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }


                Task.Factory.StartNew(() => ReadEvents());

                //Initialize the source of performance counters events
                Task.Factory.StartNew(() => ReadPerfCountersEvents());

                // Initialize the source of wait stats events
                Task.Factory.StartNew(() => ReadWaitStatsEvents());
            }
        }

private void StopSession(SqlConnection conn)
        {
            string sql = @"
                DECLARE @condition bit = 0;

                IF SERVERPROPERTY('Edition') = 'SQL Azure'
                    AND SERVERPROPERTY('EngineEdition') = 5
                BEGIN
	                SELECT @condition = 1
	                WHERE EXISTS (
		                SELECT *
		                FROM sys.database_event_sessions
		                WHERE name = '{1}'
	                )
                END
                ELSE
                BEGIN 
	                SELECT @condition = 1
	                WHERE EXISTS (
		                SELECT *
		                FROM sys.server_event_sessions
		                WHERE name = '{1}'
	                )
                END

                IF @condition = 1
                BEGIN
	                BEGIN TRY
		                ALTER EVENT SESSION [{1}] ON {0} STATE = STOP;
	                END TRY
	                BEGIN CATCH
		                -- whoops...
		                PRINT ERROR_MESSAGE()
	                END CATCH
	                BEGIN TRY
		                DROP EVENT SESSION [{1}] ON {0};
	                END TRY
	                BEGIN CATCH
		                -- whoops...
		                PRINT ERROR_MESSAGE()
	                END CATCH
                END
            ";
            sql = String.Format(sql, serverType == ServerType.AzureSqlDatabase ? "DATABASE" : "SERVER", SessionName);
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

private void LoadServerType(SqlConnection conn)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT SERVERPROPERTY('Edition')";
                string edition = (string)cmd.ExecuteScalar();
                cmd.CommandText = "SELECT SERVERPROPERTY('EngineEdition')";
                int engineEdition = (int)cmd.ExecuteScalar();
                if (edition == "SQL Azure")
                {
                    serverType = ServerType.AzureSqlDatabase;
                    if (engineEdition == 8)
                    {
                        serverType = ServerType.AzureSqlManagedInstance;
                    }
                }
                else
                {
                    serverType = ServerType.FullInstance;
                }
            }
        }

public override void Initialize()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConnectionInfo.ConnectionString;
                conn.Open();

                string traceSql = null;
                try
                {
                    traceSql = System.IO.File.ReadAllText(Source);

                    // Push Down EventFilters
                    string filters = "";
                    filters += Environment.NewLine + Filter.ApplicationFilter.PushDown();
                    filters += Environment.NewLine + Filter.DatabaseFilter.PushDown();
                    filters += Environment.NewLine + Filter.HostFilter.PushDown();
                    filters += Environment.NewLine + Filter.LoginFilter.PushDown();

                    tracePath = utils.GetSqlDefaultLogPath(conn);
                    traceSql = String.Format(traceSql, TraceSizeMB, TraceRolloverCount, Path.Combine(tracePath  ,"sqlworkload"), filters);
                }
                catch (Exception e)
                {
                    throw new ArgumentException("Cannot open the source script to start the sql trace", e);
                }

                int id = utils.GetTraceId(conn, Path.Combine(tracePath, "sqlworkload"));
                if(id > 0)
                {
                    StopTrace(conn, id);
                }

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = traceSql;
                traceId = (int)cmd.ExecuteScalar();

                // Initialize the source of execution related events
                if(StreamSource == StreamSourceEnum.StreamFromFile)
                    Task.Factory.StartNew(() => ReadEventsFromFile());
                else if (StreamSource == StreamSourceEnum.StreamFromTDS)
                    Task.Factory.StartNew(() => ReadEventsFromTDS());


                // Initialize the source of performance counters events
                Task.Factory.StartNew(() => ReadPerfCountersEvents());

                // Initialize the source of wait stats events
                Task.Factory.StartNew(() => ReadWaitStatsEvents());
            }
        }

private void ReadEventsFromFile()
        {
            try
            {
                SqlConnectionInfo info = new SqlConnectionInfo();
                info.ServerName = "(localdb)\\MSSQLLocalDB";

                string sqlCreateTable = @"
                    IF OBJECT_ID('tempdb.dbo.trace_reader_queue') IS NULL
                    BEGIN
                        CREATE TABLE tempdb.dbo.trace_reader_queue (
                            ts datetime DEFAULT GETDATE(),
                            path nvarchar(4000)
                        )
                    END
                    TRUNCATE TABLE tempdb.dbo.trace_reader_queue;
                    INSERT INTO tempdb.dbo.trace_reader_queue (path) VALUES(@path);
                ";

                using (SqlConnection conn = new SqlConnection())
                {
                    conn.ConnectionString = info.ConnectionString;
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sqlCreateTable;
                        SqlParameter prm = new SqlParameter()
                        {
                            ParameterName = "@path",
                            DbType = System.Data.DbType.String,
                            Size = 4000,
                            Value = filePath
                        };
                        cmd.Parameters.Add(prm);
                        cmd.ExecuteNonQuery();
                    }
                }

                reader = new FileTargetXEventDataReader(info.ConnectionString, null, Events, ExtendedEventsWorkloadListener.ServerType.LocalDB);
                reader.ReadEvents();
                finished = true;
                    
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);

                if (ex.InnerException != null)
                    logger.Error(ex.InnerException.Message);

                Dispose();
            }
        }

private void WriteDictionary(Dictionary<string, int> values, SqlConnection conn, SqlTransaction tran, string name)
        {

            // create a temporary table

            string sql = @"
                SELECT TOP(0) *
                INTO #{0}
                FROM [{1}].[{0}];
            ";
            sql = String.Format(sql, name, ConnectionInfo.SchemaName);

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }

            // bulk insert into temporary
            using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn,
                                                                SqlBulkCopyOptions.KeepIdenreplacedy |
                                                                SqlBulkCopyOptions.FireTriggers |
                                                                SqlBulkCopyOptions.CheckConstraints |
                                                                SqlBulkCopyOptions.TableLock,
                                                                tran))
            {

                bulkCopy.DestinationTableName = "#" + name;
                bulkCopy.BatchSize = 1000;
                bulkCopy.BulkCopyTimeout = 300;
                bulkCopy.WriteToServer(DataUtils.ToDataTable(from t in values select new { t.Value, t.Key }));

            }

            // merge new data

            sql = @"
                INSERT INTO [{1}].[{0}s]
                SELECT *
                FROM #{0}s AS src
                WHERE NOT EXISTS (
                    SELECT *
                    FROM [{1}].[{0}s] AS dst 
                    WHERE dst.[{0}_id] = src.[{0}_id]
                );
            ";
            sql = String.Format(sql, name.Substring(0, name.Length - 1), ConnectionInfo.SchemaName);

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

private void WriteNormalizedQueries(Dictionary<long, NormalizedQuery> values, SqlConnection conn, SqlTransaction tran)
        {
            // create a temporary table

            string sql = @"
                SELECT TOP(0) *
                INTO #NormalizedQueries
                FROM [{0}].[NormalizedQueries];
            ";
            sql = String.Format(sql, ConnectionInfo.SchemaName);

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }

            // bulk insert into temporary
            using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn,
                                                                SqlBulkCopyOptions.KeepIdenreplacedy |
                                                                SqlBulkCopyOptions.FireTriggers |
                                                                SqlBulkCopyOptions.CheckConstraints |
                                                                SqlBulkCopyOptions.TableLock,
                                                                tran))
            {

                bulkCopy.DestinationTableName = "#NormalizedQueries";
                bulkCopy.BatchSize = 1000;
                bulkCopy.BulkCopyTimeout = 300;
                bulkCopy.WriteToServer(DataUtils.ToDataTable(from t in values where (t.Value != null) select new { t.Value.Hash, t.Value.NormalizedText, t.Value.ExampleText }));

            }

            // merge new data

            sql = @"
                INSERT INTO [{0}].[NormalizedQueries]
                SELECT *
                FROM #NormalizedQueries AS src
                WHERE NOT EXISTS (
                    SELECT *
                    FROM [{0}].[NormalizedQueries] AS dst 
                    WHERE dst.[sql_hash] = src.[sql_hash]
                );
            ";
            sql = String.Format(sql, ConnectionInfo.SchemaName);

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }


            // Erase from memory all the normalized queries 
            // already written to the database. This should reduce
            // the memory footprint quite a lot
            foreach(var hash in values.Keys.ToList())
            {
                values[hash] = null;
            }
            // Run the Garbage Collector in a separate task
            Task.Factory.StartNew(() => InvokeGC());
        }

public bool CheckTraceFormat(SqlConnection conn, string path)
        {
            string sql = @"
                SELECT COUNT(*) AS cnt
                FROM(
                    SELECT TOP(100) *
                    FROM fn_trace_gettable(@path, default)
                ) AS data
                WHERE EventSequence IS NOT NULL
                    AND SPID IS NOT NULL
            ";
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                var p = cmd.CreateParameter();
                p.ParameterName = "@path";
                p.DbType = System.Data.DbType.AnsiString;
                p.Value = path;
                cmd.Parameters.Add(p);
                return ((int)cmd.ExecuteScalar()) > 0;
            }

        }

private int GetLastCPUUsage()
        {

            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConnectionInfo.ConnectionString;
                conn.Open();
                // Calculate CPU usage during the last minute interval
                string sql = @"
                    IF SERVERPROPERTY('Edition') = 'SQL Azure'
                        AND SERVERPROPERTY('EngineEdition') = 5
                    BEGIN
                        WITH CPU_Usage AS (
                            SELECT avg_cpu_percent, end_time AS Event_Time
                            FROM sys.dm_db_resource_stats WITH (NOLOCK) 
                        )
                        SELECT 
                            CAST(ISNULL(AVG(avg_cpu_percent),0) AS int) AS avg_CPU_percent
                        FROM CPU_Usage
                        WHERE [Event_Time] >= DATEADD(minute, -{0}, GETDATE())
                        OPTION (RECOMPILE);
                    END

                    IF SERVERPROPERTY('Edition') = 'SQL Azure'
                        AND SERVERPROPERTY('EngineEdition') = 8 -- Managed Instance
                    BEGIN
                        WITH PerfCounters AS (
	                        SELECT DISTINCT
	                             RTrim(spi.[object_name]) AS [object_name]
	                            ,RTrim(spi.[counter_name]) AS [counter_name]
	                            ,RTRIM(spi.instance_name) AS [instance_name]
	                            ,CAST(spi.[cntr_value] AS BIGINT) AS [cntr_value]
	                            ,spi.[cntr_type]
	                        FROM sys.dm_os_performance_counters AS spi 
	                        LEFT JOIN sys.databases AS d
		                        ON LEFT(spi.[instance_name], 36) -- some instance_name values have an additional identifier appended after the GUID
		                        = d.[name]
	                        WHERE
		                        counter_name IN (
			                         'CPU usage %'
			                        ,'CPU usage % base'
		                        ) 
                        )
                        SELECT CAST(SUM(value) AS int) AS avg_CPU_percent
                        FROM (
                            SELECT 
	                            CAST(CASE WHEN pc.[cntr_type] = 537003264 AND pc1.[cntr_value] > 0 THEN (pc.[cntr_value] * 1.0) / (pc1.[cntr_value] * 1.0) * 100 ELSE pc.[cntr_value] END AS float(10)) AS [value]
                            from PerfCounters pc
                            LEFT OUTER JOIN PerfCounters AS pc1
	                            ON (
		                            pc.[counter_name] = REPLACE(pc1.[counter_name],' base','')
		                            OR pc.[counter_name] = REPLACE(pc1.[counter_name],' base',' (ms)')
	                            )
	                            AND pc.[object_name] = pc1.[object_name]
	                            AND pc.[instance_name] = pc1.[instance_name]
	                            AND pc1.[counter_name] LIKE '%base'
                            WHERE
	                            pc.[counter_name] NOT LIKE '% base'
                                AND pc.object_name LIKE '%:Resource Pool Stats'
                        ) AS p
                        OPTION (RECOMPILE);
                    END


                    ELSE -- On Premises

                    BEGIN
                        WITH ts_now(ts_now) AS (
                            SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)
                        ),
                        CPU_Usage AS (
                            SELECT TOP(256) SQLProcessUtilization, 
                                            DATEADD(ms, -1 * (ts_now.ts_now - [timestamp]), GETDATE()) AS [Event_Time] 
                            FROM (
                                SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                                    AS [SystemIdle], 
                                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
                                    AS [SQLProcessUtilization], [timestamp] 
                                FROM (
                                    SELECT [timestamp], CONVERT(xml, record) AS [record] 
                                    FROM sys.dm_os_ring_buffers WITH (NOLOCK)
                                    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                                        AND record LIKE N'%<SystemHealth>%'
                                ) AS x
                            ) AS y 
                            CROSS JOIN ts_now
                        )
                        SELECT 
                            ISNULL(AVG(SQLProcessUtilization),0) AS avg_CPU_percent
                        FROM CPU_Usage
                        WHERE [Event_Time] >= DATEADD(minute, -{0}, GETDATE())
                        OPTION (RECOMPILE);
                    END
                ";

                sql = String.Format(sql,StatsCollectionIntervalSeconds / 60);

                int avg_CPU_percent = -1;

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    avg_CPU_percent = (int)cmd.ExecuteScalar();
                }

                return avg_CPU_percent;
            }
        }

public static replacedyzerExecutionResult CreateFromBatch(string connectionStringBatch, string batchID, ClearCacheMode clearCacheMode)
        {
            // TODO: parallelizzare e sistemare codice
            Action<string, Action<DataTable>, Action<SqlParameterCollection>> load = (commandText, action, param) =>
            {
                using (var connection = new SqlConnection(connectionStringBatch))
                {
                    connection.Open();

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = commandText;
                        command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = Guid.Parse(batchID);
                        command.Parameters.Add(new SqlParameter("@clearCacheMode", SqlDbType.NVarChar)).Value = clearCacheMode;
                        param(command.Parameters);

                        using (var reader = command.ExecuteReader())
                        using (var table = new DataTable())
                        {
                            table.Load(reader);
                            action(table);
                        }
                    }
                }
            };

            var result = new replacedyzerExecutionResult();

            load("[asqa].[sp_read_CommonEnginePerformance]", (table) => result.EnginePerformances = EnginePerformanceCollection.CreateFromDataTable(table), (param) => { });
            load("[asqa].[sp_read_CommonAggregationsRead]", (table) => result.AggregationsReads = AggregationsReadCollection.CreateFromDataTable(table), (param) => { });
            load("[asqa].[sp_read_CommonParreplacedionsRead]", (table) => result.ParreplacedionsReads = ParreplacedionsReadCollection.CreateFromDataTable(table), (param) => { });
            load("[asqa].[sp_read_CommonCachesRead]", (table) => result.CachesReads = CachesReadCollection.CreateFromDataTable(table), (param) => { });
            load("[asqa].[sp_read_ExecutionInfo]", (table) => result.ExecutionInfo = table.Copy(), (param) => { });

            #region Load performance

            DataTable performanceTypes = null;
            load("[asqa].[sp_read_PerformanceTypes]", (table) => performanceTypes = table.Copy(), (param) => { });

            result.Performances = new PerformanceItemCollectionList();

            foreach (DataRow type in performanceTypes.Rows)
            {
                string category = Convert.ToString(type["CategoryName"]);
                string name = Convert.ToString(type["CounterName"]);           

                load("[asqa].[sp_read_Performance]", 
                    (table) => result.Performances.Add(PerformanceItemCollection.CreateFromDataTable(table, category, name)), 
                    (param) => 
                    {
                        param.Add(new SqlParameter("@categoryName", SqlDbType.NVarChar)).Value = category;
                        param.Add(new SqlParameter("@counterName", SqlDbType.NVarChar)).Value = name;
                    });
            }

            #endregion

            #region Load trace

            DataTable profilerTypes = null;
            load("[asqa].[sp_read_TraceTypes]", (table) => profilerTypes = table.Copy(), (param) => { });

            result.Profilers = new ProfilerItemCollectionList();

            foreach (DataRow type in profilerTypes.Rows)
            {
                string eventClreplaced = type["EventClreplaced"].ToString();

                load("[asqa].[sp_read_Trace]", 
                    (table) => result.Profilers.Add(ProfilerItemCollection.CreateFromDataTable(table, eventClreplaced)),
                    (param) => param.Add(new SqlParameter("@eventClreplaced", SqlDbType.NVarChar)).Value = eventClreplaced);
            }

            #endregion

            return result;
        }

public static void WriteToServer(ProcedureContext procedureContext, IDataReader reader, string destinationTableName, string beforeActionCommandText = null, string afterActionCommandText = null)
        {
            using (procedureContext.ImpersonationIdenreplacedy.Impersonate())
            {
                using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
                {
                    connection.Open();

                    if (beforeActionCommandText != null)
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandText = beforeActionCommandText;
                            command.ExecuteNonQuery();
                        }
                    }

                    // http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf
                    using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, externalTransaction: null))
                    {
                        var fields = Enumerable.Range(0, reader.FieldCount);
                        foreach (var columnName in fields.Select((i) => reader.GetName(i)))
                            bulkCopy.ColumnMappings.Add(columnName, columnName);

                        bulkCopy.BatchSize = BatchHelper.BulkCopyBatchSize;
                        bulkCopy.BulkCopyTimeout = BatchHelper.BulkCopyTimeout;
                        bulkCopy.DestinationTableName = destinationTableName;

                        do
                        {
                            bulkCopy.WriteToServer(reader);
                        }
                        while (reader.NextResult());
                    }

                    if (afterActionCommandText != null)
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandText = afterActionCommandText;
                            command.CommandTimeout = 0;
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void RefreshCurrentSQLInstance()
        {
            try
            {
                DatabaseVersion = null;
                SetSQLInstanceControlsDisabled();

                var connectionString = GetCurrentSQLInstanceConnectionString();
                if (connectionString == null)
                    return;
             
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    SqlInstanceConnected = true;

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = CommandTextCheckDatabaseExist.FormatWith(BatchDatabaseName);
                        command.CommandType = CommandType.Text;

                        DatabaseInstalled = command.ExecuteScalar() != null;
                    }

                    if (DatabaseInstalled)
                    {
                        connection.Close();
                        connection.ConnectionString = connectionString.ToAsqaSqlConnectionString();
                        connection.Open();

                        using (var command = connection.CreateCommand())
                        {
                            command.CommandText = CommandTextCheckExtendedPropertyValue.FormatWith(BatchDatabaseExtendedPropertyVersionName);
                            command.CommandType = CommandType.Text;

                            DatabaseVersion = (string)command.ExecuteScalar();
                        }
                    }
                }
                
                DatabaseInstalled = DatabaseVersion != null;

                EnableASQADBControls();
            }
            catch (Exception ex)
            {
                ex.HandleException(display: true);
            }
        }

See More Examples