CREATE PROCEDURE dbo.DimProduct_By_Class
(@param1 NCHAR(2))
AS
BEGIN
-- SET NOCOUNT ON阻止在結果集中返回顯示存儲過程影響的行計數消息
SET NOCOUNT ON;
SELECT ProductKey, EnglishProductName, ListPrice, Class from DimProduct
as c where c.Class=@param1
END
在Server Explorer展開數據庫AdventureWorksDW2008節點,右擊Stored Procedures,選擇Add New Stored Procedures。窗體打開存儲過程文件,將上面的存儲過程代碼復制到編輯框,如圖18-20所示。保存文件,存儲過程添加到數據庫。
用O/R設計器添加實體類和存儲過程的映射。將DimProduct表拖到O/R設計器的實體欄,將存儲過程拖到方法欄。有關實體類的代碼格式前面已經介紹,現在查看映射存儲過程的代碼。AdventureWorksDW2008DataContext類包含下面的方法:
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.DimProduct_By_Class")]
public ISingleResult<DimProduct_By_ClassResult> DimProduct_By_Class(
[global::System.Data.Linq.Mapping.ParameterAttribute(DbType="NChar(2)")] string param1)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
return ((ISingleResult<DimProduct_By_ClassResult>)(result.ReturnValue));
}
方法的返回類型ISingleResult<DimProduct_By_ClassResult>是O/R設計器根據存儲過程的名字自動添加的,如果想要改動返回的數據類型,可在屬性欄將存儲過程的return屬性修改。根據返回類型可以添加查詢語句。在Main方法中輸入以下代碼:
string connectionString =
"Data Source=.;Initial Catalog=AdventureWorksDW2008;Integrated Security=True";
AdventureWorksDW2008DataContext db =
new AdventureWorksDW2008DataContext(connectionString);
ISingleResult<DimProduct_By_ClassResult> result = db.DimProduct_By_Class("H");
foreach (DimProduct_By_ClassResult r in result)
Console.WriteLine("ID:{0}; Name:{1}, ListPrice:{2}, Class:{3}",
r.ProductKey,r.EnglishProductName,r.ListPrice,r.Class);
輸出:
ID:238, Name:HL Road Frame - Red 62, ListPrice:1263.4598, Class=H
ID:239, Name:HL Road Frame - Red 62, ListPrice:1301.3636, Class=H
--------------------
th79d:此例中建立存儲過程的返回集是語句
ISingleResult<DimProduct_By_ClassResult> result = db.DimProduct_By_Class("H");
只要知道返回類型就可寫查詢,返回類型由O/R設計器得出。