How to: Use Stored Procedures that Take Parameters (LINQ to SQL)
LINQ to SQL maps output parameters to reference parameters, and for value types declares the parameter as nullable.
For an example of how to use an input parameter in a query that returns a rowset, see How to: Use Stored Procedures to Return Rowsets (LINQ to SQL).
Example
The following example takes a single input parameter (the customer ID) and returns an out parameter (the total sales for that customer).
CREATE 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
<FunctionAttribute(Name:="dbo.CustOrderTotal")> _
Public Function CustOrderTotal(<Parameter(Name:="CustomerID", DbType:="NChar(5)")> ByVal customerID As String, <Parameter(Name:="TotalSales", DbType:="Money")> ByRef totalSales As System.Nullable(Of Decimal)) As <Parameter(DbType:="Int")> Integer
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), customerID, totalSales)
totalSales = CType(result.GetParameterValue(1), System.Nullable(Of Decimal))
Return CType(result.ReturnValue, Integer)
End Function
[Function(Name="dbo.CustOrderTotal")]
[return: Parameter(DbType="Int")]
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));
}
You would call this stored procedure as follows:
Dim db As New Northwnd("C:\...\northwnd.mdf")
Dim totalSales As Decimal? = 0
db.CustOrderTotal("alfki", totalSales)
Console.WriteLine(totalSales)
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
decimal? totalSales = 0;
db.CustOrderTotal("alfki", ref totalSales);
Console.WriteLine(totalSales);
See Also
Concepts
Downloading Sample Databases (LINQ to SQL)
Reference
Using Nullable Types (C# Programming Guide)