Get result of a scalar-valued SQL function from C#

Question

I do not receive files on holidays; I only receive files on regular days—i.e., non-holidays. I want to skip 2021-09-06 since that's a holiday (Labor Day) and I know I will not receive any files. Instead, I want to get a new file on 2021-09-07, since I will receive a file on that day.

I created an automated skip logic function for holidays which runs fine in SQL. Now, I want to call that same function from C#, and I want to make sure the C# code is correct.

This is the scalar-valued SQL function. I ran the function in SQL, and it returns the new date 2021-09-07.

IF OBJECT_ID('dbo.usfGetMGAHolidayCalendar') IS NOT NULL
    DROP FUNCTION dbo.usfGetMGAHolidayCalendar;

GO
CREATE FUNCTION dbo.usfGetMGAHolidayCalendar(@HolidayDate DATE)

RETURNS DATE
AS

BEGIN

DECLARE @MGAID INT = 1,
        @SCJMGAID INT=8,
        @ARSMGAID INT=16,
        @MaskDate DATETIME = '2021-09-06', /*Single day holiday example*/
        --@MaskDate DATETIME = '2021-11-25', /*Two day holiday example*/
        @NewMaskDate DATETIME,
        @IsMGAHolidayCalendar INT = 0;

SET @IsMGAHolidayCalendar=
(
    SELECT COUNT(HolidayDate)
    FROM xml.MGAHolidayCalendar
    WHERE HolidayDate = @MaskDate

);

IF @IsMGAHolidayCalendar > 0
          
        SET @NewMaskDate= DATEADD(dd, 1,@MaskDate)
    ELSE
        
        SET @NewMaskDate=@MaskDate

SET @IsMGAHolidayCalendar =
(

    SELECT COUNT(HolidayDate)
    FROM   xml.MGAHolidayCalendar
    WHERE HolidayDate=@NewMaskDate

);

 
IF @IsMGAHolidayCalendar = 1
         
         SET @NewMaskDate = DATEADD(dd, 1,@NewMaskDate)
    ELSE
        
        SET @NewMaskDate = @NewMaskDate

RETURN @NewMaskDate;

END;
GO

Now, I'm calling that function from C#. I want the function to return the NewFileMask in this date format, mm-dd-yyyy. I don't want the time to be included in the date.

This is what I did, but I am not sure if this is correct.

static void Main(string[] args)
{
    // Set the connection string//
    string connString = @"Server=.\SQL2k17; Database = SampleDB; Trusted_Connection = True;";

    try
    {
        // sql connection object
        using (SqlConnection conn = new SqlConnection(connString))
        {
            // define the query text
            string query = @"SELECT DISTINCT dbo.usfGetMGAHolidayCalendar(@HolidayDate) AS NewFileMask;";

            // define the SqlCommand object
            SqlCommand cmd = new SqlCommand(query, conn);

            // parameter value will be set from command line
            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@HolidayDate";
            param1.SqlDbType = SqlDbType.Date;
            param1.Value = "2021-09-07";
                
            // pass parameter to the SQL Command
            cmd.Parameters.Add(param1);

            // open connection
            conn.Open();

            // execute the SQLCommand
            DateTime functionResult = (DateTime)cmd.ExecuteScalar();

            Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
            Console.WriteLine("Retrieved result:");

            // display retrieved result
            Console.WriteLine("NewFileMask:{0}", functionResult.ToString("MM-dd-yyyy"));

            // close connection
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        // display error message
        Console.WriteLine("Exception: " + ex.Message);
    }
}

Answer 1

Your SQL function would only covers 1-day holiday, how about multi-day holidays like Memorial Day, Thanksgiving ..etc.?

To cover them, you will need to get the maximum total holidays in your company, and use that number to your query.

For example, say your company's holidays are usually 1-3 days, and only one holiday that is 9-day holiday. Then, what you can do in your function, is to get the given date @HolidayDate, add 9 days to it, then create a list of dates using CTE covering these 9 days range. Then, check them against xml.MGAHolidayCalendar excluding any matched dates. Finally, get the minimum date (which will be the next working day).

Here is an example :

DECLARE
    -- this should be the official max of total holidays in your company for a particular holiday.
    -- here I added 9 days as example of Christmas holiday + New Year (from dec 25th, to Jan 3)
    @EndDate DATE = DATEADD(DAY, 9, @HolidayDate) 
,   @Result DATE  

;WITH CTE AS (

    SELECT @StartDate WorkingDay
    UNION ALL 
    SELECT DATEADD(DAY, 1, WorkingDay) WorkingDay   
    FROM CTE 
    WHERE WorkingDay < @EndDate
)
SELECT
    @Result = MIN(WorkingDay)
FROM CTE 
WHERE 
    WorkingDay NOT IN (
    SELECT
        HolidayDate 
    FROM 
        xml.MGAHolidayCalendar
    WHERE
        HolidayDate >= @HolidayDate 
    AND HolidayDate <= @EndDate
)

/*
    IF the given @HolidayDate is not present in xml.MGAHolidayCalendar
    Then it's a working day and the @Result should be NULL
*/

RETURN ISNULL(@Result, @HolidayDate)

For the C# part, your work is fine, however, there some points needs to be mention here :

1. you should always have a better naming for your variables, and avoid shortcuts, like conn should be connection, cmd should be command ..etc. the reason is short names are easy to miss, and may not be readable enough.
2. Don't use Close(), Dispose() inside using blocks. as the using blocks will do that for you.
3. Don't cast object without validating its value first. as the object might have a different value that is not compatible with the casting type.
4. Since you're calling a scalar function, it would be better if you create a method that accepts DateTime and return DateTime, this would make it reusable.

Here is a revision proposal :

try
{
    using(var connection = new SqlConnection(connString))
    using(var command = = new SqlCommand("SELECT DISTINCT dbo.usfGetMGAHolidayCalendar(@HolidayDate) AS NewFileMask;", connection))
    {
        // parameter value will be set from command line
        SqlParameter param1 = new SqlParameter();
        param1.ParameterName = "@HolidayDate";
        param1.SqlDbType = SqlDbType.Date;
        param1.Value = "2021-09-07";
            
        // pass parameter to the SQL Command
        command.Parameters.Add(param1);
        
        connection.Open();
        var functionResult = cmd.ExecuteScalar();

        Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
        Console.WriteLine("Retrieved result:");
        
        if(DateTime.TryParse(functionResult?.ToString(), out DateTime parsedResult))
        {
            // display retrieved result
            Console.WriteLine("NewFileMask:{0}", parsedResult.ToString("MM-dd-yyyy"));
        }
        else
        {
            Console.WriteLine("Error parsing the returned value : {0}", functionResult?.ToString());
        }
        
    }

}
catch (Exception ex)
{
    // display error message
    Console.WriteLine("Exception: " + ex.Message);
}