I am trying to build a .NET web application using SQL to query AS400 database. This is my first time encountering the AS400.
What do I have to install on my machine (or the AS400 server) in order to connect? (IBM iSeries Access for Windows ??)
What are the components of the connection string?
Where can I find sample codes on building the Data Access Layer using SQL commands?
Thanks.
7 Answers
You need the AS400 .Net data provider. Check here:
For connection string samples, check here:
Also, check out the redbook for code examples and getting started.
6Following is what I did to resolve the issue.
Installed the IBM i Access for Windows. Not free
Referred the following dlls in the project
- IBM.Data.DB2.iSeries.dll
- Interop.cwbx.dll (If Data Queue used)
- Interop.AD400.dll (If Data Queue used)
Data Access
using (iDB2Command command = new iDB2Command()) { command.Connection = (iDB2Connection)_connection; command.CommandType = CommandType.Text; command.Parameters.AddWithValue(Constants.ParamInterfaceTransactionNo, 1); command.CommandText = dynamicInsertString; command.ExecuteScalar(); }Connection String
<add name="InterfaceConnection"
connectionString="Data Source=myserver.mycompany.com;User ID=idbname;Password=mypassxxx;
Default Collection=ASIPTA;Naming=System"/>UPDATE
i Access for Windows on operating systems beyond Windows 8.1 may not be supported. Try the replacement product IBM i Access Client Solutions
As mentioned in other answers, if you have the IBM i Access client already installed, you can use the IBM.Data.DB2.iSeries package.
If you don't have the IBM i Access software, you can leverage JTOpen and use the Java drivers. You'll need the nuget package JT400.78 which will pull in the IKVM Runtime.
In my case I needed to query a DB2 database on an AS400 and output a DataTable. I found several hints and small snippets of code but nothing comprehensive so I wanted to share what I was able to build up in case it helps someone else:
using com.ibm.as400.access;
using java.sql;
var sql = "SELECT * FROM FOO WITH UR";
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
Connection conn = DriverManager.getConnection( "jdbc:as400:" + ServerName + ";prompt=false", UserName, Password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
int ct = md.getColumnCount();
DataTable dt = new DataTable();
for(int i=1; i<=ct; i++) dt.Columns.Add(md.getColumnName(i));
while (rs.next())
{ var dr = dt.NewRow(); for (int i = 1; i <= ct; i++) dr[i - 1] = rs.getObject(i); dt.Rows.Add(dr);
}
rs.close();The conversion from RecordSet to DataTable is a little clunky and gave me bad flashbacks to my VBScript days. Performance likely isn't blinding fast, but it works.
Extremely old question - but this is still relevant. I needed to query our AS/400 using .NET but none of the answers above worked and so I ended up creating my own method using OleDb:
public DataSet query_iseries(string datasource, string query, string[] parameterName, string[] parameterValue) { try { // Open a new stream connection to the iSeries using (var iseries_connection = new OleDbConnection(datasource)) { // Create a new command OleDbCommand command = new OleDbCommand(query, iseries_connection); // Bind parameters to command query if (parameterName.Count() >= 1) { for (int i = 0; i < parameterName.Count(); i++) { command.Parameters.AddWithValue("@" + parameterName[i], parameterValue[i]); } } // Open the connection iseries_connection.Open(); // Create a DataSet to hold the data DataSet iseries_data = new DataSet(); // Create a data adapter to hold results of the executed command using (OleDbDataAdapter data_adapter = new OleDbDataAdapter(command)) { // Fill the data set with the results of the data adapter data_adapter.Fill(iseries_data); } return iseries_data; } } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } }And you would use it like so:
DataSet results = query_iseries("YOUR DATA SOURCE", "YOUR SQL QUERY", new string[] { "param_one", "param_two" }, new string[] { "param_one_value", "param_two_value"}); It returns a DataSet of the results returned. If anyone needs/wants a method for inserting/updating values within the IBM AS/400, leave a comment and I'll share...
I'm using this code and work very good for me!
Try Dim sqltxt As String = "SELECT * FROM mplib.pfcarfib where LOTEF=" & My.Settings.loteproceso dt1 = New DataTable Dim ConAS400 As New OleDb.OleDbConnection ConAS400.ConnectionString = "Provider=IBMDA400;" & _ "Data Source=192.168.100.100;" & _ "User;" & _ "Password=" & My.Settings.contrasena Dim CmdAS400 As New OleDb.OleDbCommand(sqltxt, ConAS400) Dim sqlAS400 As New OleDb.OleDbDataAdapter sqlAS400.SelectCommand = CmdAS400 ConAS400.Open() sqlAS400.Fill(dt1) grid_detalle.DataSource = dt1 grid_detalle.DataMember = dt1.TableName Catch ex As Exception DevExpress.XtraEditors.XtraMessageBox.Show("Comunicación Con El AS400 No Establecida, Notifique a Informatica..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) Me.Close() End TryI recently found the ADO.Net driver available on NuGet. I have the iSeries client access installed on my PC, so I can't say if it works as a standalone, but it does connect. Theonly problem is I can't actually see any tables or procedures. I think there may be a schema or library or something I still haven't gotten down to. I will post if I find the answer. Meanwhile I can still get to the server and write most of my code with the NuGet adapter.
Check out as they have some development tools working with SQL and the AS400.
