Microsoft .NET 2.0 for System i Developers: Building Windows Forms Using the DataGridview Control
February 13, 2008 Michael Sansoterra
|
This is the third in a series of articles by Michael Sansoterra on Microsoft .NET 2.0. Note: The code accompanying this article is available for download here. Part 1 and Part 2 of this series provided a general introduction to using .NET with the IBM System i. Attention was given to how much could be done in .NET without any coding at all. Part 1 started with a trivial example of how to build an interactive Web page capable of editing data in a DB2 table using ASP.NET. Part 2 built on that foundation by AJAX-enabling the majority of the Web page so that entire page refreshes in the browser could be avoided. In this article, we’ll duplicate the work that was done in Part 1 using a Windows form instead of a Web page. Before we start, an important question should be addressed. Given that…
why would we want to write a Windows application instead of a browser-based application? There are a few possible reasons, depending on your application’s requirements, including:
In my opinion, it is much easier to develop and debug a Windows application than a Web app, which results in greater developer productivity. As opposed to Web apps, deployment of complex Windows applications to many users can be problematic, but Microsoft‘s ClickOnce technology greatly helps with this task. If you’re new to .NET and haven’t read my prior articles, please read them now (at least read Part 1.) The tutorial in this article will basically cover in a step-by-step fashion how to create a Windows form to edit data against a remote data source, which is intended to help you see the power and ease of .NET. Keep in mind I’m not intending to provide a full introduction to Visual Studio 2005 (hereafter VS) or the .NET framework because that would simply make this article balloon to an unbearable size. However, in keeping with the theme of my previous articles, I desire to show that much can be done in the .NET environment with little or no coding. One other thing needs to be mentioned. In November of 2007, Microsoft released VS 2008 along with Version 3.5 of the .NET Framework. This article was written with VS 2005 but VS 2008 can be substituted as well. Requirements To follow along, you’ll need the following: 1. A version of Visual Studio 2005 (or 2008). In my previous articles, you could follow the Web page development with the free Visual Web Developer 2005 Express edition. For building Windows Applications Microsoft released two free editions: Visual C# 2005 Express Edition or Visual Basic 2005 Express Edition. However, for this tutorial, you will not be able to follow along using either Express edition. This is because the DataSource wizards in these freebie versions of VS do not include the ODBC driver as a selectable option (which excludes connecting to the System i). In fact, Microsoft only allows connections to SQL Server and Access! You can still program using ODBC with these editions, you just have to do it the old-fashioned way, which isn’t necessarily conducive to beginners. Therefore I will be using VS 2005 Professional Edition for this tutorial. I regret to add that this limitation still exists in the VS 2008 Express editions as well. How I had hoped Microsoft would remove it. Interesting thing, the Express edition can run the code, it just can’t generate it! There will be a little bit of coding in this article. There is no major difference in the capability of Microsoft’s flagship .NET languages of C# and VB.NET. Pick one based on your comfort level. If you’ve dabbled in Java, then C# is the better choice for you. Likewise, if you’ve done VisualBasic 6.0 or VBA coding then VB.NET, might be right. If you’re a System i-AS/400 developer with no preference, then I would recommend you learn C# first because it will open your horizons to Java in the near future since the syntax of the languages are very similar. I will be doing an example in C#. 2. iSeries Access should be installed on a development PC including the iSeries Access ODBC Driver component. You should also know how to configure an ODBC data connection to your System i. Here is a word of clarification from the first article. Several people emailed me stating that IBM offers a native .NET managed provider for the System i that comes with iSeries Access V5R3 and higher. That’s true, but IBM’s provider was written for Version 1.0 of the .NET framework. While IBM’s provider works great, it does not work with the wizards available in VS 2005/2008. For that reason I usually stick with the iSeries Access ODBC driver in .NET 2.0 and higher applications. To recap once more, in this article I’ll cover how to quickly build a client/server Windows application that provides full edit capability for a DB2 for i5/OS table. In other words, it will be like building an RPG subfile program against a DB2 table. Creating a Windows Application Start VS and create a new project by choosing File→New→Project. Under the Visual C# project types node, choose the Windows application template. Enter a project name and location. Uncheck the “Create Directory for Solution” option. (A solution is a container that holds multiple projects. In the case of a single project tutorial such as this one, the solution and project are virtually synonymous.) I named my project “DB2Demo”.
The VS IDE will present you with a default Windows form called “Form1”. A “form” is simply a Window that is used to hold text and various controls (such as text boxes and buttons) that a user can interact with. You can change the characteristic of the form, such as its size, by clicking on the form in the designer. Little “handles” (or small square boxes) will appear on each side of the form when the form is selected for resizing. These handles can be dragged to make the form larger or smaller. Expand the default form to about twice its current size by clicking on the lower right corner and moving the mouse to the right and down. (GUI design requires a little more finesse and artistry on the part of the developer than text-based programming. Since GUI stuff doesn’t usually fit nicely into rows and columns, you’ll just have to tinker a bit to get used to sizing and positioning things.) Other characteristics of the form can be changed by setting various properties in the property window, which is usually located in the lower right portion of the IDE. If you’re not sure where the Properties window is, just click the form and then press F4. Find the “name” property in the list and change its value from “Form1” to “frmDB2Demo”. This name will determine how we reference the form in code. Find the Text property and change it from “Form1” to “DB2 Demo. ” The Text property controls the form’s caption text that appears in the form’s title bar, which is the colored bar that appears at the top of all standard Windows. If you browse through the Properties window you’ll see there are plenty of things that will change the look of your form, such as background color and whether or not the form has the ubiquitous minimize, maximize, and restore buttons in the upper right corner of the window. So what do we do with the form now? Developers allow users to interact with a form by placing controls on it. Controls come in many varieties, including text boxes, check boxes, calendars and buttons. The available controls are found in the Toolbox, which usually appears on the left side of the VS IDE. If the toolbox is not visible, you can use the short cut key Ctrl+Alt+X to make it appear. Figure 1 shows a picture of the VS toolbox. For this form, we’re going to use a powerful new control introduced in .NET 2.0: the DataGridView. Do not confuse the DataGridView control with the GridView control introduced in prior articles. While similar in function, the DataGridView is used with Windows applications and the GridView is used for Web applications. This control gives us all the functionality of a matrix of text boxes similar to an Excel worksheet or an Access table. Just so you know where we’re going, Figure 2 shows the completed form we’re trying to build. The ability to edit data and sort columns and the like is all provided by a single control! As you can see, the DataGridView is used for displaying tabular data (similar to a subfile) and the resulting form simply mimics a DFU against a DB2 table.
We need to do a few things to make the form like the one shown in the picture:
Modify the Form Step-by-Step To place a DataGridView control on the form, click the Data tab on the Toolbox, select DataGridView, then click on the form somewhere in the upper-left gray vicinity of the window. You should now have the control on your form. A DataGridView tasks menu will appear–ignore it for the moment. Click on the control itself (not the “tasks” smart menu) to make sure it is selected (as evidenced by the handles on the control’s border) and stretch the size of the control to make it cover almost the entire form leaving about 10 percent empty space at the top and the bottom so we can put a toolbar and buttons there later. While the control is selected, press F4 to view the properties of the control. Find the “name” property in the Properties window. Every control on a form must have a unique name. By default, the name of this first control is “DataGridView1. ” Change the name property of the control to “dgvDB2Data”. (Sometimes .NET developers prefix the name of the control with an abbreviation to easily identify via the code what type of control is being used, hence the “dgv” prefix above indicates the control is a DataGridView.) If you need to refer to the control in code, of course you will need to know its name or use the VS “intellisense” feature to look it up. Also, in the Properties window locate the anchor property and set its value to “Top, Bottom, Left, Right.” This anchor setting will cause the control to shrink and grow along with the main form if the user chooses to resize the form. You should now have a form that looks similar to the form shown in Figure 3.
Now let’s bind this powerful matrix control to a DB2 for i5/OS table. We’ll be using ODBC to access the data so make sure your workstation has the ODBC driver that comes with iSeries Access installed. For this exercise, go into Windows administrative tools, choose the ODBC applet and create an ODBC data source name called “AS400” that will connect to your AS/400, iSeries or System i. For this demo, I again used the CORPDATA sample data. To create this sample data, use interactive SQL (STRSQL) or the “Run SQL Scripts” utility in System i Navigator and run the following SQL statement: Call QSYS.Create_SQL_Sample (‘CORPDATA’). This procedure will create a library (schema) called CORPDATA on your system that contains several tables populated with data. The specific table we will be using in this sample database is called “DEPARTMENT”. Now that we have our sample data and ODBC connection ready, let’s tell the DataGridView control where to find this data. Go back to VS and click on the DataGridView control. In the upper-right corner of the control, a small box with an arrow (called a “SmartTag”) will appear. Click this arrow to view the DataGridView tasks menu. Click the Choose Data Source drop-down box and then click the Add Project Data Source option that appears toward the bottom of the dialog window. The data source configuration wizard will appear. Choose Database as your data source and click Next. Click New Connection, then choose Microsoft ODBC Data Source, and click Continue. (If you’re using VS Express, you won’t see the ODBC option.) Also, in case you’re wondering, we cannot use the DB2 UDB for iSeries .NET managed provider for this project because it was written for version 1.0 of the .NET framework. However, at long last, the V6R1 release of System i Access is reported to provide a .NET 2.0 compatible provider so we’ll be able to use these wizards. Three cheers for IBM! When the add connection window appears, choose the Use Connection String option. Modify the following connection string by putting in your own information and paste it in the Connection String box: Dsn=YOUR_DSN;uid=YOUR_USERNAME; Please note, I added the DBQ entry in this string that sets the library list of the connection to use the CORPDATA library. Be aware unless you’re using “single sign on” or Windows authentication, you will have a user name and password stored in your connection string (unless you can allow the user to be prompted or go under the assumption that the user will already have a connection to the System i). As a general rule, all client/server user IDs should have minimal authority to table data. In an ideal situation, no direct table access should be allowed for a client/server user profile. Instead, all data access logic should be wrapped in table functions and stored procedures and users should be given permission to execute those SQL routines. However, that’s a lot of extra work and security that is a whole different story outside the scope of this tutorial! So for now, we’ll code directly against the table. Back to the Add Connection” window. Click the Test Connection button to test your connection. If the test is successful, click the OK button. If you’re prompted to include sensitive data (i.e., the user name and password) to be saved, choose Yes and click Next. You’ll be asked to provide a name for your database connection. I called mine “DB2”. For the record, your connection string information will be stored in a special application XML file called “app.config”. Because the content of app.config is accessible to the entire application, when creating forms against other DB2 tables, you’ll be able to reuse this data connection for other forms that need database access. You can also change your connection string information by editing the app.config file, which you may want to do when transitioning from a test ODBC DSN to a live one. Continuing with the Data Source Configuration wizard, click Next and you’ll be prompted to select database object types to include and also asked to provide a Dataset name. Expand the Tables node and check the box next to the DEPARTMENT table. Enter “CorpData” for the dataset name and then click Finish. (Note: This command is case sensitive, if you want to use my code.) If you’re wondering, a dataset is a .NET class that provides the functionality for storing and navigating data for one or more database objects. It can actually do more than this, but for now realize that this dataset object offers programmable access to the data once it has been retrieved from DB2. If you want more info research “ADO.NET Tutorial” on the Web. The DataGridView control should now contain column names that match the column names from the DB2 DEPARTMENT table. You will probably notice that three new controls have been placed in the lower portion of the designer window by the wizard: corpData, dEPARTMENTBindingSource and dEPARTMENTTableAdapter. These controls are “non-visual” controls and are displayed there for the purpose of allowing their properties to be edited in the VS designer. If you don’t like the case of these control names (and C# is case sensitive), you’re not alone. However, this strange mix of letters is due to the fact that C# classes typically are given names starting with an upper-case character, variable names start with a lower-case character, and the fact that DB2 for i5/OS usually has its table names stored as upper case. The VS IDE should now be presenting the Form along with the DataGridView Tasks menu. (If it’s not, select the DataGridView control and then click on the smart tag.) Finally notice that the Enable Adding, Enable Editing, and Enable Deleting options are selected by default. Enabling these options allows the DataGridView to perform all of these operations against the database table. If you do not see these options checked, it may have something to do with table indexes. If you want a remote table to be editable from the .NET world via the data wizards, the table should have a unique key (i.e., Primary Key constraint, Unique Index, DDS defined file with UNIQUE keyword specified). A unique key will allow the .NET data wizards to identify how the data can be efficiently updated or deleted. At this point, press F5 and the VS IDE will attempt to run the project. Since we haven’t written any code, there shouldn’t be any errors! Your grid should appear and even allow you to insert, edit, and delete data. (You can delete by clicking on a row handle and then pressing the delete key.) However, these changes are only local (i.e., in memory) being tracked in the aforementioned DataSet object–they are not yet being executed against the database. Exit the application by clicking on the form’s “X” button in the upper-right corner. Back in VS, we’ll add two buttons to the form to aid us with the task of saving the changes to DB2: a Save button and a Cancel button. From the toolbox, locate the Button control and click it. Move the mouse under the DataGridView control and click just under its left corner. (If you didn’t leave enough room between the DataGridView control and the bottom of the form, you may need to shrink the DataGridView a little.) Repeat this process, placing a second button just to the right of the first. Click the first button and press F4 to view the properties widow. Change the button’s name property to “btnSave” and its text property to “Save”. For the second button, change its name to “btnCancel” and its text property to “Cancel”. Now let’s add a little code to the project. Click btnSave and then press F4 to view the properties window. Click the lightning bolt icon at the top of the properties window. This will change the window to show the button’s events instead of its properties. In GUI programming, an “event” primarily refers to something that the user can do that a program may need to respond to, such as clicking a mouse button, letting the mouse hover, dragging a scroll bar, typing a key, etc. Double-click in the empty box next to the Click event. The IDE will switch from the form designer view to the code view and you should see a method stub created called “btnSave_Click”. (For those new to C#, a method is similar to an RPG subprocedure. A stub is basically an empty code block to save us the hassle of typing the method’s name and parameters.) This stub allows us to fill in code that will be executed when the save button is clicked. Go back to the design view of the form (View→Designer) and repeat this process for the cancel button. When completed, you should be in the code view window with another method stub called “btnCancel_Click”. The stubs look like this:
private void btnCancel_Click(object sender, EventArgs e)
{
}
private void btnSave_Click(object sender, EventArgs e)
{
}
If the order of your stubs is different, don’t worry, it makes no difference to the compiler. Of course, since we’re in the code window, this is where we type our code. Two method definitions have been created for us: “btnCancel_Click” and “btnSave_Click”. We will put in code between the curly braces {} to let the program know what to do when the Save or Cancel buttons are clicked. Since this article is already running long, just paste the following code in the stub definitions. Place this code between the curly braces of the “btnSave_Click” method:
Int32 RowsChanged = 0, RowsInserted = 0, RowsDeleted = 0;
//
// Track Changes made to DataSet by Updates, Adds, Deletes
// The DataSet object tracks all of this info automatically.
//
if (corpData.HasChanges(DataRowState.Modified))
{
RowsChanged =
corpData.DEPARTMENT.GetChanges(DataRowState.Modified).Rows.Count;
}
if (corpData.HasChanges(DataRowState.Added))
{
RowsInserted =
corpData.DEPARTMENT.GetChanges(DataRowState.Added).Rows.Count;
}
if (corpData.HasChanges(DataRowState.Deleted))
{
RowsDeleted =
corpData.DEPARTMENT.GetChanges(DataRowState.Deleted).Rows.Count;
}
//
// Change the cursor to an hourglass while the updates are
// sent to the database
//
this.Cursor = Cursors.WaitCursor;
//
// The update method of the table adapter sends the changes
// in the DataSet to the database in a single step!
//
this.dEPARTMENTTableAdapter.Update(this.corpData.DEPARTMENT);
//
// Change the cursor back to normal and kindly show the
// user a summary of the changes
//
this.Cursor = Cursors.Default;
MessageBox.Show(String.Format("{0:G} Rows Changedn" +
"{1:G} Rows Insertedn" +
"{2:G} Rows Deleted",
RowsChanged, RowsInserted, RowsDeleted),
"Changes Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
Don’t be intimidated by all this code because actually only one line of code is required to send the changes stored in the dataset to the database. I added extra code for the sake of changing the mouse icon to an hourglass, to display the number of rows changed, etc. Now place this code between the curly braces of the “btnCancel_Click” method to cancel any pending changes to the database: // // The "RejectChanges" method will rollback all of the // backed changes (again the DataSet tracks all changed // rows including before & after pictures of the data) // this.corpData.DEPARTMENT.RejectChanges(); Please note that out of the box, the DataGridView uses a “batch” approach to updating the data. All changes made to the data are stored locally in the DataSet object until the save button is pressed. Likewise if the Cancel button is pressed, all unsaved changes are rolled back. As you can imagine, it is possible to configure the application to have each row change immediately propagated to the database. The application is almost ready to run, but there is a problem lurking. Apparently there is a bug when working with VS 2005 (or maybe this feature is something that only works with SQL Server), but the SQL statements that are automatically generated for us by the wizard to INSERT and UPDATE rows in the database table is incomplete. Therefore, we have to fix this, which we can do with the following steps:
In the Properties window, you will see a property called “InsertCommand”. Click the plus sign to the left of the property name to show all the sub-properties. Sub-property CommandText contains an incomplete SQL INSERT statement. Click on the builder icon (the button with three dots) to build the complete statement. The Query Builder window will appear and will allow you to build the command graphically. For each column added to the statement, make sure you enter a question mark in the corresponding “new value” column. The question mark represents a parameter that will be populated automatically from the DataGridView column of the same name as the table column. Alternatively, you can paste the INSERT statement below into the SQL portion of the query builder window: INSERT INTO CORPDATA.DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) VALUES (?, ?, ?, ?, ?)
UPDATE CORPDATA.DEPARTMENT
SET DEPTNO = ?, DEPTNAME = ?, MGRNO = ?,
ADMRDEPT = ?, LOCATION = ?
WHERE (DEPTNO = ?)
Press F5 again to run the application. When your form appears, change some data and then click the save button; the changes should be stored permanently. Likewise, make some changes or deletions and press cancel and all of your changes will be undone. Also note that the DataSet object understands some of the database rules against the data. For example, it knows, from the metadata received from the database server, that certain columns are not allowed to contain NULLs and will not allow the program to leave those columns blank. It also knows which column must be unique and can enforce that rule, to a certain degree, without being connected to the database. As you might have guessed, the database can also spew data integrity errors once the batched changes are sent to it. Finally, note you can adjust the size of the Grid columns like in Excel, and you can click the row handle to select a line and then delete it. Exit the application now and go back to VS. Adding a Navigation Control At this time, there is one more control that I would like to introduce for this kind of Windows form: the BindingNavigator control. This control is an MS Access like control that informs the user how many rows there are in the DataGridView, which row number is currently being edited, etc. It also provides buttons for going to the first and last rows in the table and another set of buttons for moving backward and forward among records. Finally, by default, the BindingNavigator displays add and delete record buttons. The great thing about this control is that it binds itself to the same underlying data source as the DataGridView control. So that as you click the navigation buttons or delete a row, the DataGridView is updated automatically. That’s right, no code! Go to VS and view your form in the designer (not in the source code editor). Locate the BindingNavigator on the Toolbox (under the Data tab) and click it. Then click anywhere on the form and you will see the BindingNavigator’s toolbar display appear at the top of the form. (Unless you left room between the top of the DataGridView control and the top of the form, you may need to select the DataGridView control and resize it so there is enough room for the toolbar.) As shown in Figure 4, the BindingNavigator provides quite a bit of built-in functionality to help users navigate among rows in the DataGridView.
The only thing we need to do to make the BindingNavigator work as advertised is to set its BindingSource property. This source should match the DataGridView’s DataSource property. In this case, the source is called “DepartmentBindingSource”. In the form designer, click on the BindingNavigator and press F4 to view its properties. (If the lightning bolt is still selected, you are viewing the controls events, not its properties. Click the button to the left of the lightning bolt, it looks like a bullet list, to view the properties again.) Locate the BindingSource property and select the value of dEPARTMENTBindingSource. Now, press F5 again to run the form. When a row is selected on the DataGridView control, the change is reflected in the BindingNavigator and vice versa. You can also use the toolbar buttons to add (position to the empty grid row) and delete a grid row. Normally, as a space saver and a matter of windows convention, I would have put the Save and Cancel buttons as additional icons on the BindingNavigator tool strip rather than place them as distinct buttons on the bottom of the form. However, that would’ve complicated this already long example so I dropped the idea! And there you have it, a .NET subfile equivalent, complete with the ability to scroll, sort, and maintain data! Again, at this basic level when proficient, I would guess it would take someone an average of 20 minutes to do something similar on their own (not including the parameterized SQL statement creation). Of course, there are more things to be considered here to make the application complete. For one thing, we need to introduce better error handling. For another thing, when working with numeric or date data, we need to mask the input to the text boxes to make sure we don’t send invalid data to the database server. We can also change the form colors, change column headings, etc. It will take time to learn all of these tweaks, but as you can see the majority of the form is done with a little code. The C# source code for this project can be downloaded here. Don’t forget to edit the app.config file to change your DSN, user and password before running it. .NET and the System i OK, so we’ve finished yet another tutorial. As promised, .NET can be a valuable development framework in System i shops. Its ability to offer rapid application development to new comers is second to none. Further, when the developers mature and are comfortable with the environment, .NET is versatile enough to allow most behaviors to be overridden and hence does not suffer the complexity limitations that other RAD environments offer. Finally, there are plenty of tutorials, blogs, questions, and answers plastered all over the Web, so there’s no excuse for not learning how to do something. Whether Web, Windows, or mobile front-ends, whether SQL Server, Oracle, or DB2 on the backend, .NET provides a compelling framework for System i developers seeking new skills to learn. Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. You can contact him through our contact page. RELATED ARTICLES System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model System i Developers and .NET 2.0, Part 2: Web Development Using ASP.NET AJAX
|
