timers and threads and calls to SQL

Question

I have an application which collects parameters and then calls a very complex SQL SP. The process works perfectly.

I wanted to put in a timer so that I could have some measure of the progress of the process in real time. For this purpose, I created a timer object, instantiated it, and within in the timer_tick event I have a text box capture the strings generated indicating elapsed time.

This functionality works perfectly well - that is, I am able to compute elapsed time and display it "in real time" **** E X C E P T ***** when the call to the SQL SP is made. Something happens, and my timer_tick event, although it fires, does not display anything.

My test for this was to comment out the call to the SQL SP - and under this circumstance, it works. It is ONLY when their is a call to the external SQL SP that it does not work properly UNTIL AFTER THE SQL PROC TERMINATES AND, I IMAGINE, RETURNS CONTROL TO THE Visual Studio/ADO/C# application.

Is there a way to override what appears to be an "unknown" condition that prevents my timer from displaying its contents?

Answer 1

Is the call to SQL SP in a background worker? If not it should be....

---

William Wegerson (www.OmegaCoder.Com)

Answer 2

"background worker?"

Answer 3

You call to your stored procedure is executing on the Main UI thread, and so is blocking the painting of the UI.  It is common practice to execute lengthy procedures that do not require access to the UI using a Background Worker as OmegaMan suggested.  This will allow your UI to continue to paint while the stored procedure is executing.

---

If this answers your question, please mark the question as answered.

Answer 4

Still unclear. You mean, put the SQL process into its own form and leave the timer in the "parent?" I'm not following the example. I need a baby-simple illustration absent all but the most essential elements. I'm trying to understand the concept.

Answer 5

You only need one form, but the sproc will be executed on a different thread by using a BackgroundWorker.  You can keep all your timer logic, but here's what you would need to create and run the BackgroundWorker.

BackgroundWorker worker;

        public Form1()
        {
            InitializeComponent();
            worker = new BackgroundWorker();
            worker.DoWork += new DoWorkEventHandler(worker_DoWork);
            worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //start timer
            worker.RunWorkerAsync();
           
        }

        void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            //put sproc code here
        }

        void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //stop timer
            MessageBox.Show("sproc is done");
        }

---

If this answers your question, please mark the question as answered.

Answer 6

The background worker component (it can be dragged to the design surface of the form and manipulated in the properties window) also has an update progress event which could be used as an alternate way of notifiying the user.

---

William Wegerson (www.OmegaCoder.Com)

Answer 7

Thank you both. I will play with these and see what might be made from them.

Answer 8

OK - I've isolated all the relevant code into its own form which is given below. The timer, however, still does not respond during the time the SQL SP is operating. I'm sure there's some very basic mistake - but perhaps if you eyeball the code, it will stand out since I clearly can not see it at the moment.

The form code below is instantiated from the parent form as


FLADO_RUN fr = new FLADO_RUN(LongTimeout,myFLADOConnection,SourceDB,SourceRelease, SourceDate, dPath,TorS,ProdType,sPath,WasIst);
fr.ShowDialog();

The parameter list consists of data points collected in the parent form.

----
then:
----

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace FLADO
{
    public partial class FLADO_RUN : Form
    {
        public bool RunOK;
        public BackgroundWorker worker = new BackgroundWorker();
        public Timer FT = new Timer();
        public System.DateTime st = new System.DateTime();
        public System.DateTime dt = new System.DateTime();

        public Int32 LongTimeout;
        public string myFLADOConnection;
        public string TIPS;
        public string rlno;
        public string dte;
        public string datapath;
        public string TorS;
        public string isProd;
        public string mySourceDirectory;
        public string WasIst;

       

        public FLADO_RUN(Int32 Long_TimeOut, string my_FLADOConnection, string T_IPS,
            string r_lno, string d_te, string data__path, string T_orS, string is_Prod,
            string mySource_Directory, string Was_Ist)
        {
            InitializeComponent();

            LongTimeout = Long_TimeOut;
            myFLADOConnection = my_FLADOConnection;
            TIPS = T_IPS;
            rlno = r_lno;
            dte = d_te; ;
            datapath = data__path;
            TorS = T_orS;
            isProd = is_Prod;
            mySourceDirectory = mySource_Directory;
            WasIst = Was_Ist;
                    
            worker.DoWork += new DoWorkEventHandler(worker_DoWork);
            worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
        }

        private void FLADO_RUN_Load(object sender, EventArgs e)
        {
            // start timer here, then
            FT.Interval = 1000;
            FT.Enabled = true;
            dt = System.DateTime.Now;
            st = System.DateTime.Now;
            
            RUN_FLADO_BUTTON.Enabled = false;
            worker.RunWorkerAsync();
        }

        void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            // sproc goes here
            RunOK = Run_FLADO(LongTimeout);
        }

        void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            // stop timer
            st = System.DateTime.Now;
            FT.Enabled = false;
            RUN_FLADO_LABEL.Text = "Time elapsed to process FLADO = " + (st - dt).ToString();
    
            RUN_FLADO_BUTTON.Text = "FLADO Completed. Press to Exit";
            RUN_FLADO_BUTTON.Enabled = true;
        }

        private void RUN_FLADO_BUTTON_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void FT_Tick(object sender, EventArgs e)
        {
            st = System.DateTime.Now;
            RUN_FLADO_LABEL.Text = "Processing FLADO - time elapsed = " + (st - dt).ToString();
        }

        private bool Run_FLADO(Int32 LongTimeout)
        {
            bool FLADO_OK = true;
            string a;


            System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(@myFLADOConnection);

            System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
            SqlCom.Connection = SqlCon;
            SqlCom.CommandType = CommandType.StoredProcedure;
            SqlCom.CommandText = "FLADO..FLADO_MAIN";
            SqlCom.CommandTimeout = LongTimeout;

            // trim all source before use

            // create parameters in order
            // 1st - @TIPS
            System.Data.SqlClient.SqlParameter parm1 = new System.Data.SqlClient.SqlParameter();
            parm1.ParameterName = "@TIPS";
            a = TrimFLADO(TIPS);
            parm1.Value = a;
            SqlCom.Parameters.Add(parm1);

            // 2nd - @rlno
            System.Data.SqlClient.SqlParameter parm2 = new System.Data.SqlClient.SqlParameter();
            parm2.ParameterName = "@rlno";
            a = TrimFLADO(rlno);
            parm2.Value = a;
            SqlCom.Parameters.Add(parm2);

            // 3rd - @dte
            System.Data.SqlClient.SqlParameter parm3 = new System.Data.SqlClient.SqlParameter();
            parm3.ParameterName = "@dte";
            a = TrimFLADO(dte);
            parm3.Value = a;
            SqlCom.Parameters.Add(parm3);

            // 4th - @data_path
            System.Data.SqlClient.SqlParameter parm4 = new System.Data.SqlClient.SqlParameter();
            parm4.ParameterName = "@data_path";
            a = TrimFLADO(datapath);
            parm4.Value = a;
            SqlCom.Parameters.Add(parm4);

            // 5th - @TorS
            System.Data.SqlClient.SqlParameter parm5 = new System.Data.SqlClient.SqlParameter();
            parm5.ParameterName = "@TorS";
            a = TrimFLADO(TorS);
            parm5.Value = a;
            SqlCom.Parameters.Add(parm5);

            // 6th - @isProd
            System.Data.SqlClient.SqlParameter parm6 = new System.Data.SqlClient.SqlParameter();
            parm6.ParameterName = "@isProd";
            a = TrimFLADO(isProd);
            parm6.Value = a;
            SqlCom.Parameters.Add(parm6);

            // 7th - @mySourceDirectory
            System.Data.SqlClient.SqlParameter parm7 = new System.Data.SqlClient.SqlParameter();
            parm7.ParameterName = "@mySourceDirectory";
            a = TrimFLADO(mySourceDirectory);
            parm7.Value = a;
            SqlCom.Parameters.Add(parm7);

            // 8th - @WasIst
            System.Data.SqlClient.SqlParameter parm8 = new System.Data.SqlClient.SqlParameter();
            parm8.ParameterName = "@WasIst";
            a = TrimFLADO(WasIst);
            parm8.Value = a;
            SqlCom.Parameters.Add(parm8);


            try
            {
                SqlCon.Open();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error connecting to " + myFLADOConnection + ex.Message);
                FLADO_OK = false;
            }

            if (FLADO_OK == true)
            {
                try
                {
                    SqlCom.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("FLADO_MAIN.sql failed: " + ex.Message);
                    FLADO_OK = false;
                }


                SqlCon.Close();

            }

            return FLADO_OK;
        }

        private string TrimFLADO(string a)
        {
            string b;
            if (a != null)
            {
                b = a.Trim();
            }
            else
            {
                b = a;
            }

            return b;
        }



    }
}

Answer 9

Don't do:

  MessageBox.Show("Error connecting to " + myFLADOConnection + ex.Message);

from a worker thread. It doesn't have access to the GUI thread. You can set a variable on the form to hold the error and when upon the RunWorkerCompleted event check to see if there is an error and display it in a messagebox.

Is the timer a component you dragged to the surface of the design? I don't see where it is subscribed to the tick event.

But....where so you call FT.Start() for the timer?

---

William Wegerson (www.OmegaCoder.Com )