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?

Perhaps you need to do the SQL SP in a separate thread.

OK - but I've not done this kind of operation before, and so I really need some careful guidance as to how to do it. My C# project has a number of forms, but the big SQL part occurs in the main form - and is instantiated as part of a button click. A long list of parameters, collected by the main part of the application, have to be converted to parameter objects, and then the SQL SP called. I've set up my timer to be instantiated before the call to the SQL portion, all of which is contained in its own function (i.e. method). Sounds like you're saying that that code could be run in its own thread.

The question, then, is how to set up the asynchronicity that would make this possible.

Creating threads is a subject that many articles have been written for. I can understand that you want to get this taken care of as quickly and easily as possible but multithreading is worth learning about. In a very general sense, it is very common for non-interactive tasks that are processor-intensive to be done in a separate thread. I don't know how relevant that is to your current situation but it is likely that the UI does not have a chance to do anything while the SQL SP is executing. I am not sure that is the problem, but if it is, then the common solution is to do the SQL SP or anything like taht in a separate thread.

I hope you don't mind finding and reading some articles about threads; look for articles specific to .Net.

Essentially, you will need a way for the other thread (the form is in the main thread) to communicate with the main thread to update the UI. Articles about threads should explain that and if they don't look for other articles or ask here for help with specifics. Your form will create a thread and pass the parameter that the other thread would use to communicate with the main thread. The main thread is often called the UI thread and the other thread would be the background or worker thread.

Something else that you might want to do in the UI thread before creating the worker thread is to create an event that the worker thread could use if it wants to cancel the SP, but that might be too advanced for this.

Once the thread is created, the UI thread can wait for completion of the worker thread. I am sorry I am not sure of the details; I know all the details of how to do threads using the Windows API directly but I have not done much threading using .Net. I think you should read some articles anyway and then if you have some specific questions then ask for help about that.

Note that there is a difference between multi-processing and multi-threading; you do not want multi-processing for this.

Hi Avrohom,
 
As your process is a time consuming one, when this DB call is made the thread is fully utlized by it. So it doesnt get time to update the UI.
 
So, what you can do is, using a separate thread for this process as others have said.
 
If you are not familiar with the threads the easiest way you can do is, using Backgorund worker component. You can use it even without having a minimum knowledge on threads, as it handles most of the work on its own.

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



    }
}

Hi friend,
 
Sorry for suggesting using Backgroundworker. It's not suitable for your application as your process cannot be broken in to small parts. With background worker you cannot resolve your problem. You have to use threads. Don;t worry. Change your code as follows.
 
Remove backgroundworker. Keep other code as it is and do following changes.
 

using System.Threading;//Add this using statement

namespace FLADO
{
public partial class FLADO_RUN : Form
{
private bool result; //Define this variable
private void FLADO_RUN_Load(object sender, EventArgs e)
{
//Your code
 
Thread dbprocess = new Thread(Run_FLADO);
dbprocess.Start(LongTimeout);
}
 
 
//Add this new part.
public delegate void updateResult(bool res);
public void SetResult(bool presult)
{
if (this.InvokeRequired)
{
this.Invoke(new updateResult(SetResult), new object[] { presult });
return;
}
result = presult;
}
//Change the signature of Run_FLADO method
private void Run_FLADO(object LongTimeout)
{
bool FLADO_OK = true;
string a;
//cast your object to int32 here.
//Your normal code goes here.
//Instead of returning the result you can set the result by Set result methd.
SetResult(FLADO_OK);
//Then you can use 'result' variable to check the result of this process.
}
}
}
 
 
Hope your problem is resolved with my code. If this answer helps you please do not forget to accept this answer.

Hi, Niki-

First, I do want to thank you for your help with this problem. It is very much appreciated.

I took your suggestions and reworked the code according to your specifications. It runs, that is, the SQL is called as I would expect. However, my poor timer is still getting clobbered. Here's the code as I altered it. Any thoughts?


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;
using System.Threading;

namespace FLADO
{
    public partial class FLADO_RUN : Form
    {
        public bool RunOK;
        public System.Windows.Forms.Timer FT = new System.Windows.Forms.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;

        private bool result;

       

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

        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;

            Thread dbprocess = new Thread(Run_FLADO);
            dbprocess.Start(LongTimeout);


          
        }

        public delegate void updateResult(bool res);

        public void SetResult(bool presult)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new updateResult(SetResult), new object[] { presult });
                return;
            }
            result = presult;
        }

       


      

        void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
           
        }

        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 void Run_FLADO(object LongTimeout)
        {
            bool FLADO_OK = true;
            string a;

            string x = Convert.ToString(LongTimeout);
            int Long_Timeout = Convert.ToInt32(x);
           

          

            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 = Long_Timeout;

            // 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();

            }

            SetResult(FLADO_OK);
            // 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 string TrimFLADO(string a)
        {
            string b;
            if (a != null)
            {
                b = a.Trim();
            }
            else
            {
                b = a;
            }

            return b;
        }



    }
}