The stored procedure of the LINQ to SQL statement (20), linqsql

The stored procedure of the LINQ to SQL statement (20), linqsql

In programming, we usually need some stored procedures. How can we use these stored procedures in LINQ to SQL? It may be simpler than the original one. The following describes several stored procedures in the NORTHWND. MDF database.

1. scalar return

The stored procedure named Customers Count By Region in the database. This stored procedure returns the number of "WA" regions where the customer is located.

ALTER PROCEDURE [dbo].[NonRowset]    (@param1 NVARCHAR(15))ASBEGIN    SET NOCOUNT ON;     DECLARE @count int     SELECT @count = COUNT(*)FROM Customers      WHERECustomers.Region = @Param1     RETURN @countEND

We just need to drag this stored procedure into the O/R designer and it automatically generates the following code segment:

[Function(Name = "dbo.[Customers Count By Region]")]public int Customers_Count_By_Region([Parameter(DbType = "NVarChar(15)")] string param1){    IExecuteResult result = this.ExecuteMethodCall(this,    ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);    return ((int)(result.ReturnValue));}

You can call it directly when needed, for example:

int count = db.CustomersCountByRegion("WA");Console.WriteLine(count);

Statement Description: This instance uses the stored procedure to return the number of customers in the "WA" region.

2. Single result set

Returns a row set from the database and contains input parameters for filtering results. When we execute the stored procedure of the returned row set, we will use the result class, which stores the results returned from the stored procedure.

The following example shows a stored procedure. The stored procedure returns the customer rows and uses the input parameter to return only the fixed columns of the rows that column "London" as the customer city.

ALTER PROCEDURE [dbo].[Customers By City]     -- Add the parameters for the stored procedure here     (@param1 NVARCHAR(20))ASBEGIN     -- 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=@param1END

Drag to the O/R designer to automatically generate the following code segment:

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

We use the following code to call:

ISingleResult<Customers_By_CityResult> result = db.Customers_By_City("London");foreach (Customers_By_CityResult cust in result){    Console.WriteLine("CustID={0}; City={1}", cust.CustomerID,        cust.City);}

Statement Description: This instance uses the stored procedure to return the customer's CustomerID and City in London.

3. Multiple Single result sets with possible shapes

When a stored procedure can return multiple result shapes, the return type cannot be strongly typed into a single projection shape. Although LINQ to SQL can generate all possible projection types, it cannot know the order in which they will be returned. The ResultTypeAttribute attribute is applicable to stored procedures that return multiple result types. It is used to specify a set of types that can be returned by this process.

In the following SQL code example, The result shape depends on the input (param1 = 1 or param1 = 2 ). We do not know which projection is returned first.

ALTER PROCEDURE [dbo].[SingleRowset_MultiShape]     -- Add the parameters for the stored procedure here     (@param1 int )ASBEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     if(@param1 = 1)     SELECT * from Customers as c where c.Region = 'WA'     else if (@param1 = 2)     SELECT CustomerID, ContactName, CompanyName from      Customers as c where c.Region = 'WA'END

Drag to the O/R designer to automatically generate the following code segment:

[Function(Name="dbo.[Whole Or Partial Customers Set]")]public ISingleResult<Whole_Or_Partial_Customers_SetResult> Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")] System.Nullable<int> param1){    IExecuteResult result = this.ExecuteMethodCall(this,     ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);    return ((ISingleResult<Whole_Or_Partial_Customers_SetResult>)    (result.ReturnValue));}

However, VS2008 recognizes a multi-result set stored procedure as a single-result set stored procedure. We need to manually modify the default generated code to return multiple result sets, as shown in the following code:

[Function(Name="dbo.[Whole Or Partial Customers Set]")][ResultType(typeof(WholeCustomersSetResult))][ResultType(typeof(PartialCustomersSetResult))]public IMultipleResults Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")] System.Nullable<int> param1){    IExecuteResult result = this.ExecuteMethodCall(this,     ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);    return ((IMultipleResults)(result.ReturnValue));}

We define two partial classes respectively to specify the returned type. The WholeCustomersSetResult class is as follows:

The PartialCustomersSetResult class is as follows:

In this way, you can use the following code to directly call and return their respective result sets.

// Return all Customer result sets IMultipleResults result = db. whole_Or_Partial_Customers_Set (1); IEnumerable <WholeCustomersSetResult> shape1 = result. getResult <WholeCustomersSetResult> (); foreach (WholeCustomersSetResult compName in shape1) {Console. writeLine (compName. companyName);} // returns some Customer result sets result = db. whole_Or_Partial_Customers_Set (2); IEnumerable <PartialCustomersSetResult> shape2 = result. getResult <PartialCustomersSetResult> (); foreach (PartialCustomersSetResult con in shape2) {Console. writeLine (con. contactName );}

Statement Description: This instance uses the stored procedure to return a group of customers in the "WA" region. The returned result set shape depends on the input parameters. If the parameter is equal to 1, all customer attributes are returned. If the parameter is equal to 2, The ContactName attribute is returned.

4. Multiple result sets

This stored procedure can generate multiple results shapes, but we already know the returned sequence of the results.

The following is a stored procedure Get Customer And Orders that returns multiple result sets in order. Return the customer whose ID is "SEVES" and all their orders.

ALTER PROCEDURE [dbo].[Get Customer And Orders](@CustomerID nchar(5))    -- Add the parameters for the stored procedure hereASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    SELECT * FROM Customers AS c WHERE c.CustomerID = @CustomerID      SELECT * FROM Orders AS o WHERE o.CustomerID = @CustomerIDEND

Drag the code to the designer as follows:

[Function(Name="dbo.[Get Customer And Orders]")]public ISingleResult<Get_Customer_And_OrdersResult>Get_Customer_And_Orders([Parameter(Name="CustomerID",DbType="NChar(5)")] string customerID){     IExecuteResult result = this.ExecuteMethodCall(this,     ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);     return ((ISingleResult<Get_Customer_And_OrdersResult>)     (result.ReturnValue));}

Similarly, we need to modify the automatically generated code:

[Function(Name="dbo.[Get Customer And Orders]")][ResultType(typeof(CustomerResultSet))][ResultType(typeof(OrdersResultSet))]public IMultipleResults Get_Customer_And_Orders([Parameter(Name="CustomerID",DbType="NChar(5)")]string customerID){    IExecuteResult result = this.ExecuteMethodCall(this,    ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);    return ((IMultipleResults)(result.ReturnValue));}

Similarly, you need to manually write the class so that the stored procedure returns its own result set.

CustomerResultSet class

OrdersResultSet class

In this case, you only need to call it.

IMultipleResults result = db. get_Customer_And_Orders ("SEVES"); // return the IEnumerable of the Customer result set <CustomerResultSet> customer = result. getResult <CustomerResultSet> (); // return the Orders result set IEnumerable <OrdersResultSet> orders = result. getResult <OrdersResultSet> (); // here, we read the data foreach (CustomerResultSet cust in customer) {Console. writeLine (cust. customerID );}

Statement Description: This instance uses the stored procedure to return the customer's "SEVES" and all its orders.

5. With output parameters

LINQ to SQL maps the output parameter to the reference parameter. For the value type, it declares that the parameter can be null.

The following example contains a single input parameter (customer ID) and returns an output parameter (total sales of the customer ).

ALTER PROCEDURE [dbo].[CustOrderTotal] @CustomerID nchar(5),@TotalSales money OUTPUTASSELECT @TotalSales = SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY)FROM ORDERS O, "ORDER DETAILS" ODwhere O.CUSTOMERID = @CustomerID AND O.ORDERID = OD.ORDERID

The generated code is as follows:

[Function(Name="dbo.CustOrderTotal")]public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")]string customerID,[Parameter(Name="TotalSales", DbType="Money")]  ref System.Nullable<decimal> totalSales){    IExecuteResult result = this.ExecuteMethodCall(this,    ((MethodInfo)(MethodInfo.GetCurrentMethod())),    customerID, totalSales);    totalSales = ((System.Nullable<decimal>)    (result.GetParameterValue(1)));    return ((int)(result.ReturnValue));}

We use the following statement to call this stored procedure: Note: The output parameters are passed by reference to support the "in/out" parameter scheme. In this case, the parameter is only "out ".

decimal? totalSales = 0;string customerID = "ALFKI";db.CustOrderTotal(customerID, ref totalSales);Console.WriteLine("Total Sales for Customer '{0}' = {1:C}", customerID, totalSales);

Statement Description: This instance uses the stored procedure that returns the Out parameter.

Now let's talk about it. The same is true for its addition, deletion, and modification operations. I believe that you have understood the stored procedure through these five instances.

The article is very good. I want to repost it all, so I switched it! Thank you for your patience.