How to: Return Rowsets

This example returns a rowset from the database, and includes an input parameter to filter the result.

When you execute a stored procedure that returns a rowset, you use a result class that stores the returns from the stored procedure. For more information, see Analyzing LINQ to SQL Source Code.

Example

The following example represents a stored procedure that returns rows of customers and uses an input parameter to return only those rows that list "London" as the customer city. The example assumes an enumerable CustomersByCityResult class.

CREATE PROCEDURE [dbo].[Customers By City]  
    (@param1 NVARCHAR(20))  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
    SELECT CustomerID, ContactName, CompanyName, City from Customers  
        as c where c.City=@param1  
END  

[Function(Name="dbo.Customers By City")]
public ISingleResult<CustomersByCityResult> CustomersByCity([Parameter(DbType="NVarChar(20)")] string param1)
{
    IExecuteResult result = this.ExecuteMethodCall(this,         ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
    return ((ISingleResult<CustomersByCityResult>)(result.ReturnValue));
}

// Call the stored procedure.
void ReturnRowset()
{
    Northwnd db = new Northwnd(@"c:\northwnd.mdf");

    ISingleResult<CustomersByCityResult> result =
        db.CustomersByCity("London");

    foreach (CustomersByCityResult cust in result)
    {
        Console.WriteLine($"CustID={cust.CustomerID}; City={cust.City}");
    }
}

See also

• Stored Procedures
• Downloading Sample Databases