How to Query IBM iSeries DB2 Database with ADO.NetHow to connect ADO.Net to an IBM iSeries DB2 server or as an System I or AS400 and how to use parameters to create secure query strings.
You will need a copy of the IBM iSeries DB2 provider which is available from here: IBM iSeries 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 except for "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=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, if 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 at 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); 
