Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
Hi I am reading an excel sheet and inserting the data in db on the condition that if the resource exists for a particular milestone the record will be updated with total no of hours else a new record will be inserted after calculating the total no of hours worked by resource for a particular milestone. But code hangs.
Here is the code I have written.

C#
public DateTime LastSaveDate()
       {
           DateTime date = new DateTime();
           commandText = "select top 1 LAST_UPDATED_DATE from GSA_TIME_ENTRY order by LAST_UPDATED_DATE desc";
           SqlCommand sqlCmd = new SqlCommand();
           sqlCmd.CommandText = commandText;
           sqlCmd.Connection = new SqlConnection(connstring);
           sqlCmd.CommandType = System.Data.CommandType.Text;
           sqlCmd.Connection.Open();
           date = Convert.ToDateTime(sqlCmd.ExecuteScalar());
           sqlCmd.Connection.Close();
           return date;
       }

<pre lang="C#">public void UpsertRecords(DataTable dt)
      {
              SqlCommand sqlCmd = new SqlCommand();
              string[] milestoneProject;
          try
          {
              foreach (DataRow dr in dt.Rows)
              {
                  int count = CheckRecord(dr[2].ToString(), dr[0].ToString());
                  if (count > 0)
                  {
                      commandText = "Select Actual_Hours from GSA_TIME_ENTRY where [Resource_Name]=@resource and [Milestone]=@milestone";
                      SqlCommand sqlcmd = new SqlCommand();
                      sqlcmd.CommandText = commandText;
                      sqlcmd.Connection = new SqlConnection(connstring);
                      sqlcmd.CommandType = System.Data.CommandType.Text;
                      sqlcmd.Connection.Open();

                      sqlcmd.Parameters.AddWithValue("@resource", dr[2].ToString());
                      sqlcmd.Parameters.AddWithValue("@milestone", dr[0].ToString());
                      actualHours = Convert.ToDecimal(sqlcmd.ExecuteScalar());

                      var conn = new SqlConnection(connstring);
                      conn.Open();
                      actualHours += Convert.ToDecimal(dr[8]);
                      SqlCommand update = new SqlCommand("update GSA_TIME_ENTRY set [Actual_Hours] =@actual_hours, [Last_Updated_Date]= @date where [Resource_Name]=@resource and [Milestone]=@milestone", conn);
                      update.Parameters.AddWithValue("actual_hours", actualHours);
                      update.Parameters.AddWithValue("@resource", dr[2].ToString());
                      update.Parameters.AddWithValue("@milestone", dr[0].ToString());
                      update.Parameters.AddWithValue("@date", DateTime.Now);
                      update.ExecuteReader();
                  }

                  else
                  {
                      actualHours = Convert.ToDecimal(dr[8]);
                      commandText = "Insert into GSA_TIME_ENTRY(Resource_Name,Client_Code,Client_Name,Project_Code,Project,Milestone,Mod_num,Mod_name,Actual_Hours,Last_Updated_Date,Case_Number,WR_Number,Change_Request,Category,Created_Date) Values(@val1,@val2,@val3,@val4,@val5,@val6,@val7,@val8,@val9,@val10,@val11,@val12,@val13,@val14,@val15)";
                      sqlCmd.Parameters.AddWithValue("@val1", dr[2].ToString());//Resouce Name
                      milestoneProject = dr[1].ToString().Split('-');
                      if (milestoneProject.Count() == 5)
                      {
                          if (projectCode.IsMatch(milestoneProject[4]))
                              sqlCmd.Parameters.AddWithValue("@val4", milestoneProject[4]);//Project Code
                          else
                              sqlCmd.Parameters.AddWithValue("@val4", 0);//Project Code
                      }
                      else
                      {
                          if (projectCode.IsMatch(milestoneProject[3]))
                              sqlCmd.Parameters.AddWithValue("@val4", milestoneProject[3]);//Project Code
                          else
                              sqlCmd.Parameters.AddWithValue("@val4", 0);//Project Code
                      }
                                            sqlCmd.Parameters.AddWithValue("@val6", dr[0].ToString());//Milestone
                   
                      sqlCmd.Parameters.AddWithValue("@val9", dr[8]);//actual_hours
                      sqlCmd.Parameters.AddWithValue("@val10", Convert.ToDateTime(dr[5]));//Last update date
                     
                      sqlCmd.CommandText = commandText;
                      sqlCmd.Connection = new SqlConnection(connstring);
                      sqlCmd.CommandType = System.Data.CommandType.Text;
                      sqlCmd.Connection.Open();
                      sqlCmd.ExecuteNonQuery();//sqlCmd.DataReader
                      sqlCmd.Parameters.Clear();

                      Console.WriteLine("Record Inserted successfully");
                  }
                  sqlCmd.Connection.Close();
              }
          }
          finally
          {
              sqlCmd.Connection.Close();
          }
      }


Code to read excel file row by row.
C#
private DataTable FilterDateSet(DateTime from, DateTime to, DataSet ds)
        {

            DataTable table = ds.Tables[0].AsEnumerable().Where(r => r.Field<DateTime?>("Timecard: Last Modified Date") >= from.Date && r.Field<DateTime?>("Timecard: Last Modified Date") <= to.Date).AsDataView().ToTable();
            foreach (DataRow dr in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    if (dr[i] == null)
                        dr[i] = "0";
                }
            }
            return table;
        }

Kindly help.Thanks in advance:)
Posted
Comments
[no name] 4-Dec-15 2:39am    
I suspect that your excel sheet more records. How many records are there?
Divya112 4-Dec-15 3:43am    
Hi Manas,
The excel sheet has some 3000 records only.
CHill60 4-Dec-15 6:12am    
Where does it "hang"?
Divya112 4-Dec-15 9:47am    
While running the application the code keeps on executing and later on it provides an error of connection timed out.
I have specified the connectiontimeout property in connection string=500.
So I am not sure where the code hangs.
CHill60 4-Dec-15 10:12am    
Are any records inserted into the table at all?
Have you tried using break points to determine exactly where the problem is?

1 solution

It's not a good idea to open a new connection for each sqlcommand.
Make your sqlconnection and sqlcommand global and reuse it by changing the parameters.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900