读取excel表并在sql server中插入数据但代码挂起

问题描述

您好我正在阅读excel表格并在数据库中插入数据，条件是如果特定里程碑的资源存在，则记录将更新总时间，否则在计算总数后将插入新记录资源为特定里程碑工作的小时数。但代码挂了。

这是我写的代码。

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.

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();
          }
      }

逐行读取excel文件的代码。

Code to read excel file row by row.

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:)

推荐答案

这篇关于读取excel表并在sql server中插入数据但代码挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！