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.

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.

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
   
   
   
2. From the Data Connections node, right-click and select Add Connection.
   
   Figure 6. Data Connections > Add Connection…
   
   
   
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
   
   
   
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
   
   
   

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.

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:

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.

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

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

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

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.

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

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

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.

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

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

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.

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.

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.

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

Trace and debug

You can create a log file for different modules.

For IBM 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
   
   
   
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
   
   
   

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

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, IBM 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.

Resources

Learn

• "Using UniOLEDB": Find additional information on UniVerse and UniData.
  
  
• developerWorks U2 Zone: Read articles and tutorials and connect to other resources to expand your U2 skills.
  
  
• IBM Information Management and Visual Studio .NET page: Read articles and tutorials and connect to other resources to expand your .NET skills.
  
  
• U2 Product Family page: Learn more about U2.
  
  
• U2 Community Podcasts: Find interviews and discussions on topics vital to U2 users.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

Get products and technologies

• Download the DB2 Client from the DB2 9.5 download page.
  
  
• Download the DB2 Client from the DB2 for .NET page.
  
  
• Download the Data Server Client from the DB2 9.5 download page.
  
  

Discuss

• Participate in the discussion forum (.NET Development with DB2, IDS and U2).
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

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

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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• IBM Data Server Provider for .NET for UniVerse and UniData highlights
• IBM Database Add-ins for Visual Studio for UniVerse and UniData highlights
• The architecture
• Installation
• UniVerse and UniData database preparation
• Make UniVerse and UniData connections
• Server Explorer Integration
• Rapid application development
• Connection pooling
• Trace and debug
• Documentation
• Potential new features
• Conclusion
• Resources
• About the authors
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers