此篇簡單介紹如使用ADO.NET呼叫預存程序與取得SSMS中的相關資訊。
有關於Stored Procedure的參考網站 :
低階模組(被呼叫端) 方法 :
#region 所有衍生類別都應至此查詢欲查詢資料。
/// <summary>
/// 使用SP查詢指定TB。目前只提供欄位資料型態為string的查詢,若不需要請設置NULL
/// </summary>
/// <param name="uConn">設置好的連線</param>
/// <param name="tableName">TB名稱</param>
/// <param name="columnName">指定欄位</param>
/// <param name="columnValue">欲查詢的值</param>
/// <param name="SpOut">SP參數回傳OUTPUT</param>
/// <param name="SpReturn">SP回傳RETURN</param>
/// <returns></returns>
protected DataTable connectSqlForSelectTb(SqlConnection uConn, string tableName,
string columnName, string columnValue,
out SqlParameter SpOut, out SqlParameter SpReturn)
{
// 指定委派接收 SSMS 中的Message相關資訊
uConn.InfoMessage += new SqlInfoMessageEventHandler(this.cn_InfoMessage);
// 第一個參數為預存程序的名稱
using (SqlCommand cmd2 = new SqlCommand("selectSp_testWebWms", uConn))
{
cmd2.CommandType = CommandType.StoredProcedure; //選擇SqlCommand的指令型別
// 新增參數(須對應到預存程序中的參數名稱與其形態)並賦予其值
cmd2.Parameters.Add(new SqlParameter("@TbName", SqlDbType.NVarChar)).Value = tableName;
if (columnName != null && columnValue != null)
{
cmd2.Parameters.Add(new SqlParameter("@ColumnName", SqlDbType.NVarChar)).Value = columnName;
cmd2.Parameters.Add(new SqlParameter("@ColumnValue", SqlDbType.NVarChar)).Value = columnValue;
}
SqlParameter outValParam = cmd2.Parameters.Add("@OutP", SqlDbType.Int); //新增OUT的變數
outValParam.Direction = ParameterDirection.Output; //指定為 ReturnValue,接收 SP 輸出參數
SqlParameter retValParam = cmd2.Parameters.Add("@RETURN_VALUE", SqlDbType.Int); //新增RETURN的變數
retValParam.Direction = ParameterDirection.ReturnValue; //指定為 ReturnValue,接收 SP回傳的資料
SqlDataAdapter da = new SqlDataAdapter(cmd2); //建立da並，將其初始化
DataSet ds = new DataSet(); //創建 ds
try
{
da.Fill(ds, "VirtualTbName"); //使ds與sql server橋接，並設定TB名稱。(此時啟動連線)
SpOut = outValParam;
SpReturn = retValParam;
return ds.Tables["VirtualTbName"];
}
catch (Exception ex)
{
ForWebPageGetSetInfo.javaMessageBox(ex.ToString());
throw ex.GetBaseException();
}
}
}
#endregion
#region 給 SqlInfoMessageEventHandler 事件，註冊用的方法。
protected void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// HttpContext.Current.Response.Write(e.GetType().Name.ToString())
// e.Message.ToList().ForEach(x => ForWebPageGetSetInfo.javaMessageBox("SP:" + x.ToString()));
for (int i = 0; i < e.Errors.Count; i++)
{
ForWebPageGetSetInfo.javaMessageBox("SP:" + e.Errors[i].Message);
}
}
#endregion
高階模組(呼叫端) 方法 :
#region 接收SP回傳TB，並回傳該DATATABLE。
public DataTable selectData(ForWebPageGetSetInfo.TableChnaces CheckUse, SqlConnection uConn, string seleColumnNmae = "",
string seleColumnValue = "")
{
if (CheckUse != ForWebPageGetSetInfo.TableChnaces.GoodMast)
{
throw new NotImplementedException();
}
SqlParameter sp_out, sp_return;
DataTable baseReturn;
if (seleColumnNmae != "" && seleColumnNmae != "") // 若沒有設置則是全部資料抓取
{
baseReturn = base.connectSqlForSelectTb(uConn, this.IsTbName,
seleColumnNmae, seleColumnValue, out sp_out, out sp_return);
}
else baseReturn = base.connectSqlForSelectTb(uConn, this.IsTbName, null, null, out sp_out, out sp_return);
ForWebPageGetSetInfo.javaMessageBox("sp_out: " + sp_out.Value.ToString() + " " +
"sp_return: " + sp_return.Value.ToString());
return baseReturn;
}
#endregion
使用的預存程序 :
USE [test_web_wms]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[selectSp_testWebWms](
@TbName NVARCHAR(50),@ColumnName NVARCHAR(50)='',@ColumnValue NVARCHAR(100)=''
,@OutP INT= '' OUT )--若找到回傳找到筆數@OutP
AS
DECLARE @_sqlCmd NVARCHAR(500) ='' --需先初始化否則無法使用
BEGIN
--該程序是傳回資料表格 1.TB名稱 2.欄位名稱 3.欲查詢值 (全欄位回傳)
-- 參數 2 or 3 其中一個沒有則帶回整個資料表格
-- 針對欲查詢條件 目前只開放STRING的資料型態
--若找無資料表或欄位名稱時 找無TB RETURN 1 找無欄位 RETURN 2,
--若查詢時發生錯誤,系統報錯 PRINT。並回傳 -1
--若預設值都正確，找到資料後或無找無資料 RETURN TABLE ,
--輸出參數 @OutP 回傳找到的數量。
SET NOCOUNT ON
DECLARE @spReturn INT = ''
IF @ColumnName=''
BEGIN
EXEC @spReturn = [dbo].[CheckSpHave_TableOrColumn] @TbName
IF @spReturn <> 0 BEGIN RETURN @spReturn END
END
ELSE
BEGIN
EXEC @spReturn = [dbo].[CheckSpHave_TableOrColumn] @TbName,@ColumnName
IF @spReturn <> 0 BEGIN RETURN @spReturn END
END
SET @OutP = 0
SET @_sqlCmd = @_sqlCmd + N'SELECT * FROM '+@TbName+N' '
BEGIN TRY
IF @ColumnName='' OR @ColumnValue = ''
BEGIN
SET @_sqlCmd = @_sqlCmd+N'WHERE 1=1'
EXEC [dbo].sp_executesql @_sqlCmd
SET @OutP=@@ROWCOUNT
Print '查詢成功'
END
ELSE
BEGIN
SET @_sqlCmd = @_sqlCmd+ N'WHERE ' + @ColumnName + N' = @ColumnValue'
EXEC [dbo].sp_executesql @_sqlCmd,N'@ColumnValue NVARCHAR(100)',@ColumnValue
SET @OutP=@@ROWCOUNT
Print '查詢成功'
END
END TRY
BEGIN CATCH
PRINT N'Error Line: ' + CONVERT(NVARCHAR(100),ERROR_LINE())
PRINT N'Error Number: '+CONVERT(NVARCHAR(100),ERROR_NUMBER())
PRINT N'Error Message: ' +CONVERT(NVARCHAR(100),ERROR_MESSAGE())
RETURN -1
END CATCH
END
多多指教!! 歡迎交流!!
你不知道自己不知道，那你會以為你知道