System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model
Published: September 19, 2007
by Michael Sansoterra
Note: The code accompanying this article is available for download here.
IBM's AS/400 platform (now called the System i) boasts a wonderful history of almost 20 years. Those faithful to the platform well know its strengths and the ingenuity IBM has put into the box, which have allowed it to be successful for so long. That being said, it is no secret that many System i shops maintain legacy applications using old tools. That, in turn, has often left developers out of touch with current developer tools and languages. With so many options, it is difficult to know how to select a new development paradigm, and further, it is time consuming and often frustrating to learn an entirely new skill set while also doing one's normal job.
Microsoft .NET is one option that System i developers have when adopting a new development environment. Why .NET? Two reasons to adopt .NET are popularity and versatility. Popularity is huge in terms of available resources, information on the internet, and having a sense of job security. Versatility refers to the ability of the development paradigm to be able to solve a wide array of problems equally well. In the case of .NET, it is versatile in that it can be used to program Windows applications, Web applications and mobile device applications using one or more programming languages that share a common set of APIs. Further, .NET is object oriented and has an extensive API and event model that allows a high degree of tailoring for complex tasks. At the same time, .NET is easy to use for the most common tasks and therefore does not require much tailoring until the difficult tasks arise. One other thing can be said about .NET is that it has many options for integration with the System i platform, so that the value of legacy applications can be preserved and enhanced.
This article series is aimed at AS/400 or System i developers who are considering embracing .NET as a new development technology that is both powerful and able to integrate with the System i platform. Plenty of articles explain what .NET is and how it works and how to write code in it. All of that takes a lot of time to explain and can cause developers frustration as they try to keep track of it all, so I've decided to adopt a "show and tell" approach.
I will be running through a series of examples on how to do things in the .NET environment with the aim of demonstrating how much can be accomplished in a relatively short amount of time. One great thing about .NET is that it has plenty of features for the novice and the pro. The novice approach, I believe, is what many System i developers will need to aid in their decision to adopt or pass over .NET--there may not be many who are able to delve into the .NET environment whole heartedly. Because of this simple approach, I do very little .NET coding in these samples. If .NET is a desirable product for your shop, you can pursue more thorough tutorials and examples on all the ins and outs of the development environment.
This first article demonstrates how to create a Data File Utility (DFU) style Web page that will allow users to add, insert and update rows against a DB2 table or view. Best of all, I'm going to demonstrate this without writing a single line of .NET code. We will need to do a little SQL, but it will be basic stuff!
Before we delve in, here's a little background info that will help you understand a few things about the .NET world:
- The .NET Framework, among other things, is a set of easy to use application programming interfaces (APIs) that can access Windows resources, database servers, etc.
- In the .NET world, anyone who has the desire and knowledge can create a .NET compatible language. .NET has several available languages, including RPG.NET and COBOL.NET. The two most popular .NET languages are made by Microsoft and are named VisualBasic.NET and C# (pronounced C sharp). VB.NET is similar in many ways to the long popular Visual Basic for Applications language. C# is very similar to Java.
- .NET version 2.0 is the version of the programming environment that will be used. Currently, the latest version of .NET is 3.0, which is nothing more than the 2.0 framework with a new set of APIs bolted on.
- Visual Studio 2005 is the current Integrated Development Environment (IDE) for creating .NET applications. This program has wizards and tools for creating Web pages, Web services, Windows applications, Windows services, etc. A new version of Visual Studio is expected to be released in 2008.
- ASP.NET stands for Active Server Pages .NET. Active Server Pages is Microsoft's server side technology used for creating Web pages and Web services.
- The title of this article includes the phrase "declarative programming model." Just what is this? It's just a simpler approach to doing things by using wizards and filling in certain properties. In years past you had to write tons of code to create a Web page with as much functionality as the one were about to write. But using the pre-built controls on the Web page, you only need to "declare" or fill in certain pieces of information that will allow the pre-built code to do all the work for you. This is the approach Microsoft has taken to open up the complex world of Web programming to novices. Of course, for die-hard, propeller-head developers, Microsoft has also created a very rich object model that can be very fine tuned by people who need to churn out lots of code to meet the requirements for their applications.
If you're interested in following along, here's what you'll need:
- Visual Web Developer 2005 Express Edition installed. This is a free Microsoft product (download here) that is a stripped down version of Visual Studio 2005. Visual Studio 2005 will work as well if you have it, although the menu options may be a little different than what is presented here. This free Web Developer product is only used for building Web applications - you cannot build Windows applications with this product.
- The DB2 for i5/OS CORPDATA sample data library. You can do this by executing the following SQL statement in your favorite SQL utility (QM Query, STRSQL, System i Navigator, etc.):
Call QSYS.Create_SQL_Sample ('CORPDATA')
Sorry, I am quite tired of using the old and overused QCUSTCDT table for all of my demonstrations, I needed something different! Instead, we will be building a Web page to edit the CORPDATA/DEPARTMENT table.
System i Access installed including the ODBC component.
Knowledge on how to configure an ODBC connection to the System i. (There are plenty of Web references on this!)
Knowledge of basic DB2 SQL.
Some experience with a visual programming environment would be helpful.
In this article's example, we're going to drag a couple of controls on a design window within Visual Studio, follow the steps in a few wizards, and fill in a "few blanks" here and there. When we're done, we'll have built a Web page that can display and edit tabular data that is stored in a DB2 database table.
Also, if you're unfamiliar with Visual Studio 2005 or Visual Web Developer Express take a minute to look at Figure 1 below:
This will help familiarize you with a few very important Windows panes in the Visual Web Developer IDE, as noted below in Table 1:
shows all of the folders, Web pages, class modules, images, etc. that are
included in the current Web site. Double clicking on a Web page, for example,
opens the Web page in the designer window.
2 - Design
/ Source view
the view of a Web page between the visual design of the page and the
underlying source view. The source view consists of HTML and some additional
ASP.NET control tags and directives.
toolbox contains all kinds of controls that can be placed on a Web page. The
controls range from simple HTML text boxes to complicated "databound"
controls that automatically marry database data to a control on the page.
4 - Smart
little arrow provides access to a specific menu that is relevant for a
particular task. For instance, in the example above, clicking on that button
will bring up a menu with tasks that can be used to configure the SQL Data
properties window is used to control various characteristics of a control or
a Web page, such as how a control behaves or what it looks like.
the area where developers build their documents, whether they are Web pages,
XML documents or whatever. In the case of Web pages and Windows forms, these
documents can actually show an approximation of what the finished product
will look like. In this article, I refer to this window as the design
window because it is where Web pages are built.
Table 1: These are some common Visual Web Developer Windows shown in their default locations.
Lesson 1: Create a Page with a GridView Control
OK, now that we've got that out of the way, let's walk through, step by step, how to create one of these pages!
1. Start Visual Web Developer 2005 Express Edition
2. Choose File --> New Web Site
3. Under Installed Templates, choose ASP.NET Web Site. The default language options are C# or VisualBasic. You can pick either one--since this is all declarative work, no code is needed. Finally, specify a location: File System x:\yourpath\GridViewSample
4. A blank Web page named Default.aspx will appear in the design window (if not, locate the Solution Explorer Window (Ctrl+Alt+L).) Then, double click on Default.aspx from the Solution Window. The .aspx extension is used for Web pages written with Microsoft's ASP.NET technology. By default, the Web page document will come up in source mode – that is the mode that allows you to view the HTML source of the Web page. Put the Web page in design mode (by clicking on the "design" tab located in the lower left portion of the designer window.) You are now ready to build your Web page visually rather than reviewing HTML source.
5. Locate the toolbox window. (Use Ctrl+Alt+X if it is not present.) Expand the Data node. Drag the SQLDataSource control from the toolbox to the designer window. For the record, an SQLDataSource control is used to provide SQL data access to a Web page. Please note that this is a declarative control and although it is represented in the designer as a big gray box, it will not render as a box on the Web page in a browser.
6. Click the "Configure Data Source" option.
7. Click new connection and choose "Microsoft ODBC Data Source" option. I had hoped that IBM would create a DB2 for i5/OS native driver for .NET 2.0, but thus far they have not. In my opinion, the next best access option we have is using the System i Access ODBC driver.
8. If you have an existing DB2 ODBC connection setup in your ODBC applet (Control Panel --> Administrative Tools) then you may select it from the drop down list. Alternatively, you can build a connection string on the fly by using the connection string option, followed by the build button. At this point, I won't go into the intricacies of building an ODBC connection. Generally, when configuring a DB2 for i5/OS ODBC connection, I choose the "System Naming Convention" and a commitment control level of "*NONE."
9. When finished configuring the ODBC connection, choose Next. You will be asked if you want to save the connection. Click the "Yes" checkbox and save the connection as DB2Data. Saving the connection will store the ODBC connection string in a special application configuration file called Web.config (which can be viewed by double clicking on it in the Solution Explorer.) This XML file is accessible to the entire application--by saving the ODBC settings, you can access the connection string when you add new data pages to your application. This way, you store the database connection string in one place and simply re-use it as needed. Click next to continue the wizard.
10. The SqlDataWizard is now letting us configure a SELECT statement which will be used to retrieve the data into our GridView control.
11. Choose "Specify a custom SQL statement or stored procedure" and then paste in the following SQL statement:
Select DeptNo, DeptName, MgrNo, AdmrDept, Location
This example assumes that schema (library) CORPDATA is in the ODBC job's library list and that the system naming convention is used.\r\n \r\nAlternatively, you could always build the SELECT statement with the wizard.
12. Click Next and click the Test Query button to make sure everything works. If you get an error, there is probably a problem with your ODBC connection string or your machine's connectivity to your System i.
13. Click Finish to end the wizard.
14. Note that every control on a Web page is given a unique identifier, or simply called its "id". By default, our SqlDataSource control is called SqlDataSource1. Normally we would give it a more intelligent id, but we'll leave it as SqlDataSource1 for now.
15. Next, locate the toolbox again, find the Data node and drag the GridView control from the toolbar to the designer. By default, its id will be GridView1. The GridView control is very powerful as it displays a matrix of rows and columns similar to what you would see in an Excel spreadsheet. Further, the GridView control allows users to edit and sort data as well.
16. The GridView tasks "smart menu" will appear. In the "Choose Data Source" box, select the SqlDataSource1 control. This instructs the GridView to get its data from the SELECT statement configured in the SqlDataSource1 control. Also, click the "enable paging" and "enable sorting" options. These marvelous options allow you to control the number of rows that appear in your grid (the default number of rows per page is 10) and will automatically allow your grid to be sorted by a given column by clicking a column heading. How much code did we write thus far to get this functionality? Zilch! You can see even in the design window that the GridView knows what database columns it is going to bind against, thanks to the information provided to it from the SqlDataSource1 control. If your table's column names don't appear right away in the designer, click the "Refresh Schema" option on the GridView's smart tag menu.
17. Press F5 right now to run the application. You will get a Web page with tabular data on it. (If you're prompted to modify the Web.config file to enable debugging on your development PC, just choose OK as you will most likely find the debugging of server side features useful.)
18. Your default browser will start and you should see something similar to Figure 2, shown below:
Notice that you can sort by clicking a column heading or you can move among the data page by clicking the page numbers that appear in the lower left side of the GridView control. Incidentally, the type of GridView paging presented to the user is configurable by adjusting the PagerSetting properties. For instance, you can have it show "Next Page", "Prev Page" type indicators or replace the text links with images. When you're done admiring the page, close the browser. (If you enabled debugging, you may need to close the browser so that you can edit your page within the designer.)
Lesson 2: Enable the GridView Control to Update and Delete Database Rows
OK, that was easy enough. Let's enable our GridView to update and delete rows.
1. In the designer window, click on the SqlDataSource1 control. Once the control is highlighted, press F4 to view the properties window (by default located in the lower right corner of the designer.) The SqlDataSource1 control has many properties. Find the "DeleteQuery" property and then click the query builder button (the button to the far right of the property value box that has the three dots.) In the "Delete Command" window, paste in the following SQL DELETE statement:
DELETE FROM Department WHERE DEPTNO = ?
Click OK to close the Command and Parameter Editor window.
Repeat this process for the InsertQuery and UpdateQuery properties. Paste in the following SQL statements into their respective properties:
INSERT INTO Department (DEPTNO, DEPTNAME, MGRNO,
ADMRDEPT, LOCATION) VALUES (?, ?, ?, ?, ?)
SET DEPTNAME = ?, MGRNO = ?, ADMRDEPT = ?, LOCATION = ?
WHERE DEPTNO = ?
The questions marks in these statements represent parameter markers. Fortunately, ASP.NET does all the work for us as it will take the data entered into the GridView control and automatically set the appropriate parameters in the SQL statements. The SQL statements can also be stored procedure and table function calls – however, your parameter names should match the column names from the base table – otherwise you'll have more work to do to map column names to parameter names. This type of trade off is the blessing and curse of declarative programming.
2. Note also that all these SQL statements can be generated automatically by using the wizard as presented in step #11 in Lesson 1 referenced above. If your table has a unique key defined, click the Advanced button and select the option to automatically generate Insert, Update and Delete statements. This wizard has one drawback for DB2 though, in that it delimits all column and table names with square brackets (which is a Microsoft SQL Server convention.) These square brackets will make DB2 choke. So if you choose to use the wizard, it will probably save you time but you'll need to remove the square brackets from the generated statements!
3. Close the GridView Task menu by clicking the visible area of the GridView1 control. Press F4 to open the properties window for GridView1. Locate the DataKeyNames property and enter:
The data key names should be the column or columns that make up a unique key. If you have a composite key, separate the column names with a comma. Without specifying this property, the GridView control will not give you the option to configure automatic updates and deletes! Also note that the database column names in the WHERE clauses of the UPDATE and DELETE statements must match the DataKeyNames property. This unique key is used to correlate rows in the grid with rows in the database table.
4. Now that our SQLDataSource control has the capability to perform inserts, updates and deletes, click on the GridView1 control, and click its smart tag. The GridView tasks menu will appear. Since the wizard senses that our SqlDataSource1 control knows how to perform updates and deletes, it has offered us two new options: Enable Editing and Enable Deleting. These great capabilities allow the GridView control to execute our SQL Update statement whenever data in the grid has been edited, or to execute our Delete statement to delete a row altogether. Enable these two options. Figure 3, shown below, shows a picture of the editor with the GridView's smart tag menu present.
5. Press F5 to run the application. You will now be able to edit or delete rows in your GridView. Here are a few warnings. By default there are no validations in place. Therefore, don't type in names that are too big to fit in the database column, and be careful to type in numeric data in boxes that require numeric data. Also, if you click the delete button, your row will be gone immediately--there is no confirmation prompt without writing code! When finished, close your browser or click the stop button in the Visual Studio editor.
OK, at this point we have a GridView control that mimics the Data File Utility (DFU) that we're used to in the green screen world. However, we can now easily create a browser based editing system for any table or view we want and we can easily include page, sort, edit, and delete operations by simply setting a few properties.
But, how do we insert data? Well, the GridView control was not designed to insert data. Alas, another control called a FormView will allow us to insert data.
Lesson 3: Inserting Data with a FormView Control
1. In the design window for the Web page, position the cursor under the GridView control. Press the Enter key a few times to leave a blank line under the control and then enter the text: "Add a New Record." Press Enter again.
2. Once again, locate the toolbox and find the data node. Within the list of data controls, select the FormView control and place it onto the design window (preferably drag it to the empty space below the GridView.) By default, this control's id will be FormView1.
3. The FormView tasks menu will appear. For the ChooseDataSource option, select SqlDataSource1 from the list.
4. Click the FormView1 control and press F4 to view its properties. Find the DefaultMode property and change it from ReadOnly to Insert. This setting will set the control's sole purpose to add new records (since the GridView control does everything else.) Notice that once again the FormView control generates boxes for each column to insert into the database table. Locate the DataKeyNames property and enter:
And now the form view control understands the unique key on the table.
5. Press F5 to run the application. You will now be able to use the FormView control to insert new rows into the Department table. Simply type data into the text boxes and click the insert link. When insert is clicked, .NET knows to use the data from the HTML text boxes and place them appropriately into the table column values using the SQL Insert statement we defined earlier.
Figure 4: Shows a figure of the Web page with the FormView control.
When trying this application, you may find it difficult to insert or update data. Why? Because there are a few constraints on the database table:
- Certain columns are not allowed to contain NULLs.
- The Manager# column, for example, has a foreign key constraint that requires the manager number to match a number in the Employee table.
- There is a unique key constraint on the Department table so department has to be unique. If DB2 doesn't like the data it's given, it will throw an error and ASP.NET will report the error.
The source code with a few enhancements such as the images can be downloaded here. The final product can be viewed in Figure 5, shown below:
I simply added our company logo and few images for the GridView control which I filched from an old ASP.NET starter project. You will need to edit the Web.config file and fill in your ODBC connection's user name and password in order for it to work with your system. The portion you'll need to change looks like this:
Fill in your ODBC connection DSN and user namd and password
Of course, there is much more to do and some of these things will require a little code, but the lion's share of the work required no code. To continue this application, normally one would:
- Add validation controls or code to validate input to make sure numeric columns can only have numeric data, to make sure date columns have valid dates, make sure text data has the proper case, etc.
- Add error-handling logic, especially when the database can throw back multiple errors, as is the case with the editing of the Department table.
- Limit the amount of data that can be typed in to a control. Limit your page's text boxes to 30 characters when inserting into a CHAR(30) column field, for instance.
- Add code for further business rule or database validation.
- Add a confirmation before actually removing the row from the database! What if someone clicks the delete button by accident?
- Use VARCHAR columns, rather than CHAR columns, as the trailing spaces usually make the editing experience unpleasant. Therefore, I normally use the RTRIM function for CHAR columns that have the potential for white space on the end.
- Replace some of the links with pictures to make the page more attractive.
- Add a default replacement for the NULL value to database columns that are not null-capable. For example, an empty box can be set to mean 0 or an empty string or some other default value.
- Include other controls from the GridView. For instance, when dealing with a pre-built list of choices, a DropDownBox control can be used instead of a simple TextBox. If a DropDownBox is better suited for a particular column, that aspect of the GridView control will need to be overridden.
- Show the user information that is not contained in a single base table. While there are many ways to solve this problem, one way (which requires some database expertise but will not require tinkering with more .NET properties) is by creating an updateable multi-table view. This can be accomplished by using something known as INSTEAD OF triggers.
- Clean up the look of the page as the default look of the form view control especially is less than appealing. Very often you'll use the design window's source view to do this.
- Add a filter of some type (usually on the SQL Select statement) should be implemented to limit the number of rows returned from the server. The sample data table had only a handful of rows. However, we don't want to use this technique to load a table with 10 thousand rows which is too much to page through.
- Add a title to the page by editing the source code. You can do this by clicking on the source view of the design window. At the top, you should see some code similar to this:
Changing the text within the title tags will control what the page is called when it appears in a browser.
deploy the project to a Web server (Microsoft IIS).
While this "fill in the blank" declarative approach does have some limitations that will eventually require some code to assist, it does get an awful lot done in a small amount of time. There may be many objections to this initial approach to building a Web page. (Old style HTML is generated by the GridView control, the database wizard generates DDL statements against a table instead of using stored procedures, the declarative approach may run into some limitations, etc.). Yet the point is that .NET provides an easy and productive way to get started. You can first build Web pages that function. As you learn about more controls, error handling and such, you can add that in. And eventually developers will be able to move on to a more sophisticated approach to developing their Web sites. This is what makes .NET cool--it can do plenty no matter what level of sophistication you have and you can always incrementally advance as time allows.
In the future, I'll continue this series on .NET with a few more articles. The next one will demonstrate how to add AJAX functionality to the above Web page so that operations on the Web page (inserting, deleting, sorting, etc.) do not cause the browser to reload the page (and thereby avoid that irritating page blank/rebuild cycle.) In a third article, I'll demonstrate this same technique of editing data from within a Windows application.
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.
Visual Studio SP1 Express Editions
Visual Studio SP1 with Windows Vista Updates, download this update for Visual Studio Express Web Developer, if you're using Windows Vista.
Post this story to del.icio.us
Post this story to Digg
Post this story to Slashdot