Zen v16 | Using the Data Providers

Developer Reference > Data Access Methods > Data Provider for .NET > Using the Data Providers

Using the Data Providers

The Zen data providers provide data access to any .NET-enabled application or application server. The data providers delivers high-performance point-to-point and n-tier access to industry-leading data stores across the Internet and intranets. Because they are optimized for the .NET environment, the data providers allow you to incorporate .NET technology and extend the functionality and performance of your existing system.

See Advanced Features for information on advanced features such as connection pooling, statement caching, configuring security, Zen Bulk Load, and diagnostic support.

See The ADO.NET Data Providers for information about using the Zen ADO.NET data providers in the standard Zen ADO.NET environment.

See Zen ADO.NET Entity Framework Data Providers for information about using the data providers with the Zen ADO.NET Entity Framework.

About the Data Providers

Zen data providers are built with 100% managed code, so they can run and connect to the database entirely within the common language runtime (CLR).

Code that runs in the native operating system, such as client libraries and COM components, is called unmanaged code. You can mix managed and unmanaged code within a single application. However, unmanaged code reaches outside the CLR, which means that it effectively increases complexity, reduces performance, and opens possible security risks.

For information on the .NET Framework and Entity Framework versions supported by the Zen data providers, see Supported .NET Framework Versions.

Using Connection Strings

You can define the behavior of a connection using a connection string or the properties of the PsqlConnection object.

However, values set in the connection string cannot be changed by the connection properties.

The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The following example shows the keywords and values for a simple connection string for the Zen data provider:

"Server DSN=SERVERDEMO;Host=localhost"

Guidelines

Use the following guidelines when specifying a connection string:

• The spaces in the connection string option names are required.

• All connection string option names are case-insensitive. For example, Password is the same as password. However, the values of options such as User ID and Password may be case-sensitive.

• To include values that contain a semicolon, single quote, or double quotes, enclose the value in double quotes. If the value contains both a semicolon and double quotes, use single quotes to enclose the value.

• You can also use single quotes when the value starts with a double quote. Conversely, double quotes can be used if the value starts with a single quote. If the value contains both single quotes and double quotes, the character used to enclose the value must be doubled every time it occurs within the value.

• To include leading or trailing spaces in the string value, the value must be enclosed in either single quotes or double quotes. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in single or double quotes. However, spaces within a string literal keyword or value are preserved. Single or double quotes can be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless it is the first or last character in the value.

• Special characters can be used in the value of the connection string option. To escape special characters, surround the value in single or double quotes.

• The Equals character (=) can also be repeated within the connection string. For example:

Initialization String=update mytable set col1 == 'foo'"

• If the connection string contains invalid connection string options, the connection attempt returns an error. For example, an error is returned if you specify a value for Load Balancing when Alternate Servers has not been defined.

• If the connection string contains duplicated connection string options, the data provider uses the connection string option that appears last in the connection string. For example, Connection Timeout appears twice in the following connection string, with different values. The data provider uses the second value and waits 35 seconds before terminating an attempted connection:

"Server DSN=SERVERDEMO;Host=localhost;Connection Timeout=15;Min Pool Size=50;Connection Timeout=35"

See Connection String Properties for a list of the supported connection string options.

Using the Zen Performance Tuning Wizard

You can use the Performance Wizard to select the optimal connection string options for both the Zen ADO.NET data provider or the Zen ADO.NET Entity Framework data provider.

See Using the Zen Performance Tuning Wizard for more information.

Stored Procedures

To enable stored procedures in the application, do the following:

• Set the CommandText property in the PsqlCommand object to the stored procedure name.

MyCommand.CommandText = "GetEmpSalary";

• Set the CommandType property in the PsqlCommand object to StoredProcedure.

MyCommand.CommandType = CommandType.StoredProcedure;

• Specify parameter arguments, if needed. The application should add the parameters to the parameter collection of the PsqlCommand object in the order of the arguments to the stored procedure. The application does not need to specify the parameter markers in the CommandText property of the PsqlCommand object.

To retrieve the return value from a stored procedure, the application should add an extra parameter to the parameter collection for the PsqlCommand object. This parameter’s ParameterDirection property should be set to ParameterDirection.ReturnValue. The return value parameter can be anywhere in the parameter collection because it does not correspond to a specific parameter marker in the Text property of the PsqlCommand object.

If the stored procedure does not produce a return value, parameters bound with the ParameterDirection property as ReturnValue are ignored.

If the stored procedure returns a ReturnValue from the database and the application has not bound a parameter for it, the data provider discards the value.

Note for Zen ADO.NET Entity Framework Users: The PsqlConnection object includes properties and methods that provide enhanced statistics functionality. The methods and properties are standard in the Zen ADO.NET data provider but are not available at the Zen ADO.NET Entity Framework layer. Instead, the Zen ADO.NET Entity Framework data provider exposes the same functionality through "pseudo" stored procedures. See Using Stored Procedures with the ADO.NET Entity Framework for more information.

Using IP Addresses

The data providers support Internet Protocol (IP) addresses in IPv4 and IPv6 formats. If your network supports named servers, the server name specified in the data source can resolve to an IPv4 or an IPv6 address.

The EnableIPv6 connection string option, when set to True, allows a client with IPv6 protocol installed to connect to the server using either an IPv4 address or an IPv6 address. For more information about IPv6 formats, see IPv6 in Getting Started with Zen.

Transaction Support

Zen data providers use only 100% managed code to support the transactions, which are implemented entirely within the .NET Framework.

Using Local Transactions

Local transactions use the internal transaction manager of the underlying database.

The application creates a PsqlTransaction object by calling BeginTransaction on the PsqlConnection object. Subsequent operations, such as committing or aborting the transaction, are performed on the PsqlTransaction object.

Thread Support

The PsqlConnection object is thread-safe. Multiple PsqlCommand objects, each accessed on a separate thread, can simultaneously use a single connection.

Accessing other public and data provider-specific objects simultaneously on separate threads is not thread-safe.

Unicode Support

The data provider supports Unicode as specified in the .NET Framework SDK. Effectively, this means that the data provider uses Unicode UTF-16 encoding to represent characters.

The data provider converts UTF-16 characters to the format used by the database, and returns .NET Framework strings to the application. For example, if a Zen database code page is in extended ASCII format, the data provider uses extended ASCII to represent characters sent to the database. The data provider then converts the extended ASCII characters returned before sending them back to the application.

For more information about the .NET Framework implementation of Unicode and international characters, refer to the Microsoft .NET Framework SDK documentation.

Isolation Levels

Zen supports the ReadCommitted and Serializable isolation levels. It supports record-level locking. See Locking and Isolation Levels for details.

SQL Escape Sequences

See SQL Escape Sequences for .NET for information about the SQL escape sequences supported by the Zen data provider.

Event Handling

The event handler receives an argument of type PsqlInfoMessageEventArgs, which contains data relevant to an event. See PsqlInfoMessageEventArgs Object for more information.

This event is defined as:

public event PsqlInfoMessageEventHandler InfoMessage;

Clients that want to process warnings and informational messages sent by the database server should create an PsqlInfoMessageEventHandler delegate to listen to this event.

You can use these events to capture failures that can occur when creating packages, stored procedures, or stored functions, which all create commands. If Zen encounters errors when compiling a command created by a package, stored procedure, or stored function, the object is created, even though it is not valid. An event will be sent, indicating the failure.

The following code fragment defines a delegate that represents the method that handles the InfoMessage event of a PsqlConnection object:

[Serializable]

public delegate void PsqlInfoMessageEventHandler(

object sender

PsqlInfoMessageEventArgs e

);

where sender is the object that generated the event and e is an PsqlInfoMessageEventArgs object that describes the warning. For more information on Events and Delegates, refer to the .NET Framework SDK documentation.

Error Handling

The PsqlError object collects information relevant to errors and warnings generated by the Zen server. See PsqlError Object for more information.

The PsqlException object is created and thrown when the Zen server returns an error. Exceptions generated by the data provider are returned as standard run time exceptions. See PsqlException Object for more information.

Using .NET Objects

The data provider supports the .NET public objects, exposing them as sealed objects.

See .NET Objects Supported for more information.

Developing Applications for .NET

Developers of data consumer applications must be familiar with the Microsoft .NET specification and object-oriented programming techniques.

Microsoft also provides extensive information about ADO.NET online, including the following articles.

• Upgrading to Microsoft .NET: ADO.NET for the ADO Programmer

http://msdn2.microsoft.com/en-us/library/aa302323.aspx

• Using .NET Framework Data Providers to Access Data

• http://msdn2.microsoft.com/en-us/library/s7ee2dwt(vs.71).aspx

Generic Coding with the ADO.NET 2.0 Base Classes and Factories

• http://msdn2.microsoft.com/en-us/library/ms379620(VS.80).aspx