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

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

Found this gem recently: NTi Data Provider - sharing my feedback

I think it's worth sharing here as I was recently looking for a way to call AS400 programs from .NET (see my question here) and came across Aumerial.Data.Nti nuget package.

It worked great for my initial use case, and it also provides access to DB2 for i database with standard ADO.NET and also EF Core. It's very easy and convenient to use, and it's fully standalone, ie just the package to donwload, nothing to install on the client side (no driver) + nothing to install on the AS400 as well. It works on .NET Framework as well as .NET Core environments.

Here are some examples of how I implemented it successfully :

Database query using plain ADO.NET

var connectionString = "server=....;user=....;password=....;";
using (var conn = new NTiConnection(connectionString)) {
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE LIB.CUSTOMERS SET NAME = ? WHERE ID = ?";
    cmd.DeriveParameters();
    cmd.Parameters[0].Value = "Martin";
    cmd.Parameters[1].Value = 10;
    conn.ExecuteNonQuery();
}

Database query using Dapper

var cust = new Customer() {Id = 10};
using (var conn = new NTiConnection(connectionString)) {
    var data = conn.Query<Customer>("SELECT * FROM LIB.CUSTOMERS WHERE ID = @ID", cust);
}

Database query using EF Core

Register NTi as you'd do for other DBMS, e.g. :

builder.Services.AddDbContext<ApplicationDbContext>(
    options => options.UseNTi(connectionString)
);

Executing CL Commands:

using (var conn = new NTiConnection(connectionString)) {
    conn.Open();
    conn.ExecuteClCommand("DLTLIB TEMPLIB");
}

Calling program:

Why I used NTi at first, here is a complex use case where I needed to convert POCOs to program parameter and then parse the returned data back to POCO:

//Employee instance (POCO)
var empl = new Employee() {
    Name = "Foo",
    Salary = 50000.0M
};

//Opening the connection
var conn = new NTiConnection(connectionString);
conn.Open();

//Creating the parameter according to the datastructure
var parms = new List<NTiProgramParameter>() {
    new NTiProgramParameter(empl.Name, 10).Append(empl.Salary, 9, 2)
};

//Program call
conn.CallProgram("PGMLIB", "EMPLPGM", parms);

//Retreive returned data from the DS to the POCO
empl.Name = parms[0].GetString(0, 10); //0 offset, 10 in length
empl.Salary = parms[0].GetPackedDecimal(9, 2, 10); //PACKED(9, 2), 10 offset

Getting a license key

It is a 3rd party solution from AUMERIAL, as I understood they work closely with IBM. I contacted them directly from their website and obtained a free trial key in just a few hours, they provided me with an SQL script to run on the AS400 and then everything was ready in like 10 seconds.

Hope it can help someone trying to connect to AS400 using .NET as it did for me :)

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

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.

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