摘要:利用ADO.NET接收預存程序的輸出資料
以下為預存程序的回傳型態分類：
- 資料集 -- 用 SqlDataAdapter 或 SqlDataReader 物件取回，前面說過了，這個跟從資料表或檢視查詢資料作法沒甚麼兩樣。
- 輸出參數 -- 用 SqlCommand 物件逐一加入 SqlParameters 集合物件，所有輸出參數的 ParameterDirection 屬性必須指定為 Output (或 InputOutput) 型態。
- 傳回值 -- 做法如同輸出參數，但在 ParameterDirection 屬性必須指定為 ReturnValue 型態。
- 受影響資料列數 -- 一般做法是用 SqlCommand.ExecuteNonQuery 方法即可取得，但考慮到 Stored Proc 有可能下 SET NOCOUNT ON 陳述式關閉此輸出 (此時 ExecuteNonQuery 方法的回傳值永遠是 -1)，因此可以考慮在 Stored Proc 裡宣告足夠的變數去存放每一段 DML 陳述式執行後的 @@ROWCOUNT，再考慮用輸出參數的方式取回 (若是偏好傳回資料集也是可以)。
- 警告或 PRINT 陳述式的輸出 -- 在 SQL Server 中，錯誤嚴重性層級 10 以下的視為警告訊息，且不會擲回例外狀況，還有，有時候為了除錯，我們會利用 PRINT 輸出訊息，這類的訊息可透過 SqlConnection 物件的 InfoMessage 事件處理，詳細可參考：ADO.NET - 使用連接事件。
1.新增預存程序(輸出為回傳值@tableColumns)：
CREATE PROCEDURE [dbo].[TestSelectTableColumns_sp]
( @inputTableName nvarchar(100),
@tableColumns nvarchar(500) output
)
AS
BEGIN
--撈出指定Table的欄位名稱
select @tableColumns=name from syscolumns where id=object_id(@inputTableName)
ENDADO連接資料庫、接受資料：
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Data Source=PC\\;Initial Catalog=DB;User ID=xxx;Password=xxx";
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "selectTableColumns_sp";
cmd.Parameters.Add("inputTableName", SqlDbType.NVarChar, 100).Value = "M_DAIMA"; //傳遞參數給預存程序 ；
//cmd.Parameters.Add("[SP參數名稱]", SqlDbType.[SP參數類別], [參數大小]).Value="[要給的參數值]";
SqlParameter rc = new SqlParameter("tableColumns", SqlDbType.NVarChar, 500); //tableColumns為SP參數名(不用加@)
rc.Direction = ParameterDirection.Output; //參數類型為Output (@tableColumns)
cmd.Parameters.Add(rc); //把rc參數類別加入SP的參數中
cmd.ExecuteReader();//執行Command
Response.Write(rc.Value);//撈出回傳值參數的資料2.新增預存程序(輸出為table)：
ALTER PROCEDURE [dbo].[TestSelectTableColumns_sp] ( @inputTableName nvarchar(100) ) AS BEGIN select name from syscolumns where id=object_id(@inputTableName) END
ADO連接資料庫、接受資料(接收table中的字串)：
//以上皆相同
SqlDataReader dr = cmd.ExecuteReader(); //用 SqlDataReader 接收資料表
Label label = new Label();
while (dr.Read()) //進行下一筆讀取,如讀的到傳回Ture,讀不到傳回false
{
label.Text += String.Format("{0}", dr[0])+",";
// Response.Write(dr[0]);
}
this.Page.Form.Controls.Add(label);3.新增預存程序(輸出為table)：
ALTER PROCEDURE [dbo].[selectAllTable_sp] AS BEGIN --撈出資料庫中的所有table_name SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES END
ADO連接資料庫、接受資料(接收table)：
//以上相同
cmd.CommandText = "selectAllTable_sp";
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
//adapter.TableMappings.Add("Table", "QUESTIONTABLE");
adapter.SelectCommand = cmd; //用SqlDataAdapter去接收資料
adapter.Fill(ds); //放置DataSet中
GridView gv = new GridView();
this.Page.Form.Controls.Add(gv);
gv.DataSource = ds; //以GridView顯示出來
gv.DataBind(); 4.新增預存程序(輸出為影響資料筆數)：
CREATE PROCEDURE [dbo].[InsertReturnCount_sp]
(
@inputTableName nvarchar(100),
@columns nvarchar(100),
@values nvarchar(100)
)
AS
BEGIN
DECLARE @SqlCommand nvarchar(500)
SET @SqlCommand='INSERT INTO ' + @inputTableName +'(' +@columns + ')' + ' VALUES ' + '(' + @values + ')'
exec (@SqlCommand)
--回傳影響資料筆數
return @@rowcount
ENDADO連接資料庫、接受資料(接收return rowcount)：
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Data Source=PC\\;Initial Catalog=DB;User ID=xxx;Password=xxx";
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertReturnCount_sp";
cmd.Parameters.Add("inputTableName", SqlDbType.NVarChar, 100).Value = "M_DAIMA"; //傳遞參數給預存程序 ；
//cmd.Parameters.Add("[SP參數名稱]", SqlDbType.[SP參數類別], [參數大小]).Value="[要給的參數值]";
cmd.Parameters.Add("columns", SqlDbType.NVarChar, 100).Value = "KIND,K1,K2,K3";
cmd.Parameters.Add("values", SqlDbType.NVarChar, 100).Value = "1,2,3,4";
int count=cmd.ExecuteNonQuery();//進行insert並傳回影響資料數給count變數
Label label = new Label();
label.Text = "受影響資料數為" + count.ToString() + "筆";
this.Page.Form.Controls.Add(label);