Web Design that taps into the haromny and vision of your dreams.

Querying iSeries DB2 with ADO.Net

How to connect ADO.Net to an IBM DB2 server

Written By on in C#

447 words, estimated reading time 3 minutes.

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 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=127.0.0.1;UserID=USER;Password=PASS; DataCompression=True;";
 
iDB2Connection cn = new iDB2Connection(conStr);
cn.Open();

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.DeriveParameters();
 
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)
{
  reader.Read();
  StringBuilder sb = new StringBuilder();
 
  for (int i = 0; i < reader.FieldCount; i++)
  {
    sb.AppendLine(reader[i].ToString().Trim());
  }
}
 
reader.Close();
cn.Close();

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();
da.Fill(ds);
 
GridView1.DataSource = ds;
GridView1.DataBind();

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=127.0.0.1;UserID=USER;Password=PASS; DataCompression=True;";
iDB2DataAdapter da = new iDB2DataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds);

Last updated on: Wednesday 21st June 2017

 

Comments
Marco Lozano

Marco Lozano

Where i can download the IBM DB2 iSeries package?

Reply to Marco Lozano
Tim Trott

Tim Trott

You can download the .Net provider from IBM - https://www-03.ibm.com/systems/power/software/i/access/windows/dotnet.html

Reply to Tim Trott
Michael

Michael

Greetings,
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

Reply to Michael

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me