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);
}
}
.Close()
the connection as theusing
statement will take care of that for you. However, 2.SqlCommand
is alsoIDisposable
, so its usage lifetime should be wrapped inusing
as well. \$\endgroup\$DateTime.Parse
and(DateTime)
. Please preferTryParse
method andas
oris
operators. \$\endgroup\$functionResult.ToString("MM-dd-yyyy)
seems to be missing a quote. \$\endgroup\$