Learning never exhausts the mind
Home >  Coding > C# ASP.Net MVC > Querying iSeries DB2 with ADO.Net

Published 29th January 2010 by

In this tutorial, I will show you how to connect ADO.Net to an IBM DB2 server such as a system I, iSeries or AS400. We will also look at using parameters to create secure query strings.

You will need a copy of the IBM DB2 provider which is available from here: IBM DB2 for .Net

Once you have downloaded and installed the package you will need to add a reference to IBM.Data.DB2.iSeries and add the following to your class:

using IBM.Data.DB2.iSeries;

The IBM classes are similar to the standard .Net classes with the exception of "iDB2" prefix.

The first thing to do is to create a connection string and a connection object. We then open the connection to the server.

This code is designed to illustrate the process for connection to DB2 server. It does not provide any error checking or exception handling.

string conStr = "DataSource=;UserID=USER;Password=PASS; DataCompression=True;";

iDB2Connection cn = new iDB2Connection(conStr);

Next, we create a command and pass in a select query.

iDB2Command cmd = cn.CreateCommand();
cmd.CommandText = "SELECT * FROM Products WHERE ProductCode = @ProdCode AND Language = @Lang";

You will notice that I have used the @ symbol in the command string. This denotes a SQL parameter and is a much safer way of building a command string than simply concatenating values to the string.

To use and populate SQL parameters you first need to derive parameters from the command string and then populate a collection with values.


cmd.Parameters["@ProdCode "].Value = "ABC123";
cmd.Parameters["@Lang"].Value = "EN";

Next, we execute the command and get the results back.

Using a DataReader with DB2

iDB2DataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
  StringBuilder sb = new StringBuilder();

  for (int i = 0; i < reader.FieldCount; i++)


You will need to trim the return string as DB2 will return a padding string as defined on the physical file on the AS400. For example, the field is defined as 50 characters, but only populated with 10, you will still get 50 characters back.

Using iDB2DataAdapter and DataSet with DB2

If you would prefer to retrieve all results in once, i.e. data binding to a control, you can use the iDB2DataAdapter and Microsoft DataSet classes.

iDB2DataAdapter da = new iDB2DataAdapter(cmd);
DataSet ds = new DataSet();

GridView1.DataSource = ds;

The iDB2DataAdapter also accepts as an overload a string for command and a string for connection, so a simple query could use the following code:

string sql = "SELECT * FROM Products";
string con = "DataSource=;UserID=USER;Password=PASS; DataCompression=True;";
iDB2DataAdapter da = new iDB2DataAdapter(sql, con);
DataSet ds = new DataSet();
4 thoughts on “Querying iSeries DB2 with ADO.Net
  • 6th February 2020 at 10:24 am

    string conStr = "DataSource=pub400.com;Uid=vjamdade;Pwd=vjamdade1; DataCompression=True;";
    iDB2Connection cn = new iDB2Connection(conStr);

    I have define connection string as above but i am getting error in connection string
    error is
    An unhandled exception of type 'IBM.Data.DB2.iSeries.iDB2InvalidConnectionStringException' occurred in IBM.Data.DB2.iSeries.dll
    Additional information: External component has thrown an exception.
    so kindly help me out to solve this issue

  • 26th September 2017 at 12:00 am

    Where i can download the IBM DB2 iSeries package?

  • 19th June 2014 at 12:00 am

    I am trying to run multiple statements in a iDB2DataAdapter and I do not know what separator to use for between SQL Query Statements.
    Using the System iNavigator "Run SQL Scripts functionality, you can run multiple query statements when you separate them with a semicolon ";"
    That does not work with the iDB2DataAdapter.
    If you terminate the first command with a ";", it returns the error:"SQL0104 Token ; was not valid. Valid tokens: ."
    Replacing the ";" with "END-OF-STATEMENT" returns the error:"SQL0199 Keyword END not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE."
    Putting "BEGIN" and "END' around statements did not help.

    It can be hard to find information like this. If you have any suggestions, they would be appreciated. Thanks, Michael


Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.