Connect to AS400 using .NET

Question

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.

Answer 1

You need the AS400 .Net data provider. Check here: https://www-01.ibm.com/support/docview.wss?uid=isg3T1027163

For connection string samples, check here: https://www.connectionstrings.com/as-400/

Also, check out the redbook for code examples and getting started. http://www.redbooks.ibm.com/redbooks/pdfs/sg246440.pdf

Answer 2

Following 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

IBM i Access Client Solutions

Answer 3

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.

Answer 4

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...

Answer 5

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 ID=" & My.Settings.usuario & ";" & _
        "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 Try

Answer 6

I 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.

Answer 7

Check out http://asna.com/us/ as they have some development tools working with SQL and the AS400.