這裡記錄著 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