How to handel Select,Update query on foreach loop?

Question

SERVER A: public string connStr = "Data Source=PH-09-5336;Initial Catalog=InventoryDB;Integrated Security=True";

SERVER B: public string connWIP = "Data Source=PWODU-COGNOSDB3;Initial Catalog=BI_SOURCE;

I have this method of inserting records to InventoryDB.DBO.FG_FILLIN from excell file.

Select records from BI_SOURCE.dbo.ODU_WIP_PI then update the null records(item Number) on InventoryDB.DBO.FG_FILLIN if its serialnumber is match on BI_SOURCE.dbo.ODU_WIP_PI serialnumber.

Problems:

I have tried to updated 13000 of records and it takes 5 minutes to be updated. I need to update the new inserted record InventoryDB.DBO.FG_FILLIN that has only itemnumber that have null records.

But in my code it loops and update again all records in InventoryDB.DBO.FG_FILLIN

I really stack in this problem.

-------------------------------------INSERT RECORDS----------------------------------------

  using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand("Insert into dbo.FG_FILLIN Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filepath1 + ";HDR=YES','SELECT * FROM [" + Sheetname1 + "$]')", conn))
                    {


                        try
                        {
                            conn.Open();

                            cmd.ExecuteNonQuery();

                            txtsheet1.Text = string.Empty;
                            txtpath1.Text = string.Empty;
                            txtpath1.Focus();
                            MessageBox.Show("FILL IN Mass Upload Success!");


                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                            txtsheet1.Text = string.Empty;
                            txtpath1.Text = string.Empty;
                            txtpath1.Focus();
                        }
                        finally
                        {
                            if (conn.State == ConnectionState.Open) cmd.Connection.Close();
                            conn.Close();
                        }
                    }

---------------------------------SELECT UPDATE -----------------------------------

public void Update()
        {

            using (SqlConnection conn = new SqlConnection(connWIP))
            {
                try
                {

                    conn.Open();
                    using (SqlDataAdapter dAd = new SqlDataAdapter("select WIP_serialNumber, WIP_ItemID from BI_SOURCE.dbo.ODU_WIP_PI", conn))
                    {
                        DataTable data = new DataTable();
                        dAd.Fill(data);
                        using (SqlConnection conn2 = new SqlConnection(connStr))
                        {
                            conn2.Open();
                            try
                            {

                                foreach (DataRow recordFromServerA in data.Rows)
                                {

                                    using (SqlCommand dCmd = new SqlCommand("update [dbo].[FG_FILLIN] SET ItemNumber=@ItemNumber where SerialNumber=@SerialNumber", conn2))
                                    {


dCmd.Parameters.AddWithValue("@ItemNumber", recordFromServerA["WIP_ItemAlias"]);
                                                                               dCmd.Parameters.AddWithValue("@SerialNumber", recordFromServerA["WIP_serialNumber"]);
                                        dCmd.ExecuteNonQuery();
                                    }
                                }
                            }
                            catch (Exception ee)
                            {
                                MessageBox.Show(ee.Message);
                            }
                            finally
                            {
                                if (conn2.State == ConnectionState.Open) conn2.Close();
                            }


                        }


                    }
                    MessageBox.Show("All Records Updated Successfully!");

                }

                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
        }
        #endregion

Answer 1

If I've understood your problem correctly you might be able to speed up the update process considerably by just executing a single SQL statement, rather than looping through each row individually.

How about this? After you've loaded your records into table A (FG_FILLIN), create an SQL UPDATE statement that uses a join from table A onto table B where the ItemNumber field in table A is null. Something like this should do it:

UPDATE [dbo].[FG_FILLIN]
SET ItemNumber = table2.WIP_ItemID
FROM [dbo].[FG_FILLIN] INNER JOIN BI_SOURCE.dbo.ODU_WIP_PI as table2
ON [dbo].[FG_FILLIN].SerialNumber = table2.WIP_SerialNumber
WHERE [dbo].[FG_FILLIN].ItemNumber IS NULL

Answer 2

another thing, you could also wrap you stuff into a

using(var scope = new TransactionScope())
{
stuff
scope.Complete();
}

to make it a transaction