1 創建Northwind代碼文件
執行:sqlmetal /code:"c:\linqtest\northwind.cs" /language:csharp "c:\linqtest\northwnd.mdf" /sprocs /functions /pluralize
1.2 Northwind 的儲存過程
1.3 取得訂單明細調用過程
下面為工具生成代碼。
/// <summary>
/// 獲取訂單明細
/// </summary>
/// <param name="orderID">訂單ID</param>
/// <returns>返回單個結果:CustOrdersDetailResult</returns>
/// <remarks>
/// Function:對應到數據庫的函數或儲存過程
/// Parameter:對應到函數或儲存過程的參數
/// </remarks>
[Function(Name = "dbo.CustOrdersDetail")]
public ISingleResult<CustOrdersDetailResult> CustOrdersDetail([Parameter(Name = "OrderID", DbType = "Int")] System.Nullable<int> orderID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), orderID);
return ((ISingleResult<CustOrdersDetailResult>)(result.ReturnValue));
}
注意:返回單個結果使用ISingleResult
1.4 CustOrdersDetailResult類
public partial class CustOrdersDetailResult
{
private string _ProductName;
private System.Nullable<decimal> _UnitPrice;
private System.Nullable<short> _Quantity;
private System.Nullable<int> _Discount;
private System.Nullable<decimal> _ExtendedPrice;
public CustOrdersDetailResult()
{
}
[Column(Storage = "_ProductName", DbType = "NVarChar(40)")]
public string ProductName
{
get
{
return this._ProductName;
}
set
{
if ((this._ProductName != value))
{
this._ProductName = value;
}
}
}
[Column(Storage = "_UnitPrice", DbType = "Money")]
public System.Nullable<decimal> UnitPrice
{
get
{
return this._UnitPrice;
}
set
{
if ((this._UnitPrice != value))
{
this._UnitPrice = value;
}
}
}
[Column(Storage = "_Quantity", DbType = "SmallInt")]
public System.Nullable<short> Quantity
{
get
{
return this._Quantity;
}
set
{
if ((this._Quantity != value))
{
this._Quantity = value;
}
}
}
[Column(Storage = "_Discount", DbType = "Int")]
public System.Nullable<int> Discount
{
get
{
return this._Discount;
}
set
{
if ((this._Discount != value))
{
this._Discount = value;
}
}
}
[Column(Storage = "_ExtendedPrice", DbType = "Money")]
public System.Nullable<decimal> ExtendedPrice
{
get
{
return this._ExtendedPrice;
}
set
{
if ((this._ExtendedPrice != value))
{
this._ExtendedPrice = value;
}
}
}
}
2 新建項目
新建Window Form App ,命名為SprocOnlyApp
3 添加引用和Namespace
加入 System.Data.Linq.dll
using System.Data.Linq;
4 將Northwind代碼添加到項目
northwind.cs添加到SprocOnlyApp
5 設計界面
6 編寫代碼
6.1 訂單明細代碼
/// <summary>
/// 取得客戶訂單明細
/// </summary>
/// <remarks>
/// 使用關係導航,不使用Join串聯。
/// </remarks>
private void btnOrderDetail_Click(object sender, EventArgs e)
{
string orderId = txtOrderID.Text;
var custquery = db.CustOrdersDetail(Convert.ToInt32(orderId));
// 執行procedure,並且顯示結果
string msg = "";
foreach (CustOrdersDetailResult custOrdersDetail in custquery)
{
msg = msg + custOrdersDetail.ProductName + "\n";
}
if (msg == "")
msg = "No results.";
MessageBox.Show(msg);
// 清除參數(查詢條件)
txtOrderID.Text = "";
}
6.2 客戶所下的訂單
/// <summary>
/// 獲取客戶所下的訂單
/// </summary>
/// <remarks>
/// 使用關係導航,不使用Join串聯。
/// </remarks>
private void btnOrderHistory_Click(object sender, EventArgs e)
{
string customerId = txtCustomerID.Text;
var custquery = db.CustOrderHist(customerId);
// 執行procedure,並且顯示結果
string msg = "";
foreach (CustOrderHistResult custOrdHist in custquery)
{
msg = msg + custOrdHist.ProductName + "\n";
}
MessageBox.Show(msg);
// 清除參數(查詢條件)
txtCustomerID.Text = "";
}
7 輸出參數
7.1 帶輸出參數的Procedure
/// <summary>
/// 客戶訂單總金額
/// HOW TO2:使用接受參數的預存程序
/// </summary>
/// <param name="customerID">客戶ID(輸入參數)</param>
/// <param name="totalSales">總數(輸出參數(Output))</param>
/// <returns>
/// 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
/// </returns>
/// <remarks>
/// Function:對應到數據庫的Procedure名字
/// return:返回值
/// ref:對應到Procedure的輸出參數
/// </remarks>
[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));
}
注意:(1)Function:對應到數據庫的Procedure名字
(2)return:返回值
(3)ref:對應到Procedure的輸出參數
7.2 調用測試
/// <summary>
/// 測試客戶訂單總金額
/// 如何使用輸入和輸出參數。
/// </summary>
/// <remarks>
/// [Function(Name = "dbo.CustOrderTotal")]
/// </remarks>
private void button3_Click(object sender, EventArgs e)
{
Northwnd db = new Northwnd(@"c:\data\northwnd.mdf");
decimal? totalSales = 0;
db.CustOrderTotal("alfki", ref totalSales);
Console.WriteLine(totalSales);
}
注意:ref參數