[C#.NET] 如何連接 AS400

[C#.NET] 如何連接 AS400

本文章節


開發環境

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

開啟ODBC管理工具,如下圖:

可以找到 IBM 所提供的 Provider,如下圖:

沒有 Provider 無法連接 AS400

我在此整理了幾種連 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

Image result for microsoft+mvp+logo