Innovative Systems, LLC

In this installment of the .NET journey we’re going to create a small but useful program to query the DB2 database. Later we’ll add code to export the data to Excel! How cool is that?

Creating the Project

In Visual Studio 2005, create a new Windows form project. To do this, start your version of Visual Studio and click the “Create project…” link in VS “Start Page”. You’ll then be prompted for the type of application to created. Select “Windows Appliction”.

Adding Controls

Once the project is created on the right hand side will be a window titled “Solution Explorer – yourproject name”. In the middle will be a “Form1.cs [Design]” tab with a small blank window. Make the window a little bigger by dragging the corner of the window. For this example we’ll add the following controls to the window:

• A text box for the host name
• A text box for the SQL statement to execute
• A data grid view
• A button to execute the query

On the left hand side of VS should be a box with the word “Toolbox” orientated on it’s side. Let your mouse hover over this and a toolbox should appear. If you don’t have this select “View | Toolbox” from the menu. Now select the “Label” tool from the toolbox and drop it on your form. Move the label so it’s in the upper left hand corner of the window. Now select a “Textbox” tool and drop it on your form. Move this so it’s to the right of the label control you dropped earlier. You’ll notice that as you get close to the label VS presents aligment guides to help you place the control on the same X axis as the label.

Now we need to change the visible text for our label control. Select the label control and on the left under the “Toolbox” there should be a box labeled “Properties”. Let your mouse hover over this and the properties should appear. If it doesn’t select “View | Properties Window” from the menu. Locate the “Text” property and change this from “label1″ to “System”. Also make sure the text box you added next to the label is large enough (drag the right side of the control to change).

Now we’ll add controls for the SQL statement. Add another label under the system label you added earlier and change the text to “SQL Statement”. Add a text box to the right of this label and make the control wider. Since we’re typing an SQL statement we want to have more than one line of text in the text box so we need to make it multi-line. Open the properties window for the text box and change “Multiline” to true. Now you can also change the height of the text box.

Next we’ll add a data grid view to the form. Go back to the toolbox and drop a “DataGridView” onto the form. Since the intent of this project is a simple read-only query tool, we don’t want users to type into this grid. Locate the “AllowUserToAddRows” and “AllowUserToDeleteRows” in the object’s properties and change those to False.

For the last control we’ll add an “Execute” button. Drop a “Button” object onto your form and move it to the left under the grid. Change the text for the button to “Execute”.

The First Test

Just for grins, let’s run the program. Click the “Run” button (green triangle) on the toolbar and your window should look something like this:

Start Your Connection!

Okay, now we need to add controls that let us connect to our iSeries.

First we need to add a reference to the IBM .Net data provider. On the right hand side of VS under the “Solution Explorer” window there should be an outline of your project represented by a tree view. Locate the “References” node, right click and select “Add Reference” from the popup menu. Locate “IBM DB2 UDB for iSeries .NET Provider, highlight and click “Okay”. Note that if you can’t find this provider then you need to install from the iSeries Access CD.

We need to make VS aware of the data controls available in the IBM .NET provider. From the menu, select “Tools | Choose Toolbox Items”. Under the “.NET Framework Components” tab you’ll find a list of items available for selection. To find the ones we’re intersted in, type “IBM” in the filter underneath the list. You should then see four items from the IBM.Data.DB2.iSeries namespace. Select all four and click “Okay”. These are now available on the “Toolbox” window and will be there for each subsequent project you create in VS 2005.

Now go to the “Toolbox” window and drag/drop a “iDB2Connection” object to your window. Once you do, you’ll notice that VS shows the object in a space underneath the window you created earlier. That’s because these components are not “visible”. Now go back to the “Toolbox” and drag/drop a iDB2Command and iDB2DataAdapter to your window.

We need to link some of these together so click the “iDB2Command” object and under the “Properties” window locate the “Connection” property, click into the value and click the down arrow. The small popup window should show only one available value and it should be the iDB2Connection object we dropped earlier. Go ahead and select it.

Now select the “iDB2DataAdapter1″ and locate “InsertCommand” in the object’s properties. Click into the value, click the down arrow and select the iDB2Command object in the list.

We could also set the system name to connect to in the iDB2Connection object but since we’re allowing the user to enter a system name we’ll set it later in the program.

One more object to drop onto our form. Locate a “DataSet” object and drop onto the form. You’ll then be prompted to choose either a Typed or Untyped dataset. Select Untyped and click Okay.

Now we need to link the data grid view to the data set we just added. Click the data grid and locate “DataSource” in the object’s properties. Click the down arrow which will present a window of data sources. Expand “Other Data Sources” and there you will find “Form 1 list instances” (if you’ve changed the name of your form it will show differently). Expand your form’s instances and you should find the dataset you just added. Click the data set to select.

Just A Little Code

Now that all of our objects are present we can code!

Double-click the “Execute” button you added earlier and add the following code:

            dataSet1.Clear(); // Clears the data in the data set
            try
            {
                dataSet1.Tables[0].Columns.Clear(); // Clears column information
            }
            catch { };
            // This code tells the iDB2Connection object which machine we're connecting to
            iDB2Connection1.ConnectionString = "DataSource=" + textBox1.Text;
            // Set the SQL statement to be executed
            iDB2Command1.CommandText = textBox2.Text;
            try
            {
                try
                {
                    // Fill the data set with our results
                    iDB2DataAdapter1.Fill(dataSet1);
                    // The data grid view will set column information automatically
                    dataGridView1.AutoGenerateColumns = true;
                    // Point the data grid view to the first (and only) table in the data set
                    dataGridView1.DataMember = dataSet1.Tables[0].TableName;
                }
                // This will catch communication type errors
                catch (IBM.Data.DB2.iSeries.iDB2CommErrorException CommError)
                {
                    MessageBox.Show(CommError.MessageDetails, CommError.Message);
                };
            }
            // This will catch SQL syntax errors
            catch (IBM.Data.DB2.iSeries.iDB2SQLErrorException SQLError)
            {
                MessageBox.Show(SQLError.Message);
            };

Note that if you changed any of the control’s names you may have to adjust the code.

Time For A Test Drive

Now let’s try it out! Click the “Run” button in VS and if everything goes as planned, you should see your application running. Test it by entering:

• Your iSeries name/IP address
• An SQL statement that selects records from a small table. To test, I use “select * from qgpl.qauoopt”.
• Click “Execute”

The data grid should now be populated with the data from the selected table. A word of caution here…don’t issue a select statement that returns millions of rows. Every row in the result set would be returned to your program before the data is displayed and that would not be good.

The data grid works much like the data grid in Excel. You can resize rows and columns, double-click a column to fit the widest data, etc. Play around with your new toy!

In the next installment of the .NET journey, we’ll add some bells/whistles to our example program.