Integration with the Microsoft Server Explorer
Visual Studio 2003 provided a separate explorer window called the IBM Explorer, similar in look and feel to the Visual Studio Server Explorer, to allow catalog access to DB2 database objects and for rapid application development using drag-and-drop functionality.
In Visual Studio 2005, the IBM tooling provides tight integration into the Microsoft Server Explorer, allowing connections to DB2 databases to be directly added to the Microsoft Server Explorer. Rapid application development functionality available on the Microsoft Server Explorer can now be used on DB2 database connections as well. Hence, in the latest IBM tooling, the separate IBM Explorer window that was available with earlier releases has been deprecated.
Figure 1. Creating DB2 connections in Server Explorer
Adding and customizing connections
In Visual Studio 2003, the connection name in the Add Connection dialog was editable, and the uniqueness of the connection was based on the connection name. Hence, multiple connections to the same database were possible by using different connection names to connect to the same database. Objects could be filtered only using the schema name, although several options for the filter criteria such as equal to, not equal to, and so on were available.
In Visual Studio 2005, in the Add Connection Dialog, the uniqueness of the connection is based on the collective information of the server name, port number, user ID, password, and the database name. The connection string is automatically generated and a read-only view of the connection string is displayed in the Advanced Properties, invoked by clicking on the Advanced button in the Add Connection Dialog. Hence, only one connection to a particular database using a particular user ID and password is allowed.
Figure 2. Customizing DB2 connections
Several filter options and customizations are now available:
- The schema filter has been modified to use the LIKE search criteria
- In addition to the schema filter, filters can be specified for each object folder. While the schema filter applies to all the folders, the folder filters provide a second level of filtration on the objects.
- Customization to the object folders in the Microsoft Server Explorer is now possible by specifying whether to include or not include each folder in the tree
- A user option is provided to automatically refresh the connection when the Visual Studio IDE is loaded
- An option to specify if the user would like to always run procedures to discover result sets is provided
To modify connection information, filter criteria, or any of the advanced options, use the modify connection menu option on the connection.
In Visual Studio 2003, debugging of DB2 SQL stored procedures was available only on DB2 servers for Linux®, UNIX®, and Windows® and could only be initiated from a DB2 database project and not directly from the procedure node on the IBM Explorer tree. You had to create a DB2 database project to contain the stored procedure to be able to debug it.
In Visual Studio 2005, the debugging support has been significantly enhanced and extended to support debugging of stored procedures on z/OS®, Version 8 servers. The usability of the debugging functionality has been highly enhanced by allowing debugging to be initiated directly from the procedure node in the Microsoft Server Explorer or directly from within the IBM Stored Procedure designer. It is no longer necessary to create a DB2 database project to contain the stored procedure to be debugged.
Figure 3. Debugging from Server Explorer
Creating Web services and Web methods
In Visual Studio 2003, you could create re-usable ADO.NET data adapters for objects in the IBM Explorer. Each data adapter has select, update, insert, and delete commands that could be either SQL statements or a call to a stored procedure. These individual commands on the data adapter could be either exposed as Microsoft IIS Web methods or as DB2 embedded application server Web services using the DB2 WORF technology. The Web Services Description Language (WSDL) for the Web Service and a test Web client are also generated. The menu options to create the Web services and Web methods were provided on the Data Adapters node in the IBM Explorer tree as well as on the individual data adapter nodes in the tree.
Visual Studio 2005 provides similar support to create Web services and Web methods, although the menu options for these are exposed on different nodes on the explorer tree. Since the Data Adapters node no longer exist in the explorer tree, a new node IBM Web Services is now available under each DB2 connection and lists the individual Web service nodes below it. On each Web service node the following menu options are available:
- Test - Allows you to test the Web service by opening a test client to invoke operations on the deployed Web service
- Redeploy - Opens the Web Services dialog that allows you to edit the SQL statements and redeploy the Web service on the DB2 application server
- Update user information - Allows you to modify the user name and password information for the deployed Web service
- Delete - Deletes the Web service from the application server
To also deploy Web services, you can right-click on a table, view, procedure, or function in the Microsoft Server Explorer tree and select Deploy IBM Web Service, which opens the Web Services dialog. The dialog displays the select, insert, update, and delete statements and allows modifications to the pre-defined SQL for each of these statements. It also provides options to deploy as well as test the Web service. Note that to deploy Web services, you must enable a database by using the enable script that is provided with your DB2 Application Server. Although it is recommended to use the tools database for enablement, you can use any database.
Figure 4. Deploying IBM Web service
Similarly, to create Web methods, right-click on a table, view, procedure, or function in the Microsoft Server Explorer tree and select Generate Web Methods, which will open the Generate Web Methods dialog. The dialog allows you to generate ADO .NET code for SQL commands and procedures and add them to a CLR project. The generated code is in the language of the project that the code was added to, and the generated methods are marked as Web methods. Once the project has been built, these Web methods can be deployed as Web services on the Microsoft Application Server.
Figure 5. Generating Web methods
Visual Studio 2003 made step-by-step wizards available to help you easily create DB2 database objects such as tables, views, stored procedures, CLR stored procedures, functions, indexes, and triggers. You could invoke these wizards from both the IBM Explorer as well as the Solution Explorer.
Visual Studio 2005 also makes the wizards available from both the Solution Explorer as well as the Microsoft Server Explorer. However, the Microsoft Server Explorer now launches IBM Designers, in addition to the wizards, to not only create but also alter several database objects such as tables, views, stored procedures, triggers, and indexes. These designers provide an aggregated view of an object and all its related objects. Each designer has one or more views that expose a part of the functionality available on the designer. A toolbar is associated with each designer, and you can use the buttons in the toolbar or the top level menus to switch between the various views of the designer. Common views, such as Show Script and Privileges View, are available on all the designers to provide some of the common functionality and to maintain a uniform look and feel across all the designers. The File > Save action of the IDE provides seamless integration to save and execute the definitions specified in the IBM designers.
Note: Currently, the tooling in Visual Studio 2005 does not handle case-sensitive identifiers (applicable for all features) due to a limitation on the DB2 provider for .NET.
The following designers can be launched from the Microsoft Server Explorer:
The table designer is a new feature available in the IBM tooling for Visual Studio 2005. The table designer provides several views that allow you to specify the definitions for the table and its related objects. In addition, you can alter the table definition. The level of alteration supported is based on the DB2 server of the connection.
Figure 6. Table designer
- The Columns view allows you to add columns and specify the properties of the columns.
- The Keys view allows you to define primary, unique, and foreign keys for the table. For primary and unique keys, only the not null columns are listed and can be selected to define these keys. To ease the process of defining foreign keys, there is a lookup on tables based on the schema as well as a grid that allows mapping of the columns in the current table to the primary key columns in the mapped table.
- The Indexes view allows you to create indexes for the table by selecting from the list of columns defined for the table.
- The Check constraints view allows you to create table check constraints for the table. There is also a SQL editor control that contains intellisense capabilities to edit the check constraint expression.
- The Triggers view allows you to define triggers on the table. The SQL editor is provided to edit the trigger text and, for ease of use, it is pre-filled with a skeleton SQL to create a trigger.
- The Privileges view allows you to create roles and privileges for the table for different users and groups.
- The Scripts view provides a read-only view of the script that is to be executed to define the object being designed. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution respectively.
After creating and saving the table definition, the designer allows certain alterations to the table depending on the DB2 platform of the connection it belongs to. If the designer is not already open, you can open it in the alter mode using the context menu option Open Definition, available on the table object. The user interface for the table designer enables or disables certain features, thus allowing or preventing you from performing certain alterations to the table. In cases where the data type of a column can be altered, the list of compatible data types is automatically listed in the data type drop down for each column. Adding new columns to existing tables is also supported.
The view designer is a new feature available in the IBM tooling for Visual Studio 2005. The view designer contains the following views to define a view object:
Figure 7. View designer
- The View view provides the SQL editor control to enter the SQL statement defining the view. Options are available to check the syntax of the SQL defining the view as well as to test the execution of this statement. All test executions are rolled back. If the SQL execution returns results, they are displayed in a results grid.
- The Triggers view allows you to define triggers on the view. The SQL editor is provided to edit the trigger text and, for ease of use, it is pre-filled with a skeleton SQL to create a trigger.
- The Privileges view allows you to create roles and privileges for the view for different users and groups.
- The Scripts view provides a read-only view of the script that is to be executed to define the object being designed. In addition it provides options to enter a script header and a script trailer which would be run before and after the script execution respectively.
After creating and saving the view definition, the designer allows you to make certain alterations to the view depending on the DB2 platform of the connection it belongs to. If the designer is not already open, you can open it in the alter mode using the context menu option Open Definition available on the view object. The user interface for the view designer enables or disables certain features, thus allowing or preventing you from performing certain alterations to the view. Currently, only the comment of an existing view object can be altered.
The procedure designer is a new feature available in the IBM tooling for Visual Studio 2005. The procedure designer contains the following views to define the stored procedure:
Figure 8. Procedure designer
- The Procedure view, in addition to defining the procedure attributes, allows you to define the debug mode and the number of result sets to be returned. It provides the SQL editor control to enter the SQL body for the stored procedure. You can also specify parameter definitions, including the parameter name, mode, data type, and other extended properties, for the procedure.
Data types for parameters are pre-filled in the data type drop down list based on the DB2 server. For ease of use, a default SQL script for creating a procedure is displayed initially in the procedure body.
- The Privileges view allows you to create roles and privileges for the stored procedure for different users and groups.
- Debugging and execute options for the procedure being designed are available using the top level menus and toolbar button options on the designer toolbar to either Step Into or to Execute the stored procedure.
- The Scripts view provides a read-only view of the script that is to be executed to define the object being designed. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution respectively.
Altering an existing procedure
After creating and saving the stored procedure definition, the designer allows you to make certain alterations to the stored procedure depending on the DB2 platform of the connection it belongs to. If the designer is not already open, you can open it in the alter mode using the context menu option Open Definition, available on the stored procedure object. Currently, the IBM tooling handles altering of a stored procedure by always dropping the existing procedure and then re-creating the new procedure using the new definition. Since the procedure is dropped and re-created, you can alter all the stored procedure options specified during the creation without any restrictions.
In Visual Studio 2003, you can use the View Create Script menu option, available on the function and stored procedure nodes in the IBM Explorer tree, to display the create script for a function or a stored procedure. The script is displayed in the DB2 SQL editor window, which you can then save to a DB2 database project or copy and paste the script to be used as you desire. To view the create script for table or view objects, the Generate Create Script option provides a read-only view of the script. For tables and views, the Insert Create Script option allowed inserting the script into the current cursor location in a DB2 editor window.
Figure 9. Script designer
The script designer is also a new feature of Visual Studio 2005. You can invoke the script designer from several nodes in the Microsoft Server Explorer tree and enter any arbitrary script and execute it from within the Visual Studio IDE. The New Script menu option is available at the data connection instance level in the Microsoft Server Explorer and invokes a new script designer with a blank script. In addition to SQL statements, you can also specify XQuery and SQLXML scripts in this designer.
You can also use the Visual Studio Query Builder to generate the script and invoke it using the Query Builder right-click context menu option on the editor control. Closing the Query Builder automatically pastes the SQL code in the Query Builder into the editor at the insertion point. You can also invoke the script designer using the Show Script menu option that is available on the table, view, stored procedure, and function nodes. When invoked from a particular object node, it displays the DDL used to create that particular object. Note that the script displayed is editable. Hence, in addition to viewing the script, you can also choose to save, modify, and execute it. In the case that the script returns results, the results are displayed in the Result Data section of the designer.
In Visual Studio 2003, the retrieve data feature allowed you to view the data for an object in a grid and to perform inserts, updates, and deletes to the data. This functionality was exposed using the Retrieve Data menu option on the table and view nodes in the IBM Explorer tree. An Update button allowed you to commit the changes to the database.
In Visual Studio 2005, the Show Data menu option is available on the table and view nodes in the Microsoft Server Explorer tree and allows you to view, insert, update, or delete data for the object. Visual Studio 2005 extends this feature to include the following customizations:
- Allows column filtration options to include or exclude columns from the data displayed for the table or view. The default filter option is to make all columns visible.
- Allows a column alias and the sort order of the columns to be specified
- Provides column filtration based on SQL conditions that you can enter using the SQL editor control
- Import and Export options are available on the results grid that allow you to use a variety of file types that include XML files, comma delimited files, and pipe delimited files to import the data to an object or export the data from an object
For ease of use, the File > Save action on the IDE commits the changes in the results data grid to the database.
Figure 10. Data designer
In Visual Studio 2005, the IBM tooling provides a new feature to clone the definition of an existing object using the IBM designers. The Clone Definition menu option is available on the object nodes such as tables, view, and stored procedures in the Microsoft Server Explorer. Cloning an object displays the designer for that object, which is pre-filled with the object details from the original object, and the object names are renamed for uniqueness as necessary by appending a numeric to the original object name. You can choose to modify the definition as needed and save it.
Figure 11. Cloning objects
Creating CLR stored procedures
Building CLR stored procedures is supported both in Visual Studio 2003 and Visual Studio 2005 using the CLR stored procedure wizard you can invoke from a DB2 project in the Solutions Explorer. In Visual Studio 2003, when a CLR application is open in the Solutions Explorer, you can invoke the CLR stored procedure wizard from the procedures node in the IBM Explorer. Similar capability is available in the Microsoft Server Explorer in Visual Studio 2005. However, in Visual Studio 2005, when creating a DB2 C# or Visual Basic class library, there are two templates available for creating the class library, namely DB2 Class Library for Version 8 and DB2 Class Library for Version 9. You choose the appropriate template based on the version of the DB2 server on which the CLR stored procedure is to be deployed. Note that to deploy the CLR stored procedures on a DB2 Version 8 server, the .NET Framework 2.0 is required to be installed on the server machine.
In Visual Studio 2003, using the wizards, you had an option to generate a grant statement for the object being created that would grant all privileges on the object to public. No options or customizations were allowed.
However, in Visual Studio 2005, although the wizards continue to provide the same option as in Visual Studio 2003, the IBM designers on the other hand provide several enhancements that allow you to add roles and privileges. Based on the DB2 server of the connection, the list of privileges and the values allowed for each privilege is automatically pre-filled in the privileges view of the designer. You can therefore easily grant, allow, or revoke specific privileges for each user or group specified. Look-up for existing users and groups as well as option buttons that perform actions to Grant all, Allow all, or Revoke all privileges are also provided for ease of use.
Figure 12. Privileges view
Executing procedures and functions and persisting parameter values
In Visual Studio 2003, to execute a procedure or function, you can use the Run Procedure and Run Function menus on the respective nodes in the IBM Explorer. If the procedure or function does not require any parameter values, then it is executed, and the returned result sets, if any, are displayed. If multiple result sets are returned, an option to browse through the different result sets is available. In the case that the procedure or the function requires parameter values to be specified, then the Parameter Values dialog prompts you to enter the parameter values and click OK on the dialog to perform the execution and display the results. But, if you choose to run the same procedure or function, the parameter information that you entered during the previous run is lost and you are again prompted and have to re-enter all the parameter information.
In Visual Studio 2005, the IBM tooling provides several valuable enhancements to the procedure and function execution process. Choosing the Execute menu option that is available on the procedure and function nodes in the Microsoft Server Explorer launches the Run Options dialog that allows the following options:
- Commit or rollback the changes after executing the procedure or function
- Specify pre-run and post-run scripts that will be run before and after the procedure or function is executed respectively. The SQL editor control that provides syntax colorization and intellisense is provided to enter these scripts. The SQL editor control also provides right-click menu options to load the script from a file or to save the script to a file.
- Lists the parameters for which the values can be entered
Figure 13. Executing procedures
The key enhancement provided in this area is the ability to persist the parameter information entered for each procedure or function. When you choose to run the same procedure or function again, the parameter values are pre-filled with values from the previous run of that particular procedure or function.
Rapid Application Development and Web applications
In Visual Studio 2003, applications could be built for DB2 by dragging and dropping objects from the IBM Explorer onto a windows form or Web form. This causes code to be automatically generated to create the ADO.NET connection, command, and data adapter objects that are added to the component tray. Additionally, a data grid control is also dragged and dropped onto the form. DataSets can be generated using the right-click options on the DataAdapter. Once the DataSet has been created it is bound to the data grid by making the DataSource property of the grid point to the DataSet. You can complete the simple windows application by just adding a single line of code to the Load event of the form to fill the DataAdapter using the DataSet. Running the application displays the data for the database object in the data grid and allows options to insert, update, and delete data from the object.
When you register the managed provider for DB2 on the system, the DB2Connection, DB2Command, and DB2DataAdapter objects are automatically added to the Data section of the toolbox. You can also drag and drop these objects from the toolbar onto forms to create applications. But since these objects are not pre-configured, you have to configure each separately.
Figure 14. Creating and using DataSources in applications
In Visual Studio 2005, the IBM tooling tightly integrates into the Microsoft Server Explorer, simplifying the process of building windows application and Web applications. Visual Studio 2005 uses the concept of configuring and creating a DataSource to be used by the applications. To create an application, create a connection to a database, and then choose to create a windows or a Web application. A top level menu Data > Add New Data Source is now available, and choosing this launches the DataSource Configuration Wizard provided by the Visual Studio IDE. This wizard allows you to select the connection to work with and the data objects that the data source is created for. Once the wizard is completed, it creates data sources for the selected objects. After creating a data source, to configure it, right-click on the data source and choose the menu options to either Configure Dataset with Wizard... or Edit DataSet with Designer. You can view the data sources in the DataSources window, which you can invoke using the top level menu Data > Show Data Sources. You can drag and drop the data source onto a form in the application, which automatically creates a default user interface control in the form and generates all the necessary code. You can now run the application to view and update data on the object. No coding is needed to create this application. Note that in Visual Studio 2003 this was not available. You had to manually add the user interface control, set the binding on it, generate the DataSet, and also add the code to the Load event of the form to fill the data adapter with the DataSet.
Data adapters, table adapters, and DataSets
In Visual Studio 2003, the IBM Explorer provided the capability to create re-usable ADO.NET Data Adapter objects that were displayed in the IBM Explorer tree under a separate "Data Adapters" folder. For application development, in addition to being able to drag and drop data objects, you could also drag and drop these data adapters onto forms and configure them using the data adapter wizard. The wizard automatically generated default SQL statements for the select, insert, update, and delete commands. Additionally, you could generate DataSets directly from the data adapter nodes in the IBM Explorer, eliminating the need to create a windows application to generate a DataSet.
In Visual Studio 2005, since the process of building applications using drag and drop now uses the new Table Adapter ADO.NET object instead of the Data Adapter object, the IBM tooling deprecates the support for generating DataAdapter objects from the explorer tree. You can also create Windows applications by dragging and dropping a data source from the Data Sources view onto the designer provided by the IDE. A TableAdapter and a default user interface for the object are automatically created. When running the application, the default user interface allows you to navigate through the rows of data and to insert, update, and delete data.
Creating and dropping a database
In Visual Studio 2003, you could create a database on the local DB2 LUW server directly from the IBM Explorer tree using the menu option on the Data Connections node.
In Visual Studio 2005, you can create a database on the local DB2 LUW server directly from the Microsoft Server Explorer tree using a menu option available on the Data Connections node of the tree. Additionally, there is also a menu option to drop the database.
The IBM Database Add-Ins for Visual Studio 2005 provides a broad range of key features and enhancements that are of great value to the .NET developer. This article gave an overview of the new features in IBM Database Add-Ins for Visual Studio 2005 and highlighted the differences in this latest version when compared to IBM Database Add-Ins for Visual Studio 2003. For a more hands-on experience, and for more information on IBM Database Add-Ins for Visual Studio 2005, visit DB2 UDB for .NET.
Learn
- IBM Information Management and Visual Studio .NET: Find more resources for DB2 UDB .NET developers.
- DB2 UDB for .NET: Read more information on the IBM Database Add-Ins for .NET.
- "Application development with DB2 UDB" (developerWorks, June 2005): Develop applications for DB2 UDB using .NET, Java, SOA or open source such as PHP.
- developerWorks Information Management zone: Find more resources
for DB2 UDB developers and administrators.
-
Stay current with developerWorks
technical events and webcasts.
-
Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
Get products and technologies
- Download the latest IBM Database Add-Ins for Visual Studio 2005 from DB2 UDB for .NET.
- Download a
free trial version of DB2 Universal Database.
-
Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
- 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.
Comments (Undergoing maintenance)
Table of contents
- Integration with the Microsoft Server Explorer
- Adding and customizing connections
- Debugging stored procedures
- Creating Web services and Web methods
- Creating DB2 database objects
- Table designer
- View designer
- Procedure designer
- Script designer
- Data designer
- Cloning DB2 database objects
- Creating CLR stored procedures
- Roles and privileges
- Executing procedures and functions and persisting parameter values
- Rapid Application Development and Web applications
- Data adapters, table adapters, and DataSets
- Creating and dropping a database
- Conclusion
- Resources
- About the author
- Comments
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
