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

• Jesse C. Slicer: 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.
• Peter Csala: Your code seems a bit error-prone with DateTime.Parse and (DateTime). Please prefer TryParse method and as or is operators.
• BCdotWEB: Make your life simpler and use Dapper instead of ADO.NET.
• Shelby115: functionResult.ToString("MM-dd-yyyy) seems to be missing a quote.
• Gert Arnold: 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.

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

• Kiquenet: Can I generate C# code from User-Definde Function like usfGetMGAHolidayCalendar(@HolidayDate DATE) RETURNS DATE ?
• iSR5: @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