Stored procedure and stored function with resultset

This example shows how to use a stored procedure that executes inside itself a parameterized SELECT query to a table and returns table records that satisfy the query condition as the list of corresponding complex type objects.

The script for creating a stored procedure and a table for the SQL Server DBMS is as follows:

CREATE TABLE dbo.Department (
  DeptNo int NOT NULL,
  DName varchar(14),
  Loc varchar(13),
  PRIMARY KEY (DeptNo)
)
GO
CREATE PROCEDURE dbo.SelectDepartmentByLocation(@Loc varchar(13))
WITH
EXECUTE AS CALLER
AS
SELECT * FROM dbo.Department WHERE Loc = @Loc;
GO

First, we create a model and drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click Yes to obtain metadata of procedure result set.

As a result we get a complex type and a method corresponding to the stored procedure:

Retrieve a complex type and a method

With the following parameters:

Customize parameters

If you want to map the procedure result to an existing complex type with property names different from the name of resultset columns, you can select this complex type in the method editor and then edit mapping of its properties to resultset columns in the Result Mapping Details dialog box. You may need it, for example, if you use the same complex type to map resultsets of several methods, which use different column names.

To open the Result Mapping Details dialog box, right-click the method and select Result Mapping from the shortcut menu. Then you may edit column names for the complex type properties. Note that this feature is not available for Stored procedure with multiple resultsets.

Result Mapping Details

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

C#:

public List<Department> SelectDepartmentByLocation (string Loc)
{

    List<Department> result = new List<Department>();
    DbConnection connection = this.Database.GetDbConnection();
    bool needClose = false;
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
        needClose = true;
    }

    try
    {
        using (DbCommand cmd = connection.CreateCommand())
        {
            if (this.Database.GetCommandTimeout().HasValue)
                cmd.CommandTimeout = this.Database.GetCommandTimeout().Value;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = @"dbo.SelectDepartmentByLocation";
            DbParameter LocParameter = cmd.CreateParameter();
            LocParameter.ParameterName = "Loc";
            LocParameter.Direction = ParameterDirection.Input;
            if (Loc != null)
            {
                LocParameter.Value = Loc;
            }
            else
            {
                LocParameter.DbType = DbType.String;
                LocParameter.Size = -1;
                LocParameter.Value = DBNull.Value;
            }
            cmd.Parameters.Add(LocParameter);

            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Department row = new Department();
                    if (!reader.IsDBNull(reader.GetOrdinal("DeptNo")))
                        row.DepartmentNumber = (long)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"DeptNo")), typeof(long));

                    if (!reader.IsDBNull(reader.GetOrdinal("DName")))
                        row.DepartmentName = (string)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"DName")), typeof(string));

                    if (!reader.IsDBNull(reader.GetOrdinal("Loc")))
                        row.DepartmentLocation = (string)Convert.ChangeType(reader.GetValue(reader.GetOrdinal(@"Loc")), typeof(string));

                    result.Add(row);
                }
            }
        }
    }
    finally
    {
        if (needClose)
            connection.Close();
    }
    return result;
}

If we need to retrieve the whole table, we do the following: first, we create a model and add an entity to it corresponding to the Department table, drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click No not to obtain metadata of procedure result set.

Then we double-click the method and in the Value Types dropdown list select the Department table:

Set Value Types as Department

The method has the following parameters:

Method parameters

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

C#:

public IList<Department> SelectDepartmentByLocation (string Loc)
{
    OAParameter LocParameter = new OAParameter();
        LocParameter.ParameterName = @"Loc";
            LocParameter.Direction = ParameterDirection.Input;
    if (Loc != null)
    {
        LocParameter.Value = Loc;
    }
    else
    {
        LocParameter.DbType = DbType.String;
        LocParameter.Size = -1;
        LocParameter.Value = DBNull.Value;
    }
    IList<Department> result = this.ExecuteQuery<Department>(@"dbo.SelectDepartmentByLocation", CommandType.StoredProcedure, LocParameter);
    return result;
}

Stored procedure and stored function with resultset

See also