Call Scalar function in C#

Question

I have a scalar function which ran fine in sql. I need to call that function in c#. The scalar function is down below:

CREATE FUNCTION [dbo].[usfMGAHolidayCalendar](@MGAID INT, @MaskDate DATE)

RETURNS DATE
AS
/*Returns the NewFileMask*/
    BEGIN

        DECLARE @NewMaskDate DATETIME,
                @IsMGAHolidayCalendar INT = 0;

        SET @IsMGAHolidayCalendar =
        (
            SELECT COUNT(HolidayDate)
            FROM xml.MGAHolidayCalendar
            WHERE HolidayDate = @MaskDate
            AND MgaId = @MGAID  
        );

        IF @IsMGAHolidayCalendar > 0
                /*Return @NewMaskDate (@MaskDate + 1)*/
                SET @NewMaskDate = DATEADD(dd, 1,@MaskDate)
        
            ELSE
                /*Return the original @MaskDate as @NewMaskDate*/
                SET @NewMaskDate = @MaskDate

        /*Sometimes there are two holidays in a row (e.g. Thanksgiving & Thanksgiving Friday)*/
        /*Check the table for the NewMaskDate*/
        SET @IsMGAHolidayCalendar =
        (
            SELECT COUNT(HolidayDate)
            FROM xml.MGAHolidayCalendar
            WHERE HolidayDate = @NewMaskDate
            AND MgaId = @MGAID
        );

        IF @IsMGAHolidayCalendar = 1
                /*Return @NewMaskDate (@NewMaskDate + 1)*/
                SET @NewMaskDate = DATEADD(dd, 1,@NewMaskDate)
            ELSE
                /*Return the original @NewMaskDate as @NewMaskDate*/
                SET @NewMaskDate = @NewMaskDate

        RETURN @NewMaskDate;

I started calling the Scalar function in C# and this is what I have so far. I need some guidance/ help to completed it.

//Call dbo.usfGetMGAHolidayCalendar and return NewMaskDate//

    static void Main(string[] args)
    {
        //Set the connection string//

     try
        {
            //sql connection object
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //define the query text
                string query = @"SELECT dbo.usfGetMGAHolidayCalendar(@MGAID, @MaskDate) As NewMaskDate;";

                //parameter value will be set from command line
                SqlCommand cmd = new SqlParameter("dbo.usfGetMGAHolidayCalendar", conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MGAID", SqlDbType.Int);
                cmd.Parameters.Add("@MaskDate" , SqlDbType.Date);
               

Answer 1

I got your concern, as per your scenario you could try like below:

SQL Function:

CREATE FUNCTION CallFunctionFromCSharp (@EmpId INT)
RETURNS INT
AS
BEGIN
    DECLARE @EmpProjectNumber INT;

    SELECT @EmpProjectNumber =  (SELECT WsEmployeeNumber FROM WsEmployee WHERE WsEmployeeId =@EmpId)

    IF (@EmpProjectNumber IS  NULL)
        SET @EmpProjectNumber = 0;

    RETURN @EmpProjectNumber;
END

This function Return a number like below:

Call In C#

 using (var connection = new SqlConnection("Server=ServerName;Database=YourDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"))
                {
                    int employeeId = 1;
                    string projectNumber;
                    connection.Open();
                    var command = connection.CreateCommand();
                    command.CommandText = "SELECT dbo.CallFunctionFromCSharp(@EmpId) AS projectNumber";
                    command.Parameters.AddWithValue("@EmpId", employeeId);
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        projectNumber = reader["projectNumber"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
                      
                    }
                    reader.Close();
                    command.Dispose();
                    connection.Close();

                }

Note: You have two parameter so you can write like this: dbo.CallFunctionFromCSharp(@EmpId,@anotherParam) then pass the parameter like this command.Parameters.AddWithValue("@anotherParam", anotherParamValue);

Output:

Note: Be double check the return type and parameter type if you encounter any exception.

Hope it would help you to achive your goal.