ADO.NET使用預存程序時，有時會因為"參數過多"或者是"不固定數量的參數"會導致該段程式碼過於複雜或冗長。
此篇將介紹如何因應該狀況。
說明 :
此法是將WebForm頁面中的TextBox建置成List<T>。
須統一設定每個TextBox需新增指定的屬性名稱與值 :
須統一設定每個TextBox需新增指定的屬性名稱與值 :
- 第一個屬性 : 值需對應資料表的欄位名稱(應等同SP接收的參數名稱)
- 第二個屬性 : 值需對應資料表的欄位型態
低階模組(被呼叫端) 方法 :
#region 從UI屬性判斷轉型後並新增sqlcommand.parameter
protected void setSqlCommnadForUi<T>(SqlCommand needSqlCm, List<T> infoUiCollection)
{
if (needSqlCm.Connection != null && (infoUiCollection is List<TextBox>) != false)
{
foreach (T item in infoUiCollection)
{
TextBox tb1 = null;
string tb1ColumnDataType = "";
string sp_Para_name = this._spParamaterBeginName; //sp 的參數名稱前導預設為 : @Pa
SqlDbType addPsqldbType;// 給sp參數的型態
tb1 = item as TextBox;
// 取得該欄位的型態
tb1ColumnDataType = tb1.Attributes[ForWebPageGetSetInfo.txtUiPropertyColumnDataType];
// 取得該欄位的名稱
sp_Para_name += tb1.Attributes[ForWebPageGetSetInfo.txtUiPropertyColumnName.Trim()];
switch (tb1ColumnDataType)
{
case "nvarchar":
case "char":
this.strGetSqlDbType(tb1ColumnDataType, out addPsqldbType);
needSqlCm.Parameters.Add(new SqlParameter(sp_Para_name, addPsqldbType)).Value = tb1.Text;
break;
case "bit":
bool outb;
this.stringToBool(tb1.Text, out outb, out addPsqldbType);
needSqlCm.Parameters.Add(new SqlParameter(sp_Para_name, addPsqldbType)).Value = outb;
break;
case "smallint":
Int16 outI;
this.stringToInt16(tb1.Text, out outI, out addPsqldbType);
needSqlCm.Parameters.Add(new SqlParameter(sp_Para_name, addPsqldbType)).Value = outI;
break;
case "money":
decimal outD;
this.stringToDecimal(tb1.Text, out outD, out addPsqldbType);
needSqlCm.Parameters.Add(new SqlParameter(sp_Para_name, addPsqldbType)).Value = outD;
break;
case "datetime2":
default:
throw new NotImplementedException();
}
}
}
else throw new NotImplementedException();
}
#endregion
#region 轉型方法。
private void strGetSqlDbType(string columnDataType, out SqlDbType SqlDbtypeOut)
{
if (columnDataType == "nvarchar") SqlDbtypeOut = SqlDbType.NVarChar;
else if (columnDataType == "char") SqlDbtypeOut = SqlDbType.Char;
else throw new NotImplementedException();
}
private void stringToBool(string ConverValue, out bool ConvertValue, out SqlDbType SqlDbtypeOut)
{
string changeStr = "";
if (ConverValue == "0" || ConverValue.ToLower()== "false") changeStr = "false";
else if (ConverValue == "1" || ConverValue.ToLower() == "true") changeStr = "true";
if (bool.TryParse(changeStr, out ConvertValue)) SqlDbtypeOut = SqlDbType.Bit;
else throw new NotImplementedException();
}
private void stringToInt(string ConverValue, out int ConvertValue, out SqlDbType SqlDbtypeOut)
{
if (Int32.TryParse(ConverValue, out ConvertValue)) SqlDbtypeOut = SqlDbType.Int;
else throw new NotImplementedException();
}
private void stringToInt16(string ConverValue, out Int16 ConvertValue, out SqlDbType SqlDbtypeOut)
{
if (Int16.TryParse(ConverValue, out ConvertValue)) SqlDbtypeOut = SqlDbType.SmallInt;
else throw new NotImplementedException();
}
private void stringToDecimal(string ConverValue, out decimal ConvertValue, out SqlDbType SqlDbtypeOut)
{
if (decimal.TryParse(ConverValue, out ConvertValue)) SqlDbtypeOut = SqlDbType.Money;
else throw new NotImplementedException();
}
#endregion
高階模組(呼叫端) 方法 :
#region 使用個別TB之修改SP修改資料。每個頁面使用此法必須保留該筆資料原"主鍵欄位"的值。
public void updateSqlSp<T>(ForWebPageGetSetInfo.TableChnaces CheckUse, SqlConnection uConn, List<T> webUiList, string beforePK_Value)
{
if (CheckUse != ForWebPageGetSetInfo.TableChnaces.GoodMast)
{
throw new NotImplementedException();
}
string spPaForBeforePkName = "@PaPkBeforeValue"; // SP的參數名稱
string beforePK = beforePK_Value;
using (SqlCommand cmd1 = new SqlCommand(base._sp_name[6, 0], uConn))
{
//string abc = HttpContext.Current.Session["abc"].ToString();
// 註冊事件處裡常式
uConn.InfoMessage += new SqlInfoMessageEventHandler(base.cn_InfoMessage);
cmd1.CommandType = CommandType.StoredProcedure; // 選擇SqlCommand的指令型別
base.setSqlCommnadForUi<T>(cmd1, webUiList); // 建立sql命令
cmd1.Parameters.Add(new SqlParameter(spPaForBeforePkName, SqlDbType.NVarChar)).Value = beforePK; //此為修改SP專用的參數
SqlParameter retValParam = cmd1.Parameters.Add("@RETURN_VALUE", SqlDbType.Int); // 新增RETURN的變數
retValParam.Direction = ParameterDirection.ReturnValue; // 指定為 ReturnValue,接收 SP回傳的資料
base.connectSqlAndRunCommmnad(uConn, cmd1, retValParam);
}
}
#endregion
使用的預存程序 :
USE [test_web_wms]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FangSp_UpdateTB_FangGoodsMast](
--該程序為商品主檔 的修改。若 成功回傳 0 失敗1。sqlServer 錯誤 回傳 -1
--無須再外部啟動交易所有SP若有UPD資料全都是在交易模式下運作。
--若找無欲修改資料回傳2
@PaPkBeforeValue NVARCHAR(20), --PK欄位的原值，不管有無修改都需要，若找無則不會繼續
@PaPdCode NVARCHAR(20)='',
@PaPdName NVARCHAR(50)='',
@PaDeleteFlag BIT ='',
@PaAllowDay SMALLINT='',
@PaCaseQty SMALLINT='',
@PaBoxQty SMALLINT='',
@PaCaseQty_Level SMALLINT='',
@PaHighLevel_Plt SMALLINT='',
@PaPickStock CHAR(7)='',
@PaPdLength SMALLINT='',
@PaPdWidth SMALLINT='',
@PaPdHeight SMALLINT='',
@PaPdWeight SMALLINT='',
@PaTemperatureLayer CHAR(2)='',
@PaPdSort NVARCHAR(12)='',
@PaStopSell BIT ='',
@PaLowestSellDay SMALLINT='',
@PaEaBarcode_1 NVARCHAR(20)='',
@PaEaBarcode_2 NVARCHAR(20)='',
@PaBoxBarcode_1 NVARCHAR(20)='',
@PaBoxBarcode_2 NVARCHAR(20)='',
@PaCaseBarcode_1 NVARCHAR(20)='',
@PaCaseBarcode_2 NVARCHAR(20)='',
@PaSupplier_1 CHAR(3)='',
@PaSupplier_2 CHAR(3)='',
@PaSupplier_3 CHAR(3)='',
@PaReplenish_Online SMALLINT='',
@PaReplenish_Reserve SMALLINT='',
@PaCostPriceAve MONEY='',
@PaLastInPrice MONEY ='',
@PaSuggestPrice MONEY='',
@PaSuggestPriceAve MONEY='',
@PaSellPrice MONEY='',
@PaSellPriceAve MONEY='',
--@PaUpdTime DATETIME2(1),
@PaUpdUser NVARCHAR(16)=''
)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM [dbo].[FangGoodsMast] WHERE [PdCode] = @PaPkBeforeValue)
BEGIN PRINT '找無欲修改資料' RETURN 2 END
DECLARE @_sqlCmd NVARCHAR(3000) = ''
SET @_sqlCmd = @_sqlCmd + N'UPDATE [FangGoodsMast] SET '
------
IF(@PaPdCode <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N'[PdCode] =''',@PaPdCode,N''' ') END
ELSE BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N'[PdCode] =''',@PaPkBeforeValue,N''' ') END
IF(@PaPdName <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[PdName] =''',@PaPdName,N''' ') END
IF(@PaDeleteFlag <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[DeleteFlag] =''',@PaDeleteFlag,N''' ')END
IF(@PaAllowDay <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[AllowDay] =''',@PaAllowDay,N''' ') END
IF(@PaCaseQty <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[CaseQty] =''',@PaCaseQty,N''' ') END
IF(@PaBoxQty <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[BoxQty] =''',@PaBoxQty,N''' ') END
IF(@PaCaseQty_Level <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[CaseQty_Level] =''',@PaCaseQty_Level,N''' ') END
IF(@PaHighLevel_Plt <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[HighLevel_Plt] =''',@PaHighLevel_Plt,N''' ') END
IF(@PaPickStock <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[PickStock] =''',@PaPickStock,N''' ') END
IF(@PaPdWidth <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[PdWidth] =''',@PaPdWidth,N''' ') END
IF(@PaPdHeight <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[PdHeight] =''',@PaPdHeight,N''' ') END
IF(@PaPdWeight <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[PdWeight] =''',@PaPdWeight,N''' ') END
IF(@PaTemperatureLayer <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[TemperatureLayer] =''',@PaTemperatureLayer,N''' ') END
IF(@PaStopSell <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[StopSell] =''',@PaStopSell,N''' ') END
IF(@PaLowestSellDay <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[LowestSellDay] =''',@PaLowestSellDay,N''' ') END
IF(@PaEaBarcode_1 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[EaBarcode_1] =''',@PaEaBarcode_1,N''' ') END
IF(@PaEaBarcode_2 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[EaBarcode_2] =''',@PaEaBarcode_2,N''' ') END
IF(@PaBoxBarcode_1 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[BoxBarcode_1] =''',@PaBoxBarcode_1,N''' ') END
IF(@PaBoxBarcode_2 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[BoxBarcode_2] =''',@PaBoxBarcode_2,N''' ') END
IF(@PaCaseBarcode_1 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[CaseBarcode_1] =''',@PaCaseBarcode_1,N''' ') END
IF(@PaCaseBarcode_2 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[CaseBarcode_2] =''',@PaCaseBarcode_2,N''' ') END
IF(@PaSupplier_1 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[Supplier_1] =''',@PaSupplier_1,N''' ') END
IF(@PaSupplier_2 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[Supplier_2] =''',@PaSupplier_2,N''' ') END
IF(@PaSupplier_3 <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[Supplier_3] =''',@PaSupplier_3,N''' ') END
IF(@PaReplenish_Online <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[Replenish_Online] =''',@PaReplenish_Online,N''' ') END
IF(@PaReplenish_Reserve <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[Replenish_Reserve] =''',@PaReplenish_Reserve,N''' ') END
IF(@PaCostPriceAve <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[CostPriceAve] =''',@PaCostPriceAve,N''' ') END
IF(@PaLastInPrice <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[LastInPrice] =''',@PaLastInPrice,N''' ') END
IF(@PaSuggestPrice <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[SuggestPrice] =''',@PaSuggestPrice,N''' ') END
IF(@PaSuggestPriceAve <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[SuggestPriceAve] =''',@PaSuggestPriceAve,N''' ') END
IF(@PaSellPrice <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[SellPrice] =''',@PaSellPrice,N''' ') END
IF(@PaSellPriceAve <> '') BEGIN SET @_sqlCmd = @_sqlCmd + CONCAT(N',[SellPriceAve] =''',@PaSellPriceAve,N''' ') END
DECLARE @SET_datetimeP DATETIME2(1) = GETDATE()
SET @_sqlCmd = @_sqlCmd +CONCAT(N',[UpdTime] =''',@SET_datetimeP,N''' ')
IF(@PaUpdUser <> '') BEGIN SET @_sqlCmd = @_sqlCmd +CONCAT(N',[UpdUser] =''',@PaUpdUser,N''' ') END
SET @_sqlCmd = @_sqlCmd +CONCAT(N'WHERE [PdCode] = ''',@PaPkBeforeValue,N''' ')
-----
DECLARE @chk INT = ''
BEGIN TRY
BEGIN TRAN T1
EXEC [dbo].sp_executesql @_sqlCmd
IF @@ROWCOUNT <> 1 BEGIN Print '修改資料筆數大於1或等於0請確認: ' + CONVERT(CHAR(5),@@ROWCOUNT) END
IF @@ERROR <> 0 BEGIN SET @chk=1 END ELSE BEGIN SET @chk=0 END
IF @chk = 0 BEGIN COMMIT TRAN T1 PRINT '交易成功' RETURN 0 END
ELSE BEGIN ROLLBACK TRAN T1 PRINT '交易失敗' RETURN 1 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())
ROLLBACK TRAN T1
RETURN -1
END CATCH
END
多多指教!! 歡迎交流!!
你不知道自己不知道，那你會以為你知道