Skip to content. | Skip to navigation

Sections
Personal tools

Access Rocket U2 Data Server from Your .NET Applications, Part 1

Overview of IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio for UniVerse and UniData

Rapid application development using the IBM .NET provider and Database Add-ins for Visual Studio

Level: Intermediate

Rajan Kumar ( rkumar@rs.com), Software Developer, Rocket Software
Brent Gross ( gross@ca.ibm.com), .NET Architect for IBM Data Servers, IBM

29 Nov 2007

IBM® Data Server Provider for .NET and IBM Database Add-ins for Visual Studio support multiple IBM data servers, including DB2®, Informix® Dynamic Server, and the U2 data servers, UniVerse®, and UniData®. This article is the first of a series of articles and tutorials that explain the functionality of the .NET Provider and the Add-ins for the U2 data servers. As a U2 developer, you'll learn to build Windows®, ASP.NET Web, Web services, AJAX, reporting, and Windows Presentation Foundation (WPF) and Extensible Application Markup Language (XAML) applications with little or no code. You'll use standard interfaces such as ADO.NET against U2 data servers.

Introduction

IBM provides the following products to support application development on the .NET platform:

  • IBM Data Server Provider for .NET
  • IBM Database Add-ins for Visual Studio

IBM Data Server Provider for .NET and IBM Database Add-ins are integrated tightly into the Visual Studio Integrated Development Environment (IDE), a highly productive development environment for building data-centric solutions. It supports developing various target application types such as Windows, Web, reporting, and office applications. Server Explorer Integration allows you to develop a .NET application using UniVerse and UniData as you use other modern relational databases.



IBM Data Server Provider for .NET for UniVerse and UniData highlights

The IBM Data Server Provider for .NET for UniVerse and UniData is a set of classes. This set of classes is found in the IBM.Data.DB2.Dll assembly, and has the namespace IBM.Data.DB2.

The IBM Data Server Provider for .NET is a high performance, managed ADO.NET data provider. Applications can use ADO.NET Provider to connect to UniVerse and UniData, and can retrieve, manipulate, and update the data. ADO.NET supports both connected and disconnected access. For improved scalability, data processing applications running in a multi-tier environment employ disconnected access.


 


IBM Database Add-ins for Visual Studio for UniVerse and UniData highlights

IBM Database Add-ins for Visual Studio supports Server Explorer Integration for UniVerse and UniData. You can use the Add-ins to connect to the UniVerse and UniData data servers, test the connection, and populate server-side objects such as basic routines and tables. With the Database Add-ins for Visual Studio, you can do the following:

  • Use the IBM Data Server Provider for .NET to achieve seamless integration of your data sources.
  • Take advantage of the rapid application development environment.
  • Build Windows, Web, Web services, and reporting applications without writing a single line of code (zero programming).
  • Utilize support for integrating UniVerse and UniData data into your application.
  • Execute U2 basic routines in the Server Explorer.
  • Show data from U2 tables and views in the Server Explorer.
  • Generate ASP.NET Web services from U2 basic routines. 

 


The architecture

Figure 1 shows the architecture of .NET application development for UniVerse and UniData.


 


Installation

This is a part of DB2 9.5. You can download the ZIP file in the Resources section at the end of this article.

The DB2 Client contains the installation files for both of these:

  • IBM Data Server Provider for .NET
  • IBM Database Add-ins for Visual Studio


You can see the installed program by navigating to Control Panel > Add or Remove Programs.

Confirmation — IBM Data Server Provider for .NET installation

Use the testconn20.exe utility tool to confirm the IBM .NET Provider installation.

DB2 Client installation contains a utility tool called testconn20.exe. This is shipped with installation and generally found in C:\Program Files\IBM\SQLLIB\BIN. Perform the following steps to confirm the IBM .NET Provider installation.
  1. Open a command prompt.
  2. Type the following command:
    testconn20.exe "User ID=user;Password=****;Database=HS.SALES;
    Server=localhost;ServerType=universe;pooling=false"
    

Confirmation — IBM Database Add-ins Installation

You should see the following window if you run Help/"About Microsoft Visual Studio" from Visual Studio IDE.

 

Figure 4. IBM Add-ins confirmation

 

IBM Add-ins confirmation

 

 


 

Back to top

 

 

UniVerse and UniData database preparation

For more information on UniVerse, refer to Chapter 4 Accessing UniVerse Data in the "Using UniOLEDB" manual.

For more information on UniData, refer to Chapter 3 Accessing UniData Data in the "Using UniOLEDB" manual.

You have to first run the ECL command MIGRATE.SQL.

Use Visual Schema Generator (VSG), or the schema API, to create 1NF views and sub-tables for the runtime normalization of multi-valued and sub-valued data.


 

Back to top

 

 

Make UniVerse and UniData connections

Connection using testconn20.exe

The DB2 Client installation contains a utility tool called testconn20.exe. This is shipped with the installation and generally found in C:\Program Files\IBM\SQLLIB\BIN.

UniVerse

testconn20.exe "User ID=user;Password=****;Database=HS.SALES;
Server=localhost;ServerType=universe;pooling=false"

 

UniData

testconn20.exe "User ID=user;Password=****;Database=DEMO;
Server=localhost;ServerType=unidata;pooling=false"

 

Connection using programmatically

You can write simple program in C# to connect to UniVerse or UniData as shown below:

UniVerse

DbProviderFactory provider = DbProviderFactories.GetFactory("IBM.Data.DB2");
DbConnection con = provider.CreateConnection();
string sConnectionString = "User ID=user;Password=****;Database=HS.SALES;Server=localhost;
        ServerType=universe;pooling=false";
con.ConnectionString =sConnectionString;
con.Open();
con.Close();

 

UniData

DbProviderFactory provider = DbProviderFactories.GetFactory("IBM.Data.DB2");
DbConnection con = provider.CreateConnection();
string sConnectionString = "User ID=user;Password=****;Database=DEMO;
        Server=localhost;ServerType=unidata;pooling=false";
con.ConnectionString =sConnectionString;
con.Open();
con.Close();

 

Connection using Visual Studio Add-ins

You can add UniVerse or UniData connections into Server Explorer using the Add Connection option. Follow these simple steps to setup UniVerse or UniData connection:

  1. Open Visual Studio. If Server Explorer is not already open, click View > Server Explorer.



    Figure 5. View > Server Explorer
    View SE

  2. From the Data Connections node, right-click and select Add Connection.



    Figure 6. Data Connections > Add Connection…
    DC_AC

  3. In the Data Source field, select IBM DB2. Note that UniVerse and UniData are a part of the IBM.DB2 data source. (You can check the box for Always use this selection, to avoid this step every time an IBM DB2 connection is added.) Click OK.



    Figure 7. Change the data source
    SELECT DS

  4. Add the server name, database name, user ID, and password. (You can select save password to persist the password across Visual Studio sessions. For UniVerse and UniData, Click “Advanced…” Button and specify ServerType as “universe" or “unidata". If you are not using Connection Pooling, specify pooling is equal to false. You can also opt to Test Connection.) Click OK. A connection to your database will be added in Server Explorer.



    Figure 8. Add Connection dialog window and Advanced Properties window
    Add Connection dialog window and Advanced Properties window

     

Server Explorer Integration

After adding a new connection, all schema information is synchronously prefetched and cached. This allows rapid access to this information when designing applications. These fetched server objects are listed as folders under the corresponding connection.

Table enumeration: Expanding the Tables folder lists the tables available in the database, depending upon whether the Filter option or Exclude system schema option is supplied in the Add Connection window. By expanding a particular table, you can see the list of columns with which the table was built.

 

Figure 9. Tables enumeration in Server Explorer

 

Server Explorer

 

The Properties window lists the important properties of the database object selected in the Server Explorer. For example, if you've selected the column FANME of table CUSTOMER of the HS.SALES database, the corresponding Properties window would appear as follows:

 

Figure 10. Tables properties window

 

Table PW

 

Procedure (U2 basic routines) enumeration: You can view a list of stored procedures (U2 basic routines) by clicking the Procedures folders respectively. Expanding each stored procedure or function lists the parameters involved. To view the properties of parameters, right-click on the parameter and select Properties. You can execute a U2 basic routine and see the result.

 

Figure 11. Procedures (U2 basic routines) enumeration in Server Explorer

 

Procedure SE

 

You can execute a UniVerse or UniData basic routine. Select a basic routine, right click and then click Run to execute.

 

Figure 12. Run procedures (U2 basic routines) in Server Explorer

 

Run SP

 

If the basic routine has parameters, you can specify parameters before execution.

 

Figure 13. Specify parameters for procedures (U2 basic routines) in Server Explorer

 

Figure 13: Param SP

 

The result of a basic routine is displayed in Visual Studio IDE.

 

Figure 14. Output of procedures (U2 basic routines) in Server Explorer

 

Result SP

 

 


 

Back to top

 

 

Rapid application development

The core of rapid application development is to create data sources from tables, views, and store procedures (U2 basic routines). To achieve zero programming development, drag and drop these data sources into Windows or Web Forms. Data sources can be created two ways:

  • Use the Data Source Configuration Wizard
  • Add DataSet into your project

 

Create data sources — Use the Data Source Configuration Wizard

You display the Data Sources window by clicking Show Data Sources on the Data menu of Visual Studio IDE. The Data menu is only available when a project is open in Visual Studio. To populate the Data Sources, add new data sources with the Data Source Configuration Wizard.

 

Figure 15. Data Source window

 

DS WND

 

Click Add New Data Sources…, to see the following screen:

 

Figure 16. Data Sources Configuration Wizard (select database)

 

DB WZ

 

Select Database, and click Next. You see the following screen:

 

Figure 17. Data Sources Configuration Wizard (select connection)

 

CON WZ

 

Select Connection String, and click Next. You see the following screen. You can use New Connection… to create a new node in Server Explorer. Select Yes to save the connection string in the project file.

 

Figure 18. Data Sources Configuration Wizard (save connection string)

 

Save Constr WZ

 

Use the Default, and click Next. You see the following screen:

 

Figure 19. Data Sources Configuration Wizard (select table or procedure object)

 

Data Sources Configuration Wizard (select table or procedure object)

 

Select a table or a procedure, and click Next. You see the following screen:

 

Figure 20. Data Sources Configuration Wizard (CUSTOMER Table)

 

Data Sources Configuration Wizard (CUSTOMER Table)

 

You see that a data source is added into the data source windows. You can edit the data source by right clicking and selecting Edit with Data Set Designer. You can see the properties of the generated table adapter.

 

Figure 21. Data sources, DataSet Designer, table adapter

 

TableAdapter WZ

 

Create data sources — Add DataSet into your project

You can also populate the data sources window by adding DataSet items to your project.

  1. On the Project menu, click Add New Item.
  2. Select DataSet from the Add New Item dialog box.
  3. Type a name for the data set.
  4. Click Add.You see the following screen.

 

Figure 22. Add new item (DataSet)

 

Add new item (DataSet)

 

The data set is added to the project and opens in the DataSet Designer. Drag items from the DataSet tab of the Toolbox onto the designer. You can also drag items from an active connection in Server Explorer or Database Explorer onto the DataSet Designer.

 

Figure 23. DataSet Designer (drag and drop from Toolbox or Server Explorer)

 

DS Designer

 


 

Back to top

 

 

Connection pooling

UniVerse and UniData data servers support connection pooling. You can specify connection pooling parameters programmatically (IBM .NET Provider) or Server Explorer’s Add Connection Dialog (IBM Add-ins). You need to enable U2 server-side connection pooling if you specify pooling=true in your application. Otherwise, you get an exception. For more information about connection pooling licenses for UniData and UniVerse Data Servers, see Chapter 2 Using UniObjects for .NET in the "UniObjects for .NET Developer's Guide."


 

Back to top

 

 

Trace and debug

You can create a log file for different modules.

For Rocket U2 protocol, specify environment variables UCINETTRACE=c:\temp and UCINETTRACESWITCH=4.

For IBM Database Add-ins, specify environment variables vstrace=c:\temp.

Documentation

The documentation help for IBM Database Add-ins for Visual Studio is integrated into IDE. You can see this in the steps below:

  1. Navigate to Help > Contents.



    Figure 24. Select Contents
    Help About

  2. Select IBM Database Documentation, then IBM Database Add-ins Help or IBM Data Server Provider for .NET.



    Figure 25. Select IBM Database Add-ins Help or IBM Data Server Provider for .NET
    Help Doc

 


 

Back to top

 

 

Potential new features

The following are some areas under consideration for future releases of the IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio:

  • Support for Web services (Create a Web Method from tables). Note that there is already support for Web Methods from U2 basic routines.
  • Data Definition Language (DDL) support
  • Secure Sockets Layer (SSL) support
  • Language Integrated Query (LINQ) support
  • Create U2 Common language runtime (CLR) procedures from existing methods in a .NET managed language (such as C# or Visual Basic).

 


 

Back to top

 

 

Conclusion

Now IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio support UniVerse and UniData data servers. These products have brought many new features to the UniVerse and UniData developer, including seamless integration with Microsoft Server Explorer, rapid application development with the Data Sources Configuration Wizard, typed dataset creation from Universe and UniData basic routines, the capability to export UniVerse and UniData data as XML, the ability to develop reporting applications using Microsoft Reporting Services and Crystal Reports, and connection pooling for Web application.

In future articles and tutorials in this series, you'll learn to develop the following kinds of .NET applications using the IBM .NET Provider, IBM Database Add-ins, and U2 data servers:

  • Master detail window application. Hook a table adapter’s Update, Delete, and Insert statement to UniVerse and UniData subroutine.
  • Master details ASP.NET Web application, master page, theme and style, data access layer, business logic layer, and Web sitemap
  • ASP.NET AJAX application (server centric and client centric)
  • Create and consume a Web service from UniVerse and UniData subroutines and tables
  • Master detail Crystal Report application using Visual Studio Crystal Report Add-ins
  • Master Detail Microsoft Reporting Services application using SQL Server Business Intelligence Studio
  • Master Detail WPF or XAML application using Microsoft Expression Blend, LINQ to DataSet, CLR Object Data Provider, and XML Data Provider

 

Acknowledgement

This release of IBM Data Server Provider for .NET, Rocket U2 Runtime, and IBM Database Add-ins for Visual Studio have been developed by IBM teams in Toronto, Lenexa, Denver, San Francisco, and Silicon Valley Lab.


 

Back to top

 

 

 

Resources

Learn

 

Get products and technologies

 

 

About the authors

Rajan Kumar photo

Rajan Kumar is a developer with the Rocket Software U2 Group in Denver, Colorado. He works on .NET, Windows, and Java data access tools for UniVerse and UniData. He is currently working on the Rocket U2 data server provider for .NET and the IBM Database Add-ins for Visual Studio projects.

 

Brent Gross photo

Brent Gross is a .NET architect for IBM data servers with IBM Information Management Development in the Toronto Lab. He has more than 20 years of experience with IBM and has been developing for DB2 since 1995. His responsibilities have included architecture and development of stored procedure support and client interfaces. Brent has presented at many Information Management and user group conferences. His current role is the architect of the .NET support for IBM data servers.

Document Actions