[AS400] 使用 Dapper 動態參數異動資料

這裡記錄著 AS400 基本操作的 CRUD 和 防止 SQL Injection 的命令用法,開始之前,要先學會 Dapper 的 Pamemter 用法

開發環境:

  • Windows 10 Enterprise x64 CHT
  • iSeries Access V5R4 | V7R1
  • Dapper
操作AS400資料庫的時候,在連線字串設定預設的資料料庫,這樣比較容易切換測試跟正式環境

 

以下會利用 Dapper 所提供的 Dynamic Parameter 功能撰寫,對 Dapper 用法不請清楚的請參考

https://dotblogs.com.tw/yc421206/2015/03/11/150703

 

Odbc

比較特別的是,這裡的參數前後要用問號包起來。
 

[TestMethod]
public void AS400_Odbc_Dapper_CUD()
{
    //arrange
    var connectString = @"DRIVER={Client Access ODBC Driver (32-bit)};
    SYSTEM = your server;
    UID = your id;
    PWD = your password;
    EXTCOLINFO = 1;
    DefaultLibraries =your default library;";
    var deleteCommandText = "DELETE from Table1 WHERE C3 = 'TEST'";

    var insertCommandText = @"INSERT INTO Table1
(
C1,
C2,
C3,
)
VALUES
(
?C1?,
?C2?,
?C3?,
)
";
    var updateCommandText = "UPDATE Table1 SET C3 = ? WHERE PTWKNO = 'TEST'";

    using (var connection = new OdbcConnection(connectString))
    {
        connection.Open();
        //act
        //還原DB
        var deleteCount = connection.Execute(deleteCommandText);

        //新增一筆
        var insertCount = connection.Execute(insertCommandText, new
        {
            C1 = "A",
            C2 = 175858,
            C3 = "TEST",
        });

        var updateCount = connection.Execute(updateCommandText, new Table1()
        {
            C3 = "C"
        });
        //還原DB
        connection.Execute(deleteCommandText);
        //assert
        Assert.IsTrue(insertCount == 1);
        Assert.IsTrue(updateCount == 1);
    }
}
Dapper 的參數,執行非查詢類的 SQL 命令,可以用匿名型別或具名型別,超強大

 

.NET Provider

@符號就是我們熟悉的用法了 in V5R4
 

[TestMethod]
public void AS400_iDB2_Dapper_CUD()
{
    //arrange
    var connectString = @"DataSource=your server;
    UserID = your id;
    Password = your password;
    DataCompression = True;
    Default Collection = your default library;";
    var deleteCommandText = "DELETE from Table1 WHERE C3 = 'TEST'";

    var insertCommandText = @"INSERT INTO Table1
(
C1,
C2,
C3,
)
VALUES
(
@C1,
@C2,
@C3,
)
";
    var updateCommandText = "UPDATE Table1 SET C1 = @ WHERE C3 = 'TEST'";

    using (iDB2Connection connection = new iDB2Connection(connectString))
    {
        connection.Open();
        //act
        //還原DB
        var deleteCount = connection.Execute(deleteCommandText);

        //新增一筆
        var insertCount = connection.Execute(insertCommandText, new
        {
            C1 = "A",
            C2 = 175858,
            C3 = "TEST",

        });

        var updateCount = connection.Execute(updateCommandText, new Table1()
        {
            C3 = "C"
        });
        //還原DB
        connection.Execute(deleteCommandText);
        //assert
        Assert.IsTrue(insertCount == 1);
        Assert.IsTrue(updateCount == 1);
    }
}
在 V7R1 的版本,所有的 Provider 都是用 ? 了

Dapper 的 Parameter 用法太令人陶醉。

另外,無法使用交易已經卡了我好久,以下是我的片段程式碼,願善心人告訴我如何交易

iDB2Transaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
	iDB2Command cmd = new iDB2Command(deleteCommandText, connection, transaction);
	cmd.ExecuteNonQuery();
}
catch (Exception)
{
	transaction.Rollback();
}

 

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo