locked
System get hanging after the execution of ExecuteSqlCommand , Please help to fix RRS feed

  • Question

  • User-1355965324 posted

    I am using the following  statement to insert the record ,  the statement is  working fine , but after   the execute , the system would be hanging. Any help to fix this problem would be very appreciated. The system get hanging only when give the breakpoint in ExecuteSQLCommand line to debug

    public long BulkUpdate(BulkAttendanceUpdateVM bulkAttendanceUpdateVM)
            {
                int logid = 0;
                var param = new SqlParameter[] { };
                try
                {
                    param = new SqlParameter[]
                    {
                         new SqlParameter() {
                                ParameterName = "@LogID",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Size = 100,
                                Direction = System.Data.ParameterDirection.Output,
                                Value = bulkAttendanceUpdateVM.Id
                            },
                         new SqlParameter() {
                                ParameterName = "@EmployeeID",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.EmployeeID
                            },
                         new SqlParameter() {
                                ParameterName = "@AttendanceFromDate",
                                SqlDbType =  System.Data.SqlDbType.DateTime,
                                Direction = System.Data.ParameterDirection.Input,
                                Value =Convert.ToDateTime(bulkAttendanceUpdateVM.FromDate)
                            },
                         new SqlParameter() {
                                ParameterName = "@AttendanceToDate",
                                SqlDbType =  System.Data.SqlDbType.DateTime,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = Convert.ToDateTime(bulkAttendanceUpdateVM.ToDate)
                            },
                          new SqlParameter() {
                                ParameterName = "@UserID",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.UserId
                            },
                        
    
                          new SqlParameter() {
                                ParameterName = "@IsNormalAttendance",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsNormalAttendance
                            },
    
                          new SqlParameter() {
                                ParameterName = "@NormalHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.NormalHrs
                            },
    
                           new SqlParameter() {
                                ParameterName = "@IsSickMarked",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsSick
                            },
    
                          new SqlParameter() {
                                ParameterName = "@SickHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.SickHrs
                            },
    
                            new SqlParameter() {
                                ParameterName = "@IsHolidayMarked",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsHoliday
                            },
    
                          new SqlParameter() {
                                ParameterName = "@HolidayHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.HolidayHrs
                          },
                        
    
                           new SqlParameter() {
                                ParameterName = "@IsDayOffMarked",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsDayOff
                            },
                            new SqlParameter() {
                                ParameterName = "@DayOffHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.DayOffHrs
                            },
    
                             new SqlParameter() {
                                ParameterName = "@IsMaternityLeave",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsMaternity
                            },
                            new SqlParameter() {
                                ParameterName = "@MaternityHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.MaternityHrs
                            },
                             new SqlParameter() {
                                ParameterName = "@IsTimeOff",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsTimeOff
                            },
                            new SqlParameter() {
                                ParameterName = "@TimeOffHrs",
                                SqlDbType =  System.Data.SqlDbType.Decimal,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.TimeOffHrs
                            },
                             new SqlParameter() {
                                ParameterName = "@IsDeductionMarked",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsDeducted
                             },
                              new SqlParameter() {
                                ParameterName = "@Remark",
                                SqlDbType =  System.Data.SqlDbType.NVarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.Comment
                             },
    
                             new SqlParameter() {
                                ParameterName = "@IsHolidayApproved",
                                SqlDbType =  System.Data.SqlDbType.Bit,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = bulkAttendanceUpdateVM.IsHolidayApproved
                             }
                    };
    
                      logid = _db.Database.ExecuteSqlCommand("dbo.goInsertAttendanceByTwoDates @LogID OUTPUT, " +
                        "@EmployeeID,"  +
                        "@AttendanceFromDate," +
                        "@AttendanceToDate," +
                        "@UserID," +
                        "@IsNormalAttendance," +
                        "@NormalHrs," +
                        "@IsSickMarked," +
                        "@SickHrs," +
                        "@IsHolidayMarked," +
                        "@HolidayHrs," +
                        "@IsDayOffMarked," +
                        "@DayOffHrs," +
                        "@IsMaternityLeave," +
                        "@MaternityHrs," +
                        "@IsTimeOff," +
                        "@TimeOffHrs,"+
                        "@IsDeductionMarked,"+
                        "@Remark," +
                        "@IsHolidayApproved",
                        param);
                }
                catch(Exception ex)
                {
                    var error = ex.ToString();
                }
                return logid;
            }

    Pol

    Wednesday, September 9, 2020 8:36 AM

All replies

  • User475983607 posted

    The code shown hides exceptions, it always returns 0, and it is incomplete.  We can't review the View, Model, or stored procedure code. 

    If the procedure is taking a long time then use standard SQL debugging tools (SQL Profiler) to figure out what is going on with the procedure.  

    Wednesday, September 9, 2020 10:24 AM
  • User-1355965324 posted

    There is no any problem with the program When I debug ExecuteSqlCommand  it would be hanging . But if I remove the breakepoint from there it will work without  hangs

    Wednesday, September 9, 2020 10:56 AM
  • User475983607 posted

    polachan

    There is no any problem with the program When I debug ExecuteSqlCommand  it would be hanging . But if I remove the breakepoint from there it will work without  hangs

    Yes, the code shown has problems.  The catch block hides errors and the ExecuteSqlCommand returns the records affected not an id, typically FromSqlRaw is used to execute a stored procedure  The BulkInsert returns zero if there is an exception.  I assume the code that calls this function also has logical problems.

    What happens if you place the break point on...

    return logid;

    Or place the break point after the code that calls the method.  What is the value of returned by the method?  Are you expecting an actual Id or the records affected?

    You still have not shared the most important bits of code; View, Model, and procedure...

    Wednesday, September 9, 2020 11:33 AM
  • User-474980206 posted

    Along with your other issues, you should never use sync database calls with asp.net core.

    Wednesday, September 9, 2020 2:40 PM