Stored Function with Scalar Result

This example shows how to use a stored function that executes some operations and returns a scalar value as a result, for example, like this one:

The script for creating a stored function for the SQL Server DBMS is as follows:

CREATE FUNCTION dbo.Sum(@p1 int, @p2 int)
RETURNS int
AS
BEGIN
RETURN @p1 + @p2;
END
GO

Create a model and add a new method to it corresponding to the function (either at the stage of creation at the Select database objects page of Create Model Wizard, or by dragging the corresponding stored function from the Database Explorer window to the diagram area or in the Model Explorer window of an already existing model).

As a result, we will get a method in the model corresponding to the stored function:

images_SFwithScalarResultGeneralTDA

With the following parameters:

images_SFwithScalarResultParametersTDA

As a result of code generation for the model, the corresponding method will be generated having a signature close to the relevant stored function:

C#:

        public System.Nullable<int> Sum (System.Nullable<int> p1, System.Nullable<int> p2)
        {
            OAParameter p1Parameter = new OAParameter();
                p1Parameter.ParameterName = @"p1";
                  p1Parameter.Direction = ParameterDirection.Input;
            if (p1.HasValue)
            {
                p1Parameter.Value = p1.Value;
            }
            else
            {
                p1Parameter.DbType = DbType.Int32;
                p1Parameter.Size = -1;
                p1Parameter.Value = DBNull.Value;
            }
            OAParameter p2Parameter = new OAParameter();
                p2Parameter.ParameterName = @"p2";
                  p2Parameter.Direction = ParameterDirection.Input;
            if (p2.HasValue)
            {
                p2Parameter.Value = p2.Value;
            }
            else
            {
                p2Parameter.DbType = DbType.Int32;
                p2Parameter.Size = -1;
                p2Parameter.Value = DBNull.Value;
            }
            OAParameter returnValueParameter = new OAParameter();
            returnValueParameter.Direction = ParameterDirection.ReturnValue;
            returnValueParameter.DbType = DbType.Int32;
            returnValueParameter.Size = -1;
            this.ExecuteNonQuery(@"dbo.[Sum]", CommandType.StoredProcedure, p1Parameter, p2Parameter, returnValueParameter);
            System.Nullable<int> result = (System.Nullable<int>)returnValueParameter.Value;
            return result;
        }

Now it is possible to use this stored function in the application with the help of method wrapper. This allows working with the stored function with all possible convenience, as wrapper methods are strongly typed, are found by IntelliSense and have signatures close to the corresponding stored functions.

Stored Function with Scalar Result

See Also