How to add a try catch for a stored procedure

Question

I have a stored procedure 'Class_set' which is triggered to when user attempts to add a class to the database. The stored procedure checks if there are any clashes in the database and if no clashes exist it inserts the class to the database.

Currently if a clash exists it throws a 51000 error but I want the error to be visible on the front end using a label or a panel to display the appropriate message. I tried to use try catch but it wasn't successful.

My Stored Procedure:

CREATE procedure dbo.Class_set ( 
   @moduledata int 
  , @startdatedata datetime 
  , @enddatedata datetime 
  , @classtypedata int 
  , @roomcodedata int 
  , @starttimedata int 
  , @endtimedata int 
  , @recurrencedata int 
  , @daydata int 
) as 
begin; 
  set nocount, xact_abort on; 
  /* temp table */ 
 select 
    DayId = @daydata 
  , ModuleId =@moduledata 
  , ClassTypeId = @classtypedata 
  , ClassScheduleStartTimeId = @starttimedata 
  , ClassScheduleEndTimeId = @endtimedata 
  , RoomCodeId = @roomcodedata 
  , StartTime= convert(datetime,c.Date) + s.StartTime 
  , EndTime = convert(datetime,c.Date) + e.EndTime 
  , RecurrenceId = @recurrencedata 
into #temp_class 
from Calendar c 
  cross apply ( 
    select StartTime = convert(datetime,ClassTime) 
    from ClassSchedule 
    where ClassScheduleId = @starttimedata 
    ) as s 
  cross apply ( 
   select EndTime = convert(datetime,ClassTime) 
   from ClassSchedule 
   where ClassScheduleId = @endtimedata 
   ) as e 
where c.Date >= @startdatedata 
  and c.Date <= @enddatedata 
  and c.isWeekDay = 1 
  and ( @recurrencedata != 2 
      or (@recurrencedata = 2 and c.DayOfWeek = @daydata) 
       ); 

/* check for conflicts */ 
if exists( 
  select 1 
  from dbo.[Class] c 
     inner join #temp_class t 
       on c.RoomCodeId = t.RoomCodeId 
      and t.EndTime > c.StartTime 
      and c.EndTime > t.StartTime 
  ) 
begin; 
    throw 51000, 'Room conflict exists',1; 
    return -1; 
    end; 

/* insert */ 
insert into dbo.[Class] (DayId, ModuleId, ClassTypeId 
    , ClassScheduleStartTimeId, ClassScheduleEndTimeId 
    , RoomCodeId, StartTime, EndTime, RecurrenceId ) 
select DayId, ModuleId, ClassTypeId 
   , ClassScheduleStartTimeId, ClassScheduleEndTimeId 
   , RoomCodeId, StartTime, EndTime, RecurrenceId 
from #temp_class 

end;
go

My method which calls the stored procedure:

  internal void insert_days( int daydata, int moduledata, int? recurrencedata, DateTime startdatedata, DateTime enddatedata, int classtypedata, int roomcodedata, int starttimedata, int endtimedata, string totalday)
    {

        {

            string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (SqlConnection myConnection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("Class_set", myConnection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@daydata", daydata);
                    cmd.Parameters.AddWithValue("@moduledata", moduledata);
                    cmd.Parameters.AddWithValue("@classtypedata", classtypedata);
                    cmd.Parameters.AddWithValue("@startdatedata", startdatedata);
                    cmd.Parameters.AddWithValue("@enddatedata", enddatedata);
                    cmd.Parameters.AddWithValue("@roomcodedata", roomcodedata);
                    cmd.Parameters.AddWithValue("@starttimedata", starttimedata);
                    cmd.Parameters.AddWithValue("@endtimedata", endtimedata);
                    cmd.Parameters.AddWithValue("@recurrencedata", recurrencedata);

                    myConnection.Open();
                    cmd.ExecuteNonQuery();
                }
            }


        }
    }

My attempt at the try catch:

  internal void insert_days(int daydata, int moduledata, int? recurrencedata, DateTime startdatedata, DateTime enddatedata, int classtypedata, int roomcodedata, int starttimedata, int endtimedata, string totalday) 
{ 
  { 
    try 
    { 
       string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; 

      SqlConnection myConnection = new SqlConnection(connectionString); 

     using (SqlCommand cmd = new SqlCommand("Class_set", myConnection)) 
        { 
        cmd.CommandType = CommandType.StoredProcedure; 
        cmd.Parameters.AddWithValue("@daydata", daydata); 
        cmd.Parameters.AddWithValue("@moduledata", moduledata); 
        cmd.Parameters.AddWithValue("@classtypedata", classtypedata); 
        cmd.Parameters.AddWithValue("@startdatedata", startdatedata); 
        cmd.Parameters.AddWithValue("@enddatedata", enddatedata); 
        cmd.Parameters.AddWithValue("@roomcodedata", roomcodedata); 
        cmd.Parameters.AddWithValue("@starttimedata", starttimedata); 
        cmd.Parameters.AddWithValue("@endtimedata", endtimedata); 
        cmd.Parameters.AddWithValue("@recurrencedata", recurrencedata); 

        myConnection.Open(); 
        cmd.ExecuteNonQuery(); 
    } 
} 
 catch (SqlException ex) 
  { 
      switch (ex.Number) 
       { 
         case 51000: 
         Console.WriteLine("Error: Clashes exist with your current Room selection"); 
         break; 

         default:
         Console.WriteLine("Success: No clashes exist with your current Room selection");            

      } 
   } 
 }  
}  

Image of current error thrown:

Answer 1

I was able to edit the Web.Config file to add customErrors which redirects the user to a new page on the website where I have explained the issue. It is not ideal as I would liked to have displayed something on the page they are currently on but this is the only solution I can find at the moment.

   <configuration>
      <system.web>
             <customErrors mode="On" defaultRedirect="ErrorPage.aspx?handler=customErrors%20section%20-%20Web.config">
              <error statusCode="510" redirect="ErrorPage.aspx?msg=404&amp;handler=customErrors%20section%20-%20Web.config"/>
              </customErrors>
       </system.web>
  </configuration>

Answer 2

Wrap your SQL in a try catch.

CREATE PROCEDURE dbo.Class_set ( 
   @moduledata int 
  , @startdatedata datetime 
  , @enddatedata datetime 
  , @classtypedata int 
  , @roomcodedata int 
  , @starttimedata int 
  , @endtimedata int 
  , @recurrencedata int 
  , @daydata int 
) AS 
BEGIN 
    set nocount, xact_abort on; 
    /* temp table */ 

    BEGIN TRY
        select 
            DayId = @daydata 
          , ModuleId =@moduledata 
          , ClassTypeId = @classtypedata 
          , ClassScheduleStartTimeId = @starttimedata 
          , ClassScheduleEndTimeId = @endtimedata 
          , RoomCodeId = @roomcodedata 
          , StartTime= convert(datetime,c.Date) + s.StartTime 
          , EndTime = convert(datetime,c.Date) + e.EndTime 
          , RecurrenceId = @recurrencedata 
        into 
            #temp_class 
        from 
            Calendar c 
            cross apply ( 
            select StartTime = convert(datetime,ClassTime) 
            from ClassSchedule 
            where ClassScheduleId = @starttimedata 
            ) as s 
            cross apply ( 
            select EndTime = convert(datetime,ClassTime) 
            from ClassSchedule 
            where ClassScheduleId = @endtimedata 
            ) as e 
        where 
            c.Date >= @startdatedata 
            and c.Date <= @enddatedata 
            and c.isWeekDay = 1 
            and ( @recurrencedata != 2 
                or (@recurrencedata = 2 and c.DayOfWeek = @daydata) 
            ); 

            /* insert */ 
            insert into dbo.[Class] (DayId, ModuleId, ClassTypeId 
                , ClassScheduleStartTimeId, ClassScheduleEndTimeId 
                , RoomCodeId, StartTime, EndTime, RecurrenceId ) 
            select DayId, ModuleId, ClassTypeId 
               , ClassScheduleStartTimeId, ClassScheduleEndTimeId 
               , RoomCodeId, StartTime, EndTime, RecurrenceId 
            from #temp_class 
    END TRY
    BEGIN CATCH
        THROW
    END CATCH;
END
GO

Add a scalar function to do the validation:

CREATE FUNCTION dbo.RoomConflict
(
    @RoomCode INT,
    @StartTime TIME(7),
    @EndTime TIME(7)
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT 
            COUNT(*) 
        FROM 
            dbo.Class A 
        WHERE 
            A.RoomCodeId = @RoomCode AND @EndTime > A.StartTime AND A.EndTime > @StartTime)
END
GO

Then add a constraint to the dbo.Class table:

ALTER TABLE [dbo].[Class] WITH NOCHECK ADD CONSTRAINT [CK_NoRoomConflict] CHECK (
    [dbo].[RoomConflict]([RoomCodeId],[StartTime],[EndTime]) = 0
)
GO
ALTER TABLE [dbo].[Class] CHECK CONSTRAINT [CK_NoRoomConflict]
GO