C# Subfile - Display an iSeries table in a Grid

We're all used to subfiles on the iSeries. Who can fondly recall many a debate over page by page vs. load all subfile?   How do you create the equivalent of a subfile in .Net?

Easy.

Download the visual studio project from here.

Here's a basic example to get started. The Visual Studio IDE can actually do a lot of the work for you. You can even create a grid (read subfile) with just one or two lines of code. (Post to come)  However, it's more prudent to begin with code you can understand rather than wading through what looks like binary spaghetti.

This example also introduces data sets and data tables. Data sets are just like data structures but without any definition and data tables are just like the field definitions of data structures -not to be confused with tables in databases. We use Data sets and data tables to disconnect from the data source. Connect to the data source, get the data, fill the data set with the data, disconnect from the data source and use the data set in lieu of the actual data. Another way to think of data sets is to think of them as a cache, bucket, container, plastic bag. See my earlier post on data sets for more info.

 

Steps

1. Define your grid (i.e. subfile)
2. Attach the grid to your form (VS creates one for you auto-)
3. Create a data set to hold your data
4. Create a data table to define the fields in the data set
5. Add the data table to the data set
6. Connect  to your iSeries
7. Execute an SQL statement to read from a table
8. Read each row and add to the data set
9. Disconnect from the iSeries
10. Attach the data set to the grid
11. Display the grid

It sounds like a lot of work just to output data to a grid - and it is. Why bother with the data set and the table - can't I just write directly out the grid? Yes you can - but this example is here to show you not only how to display data in a grid from the iSeries but how to best manage that data as well. A Data Set will help you do that. 
It is true that there are much simpler approaches on the iSeries but that comes at a price. Once you get out of the db2 and green screen box things get quite tricky on the As/400.  .Net is more complicated yes but its complexity comes from flexibility.

iSeries prerequisites:

The table on the iSeries in this example is called customers. Create it in library QGPL
create the table in DDS or go into SQL by typing 'strsql' in the iSeries command prompt and create the table as follows:

CREATE TABLE QGPL/CUSTOMERS (NAME CHAR (30 ) NOT NULL WITH DEFAULT,
BALANCE DEC (5 ) NOT NULL WITH DEFAULT)

Add records using the INSERT sql command, DBU or your favorite data editor on the iSeries

C# Code:

This is the code for the form. The 'Program.cs' in solution explorer is unchanged. Simply create a windows project, double click on the form that appears and replace all the code with the code below. Insert your iSeries IP address and make sure you have created the iSeries table as describe above or replace with your own ensuring that you correctly specify the columns.  

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries; // Make sure you add this under 'References' in Solution Explorer
// You need the above reference as a dll which is part of iSeries client access.

namespace iSeries_Grid
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Define the grid size
            DataGrid subfile = new DataGrid();
            subfile.Location = new Point(0, 0);
            subfile.Size = new Size(400, 500);

            //Attach it to the form
            Controls.AddRange(new Control[] { subfile });


            // Create a DataSet to hold data from iSeries Table
            DataSet dataStructure = new DataSet();

            //Create a table to hold the iSeries data
            DataTable dt = new DataTable("Customers");
            dt.Columns.Add("Name");
            dt.Columns.Add("Balance");


            //Add the datatable to the data set
            dataStructure.Tables.Add(dt);

            // Open connection to the iSeries
            iDB2Connection conn = new iDB2Connection();
           conn.ConnectionString = "DataSource=192.168.0.1";

 // You can put "UserID=myuserid;Password=mypass" 
// if you don't want to be prompted

   //  Create a command to select records from the customer table
            iDB2Command command = new iDB2Command();
          command.CommandText = "Select * from qgpl.customers";
         command.Connection = conn; 
 // ties the command to the connection to the iSeries

         conn.Open();

            // Execute the sql statement. Get a Data Reader object 
            iDB2DataReader readFile = command.ExecuteReader();

            // Read each row from the table and output the results into the data set

            while (readFile.Read())
            {
                // Create a row to hold data
                DataRow datarow = dataStructure.Tables["customers"].NewRow();

                datarow["Name"] = readFile.GetString(0);
                datarow["Balance"] = readFile.GetiDB2Integer(1);
                
                // add the row to the data table customer
                dataStructure.Tables["customers"].Rows.Add(datarow);


                }       

            // Clean up - Close connections
            readFile.Close();
            command.Dispose();
            conn.Close();

         //   Attach the data set to the data grid
            subfile.DataSource = dataStructure;
            subfile.DataMember = dataStructure.Tables[0].TableName;



            // Display the subfile
            subfile.Show();
      

        } // End of Method


    } //End of Class

} // End of Namespace

 

This code is based on an example in the IBM .Net Redbook modified

for this post.