这里记录着 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();
}

若有谬误，烦请告知，新手发帖请多包涵

2010~2017 C# 第四季

原文:大专栏  [AS400] 使用 Dapper 动态参数异动数据