Prerequisites

Before you begin the exercise of building an application and a Web site, make sure you have already performed the following steps:

• Download and install the IBM Database Add-ins for Visual Studio 2005. For download and install instructions, please visit the developerWorks Information Management Visual Studio .NET zone.
• Create the DB2 UDB sample database.

Using Visual Studio 2005 greatly simplifies the task of building Windows applications, requiring you to write not a single line of code. The default generated application functionality depends on the definition of the table being used. You can build Windows applications using the Microsoft wizard to create a data source or using drag and drop.

• The "Build a Windows application for department table using the Microsoft wizard" section of this article walks you through the creation of a Windows applicaiton for a simple department table using the Microsoft wizard.
• "Build a Windows application for MyTable table using drag and drop" shows you how to create a table with a primary key and creates a Windows application using drag and drop.

Back to top

Build a Windows application for department table using the Microsoft wizard

To create a Windows application for a simple department table using the Microsoft wizard, follow the steps below:

1. Create a new Windows application using the File > New > Project menu. Call it departmentApp. In this article, we will create a C# application.
   
   Figure 1. Creating a new C# Windows application
   
   
   
   Note that the same steps in this article can be followed to create a VB.NET application.
2. Add a connection to DB2 sample database in Microsoft Server Explorer.
   1. Enter your server name, your database name as "sample," your user name, and your password.
   2. Check option for "Save my password."
   3. Expand the "Specify filtering options" section and specify a filter for Tables as "DEP." Note that filters follow a like search pattern and are case sensitive.
      

      Adding this filter will retrieve all tables starting with "DEP." Note that adding this filter is optional for application development, but is recommended for better design time performance if you have a large number of tables in your database.

   4. Optionally click Test Connection. Click OK. A connection to the sample database will be added in Microsoft Server Explorer.
   
   Figure 2. Add Connection to DB2 sample database
   
   
   
   
3. Create a data source for department table.
   1. Using the top level menu Data > Add New Data Source, invoke the wizard.
   
   Figure 3. Add new data source wizard
   
   
   2. In the wizard, keep the default selection for "Database." Click Next.
   3. From the list of connections, select the connection to sample that was created earlier. Select the option to include sensitive data in connection string. Click Next.
   
   Figure 4. Select database
   
   
   4. Optionally check the box to save the connection string as "sampleConnectionString." Click Next.
   5. Select the Department table from the list of tables, and click Finish.
   
   Figure 5. Select department table
   
   
   6. A data source for department will be added in the "Data Sources" window. You can make the data sources window visible using the top level menu Data > Show Data Sources.
   
   Figure 6. Department data source added
   
   
4. Design the Windows application.
   1. Ensure the Form1.cs is open in design mode.
   
   Figure 7. Form in design mode
   
   
   2. Drag and drop the department data source from the data sources window onto the form. Notice the default-generated user interface.
      
      
   
   Figure 8. Designed Windows form
   
   
   
   
5. Run the Windows application.
   
   
   Figure 9. Running departmentApp
   
   
   
   You can use the pre-created user interface to navigate the rows. You can also add new rows.

Analysis of code generated

As mentioned previously, the definition of the table used to create a data source defines the functionality that is pre-generated.

Let's look at the definition of the department table. Follow the steps below:

1. Using Microsoft Server Explorer, expand the connection to sample database. Expand the "Tables" folder, and right click on the Department table. Select Show Script context menu.
2. The IBM Script Designer displays the script used to create the department table. Notice that the department table does not have any primary keys, hence the Windows application created allows inserting new entries and viewing existing entries for the department table.
   
   
   Figure 10. Department script
   
   

Let's now look at the default SQL statements generated on the Department Data Source. Follow the steps below:

1. Using the data sources window, right click on the Department data source, and select Edit using Dataset Designer. The Microsoft Data Sources designer is opened with the department data source.
2. On the open data source designer, select DepartmentTableAdapter, and right click to view its properties. Notice that the select and insert statements have been pre-generated.

Back to top

Build a Windows application for MyTable table using drag and drop

In this section, create a Windows application using drag drop for a table with a primary key.

First, create a table with a primary key. There are several ways to create a table using IBM tools for Visual Studio, such as using the IBM Table Designer. In this article, we will use the IBM Script Designer to execute a script that creates a table with a primary key.

Follow the steps below:

1. Using Microsoft Server Explorer, right-click on the sample connection, and select New Script.
   
   
   Figure 11. Creating MyTable
   
   
   
   
2. Copy and paste the following script into the script designer:

						
						
CREATE TABLE MyTable
(ID           SMALLINT NOT NULL,
NAME         VARCHAR(9),
DEPT         SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB          CHAR(5)  CHECK (JOB IN ('Sales', 'Mgr', 'Clerk', 'Tech')),
HIREDATE     DATE,
SALARY       DECIMAL(7,2),
COMM         DECIMAL(7,2),
PRIMARY KEY (ID))
@
INSERT INTO MyTable VALUES (1,'John', 15, 'Mgr', '1990-01-01' , 40000.00, 1000.00)
@
INSERT INTO MyTable VALUES (2,'Smith', 15, 'Tech', '1995-12-10' , 25000.00, 1000.00)
@
INSERT INTO MyTable VALUES (3,'James', 15, 'Sales', '1997-05-21' , 10000.00, 5000.00)
@

        

3. Click Execute. Notice that the IBM Database message pane shows "Executed successfully" message. The table is now created on the database.
4. Let's now modify the filter criteria on the "Tables" folder to show items starting with "MY". Using Microsoft Server Explorer, right-click on the sample connection, and select "Modify Connection". Expand the "Specify filtering options" section, and change the filter on the "Tables" folder from "DEP" to "MY." This will refresh the connection, and an entry for "MYTABLE" will be created under the "Tables" node. Use this table to build the Windows application.
   
   
   Figure 12. Modify connection
   
   

Building the Windows application using drag and drop:

1. As in the previous section, create a new Windows application using File > New > Project menu. Call it "MyTableApp." In this article, we will create a C# application.
2. Using the Microsoft Solution Explorer, right-click on the MyTableApp, and select Add > New Item.
   
   
   Figure 13. Add new item
   
   
   
   
3. Select DataSet. Click Add. The Microsoft Dataset designer will be opened.
   
   
   Figure 14. Add new dataset
   
   
   
   
4. From the Microsoft Server Explorer, drag drop "MyTable" onto the open designer. This will create the data source for MyTable.
5. Ensure the password is added to the connection string. Select MyTableTableAdapter, and right-click to view its properties. Append the password to the connection string using the format Password = yourpwd.
   
   
   Figure 15. MyTable TableAdapter
   
   
   
   
6. Designing the Windows application:
   1. Ensure the Form1.cs is open in design mode.
   2. Using the Microsoft data sources window, use the smart menu on the MyTable data source, and select Details.
   
   Figure 16. MyTable data source details mode
   
   
   
   
   3. Drag drop the MyTable data source from the data sources window onto the form. Notice the default-generated user interface contains text boxes .
   
   Figure 17. Form design for MyTable data source
   
   
   
   
7. Running the Windows application:
   1. Run the application using top level menu Debug > Start without debugging. You can use the pre-created user interface to navigate the rows. You can delete, insert rows, and change the existing data. Simply click the Save button after all actions are complete, and the insert, update, or delete actions will be saved to the database.
   
   Figure 18. Update MyTable
   
   

Analysis of code generated

As previously mentioned, the definition of the table used to create a data source defines the functionality that is pre-generated.

The "MyTable" table we created earlier had a primary key. Let's now look at the default SQL statements generated on the MyTable data source. Follow the steps below:

1. Using the data sources window, right-click on the MyTable data source, and select Edit in Dataset Designer. The Microsoft data sources designer is opened with the department data source.
2. On the open data source designer, select MyTableTableAdapter, and right-click to view its properties. Notice that the select, insert, update, and delete statements have been pre-generated, hence the Windows application created allows inserting new entries and viewing, deleting, and updating existing entries into the MyTable table.

Back to top

Building a Web site using IBM tooling

We will build a Web site using the MyTable table created in the previous section. Follow the steps below:

1. Create a new Web site using the File > New Web site menu, create a Web site and, call it "MyWebsite."
   
   
   Figure 19. Create new Web site menu
   
   
   Figure 20. Create new Web site
   
   
   
   
2. Ensure Web site is opened in design mode using "View Designer" context menu on Default.aspx.
   
   
   Figure 21. Web site design mode
   
   
   
   
3. From the toolbox window, expand the "Data" section, and drag drop SQLDataSource onto the open designer.
   
   
   Figure 22. Drag drop datasource
   
   
   
   
4. Use the right menu on the on the SQLDataSource, and select Customize Data Source. A wizard will be invoked. Complete the wizard as follows:
   1. Select the connection to sample database. Click Next.
   2. Select MyTable from the list of tables. Click Next.
   3. Select all columns by checking the box for "*." Click Next.
   4. Click Advanced, and check the boxes to generate, insert, and update statements.
   
   Figure 23. Select MyTable in wizard
   
   
   
   The data source has now been customized for "MyTable."

5. Binding a grid to the data source:
   
   Follow the steps listed below:
   1. From the toolbox window, drag drop a GridView onto the dataset designer.
   
   Figure 24. Drag drop GridView
   
   
   2. Select SqlDataSource1 from the list of data sources. Also check boxes to "Enable editing" and "Enable deleting."
   
   Figure 25. Customize GridView
   
   
   3. From the toolbox drag drop "DetailsView" control on the dataset designer:
   
   Figure 26. Drag drop DetailsView
   
   
   
   
   4. Select SqlDataSource1 from the list of data sources. Also check boxes to "Enable inserting."
   
   Figure 27. Customize DetailsView
   
   
   
   The user interface is now bound to the data source for "MyTable."
6. Running the Web site:
   
   Using the top level menu for "Debug," select Start without Debugging.
   You can now browse existing rows, insert new rows, or update and delete existing rows.

Analysis of code generated

The "MyTable" table created earlier had a primary key, hence the SQLDatasource wizard allowed creating delete and update statements. The Web site allows browsing, inserting, updating, and deleting rows. Note that when tables without primary keys are used, only select and insert statements are generated.

Back to top

Conclusion

In this article, the new paradigm for building windows applications and Web sites for DB2 was introduced. The article demonstrated how developers can build such applications without writing any code using IBM Database Add-ins for Visual Studio 2005.

Resources

Learn

• "DB2 UDB for Visual Studio 2005 developers, Part 1: Overview of IBM Database Add-ins for Visual Studio 2005" (developerWorks, December 2005): This article provides an overview of the new functionality provided by IBM for Visual Studio 2005.
  
  
• For more information about the add-ins for DB2 UDB for .NET, visit the IBM DB2 UDB for .NET page.
  
  
• Webcast: Developing DB2 applications with Visual Studio 2005 and .NET Framework 2.0: This webcast presented by Leon Katsnelson explains how it is easier than ever to develop .NET applications for DB2 servers.
  
  
• " Application development with DB2 UDB" (developerWorks, 2005): These e-books explain three methods for developing for DB2 UDB: .NET, Java, and open source.
  
  
• DB2 UDB Information center: The chapter on the .NET Data Provider is a good reference.
  
  
• developerWorks Information Management Visual Studio .NET zone: Find more resources for DB2 UDB .NET developers.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  

Get products and technologies

• IBM DB2 Universal Database Enterprise Server Edition V8.2: Download a trial version of DB2 Universal Database.
  
  
• IBM trial products for download: Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• developerWorks Information Management blogs: Get involved in the developerWorks Information Management community.
  
  

About the author

Sonali Surange is an Advisory Software Engineer working at the IBM San Francisco lab, providing tools for seamless integration with IBM Servers on Visual Studio .NET.

Rate this article

Comments

Back to top

Table of contents

• Prerequisites
• Build a Windows application for department table using the Microsoft wizard
• Build a Windows application for MyTable table using drag and drop
• Building a Web site using IBM tooling
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers