1
\$\begingroup\$

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);
    }
}
\$\endgroup\$
8
  • 2
    \$\begingroup\$ 1. You don't need to explicitly .Close() the connection as the using statement will take care of that for you. However, 2. SqlCommand is also IDisposable, so its usage lifetime should be wrapped in using as well. \$\endgroup\$ Sep 1, 2021 at 22:47
  • \$\begingroup\$ Your code seems a bit error-prone with DateTime.Parse and (DateTime). Please prefer TryParse method and as or is operators. \$\endgroup\$ Sep 2, 2021 at 13:18
  • 1
    \$\begingroup\$ Make your life simpler and use Dapper instead of ADO.NET. \$\endgroup\$
    – BCdotWEB
    Sep 2, 2021 at 14:03
  • \$\begingroup\$ functionResult.ToString("MM-dd-yyyy) seems to be missing a quote. \$\endgroup\$
    – Shelby115
    Sep 3, 2021 at 0:54
  • \$\begingroup\$ A question that start with "I do not received files on holidays" is pretty puzzling. What files, how/where do you receive them, why not receive them anyway and process them later? In other words, it needs background. Maybe the problem also needs abstraction, i.e. reduction to function that is independent of whichever process it serves. As far as I can see it, you only need a function that returns the first date that's not in a holiday and is on or after a given date. Just a date, not "maskdate". The consumer of the function decides what to use it for. \$\endgroup\$ Sep 19, 2021 at 8:55

1 Answer 1

3
\$\begingroup\$

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);
}
\$\endgroup\$
2
  • \$\begingroup\$ Can I generate C# code from User-Definde Function like usfGetMGAHolidayCalendar(@HolidayDate DATE) RETURNS DATE ? \$\endgroup\$
    – Kiquenet
    Dec 1, 2022 at 20:04
  • 1
    \$\begingroup\$ @Kiquenet yes, this would be using CLR functions. In short, you can create a library class project, implement the required functions, then set it up to be used for SQL Server, then use the compiled file in SQL Server Assembly, and set it up as well. here is a good article about it sqlshack.com/getting-started-with-sql-server-clr-functions \$\endgroup\$
    – iSR5
    Dec 2, 2022 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.