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

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*/
@IsMGAHolidayCalendar INT = 0;

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

);

IF @IsMGAHolidayCalendar > 0

ELSE

SET @IsMGAHolidayCalendar =
(

SELECT COUNT(HolidayDate)
FROM   xml.MGAHolidayCalendar

);

IF @IsMGAHolidayCalendar = 1

ELSE

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

// 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

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

• 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. Sep 1 at 22:47
• Your code seems a bit error-prone with DateTime.Parse and (DateTime). Please prefer TryParse method and as or is operators. Sep 2 at 13:18
• functionResult.ToString("MM-dd-yyyy) seems to be missing a quote. Sep 3 at 0:54
• 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. Sep 19 at 8:55

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
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

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
}
else
{
Console.WriteLine("Error parsing the returned value : {0}", functionResult?.ToString());
}

}

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