multiple threads for an update into SQL database

[danimischiu]

Hy all,

I'm facing a problem with some data that I need to check and update it's status in my database.I've got a data grid with some info from a table in the database..now for evry entry I
send that data to a server server then I get the server response as a string.The response is the info that I use to update the table in database but it's taking forever to do it.
Could I somehow use Threading for my app to make it update data faster?I never used Threading in a winform application so I need help..

 SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.dbPartnersConnectionString);

            DataTable dt = (DataTable)gridControl.DataSource;
            string cui = "";
            string update = "";


            for (int i = 0; i < gridView1.RowCount ; i++) //getting data that is going to be checked
            {

                cui = dt.Rows[i]["cui"].ToString().Trim();
                cui = cui.Replace("R", "");
                cui = cui.Replace("O", "");
                cui = cui.Replace(" ", "");

                try
                {

                    if (Main_App.Clase.VerificareCIF.ValidareCIF(cui))
                    {
                        update = "UPDATE  Terti SET cui=@cui,cod_asis=@cod_asis,denumire=@denumire,adresa=@adresa,judet=@judet,cod_postal=@cod_postal,nr_reg_com=@reg_comert,telefon=@tel,fax=@fax,platitor_TVA=@platitor_TVA,tva_Incasare=@tva_incasare,data_start_tvai=@data_start,verificat='DA' where cod_asis=@cod_asis";
                        string rsp = "";
                        try
                        {
                            using (WebClient client = new WebClient())
                            { rsp = client.DownloadString("http://www.alza.ro/" + cui); } //getting server response
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        rsp = rsp.Remove(rsp.Length - 1, 1);
                        //MessageBox.Show(rsp);

                        string mesaj = "";
                        string[] words = rsp.ToString().Split('|');

                        foreach (string word in words)
                        {
                            if (word != "")
                                mesaj = mesaj + " " + word.Trim() + Environment.NewLine;
                        }
                        // MessageBox.Show(mesaj);
                        string tva = "NU";
                        string cod_asis = "";
                        string adresa = "";
                        string fax = "";
                        string denumire = "";
                        string tel = "";
                        string regCom = "";
                        string judet = "";
                        string platitorTVA = "NU";
                        string codPostal = "";
                        string dataS = "";
                        string dataE = "";

                        //extracting data that I need   

                        denumire = words[1];
                        adresa = normalizare(words[3]);
                        judet = normalizare(words[5]);
                        regCom = normalizare(words[7]);
                        tel = words[13];
                        fax = words[15];
                        codPostal = words[11];
                        cod_asis = dt.Rows[i]["cod_asis"].ToString().Trim();
                        // MessageBox.Show(words[30] + "   " + words[31]);
                        if (words[31].Trim() != "NU")
                            platitorTVA = "DA";
                        //tva = true;


                        //string con ="Data Source"+Path.Combine( Application.StartupPath,"dbPartners.sdf");

                        if (connection.State == ConnectionState.Closed)
                        {
                            //connection = new SqlCeConnection(con);
                            connection.Open();
                            using (SqlCeCommand com = new SqlCeCommand(update, connection))
                            {
                                com.Parameters.AddWithValue("@denumire", denumire);
                                com.Parameters.AddWithValue("@cui", cui);
                                com.Parameters.AddWithValue("@adresa", adresa);
                                com.Parameters.AddWithValue("@cod_asis", cod_asis);
                                com.Parameters.AddWithValue("@judet", judet);
                                com.Parameters.AddWithValue("@reg_comert", regCom);
                                com.Parameters.AddWithValue("@tel", tel);
                                com.Parameters.AddWithValue("@fax", fax);
                                com.Parameters.AddWithValue("@cod_postal", codPostal);
                                com.Parameters.AddWithValue("@platitor_TVA", platitorTVA);
                                com.Parameters.AddWithValue("@tva_incasare", tva);
                                com.Parameters.AddWithValue("@data_start", dataS);
                                // com.Parameters.AddWithValue("@data_end", dataE);


                                com.ExecuteNonQuery();
                                com.Dispose();
                                // MessageBox.Show("Data added");
                            }

                            connection.Close();
                        }

                    }
                    else
                    {
                        update = "UPDATE  Terti set verificat='DA'  where cod_asis=@cod_asis";
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

[TnTinMN]

Before you put this on it's own thread, I would suggest that you consider optimizing the code first. You are doing a lot of string manipulations that may be better handled in a stringbuilder instead.

  for (int i = 0; i < gridView1.RowCount ; i++) //getting data that is going to be checked
{
cui = dt.Rows[i]["cui"].ToString().Trim();
cui = cui.Replace("R", "");
cui = cui.Replace("O", "");
cui = cui.Replace(" ", "");

change to:

System.Text.StringBuilder sb = new System.Text.StringBuilder(100);  // adjust initial size base on your anticipated string length
 for (int i = 0; i < gridView1.RowCount ; i++) //getting data that is going to be checked
{
sb.Length = 0; // resets stringbuilder text
sb.Append(dt.Rows[i]["cui"].ToString()); // no need to trim as you are removing all " " anyways
sb.Replace("R", "");
sb.Replace("O", "");
sb.Replace(" ", "");

// now replace all further references to: cui
// with:  sb.ToString()

This appears to dead code left over from debug/development. Concatenating strings like this is very slow, I see a word[31] so at least 32 times this this loops. Comment it out or remove.

 foreach (string word in words)
{
if (word != "")
mesaj = mesaj + " " + word.Trim() + Environment.NewLine;
}