Shulou(Shulou.com)06/02 Report--

This article mainly explains "what the LINQ to SQL stored procedure is like". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how the LINQ to SQL stored procedure is.

When we write programs, we often need some stored procedures, how to use LINQ to SQL stored procedures? Maybe it's simpler than the original one. Let's take a look at a few stored procedures that come with the NORTHWND.MDF database.

Scalar return of 1.LINQ to SQL stored procedure

In the database, there is a stored procedure called Customers Count By Region. This stored procedure returns the number of customers in the "WA" area.

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

END all we have to do is drag the stored procedure into the Omax R designer, which automatically generates the following code snippet:

[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))

When we need it, we can just call it directly, for example:

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

Statement description: this instance uses stored procedures to return the number of customers in the "WA" area.

A single result set of 2.LINQ to SQL stored procedures

Returns a rowset from the database and contains input parameters for filtering results. When we execute a stored procedure that returns a rowset, we use the result class, which stores the results returned from the stored procedure.

The following example represents a stored procedure that returns customer rows and uses input parameters to return only fixed columns of those rows that list "London" as the customer city.

ALTER PROCEDURE [dbo]. [Customers By City]-Add the parameters for the stored procedure here (@ 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 is dragged into the Omax R designer, which automatically generates the following code snippet:

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

We call it with the following code:

ISingleResult 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 example uses stored procedures to return the CustomerID and City of customers in London.

A single result set of multiple possible shapes of 3.LINQ to SQL stored procedures

When a stored procedure can return multiple result shapes, the return type cannot be strongly typed as a single projection shape. Although LINQ to SQL can generate all possible projection types, it cannot know in what order they will be returned. The ResultTypeAttribute property applies to stored procedures that return multiple result types to specify a collection of types that the procedure can return.

In the following SQL code example, the resulting shape depends on the input (param1 = 1 or param1 = 2). We don't know which projection to return first.

ALTER PROCEDURE [dbo]. [SingleRowset_MultiShape]-Add the parameters for the stored procedure here (@ param1 int) AS BEGIN-- 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 is dragged into the Omax R designer, which automatically generates the following code snippet:

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

However, VS2008 recognizes multiple result set stored procedures as single result set stored procedures, and we manually modify the default generated code to require multiple result sets to be returned, like this:

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

We define two partial classes respectively to specify the type to be returned. The WholeCustomersSetResult class is as follows: (click to expand)

The code unfolds here.

Public partial class WholeCustomersSetResult {private string _ CustomerID; private string _ CompanyName; private string _ ContactName; private string _ ContactTitle; private string _ Address; private string _ City; private string _ Region; private string _ PostalCode; private string _ Country; private string _ Phone; private string _ Fax Public WholeCustomersSetResult () {} [Column (Storage = "_ CustomerID", DbType = "NChar (5)")] public string CustomerID {get {return this._CustomerID;} set {if ((this._CustomerID! = value)) this._CustomerID = value }} [Column (Storage = "_ CompanyName", DbType = "NVarChar (40)")] public string CompanyName {get {return this._CompanyName;} set {if ((this._CompanyName! = value)) this._CompanyName = value }} [Column (Storage = "_ ContactName", DbType = "NVarChar (30)")] public string ContactName {get {return this._ContactName;} set {if ((this._ContactName! = value)) this._ContactName = value }} [Column (Storage = "_ ContactTitle", DbType = "NVarChar (30)")] public string ContactTitle {get {return this._ContactTitle;} set {if ((this._ContactTitle! = value)) this._ContactTitle = value }} [Column (Storage = "_ Address", DbType = "NVarChar (60)")] public string Address {get {return this._Address;} set {if ((this._Address! = value)) this._Address = value }} [Column (Storage = "_ City", DbType = "NVarChar (15)")] public string City {get {return this._City;} set {if ((this._City! = value)) this._City = value }} [Column (Storage = "_ Region", DbType = "NVarChar (15)")] public string Region {get {return this._Region;} set {if ((this._Region! = value)) this._Region = value }} [Column (Storage = "_ PostalCode", DbType = "NVarChar (10)")] public string PostalCode {get {return this._PostalCode;} set {if ((this._PostalCode! = value)) this._PostalCode = value }} [Column (Storage = "_ Country", DbType = "NVarChar (15)")] public string Country {get {return this._Country;} set {if ((this._Country! = value)) this._Country = value }} [Column (Storage = "_ Phone", DbType = "NVarChar (24)")] public string Phone {get {return this._Phone;} set {if ((this._Phone! = value)) this._Phone = value }} [Column (Storage = "_ Fax", DbType = "NVarChar (24)")] public string Fax {get {return this._Fax;} set {if ((this._Fax! = value)) this._Fax = value;}

The PartialCustomersSetResult class is as follows: (click to expand)

The code unfolds here.

Public partial class PartialCustomersSetResult {private string _ CustomerID; private string _ ContactName; private string _ CompanyName; public PartialCustomersSetResult () {} [Column (Storage = "_ CustomerID", DbType = "NChar (5)")] public string CustomerID {get {return this._CustomerID } set {if ((this._CustomerID! = value)) this._CustomerID = value;}} [Column (Storage = "_ ContactName", DbType = "NVarChar (30)")] public string ContactName {get {return this._ContactName } set {if ((this._ContactName! = value)) this._ContactName = value;}} [Column (Storage = "_ CompanyName", DbType = "NVarChar (40)")] public string CompanyName {get {return this._CompanyName } set {if ((this._CompanyName! = value)) this._CompanyName = value;}

In this way, you can use the following code to call directly to return their respective set of results.

/ / return all Customer result sets IMultipleResults result = db.Whole_Or_Partial_Customers_Set (1); IEnumerable shape1 = result.GetResult (); foreach (WholeCustomersSetResult compName in shape1) {Console.WriteLine (compName.CompanyName);} / / return some Customer result sets result = db.Whole_Or_Partial_Customers_Set (2); IEnumerable shape2 = result.GetResult (); foreach (PartialCustomersSetResult con in shape2) {Console.WriteLine (con.ContactName);}

Statement description: this example uses stored procedures to return a group of customers in the "WA" region. The shape of the returned result set depends on the parameters passed in. If the parameter is equal to 1, all customer properties are returned. If the parameter is equal to 2, the ContactName property is returned.

Multiple result sets of 4.LINQ to SQL stored procedures

This stored procedure can generate multiple result shapes, but we already know the order in which the results are returned.

The following is a stored procedure Get Customer And Orders that returns multiple result sets sequentially. Returns customers 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 here AS BEGIN-- 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 = @ CustomerID END drag to the designer code as follows: [Function (Name= "dbo. [Get Customer And Orders]")] public ISingleResult Get_Customer_And_Orders ([Parameter (Name= "CustomerID", DbType= "NChar (5)")] string customerID) {IExecuteResult result = this.ExecuteMethodCall (this, (MethodInfo) (MethodInfo.GetCurrentMethod ()), customerID) Return ((ISingleResult) (result.ReturnValue)) } similarly, we want 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, write your own classes and have their stored procedures return their respective result sets.

The CustomerResultSet class code is expanded here

Public partial class CustomerResultSet {private string _ CustomerID; private string _ CompanyName; private string _ ContactName; private string _ ContactTitle; private string _ Address; private string _ City; private string _ Region; private string _ PostalCode; private string _ Country; private string _ Phone; private string _ Fax Public CustomerResultSet () {} [Column (Storage = "_ CustomerID", DbType = "NChar (5)")] public string CustomerID {get {return this._CustomerID;} set {if ((this._CustomerID! = value)) this._CustomerID = value }} [Column (Storage = "_ CompanyName", DbType = "NVarChar (40)")] public string CompanyName {get {return this._CompanyName;} set {if ((this._CompanyName! = value)) this._CompanyName = value }} [Column (Storage = "_ ContactName", DbType = "NVarChar (30)")] public string ContactName {get {return this._ContactName;} set {if ((this._ContactName! = value)) this._ContactName = value }} [Column (Storage = "_ ContactTitle", DbType = "NVarChar (30)")] public string ContactTitle {get {return this._ContactTitle;} set {if ((this._ContactTitle! = value)) this._ContactTitle = value }} [Column (Storage = "_ Address", DbType = "NVarChar (60)")] public string Address {get {return this._Address;} set {if ((this._Address! = value)) this._Address = value }} [Column (Storage = "_ City", DbType = "NVarChar (15)")] public string City {get {return this._City;} set {if ((this._City! = value)) this._City = value }} [Column (Storage = "_ Region", DbType = "NVarChar (15)")] public string Region {get {return this._Region;} set {if ((this._Region! = value)) this._Region = value }} [Column (Storage = "_ PostalCode", DbType = "NVarChar (10)")] public string PostalCode {get {return this._PostalCode;} set {if ((this._PostalCode! = value)) this._PostalCode = value }} [Column (Storage = "_ Country", DbType = "NVarChar (15)")] public string Country {get {return this._Country;} set {if ((this._Country! = value)) this._Country = value }} [Column (Storage = "_ Phone", DbType = "NVarChar (24)")] public string Phone {get {return this._Phone;} set {if ((this._Phone! = value)) this._Phone = value }} [Column (Storage = "_ Fax", DbType = "NVarChar (24)")] public string Fax {get {return this._Fax;} set {if ((this._Fax! = value)) this._Fax = value;}

The OrdersResultSet class code is expanded here

Public partial class OrdersResultSet {private System.Nullable _ OrderID; private string _ CustomerID; private System.Nullable _ EmployeeID; private System.Nullable _ OrderDate; private System.Nullable _ RequiredDate; private System.Nullable _ ShippedDate; private System.Nullable _ ShipVia; private System.Nullable _ Freight; private string _ ShipName; private string _ ShipAddress; private string _ ShipCity; private string _ ShipRegion; private string _ ShipPostalCode; private string _ ShipCountry Public OrdersResultSet () {} [Column (Storage = "_ OrderID", DbType = "Int")] public System.Nullable OrderID {get {return this._OrderID;} set {if ((this._OrderID! = value)) this._OrderID = value }} [Column (Storage = "_ CustomerID", DbType = "NChar (5)")] public string CustomerID {get {return this._CustomerID;} set {if ((this._CustomerID! = value)) this._CustomerID = value }} [Column (Storage = "_ EmployeeID", DbType = "Int")] public System.Nullable EmployeeID {get {return this._EmployeeID;} set {if ((this._EmployeeID! = value)) this._EmployeeID = value }} [Column (Storage = "_ OrderDate", DbType = "DateTime")] public System.Nullable OrderDate {get {return this._OrderDate;} set {if ((this._OrderDate! = value)) this._OrderDate = value }} [Column (Storage = "_ RequiredDate", DbType = "DateTime")] public System.Nullable RequiredDate {get {return this._RequiredDate;} set {if ((this._RequiredDate! = value)) this._RequiredDate = value }} [Column (Storage = "_ ShippedDate", DbType = "DateTime")] public System.Nullable ShippedDate {get {return this._ShippedDate;} set {if ((this._ShippedDate! = value)) this._ShippedDate = value }} [Column (Storage = "_ ShipVia", DbType = "Int")] public System.Nullable ShipVia {get {return this._ShipVia;} set {if ((this._ShipVia! = value)) this._ShipVia = value }} [Column (Storage = "_ Freight", DbType = "Money")] public System.Nullable Freight {get {return this._Freight;} set {if ((this._Freight! = value)) this._Freight = value }} [Column (Storage = "_ ShipName", DbType = "NVarChar (40)")] public string ShipName {get {return this._ShipName;} set {if ((this._ShipName! = value)) this._ShipName = value }} [Column (Storage = "_ ShipAddress", DbType = "NVarChar (60)")] public string ShipAddress {get {return this._ShipAddress;} set {if ((this._ShipAddress! = value)) this._ShipAddress = value }} [Column (Storage = "_ ShipCity", DbType = "NVarChar (15)")] public string ShipCity {get {return this._ShipCity;} set {if ((this._ShipCity! = value)) this._ShipCity = value }} [Column (Storage = "_ ShipRegion", DbType = "NVarChar (15)")] public string ShipRegion {get {return this._ShipRegion;} set {if ((this._ShipRegion! = value)) this._ShipRegion = value }} [Column (Storage = "_ ShipPostalCode", DbType = "NVarChar (10)")] public string ShipPostalCode {get {return this._ShipPostalCode;} set {if ((this._ShipPostalCode! = value)) this._ShipPostalCode = value }} [Column (Storage = "_ ShipCountry", DbType = "NVarChar (15)")] public string ShipCountry {get {return this._ShipCountry;} set {if ((this._ShipCountry! = value)) this._ShipCountry = value;}

At this point, all you have to do is call.

IMultipleResults result = db.Get_Customer_And_Orders ("SEVES"); / return Customer result set IEnumerable customer = result.GetResult (); / / return Orders result set IEnumerable orders = result.GetResult (); / / here, we read the data foreach (CustomerResultSet cust in customer) {Console.WriteLine (cust.CustomerID);} in CustomerResultSet

Statement description: this example uses stored procedures to return the customer "SEVES" and all its orders.

5.LINQ to SQL stored procedure with output parameters

LINQ to SQL maps output parameters to reference parameters, and for value types, it declares the parameters to be null.

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

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

Drag this stored procedure to the designer

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 totalSales) {IExecuteResult result = this.ExecuteMethodCall (this, (MethodInfo) (MethodInfo.GetCurrentMethod ()), customerID, totalSales); totalSales = (System.Nullable) (result.GetParameterValue (1) Return ((int) (result.ReturnValue));

We call this stored procedure with the following statement: note that the output parameter is passed by reference to support the scenario where the parameter is "in/out". 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}'= {1V C}", customerID, totalSales)

Statement description: this example uses a stored procedure that returns an Out parameter.

Thank you for reading, the above is the content of "what the LINQ to SQL stored procedure is". After the study of this article, I believe you have a deeper understanding of how the LINQ to SQL stored procedure is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.