System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model
September 19, 2007 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:
If you’re interested in following along, here’s what you’ll need:
Call QSYS.Create_SQL_Sample ('CORPDATA')
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:
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:yourpathGridViewSample 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 From Department This example assumes that schema (library) CORPDATA is in the ODBC job’s library list and that the system naming convention is used.rn rnAlternatively, 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 (?, ?, ?, ?, ?) UPDATE Department 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: DeptNo 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: DeptNo 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:
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: <connectionStrings>
<!--
Fill in your ODBC connection DSN and user namd and password
-->
<add name="DB2Data"
connectionString="Dsn=YOUR_DSN;uid=YOUR_USERNAME;
pwd=YOUR_PASSWORD;DBQ=,QTEMP,CORPDATA;NAM=1;CMT=0"
providerName="System.Data.Odbc"/>
</connectionStrings>
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:
<head runat="server">
<title>Untitled Page</title>
</head>
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. RELATED RESOURCES 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.
|
