[C#.NET] 如何連接 AS400
本文章節
- Windows 8.1 x64
- .NET 4.5
- 開始前要先確認是否有安裝 AS400 Client Provider,這會由 IBM 所提供,我是安裝 iSeries Access
開啟ODBC管理工具,如下圖:
可以找到 IBM 所提供的 Provider,如下圖:
我在此整理了幾種連 AS400 的方式
Provider=IBMDA400;User ID=your id;Password=your password;Data Source=your server;Transport Product=Client Access;SSL=DEFAULT
public void AS400_OLEDB()
{
var connectString = "Provider=IBMDA400;Data Source=your server;User ID=your id;Password=your password;Transport Product=Client Access;SSL=DEFAULT";
using (var connection = new OleDbConnection(connectString))
{
connection.Open(); if (connection.State != ConnectionState.Open) { return; }
var queryString = "SELECT * FROM your table";
var command = new OleDbCommand(queryString, connection);
var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
var readerTable = GetDataTable(reader);
}
}
安裝完 iSeries Access,必須要手動加入 IBM.Data.DB2.iSeries.dll 參考,路徑如下圖:
程式碼下:
DataSource = your server; UserID = ypur id; Password = your password; DataCompression = True;
public void AS400_iDB2()
{
var connectString = "DataSource = your server; UserID = ypur id; Password = your password; DataCompression = True;";
IDbConnection connection = new iDB2Connection(connectString);
connection.Open();
var command = new iDB2Command("SELECT * FROM your table", connection);
var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
var readerTable = GetDataTable(reader);
}
IBM提出了兩個 Provider,這兩個隨便挑一個
DRIVER={iSeries Access ODBC Driver};SYSTEM=your server;UID=your id;PWD=your password;EXTCOLINFO=1;
public void AS400_ODBC_iSeries()
{
var connectString = "DRIVER={iSeries Access ODBC Driver};SYSTEM=your server;UID=your id;PWD=your password;EXTCOLINFO=1;";
using (var connection = new OdbcConnection(connectString))
{
connection.Open();
if (connection.State != ConnectionState.Open) { return; }
var queryString = "SELECT * FROM your table"; var command = new OdbcCommand(queryString, connection);
var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
var readerTable = GetDataTable(reader);
}
}
DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=your server;UID=your id;PWD=your password;EXTCOLINFO=1;
public void AS400_ODBC_Client()
{
var connectString = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=your server;UID=your id;PWD=your password;EXTCOLINFO=1;";
using (var connection = new OdbcConnection(connectString))
{
connection.Open();
if (connection.State != ConnectionState.Open) { return; }
var queryString = "SELECT * FROM your table";
var command = new OdbcCommand(queryString, connection);
var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
var readerTable = GetDataTable(reader);
}
}
處理DataTable
public DataTable GetDataTable(IDataReader reader)
{
DataTable table = new DataTable();
for (int i = 0; i < reader.FieldCount; i++)
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
while (reader.Read())
{
object[] items = new object[reader.FieldCount]; reader.GetValues(items);
table.LoadDataRow(items, true);
}
return table;
}
當然也可以透過 Dapper ORM 來處理
首先定義 POCO
public class Audit
{
public string No { get; set; }
public string DESC { get; set; }
public string Year { get; set; }
}
Dapper 擴充了 IDbConnection,基本上通吃所有的 .Net Provider,它最主要是幫我把 DataReader 轉成強型別物件
這裡我就用 OdbcConnection 演練,透過 Dapper 程式碼變得更容易閱讀
public void AS400_ODBC_Client_Dapper()
{
var connectString = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=your server;UID=your id;PWD=your password;EXTCOLINFO=1;";
using (var connection = new OdbcConnection(connectString))
{
string query = "SELECT * FROM your table";
var audits = connection.Query<Audit>(query);
}
}
文章出自:https://www.dotblogs.com.tw/yc421206/2015/04/20/as400_connect_provider
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET