HOW TO：使用預存程序傳回資料列集 (LINQ to SQL)
這個範例會從資料庫傳回資料列集 (Rowset)，並且包含用以篩選結果的輸入參數。
當您執行可傳回資料列集的預存程序 (Stored Procedure) 時，您會使用「結果」(Result) 類別來儲存從預存程序傳回的資料。 如需詳細資訊，請參閱分析 LINQ to SQL 原始程式碼。
範例
下列範例表示一個預存程序，該程序會傳回客戶資料列並使用輸入參數，以便只傳回將 "London" 列為客戶所在城市的那些資料列。 此範例假設了一個可列舉的 CustomersByCityResult 類別。
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
<FunctionAttribute(Name:="dbo.Customers By City")> _
Public Function CustomersByCity(<Parameter(DbType:="NVarChar(20)")> ByVal param1 As String) As ISingleResult(Of CustomersByCityResult)
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), param1)
Return CType(result.ReturnValue, ISingleResult(Of CustomersByCityResult))
End Function
Sub ReturnRowset()
' Call the stored procedure.
Dim db As New Northwnd("c:\northwnd.mdf")
Dim result As IEnumerable(Of CustomersByCityResult) = _
db.CustomersByCity("London")
For Each cust As CustomersByCityResult In result
Console.WriteLine("CustID={0}; City={1}", _
cust.CustomerID, cust.City)
Next
End Sub
[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={0}; City={1}", cust.CustomerID,
cust.City);
}
}