DB2 UDB for Visual Studio 2005 developers, Part 2: Build applications and Web sites for DB2 using IBM Database Add-ins for Visual Studio 2005

IBM® tooling for Visual Studio 2005 greatly simplifies the task of building Windows® applications and Web sites that access DB2® Universal Database™ (DB2 UDB). IBM tooling provides deep integration for DB2 connections in Microsoft® Server Explorer. It allows you to use existing Visual Studio 2005 wizards or drag and drop to build Windows applications and Web sites, without having to write a single line of code.

Refer to Part 1 of this series for an overview.

In this article, build a Windows application and a Web site for DB2, using IBM tooling for Visual Studio 2005. The article follows the new and easy paradigm for building such applications and Web sites introduced in Visual Studio 2005.

Share:

Sonali Surange (ssurange@us.ibm.com), Developer, EMC

Sonali Surange photoSonali 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.


developerWorks Contributing author
        level

22 December 2005

Prerequisites

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

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.


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
    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
    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
    Add new data source wizard
    1. In the wizard, keep the default selection for "Database." Click Next.
    2. 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
    Select database
    1. Optionally check the box to save the connection string as "sampleConnectionString." Click Next.
    2. Select the Department table from the list of tables, and click Finish.
    Figure 5. Select department table
    Select department table
    1. 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
    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
    Form in design mode
    1. 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
    Designed Windows form
  5. Run the Windows application.

    Figure 9. Running departmentApp
    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
    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.

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
    Creating MyTable
  2. Copy and paste the following script into the script designer:
Listing 1. MyTable SQL script
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)
@
  1. Click Execute. Notice that the IBM Database message pane shows "Executed successfully" message. The table is now created on the database.
  2. 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
    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
    Add new item
  3. Select DataSet. Click Add. The Microsoft Dataset designer will be opened.

    Figure 14. Add new dataset
    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
    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
    MyTable data source details mode

    1. 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
    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
    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.

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
    Create new Web site menu
    Figure 20. Create new Web site
    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
    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
    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
    Select MyTable in wizard

    The data source has now been customized for "MyTable."
  1. 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
    Drag drop GridView
    1. Select SqlDataSource1 from the list of data sources. Also check boxes to "Enable editing" and "Enable deleting."
    Figure 25. Customize GridView
    Customize GridView
    1. From the toolbox drag drop "DetailsView" control on the dataset designer:
    Figure 26. Drag drop DetailsView
    Drag drop DetailsView

    1. Select SqlDataSource1 from the list of data sources. Also check boxes to "Enable inserting."
    Figure 27. Customize DetailsView
    Customize DetailsView

    The user interface is now bound to the data source for "MyTable."
  2. 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.
Figure 28. Run Web site
Run Web site

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.


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

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=101159
ArticleTitle=DB2 UDB for Visual Studio 2005 developers, Part 2: Build applications and Web sites for DB2 using IBM Database Add-ins for Visual Studio 2005
publish-date=12222005