張小呆的碎碎唸

在上一篇提到，用LINQ來使用Stored Procedure(之後使用SP稱之)，但SP中往往都是「Select CustomerID From dbo.Customers Where CustomerID = @CustomerID」的方式來撰寫。對LINQ來說，是很好抓到所回傳的欄位名稱；倘若今天是用暫存資料表或者是組SQL字串的方式，那LINQ似乎就沒辦法用簡單的寫法來辨識所回傳的欄位名稱了，下面就來說說要如何解決這樣的問題。

1.改寫Northwind的dbo.CustOrderHist，並將資料寫到暫存資料表中。

Code：

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[New_CustOrderHist] @CustomerID nchar(5)
AS

--檢查是有已有#f_New_CustOrderHist暫存資料表的存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#f_New_CustOrderHist]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 DROP TABLE [dbo].[#f_New_CustOrderHist]

--建立暫存資料表
CREATE TABLE #f_New_CustOrderHist (
  [f_ProductName] nvarchar(1000) NULL,
  [f_Total] nvarchar(1000) NULL
 )

--將查詢出的資料寫入暫存資料表中
Insert into #f_New_CustOrderHist
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

--將暫存資料表的資料輸出
Select f_ProductName, f_Total From #f_New_CustOrderHist Order By f_ProductName

Result：


2.改寫Northwind的dbo.CustOrderHist，並將其中的T-SQL已組字串的方式執行輸出。

Code：

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[New_String_CustOrderHist]
(
 @CustomerID nchar(5)
)
AS

Declare @strSQL nvarchar(2000)

Set @strSQL = 'SELECT ProductName, Total=SUM(Quantity) '
Set @strSQL = @strSQL + 'FROM Products P, [Order Details] OD, Orders O, Customers C '
Set @strSQL = @strSQL + 'WHERE C.CustomerID = ''' + @CustomerID + ''' AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID '
Set @strSQL = @strSQL + 'GROUP BY ProductName Order By ProductName'

EXEC (@strSQL)

Result：


3.將新增加的SP加入到LINQ TO SQL中。


4.使用ADO.NET的方式執行SP，並將結果顯示在dataGridView中。

Code：

MyDataContext db = new MyDataContext();

using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString))
{
    SqlCommand cmd = new SqlCommand("dbo.New_CustOrderHist", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@CustomerID", "ALFKI");

    DataSet ds = new DataSet();

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    da.Fill(ds, "New_CustOrderHist");

    var result = from c in ds.Tables["New_CustOrderHist"].AsEnumerable()
                     select new NewCustOrderHist
                     {
                         f_ProductName = c.Field<string>("f_ProductName"),
                         f_Total = c.Field<int>("f_Total")
                     };

    dataGridView1.DataSource = result.ToList();
}

結果：(另一個就不演示了，寫法一樣，只是換掉上一個範例的SP而已)