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.

Solution 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

Solution 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

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

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

View more solutions

Comments

[madatanic]

There is no download link for that .NET data provider. Does that come with the AS400 itself?

[dcp]

Refer to this link: forums.asp.net/p/1497318/3610952.aspx According to that, it should be in the following folder as long as you have the IBM iSeries Access for Windows Client installed : C:\Program Files\IBM\Client Access\IBM.Data.DB2.iSeries.dll

[madatanic]

Sorry, maybe I was unclear of my question. Where can I get the IBM iSeries Access for Windows Client?

[dcp]

@madantanic - No problem. I think you have to get this software from IBM. It's been a long time since I worked on the AS/400 (I was using JAVA to access it back then), but if I remember correctly, in our shop we had to contact our support rep from IBM and they sent us the CD for it. I'd start with your AS/400 system administrator and tell them what you need. I don't think the stuff is freely downloadable, unfortunately.

[matbrgz]

You need the media from IBM, or have an IBM account allowing you to download the software.

[dcp]

@Yusha - Updated the links. Keep in mind this answer is 8 years old, so link rot is inevitable.

[singhswat]

Hi Mark, which dll reference did you add? which line of code did invoke the simulator?... can you out your sample code or project on Git and share the link here.. Thanks for the help!

[Mark]

You need to install the IBM iAccess drivers which you can get from the IBM website. Then configure your AS/400 datasource in windows ODBC Data Source Administrator (64 Bit). Once configured, you don't need to include any DLL's - just use using System.Data.OleDb and use the OleDb format to send SQL queries.

[singhswat]

thanks but this will not start the emulator? Once the connection is established then how I kick off the emulator and pass the values to it... thanks. If you have any expertise in this area pls put together a article (JDE is one of biggest ERP systems) and there's a need for such an article/ blog. thanks