08存儲過程查詢-輸入輸出參數

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參數