余小章 @ 大內殿堂

本文章節

• 開發環境
• OLEDB
• .Net Provider
• ODBC
• ORM 解決方案：Dapper

---

開發環境

• Windows 8.1 x64
• .NET 4.5
• 開始前要先確認是否有安裝 AS400 Client Provider，這會由 IBM 所提供，我是安裝 iSeries Access

開啟ODBC管理工具，如下圖：

可以找到 IBM 所提供的 Provider，如下圖：

---

我在此整理了幾種連 AS400 的方式

OLEDB

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);
	}
}

 

.Net Provider

安裝完 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);
}

 

ODBC

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;
}

 

ORM 解決方案：Dapper

當然也可以透過 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