Click here to Skip to main content
11,505,123 members (67,745 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C#
I am trying to import data from excel file and update one field(sale_price of specific_page) if new value exists.
the following code takes roughly around 15-20 min to execute which is one of the problem ,there are around 5000 rows in excel file.
the first insert query just fine records are inserted from excel file into Sales_update table . the second problem is that update query works fine in sql server but here it does not work . any help would be much appreciated .
if (str == "sale")
{
   for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
   {
      cmd.Parameters.Clear();
      if (ds.Tables[0].Rows[i]["Sr_No"] != null)
      {
         cmd.Parameters.AddWithValue("@Item", ds.Tables[0].Rows[i]["Sr_No"]);
         cmd.Parameters.AddWithValue("@SalePart", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Sale_Price", ds.Tables[0].Rows[i]["Sale_Price"]);
         cmd.Parameters.AddWithValue("@Partname", ds.Tables[0].Rows[i]["Sale_Part_Desc"]);
         cmd.Parameters.AddWithValue("@LocalPartNo", ds.Tables[0].Rows[i]["Local_Part_No"]);
         cmd.Parameters.AddWithValue("@Parent_Part_No ", ds.Tables[0].Rows[i]["Parent_Part_No"]);
         cmd.Parameters.AddWithValue("@Part_Type_TR_ENG", ds.Tables[0].Rows[i]["Part_Type_TR_ENG"]);
         cmd.Parameters.AddWithValue("@Id1", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Id2", ds.Tables[0].Rows[i]["Local_Part_No"]);
 
         string query = @"If Not Exists(select * from SalesUpdate where [Item#]=@Item)Begin insert into  SalesUpdate ([Item#],[SalePart],[Sale_Price] ,[Part name] ,[LocalPartNo],[Parent_Part_No],[Part_Type_TR_ENG])VALUES(@Item,@SalePart,@Sale_Price,@Partname,@LocalPartNo,@Parent_Part_No,@Part_Type_TR_ENG);End";
         
         cmd.CommandText = query;
         cmd.ExecuteNonQuery();
 
         cmd.CommandText = "select * from SalesUpdate CROSS JOIN specific_page where SalesUpdate.[SalePart]=specific_page.[sale_part#] and SalesUpdate.LocalPartNo=specific_page.[local_part#] and SalesUpdate.[Sale_Price]!=specific_page.[sale_price];";
         if (cmd.ExecuteScalar() != null)
         {
            cmd.CommandText = "UPDATE specific_page SET [sale_price] =@Sale_Price FROM SalesUpdate WHERE sale_part#=@Id1 and local_part#=@Id2 and specific_page.[sale_price]!= SalesUpdate.[Sale_Price];";
            cmd.ExecuteNonQuery();
         }
      }
   }
   MessageBox.Show("Successfully added.");
}
Posted 23-Nov-12 2:30am
Edited 23-Nov-12 2:42am
v3
Comments
digimanus at 23-Nov-12 8:34am
   
do you get any exceptions?
Can you change the commandtext and re-execute the command?
mubarakahmad at 23-Nov-12 8:38am
   
i used to get "The CLR has been unable to transition from COM context 0x.. to COM
context 0x.. for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long running operation without pumping Windows messages.
This situation generally has a negative performance impact and may even lead
to the application becoming non responsive or memory usage accumulating
continually over time. To avoid this problem, all single threaded apartment
(STA) threads should use pumping wait primitives (such as
CoWaitForMultipleHandles) and routinely pump messages during long running
operations." but i unchecked the context deadlock exception and it did not report again
Marcus Kramer at 23-Nov-12 8:39am
   
I'm not entirely sure it is a good idea to reuse the same command object over and over like you do.
For the sake of trying, create a separate command statement to be used with each of the ExecuteNonQuery calls.
Prathap Gangireddy at 23-Nov-12 9:06am
   
Why dont you use Stored procedures instead of Inline queries.Atleast there will be performance improvement.
digimanus at 23-Nov-12 10:54am
   
and use async calls.
better have 1 stored procedure that can update based on differences in 2 tables, the current table and the newvalues table
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

you are executing more then one query on the same command and that makes heavy loading , so on the same opened connection with same command you executing complicated queries that makes heavy loading, first try to separate your code to functions , such trying to check if exist in separate function in deferent command , then in other function do the update...
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi Mubarak,

You can move code into stored procedure and then you can execute stored procedure and this will be much faster.

2dn you can try with below logic.
if (str == "sale")
{
   for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
   {      
      if (ds.Tables[0].Rows[i]["Sr_No"] != null)
      {
         cmd.Parameters.AddWithValue("@Item", ds.Tables[0].Rows[i]["Sr_No"]);
         cmd.Parameters.AddWithValue("@SalePart", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Sale_Price", ds.Tables[0].Rows[i]["Sale_Price"]);
         cmd.Parameters.AddWithValue("@Partname", ds.Tables[0].Rows[i]["Sale_Part_Desc"]);
         cmd.Parameters.AddWithValue("@LocalPartNo", ds.Tables[0].Rows[i]["Local_Part_No"]);
         cmd.Parameters.AddWithValue("@Parent_Part_No ", ds.Tables[0].Rows[i]["Parent_Part_No"]);
         cmd.Parameters.AddWithValue("@Part_Type_TR_ENG", ds.Tables[0].Rows[i]["Part_Type_TR_ENG"]);
         cmd.Parameters.AddWithValue("@Id1", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Id2", ds.Tables[0].Rows[i]["Local_Part_No"]);
 
         string query = @"If Not Exists(select * from SalesUpdate where [Item#]=@Item)Begin insert into  SalesUpdate ([Item#],[SalePart],[Sale_Price] ,[Part name] ,[LocalPartNo],[Parent_Part_No],[Part_Type_TR_ENG])VALUES(@Item,@SalePart,@Sale_Price,@Partname,@LocalPartNo,@Parent_Part_No,@Part_Type_TR_ENG);End";
         cmd = new SqlCommand(query,con); //con is your opened sql connection.
         cmd.ExecuteNonQuery();
         cmd = new SqlCommand("select * from SalesUpdate CROSS JOIN specific_page where SalesUpdate.[SalePart]=specific_page.[sale_part#] and SalesUpdate.LocalPartNo=specific_page.[local_part#] and SalesUpdate.[Sale_Price]!=specific_page.[sale_price];", con);
         if (cmd.ExecuteScalar() != null)
         {
            cmd = new SqlCommand("UPDATE specific_page SET [sale_price] =@Sale_Price FROM SalesUpdate WHERE sale_part#=@Id1 and local_part#=@Id2 and specific_page.[sale_price]!= SalesUpdate.[Sale_Price];", con);
            cmd.ExecuteNonQuery();
         }
      }
   }
   MessageBox.Show("Successfully added.");
}
Hope this help you.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 403
1 F-ES Sitecore 355
2 Richard Deeming 270
3 CHill60 195
4 Richard MacCutchan 190
0 Sergey Alexandrovich Kryukov 723
1 Peter Leow 490
2 Maciej Los 475
3 DamithSL 470
4 OriginalGriff 437


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 24 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100