none
How to commit/rollback sql-statements from within thread RRS feed

  • Question

  • Hi there,

     

    I have the following source-code:

    Code Block

    using (_TransactionScope = new System.Transactions.TransactionScope())

    {

    using (SqlConnection oCon = oDal.CreateConnection("Data Source=$Server$;Initial Catalog=$Database$;Integrated Security=True", "005DPC0022", "MM"))

    {

    if(oCon.State != ConnectionState.Open)

    oCon.Open();

    for (int i = 0; i < listViewFoundARLs.CheckedIndices.Count; i++)

    {

    fProcess = new ManageMind.MMConverter.Classes.ProcessHandler();

    fProcess.file = listViewFoundARLs.Items[Convert.ToInt32(listViewFoundARLs.CheckedIndices[i].ToString())].Text;

    fProcess.program = comboBoxProgram.Text;

    processThread = new Thread(fProcess.ProcessFile);

    processThread.Start();

    }

    }

    }

     

     

    This all works fine.

    The fProcess will search the root directory for files with the extension .ini

    When it find a file, it will read the file and put the contents in a SQL-database.

    The code for this is show below:

    Code Block

    public void ProcessFile()

    {

    oDal = new ManageMind.MMLib.DAL();

    oDal.GetConnection("Data Source=$Server$;Initial Catalog=$Database$;Integrated Security=True", "005DPC0022", "MM");

    Process();

    oDal.DisposeSQL();

    FileProcessedHandler FileProcessed = new FileProcessedHandler(Globals.formINI.FileProcessed);

    Globals.formINI.Invoke(FileProcessed, "Done processing");

    }

    private void Process()

    {

    using (StreamReader FileReader = new StreamReader(file))

    {

    string line = string.Empty;

    string sectie = string.Empty;

    string param = string.Empty;

    string value = string.Empty;

    while ((line = FileReader.ReadLine()) != null)

    {

    if (line.Trim().Length > 0 && !line.Trim().StartsWith("'"))

    {

    line = line.Trim();

    if (line.Trim().StartsWith("["))

    {

    sectie = line.Substring(line.IndexOf("[")+1, line.IndexOf("]")-1).Trim();

    }

    else

    {

    param = line.Substring(0, line.IndexOf('='));

    value = line.Substring(line.IndexOf('=') + 1, (line.IndexOf("'") > 0 ? line.IndexOf("'") - (line.IndexOf('=')+2) : line.Length - line.IndexOf('=')) - 1).Trim();

    string dbtype = AddItem(sectie, param, value);

    FileProcessingHandler FileProcessing = new FileProcessingHandler(Globals.formINI.FileProcessing);

    Globals.formINI.Invoke(FileProcessing, file, program, sectie, param, value, dbtype);

    }

    }

    }

    }

    }

    private string AddItem(string section, string param, string value)

    {

    string dbtype = string.Empty;

    try

    {

    dbtype = ConfigFormatParser.CheckValue(value);

    oDal.AddConfigItem(program, section, param, value, dbtype);

    }

    catch

    {

    }

    return dbtype;

    }

     

     

    What I would like to do is when a user pressed the cancel button, the thread(s) will be stopped.

    If the threads are aborted, all the inserted data in SQL must rollback.

    Otherwise it must commit the SQL insertions.

     

    Can anybody please tell me how to do this ?

     

    Thank you

    Monday, January 21, 2008 1:22 PM

Answers

  • Found the solution myself:

     

    string[] process = new stringDevil;

    process[0] = file;

    process[1] = program;

    process[2] = sectie;

    process[3] = param;

    process[4] = value;

    process[5] = dbtype;

    bgwProcessFiles.ReportProgress(1, process);

     

     

     

    Monday, January 21, 2008 3:53 PM

All replies

  • There's a couple of things going on here.  Firstly your first block of code can't be run in a separate thread as it interacts with the UI.  UI interaction must occur on the UI thread, no exceptions.  Whether you use Invoke to marshal calls to the UI thread and back or simply run on the UI thread is not important but you must do it.

     

    The second issue I have is that you're creating a separate thread for each item.  For a reasonable number of selections this is a waste as you can't process more than the # of processors you have anyway.  Even worse is that if any of the threads block then your entire app will be blocked in memory even if you close the UI. 

     

    As for the transaction support you appear to be creating a single transaction and connection and sharing it amongst multiple threads.  Connections are not thread-safe so you are adding undo work on yourself to keep them valid.  Theoretically each thread should have its own connection and transaction. 

     

    In this particular case where you want to do some work asynchronously from the UI and allow cancellation you should consider using the BackgroundWorkerComponent instead.  An entire discussion about this is beyond the limits of the forums so refer to MSDN or my article here: http://p3net.mvps.org/Topics/WinForms/BackgroundWorker.aspx

     

    In a nutshell though here are the basic steps:

    1. Add a BWC to your form
    2. For the DoWork handler you'll create a method that basically combines the DB functionality of the first block of code you gave with ProcessFile.
    3. If desired set the WorkerReportsProgress property to true and handle the appropriate event to update your UI as progress continues.  Note that this handler is called in the context of the UI so no invocation is needed.
    4. For cancellation support set WorkerSupportsCancellation and handle the event. 
    5. Add a button to allow cancellation.  In the event handler call CancelAsync on the BWC to cancel the request.
    6. In your start event handler grab all the checked items from the UI and bundle them up into an object of some sort (maybe a string array)
    7. Call RunWorkerAsync to start the work on an arbitrary thread.
    8. Within the work handler you'll create your DB connection and transaction, process each string in the passed in data and then push the data to the database.  When it is all done commit the transaction. 
    9. To add cancellation support you need to periodically check the CancellationPending property of the BWC.  If it is true then rollback the transaction, set Cancel (on the event argument) to true and return. 
    10. You can add a cancellation event handler to report to the user that the operation was cancelled (after the fact) to be nice but it isn't necessary.

    If you want to treat each UI item as a separate logical entity then you would need only create a separate BWC for each item (rather than embedding it in the form).  You would have to store the list of created BWC objects so you can cancel them as needed but otherwise the code works the same.

     

    Michael Taylor - 1/21/08

    http://p3net.mvps.org

    Monday, January 21, 2008 2:02 PM
  • Hi Michael Taylor,

     

    Thank you for you're reply.

    I have changed my code, and I now have the following :

    Code Block

    public fConvertINI()

    {

    InitializeComponent();

    this.bgwProcessFiles.DoWork += new DoWorkEventHandler(bgwProcessFiles_DoWork);

    this.bgwProcessFiles.ProgressChanged += new ProgressChangedEventHandler(bgwProcessFiles_ProgressChanged);

    this.bgwProcessFiles.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgwProcessFiles_RunWorkerCompleted);

    }

     

    private void buttonStartConvert_Click(object sender, EventArgs e)

    {

    this.buttonStartConvert.Enabled = false;

    this.buttonStopConvert.Enabled = true;

    this.listViewConversion.Items.Clear();

    ArrayList arTest = new ArrayList();

    arTest.Add(comboBoxProgram.Text);

    for (int x = 0; x < listViewFoundINIs.CheckedIndices.Count; x++)

    {

    arTest.Add(listViewFoundINIs.CheckedItems[x].Text);

    }

    this.bgwProcessFiles.RunWorkerAsync(arTest);

    }

    private void buttonStopConvert_Click(object sender, EventArgs e)

    {

    if (MessageBox.Show("The operation is aborted.\r\nDo you wish to roll-back the inserted data ?", "Aborted", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)

    {

    this.bgwProcessFiles.CancelAsync();

    this.isCancelled = true;

    }

    }

    void bgwProcessFiles_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)

    {

    this.listViewConversion.Items.Add(new ListViewItem("Done"));

    this.buttonStartConvert.Enabled = true;

    this.buttonStopConvert.Enabled = false;

    }

    void bgwProcessFiles_ProgressChanged(object sender, ProgressChangedEventArgs e)

    {

    this.listViewConversion.Items.Add(new ListViewItem(e.ProgressPercentage.ToString()));

    }

    void bgwProcessFiles_DoWork(object sender, DoWorkEventArgs e)

    {

    ArrayList arProcess = (ArrayList)e.Argument;

    DoProcessWork(arProcess[0].ToString(), arProcess);

    }

    void DoProcessWork(string program, ArrayList arrFiles)

    {

    if (program != string.Empty)

    {

    isCancelled = false;

    _TransactionScope = null;

    oDal = new ManageMind.MMLib.DAL();

    using (_TransactionScope = new System.Transactions.TransactionScope())

    {

    if(!bgwProcessFiles.CancellationPending)

    {

    using (SqlConnection oCon = oDal.CreateConnection("Data Source=$Server$;Initial Catalog=$Database$;Integrated

    Security=True", "005DPC0022", "MM"))

    {

    if (!bgwProcessFiles.CancellationPending)

    {

    if (oCon.State != ConnectionState.Open)

    oCon.Open();

    for (int i = 1; i < arrFiles.Count; i++)

    {

    if (!bgwProcessFiles.CancellationPending)

    {

    using (StreamReader FileReader = new StreamReader(arrFiles[i].ToString()))

    {

    string line = string.Empty;

    string sectie = string.Empty;

    string param = string.Empty;

    string value = string.Empty;

    while ((line = FileReader.ReadLine()) != null)

    {

    if (!bgwProcessFiles.CancellationPending)

    {

    if (line.Trim().Length > 0 && !line.Trim().StartsWith("'"))

    {

    line = line.Trim();

    if (line.Trim().StartsWith("["))

    {

    sectie = line.Substring(line.IndexOf("[") + 1, line.IndexOf("]") - 1).Trim();

    }

    else

    {

    param = line.Substring(0, line.IndexOf('='));

     

    value = line.Substring(line.IndexOf('=') + 1, (line.IndexOf("'") > 0 ? line.IndexOf("'") - (line.IndexOf('=') + 2) : line.Length - line.IndexOf('=')) - 1).Trim();

     

    string dbtype = string.Empty;

    try

    {

    dbtype = ConfigFormatParser.CheckValue(value);

    oDal.AddConfigItem(program, sectie, param, value, dbtype);

    }

    catch

    {

    }

    bgwProcessFiles.ReportProgress(1);

    }

    }

    }

    }

    }

    }

    }

    }

    }

    }

    if (!isCancelled)

    {

    _TransactionScope.Complete();

    }

    }

    oDal.DisposeSQL();

    }

    }

     

     

     

    My next question is:

    Can I report anything else than an integer in the bgwProcessFiles.ReportProgress() ???

    Because I would like to report the line processed and the param/value pair of that line...

     

    Thank you.

     

    Nathan

     

    Monday, January 21, 2008 3:08 PM
  • Found the solution myself:

     

    string[] process = new stringDevil;

    process[0] = file;

    process[1] = program;

    process[2] = sectie;

    process[3] = param;

    process[4] = value;

    process[5] = dbtype;

    bgwProcessFiles.ReportProgress(1, process);

     

     

     

    Monday, January 21, 2008 3:53 PM