Procedure

MSSQL在每個資料庫裏, 都有一個 “可程式性” 項目, 點開後, 會有 預存程序, 函數等項目.
預存程序(Stored Procedure)就是把一大堆的MSSQL語法指令儲存起來, 然後日後就可以直接執行, 不用再一行一行地重寫.

mssql_procedure

預存程序和函數有時蠻類似的, 但請記得一件事 : 預存程序可以對資料庫/表進行新增修改刪除, 函數只能對變數進行變更

使用預存程序的步驟, 第一是先建立程序, 然後將要處理的事件寫在 Begin 跟 End之間. 寫完後按下執行, 就會建立新的預存程序名稱並儲存要處理的任務程式碼. 如果Begin/End之間的任務程式碼需要修改, 則在預存程序名稱按右鍵/修改, 然後再按執行進行儲存.

建立程序

可以預存程序按右鍵/再選預存程序, 或直接於命令列下執如下

if OBJECT_ID('日報表查詢') IS NOT NULL
drop procedure 日報表查詢
GO
create procedure 日報表查詢
    @startTime DateTime,
    @endTime DateTime,
    @maxCount int output
as
begin
set nocount on;
    select address, max(count) 
    from lora 
    where report_time>=startTime and report_time<=endTime 
    group by address 
    order by address
end
go

訂單資料表_建立是這個預存程序的名稱, 一開始會先檢查此程序是否已存在, 若有的話, 先砍掉再重建.

修改預存程序

要修改begin/end之間的程式碼, 請於指定的程序名按右鍵/修改

執行程序

於程序名稱按右鍵/執行預存程序

傳入參數

可在CREATE PROCEDURE 建立程序後, 加入傳入參數. 參數或變數名稱前面, 請記得要加 “@”, 並需宣告資料型態, 如下

@startTime DateTime,
@endTime DateTime

Procedure 回傳

預存程序中若有使用 select 語法, 則在執行程序後, 會自動傳出資料錄出來, 在C#等程式碼中可以直接使用 SqlDataReader 來讀取. 但除了select 語法外, 還有另外二種回傳方式:

1. create procedure後, 加入傳出參數, 即於參數後面加上output 關鍵字, 如下

CREATE PROCEDURE Test
    @maxCount int output,

2. return @maxCount, 為第二種傳回方式

在C#執行預存程序

在C#中, 可以直接執行預存程序, 把需要篩選的任務交給MSSQL的預存程序去執行, 然後再把結果傳回給C#. 這樣作有幾個好處

1. MSSQL的篩選演算法是經過千槌百練後的產品, 其效能會這比自已撰寫程式還來的高.
2. 篩選的程式碼集中在資料庫中, 方便日後管理及維護

在C#中執行預存程序的步驟如下, 請注意藍色的部份

static void Main(string[] args)
{
	string strConn = "server=localhost;database=資料庫;uid=帳號;pwd=密碼;MultipleActiveResultSets=true";
	SqlConnection conn = new SqlConnection(strConn);
	SqlCommand cmd = new SqlCommand(string.Empty, conn);
	conn.Open();
	cmd.Parameters.Clear();
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = "日報表查詢";
	cmd.Parameters.Add("@area", SqlDbType.Int).Value = 1;
	cmd.Parameters.Add("@maxCount", SqlDbType.Int).Value = 1;
	SqlDataReader dr = cmd.ExecuteReader();
	while (dr.Read())
	{
		Console.WriteLine("Road" + dr["缺失路段"]);
	}
	dr.Close();
	dr.Dispose();
	conn.Close();
	conn.Dispose();
}

Output 參數取得

在C#中, 要取回設定為output的參數, 可由如下藍色的代碼中所示, 於cmd.Parameters.Add後, 會回傳SqlParameter的物件, 將此物件的 Direction設為ParameterDirection.Output

SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test";
cmd.Parameters.Add("@area", SqlDbType.Int).Value = 1;

SqlParameter maxCount = cmd.Parameters.Add("@maxCount", SqlDbType.Int, 30);
maxCount.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
Console.WriteLine("Last Name¡G" + maxCount.Value);
conn.Close();
conn.Dispose();

return 值取得

在C#中, 若要取回預存程序的return 值, 如同output參數一樣, 只不過SqlParameter物件的 Direction設為ParameterDirection.ReturnValue

cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test";
cmd.Parameters.Add("@area", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@maxCount", SqlDbType.Int).Value = 1;
SqlParameter retID = cmd.Parameters.Add("@rtnValue", SqlDbType.Int);
retID.Direction = ParameterDirection.ReturnValue;
try
{
	cmd.ExecuteNonQuery();
}
catch (Exception ex){ }
Console.WriteLine("Return Value：" + retID.Value);
conn.Close();
conn.Dispose();