none
Execute the Query with new connection to DB RRS feed

  • Question

  • Hi Experts,

    I have data table like below inside SQL Server. I need to run them on other SQL_Instance independently for the listed execute_count with a wait for delay of 5 sec per execution with in its own session. Let say all 3 query will run in its dedicated/shared connection whatever helps to achieve it. 

    Query 1 -- > Execute 25 times --> wait for delay 5 second after every execution

    Query 2 -- > Execute 15 times --> wait for delay 5 second after every execution

    Query 3 -- > Execute 20 times --> wait for delay 5 second after every execution

    SQL_Query Execute_Count SQL_Instance
    Insert into table1 25 LocalHost
    Insert into table2 15 LocalHost
    Insert into table 3 20 LocalHost

    The count of SQL_Query request can increase/decrease but it need to run independently not executing in sequence and wait for each query to meet respective execute_count. Please help , as how to achieve this requirement.

    Thanks 

    Priya

    Wednesday, November 27, 2019 1:01 PM

All replies

  • Hi Priya,

    Thank you for posting here.

    You can use timer to achieve this function.

    Here is a code example.

     public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            static DataTable table = new DataTable();
        
            System.Timers.Timer aTimer;
            System.Timers.Timer bTimer;
            System.Timers.Timer cTimer;
            int i = 0;
            int j = 0;
            int k = 0;
            private void Form1_Load(object sender, EventArgs e)
            {
                table = new DataTable();
                table.Columns.Add("SQL_Query", typeof(string));
                table.Columns.Add("Execute_Count", typeof(int));
                table.Columns.Add("SQL_Instance", typeof(string));
    
                table.Rows.Add("select * from Student.dbo.Stu",25, "localhost");
                table.Rows.Add("select * from Student.dbo.Emp",15, "localhost");
                table.Rows.Add("select * from Student.dbo.Address", 20,"localhost");
    
                aTimer = new System.Timers.Timer(200);
                aTimer.Elapsed += OnTimedEventA;
                aTimer.SynchronizingObject = this;
                aTimer.AutoReset = true;
    
                bTimer = new System.Timers.Timer(200);
                bTimer.Elapsed += OnTimedEventB;
                bTimer.SynchronizingObject = this;
                bTimer.AutoReset = true;
    
                cTimer = new System.Timers.Timer(200);
                cTimer.Elapsed += OnTimedEventC;
                cTimer.SynchronizingObject = this;
                cTimer.AutoReset = true;
            }
    
            private void OnTimedEventA(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[0];
                if (i == (int)row["Execute_Count"])
                {
                    aTimer.Enabled = false;
                }
                    dataGridView1.DataSource = GetData(row["SQL_Query"].ToString());
                    label1.Text = i.ToString();
                i++;
            }
            private void OnTimedEventB(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[1];
                if (j == (int)row["Execute_Count"])
                {
                    bTimer.Enabled = false;
                }
               
                    dataGridView2.DataSource = GetData(row["SQL_Query"].ToString());
                    label2.Text = j.ToString();
                j++;
            }
            private void OnTimedEventC(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[2];
                if (k == (int)row["Execute_Count"])
                {
                    cTimer.Enabled = false;
                }
                     dataGridView3.DataSource = GetData(row["SQL_Query"].ToString());
                     label3.Text = k.ToString();
    
                k++;
            }
    
    
            static DataTable GetData(string sql)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sql,connection))
                    {
                        SqlDataReader reader=command.ExecuteReader();
    
                        return reader.GetSchemaTable();
                    }
                }
            }
    
            private void Began_Click(object sender, EventArgs e)
            {
                aTimer.Enabled = true;
                bTimer.Enabled = true;
                cTimer.Enabled = true;
            }
        }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 28, 2019 9:57 AM
  • Hi Priya,

    Thank you for posting here.

    You can use timer to achieve this function.

    Here is a code example.

     public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            static DataTable table = new DataTable();
        
            System.Timers.Timer aTimer;
            System.Timers.Timer bTimer;
            System.Timers.Timer cTimer;
            int i = 0;
            int j = 0;
            int k = 0;
            private void Form1_Load(object sender, EventArgs e)
            {
                table = new DataTable();
                table.Columns.Add("SQL_Query", typeof(string));
                table.Columns.Add("Execute_Count", typeof(int));
                table.Columns.Add("SQL_Instance", typeof(string));
    
                table.Rows.Add("select * from Student.dbo.Stu",25, "localhost");
                table.Rows.Add("select * from Student.dbo.Emp",15, "localhost");
                table.Rows.Add("select * from Student.dbo.Address", 20,"localhost");
    
                aTimer = new System.Timers.Timer(200);
                aTimer.Elapsed += OnTimedEventA;
                aTimer.SynchronizingObject = this;
                aTimer.AutoReset = true;
    
                bTimer = new System.Timers.Timer(200);
                bTimer.Elapsed += OnTimedEventB;
                bTimer.SynchronizingObject = this;
                bTimer.AutoReset = true;
    
                cTimer = new System.Timers.Timer(200);
                cTimer.Elapsed += OnTimedEventC;
                cTimer.SynchronizingObject = this;
                cTimer.AutoReset = true;
            }
    
            private void OnTimedEventA(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[0];
                if (i == (int)row["Execute_Count"])
                {
                    aTimer.Enabled = false;
                }
                    dataGridView1.DataSource = GetData(row["SQL_Query"].ToString());
                    label1.Text = i.ToString();
                i++;
            }
            private void OnTimedEventB(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[1];
                if (j == (int)row["Execute_Count"])
                {
                    bTimer.Enabled = false;
                }
               
                    dataGridView2.DataSource = GetData(row["SQL_Query"].ToString());
                    label2.Text = j.ToString();
                j++;
            }
            private void OnTimedEventC(object sender, ElapsedEventArgs e)
            {
                DataRow row = table.Rows[2];
                if (k == (int)row["Execute_Count"])
                {
                    cTimer.Enabled = false;
                }
                     dataGridView3.DataSource = GetData(row["SQL_Query"].ToString());
                     label3.Text = k.ToString();
    
                k++;
            }
    
    
            static DataTable GetData(string sql)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sql,connection))
                    {
                        SqlDataReader reader=command.ExecuteReader();
    
                        return reader.GetSchemaTable();
                    }
                }
            }
    
            private void Began_Click(object sender, EventArgs e)
            {
                aTimer.Enabled = true;
                bTimer.Enabled = true;
                cTimer.Enabled = true;
            }
        }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thank you Timon,

    Its wonderful support, May I ask if my queries count change increase/decrease. Can it be dynamic and run them in parallel. Apologies for not listing before

    Thursday, November 28, 2019 11:05 AM
  • Hi Priya,

    These days, I try to use loops to create timers and their callback functions, but due to the timing of execution of callback functions, I find that multiple callback functions created using loops are all the same in the end.

    I have not found a good way solve this problem.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 1:36 AM