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

The following topics describe features that pertain to the Zen ADO.NET data providers:

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

Zen ADO.NET 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 raises complexity, reduces performance, and opens possible security risks.

The namespace for the Zen ADO.NET data provider is Pervasive.Data.SqlClient. When connecting to the Zen database, you use the PsqlConnection and PsqlCommand objects in the Pervasive.Data.SqlClient namespace.

The following code fragment shows how to include a Zen ADO.NET data provider namespace in your applications:

The strongly named assembly for the Zen ADO.NET data provider is placed in the Global Assembly Cache (GAC) during installation. The assembly name is Pervasive.Data.SqlClient.dll.

The Pervasive.Data.Common assembly includes features such as support for bulk load.

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 data provider:

See Using Connection Strings for guidelines on specifying connection strings.

PsqlConnectionStringBuilder property names are the same as the connection string option names. However, the connection string option name can consist of multiple words, with required spaces between the words. For example, the Min Pool Size connection string option is equivalent to the MinPoolSize property. Using Connection Strings lists the connection string properties, and describes each property.

The connection string options have the following form:

Each connection string option value pair is separated by a semicolon. The following example shows the keywords and values for a simple connection string for the Zen ADO.NET data provider:

The performance of your application can be affected by the values you set for connection string options and the properties of some data provider objects.

Encrypt: Data encryption may adversely affect performance because of the additional overhead, mainly CPU usage, required to encrypt and decrypt data.

Max Statement Cache Size: Caching all of the prepared statements that an application uses might appear to offer the best performance. However, this approach may come at a cost of database server memory if you implement statement caching with connection pooling. In this case, each pooled connection has its own statement cache, which may contain all of the prepared statements used by the application. All of the cached prepared statements are also maintained in database server memory.

Pooling: If you enable the data provider to use connection pooling, you can define additional options that affect performance:

Schema Options: Returning some types of database metadata can affect performance. To optimize application performance, the data provider prevents the return of performance-expensive database metadata such as procedure definitions or view definitions. If your application needs this database metadata, you can specifically request its return.

To return more than one type of the omitted metadata, specify either a comma-separated list of the names, or the sum of the hexadecimal values of the column collections that you want to return. For example, to return procedure definitions and view definitions, specify one of the following:

Statement Cache Mode: In most cases, enabling statement caching results in improved performance. To enable the caching of prepared statements (Command instances), set this option to Auto. Use this setting if your application has marked prepared statements for implicit inclusion in the statement cache, or has marked some statements for implicit inclusion and others for explicit inclusion. If you want the statement cache to include only prepared statements that are marked Cache, 1) set the StatementCacheBehavior property of the Command object to Cache and 2) set this option to ExplicitOnly.

StatementCacheBehavior: If your application reuses prepared statements multiple times over an application’s lifetime, you can influence performance by using a statement cache. This property identifies how a prepared statement (a Command object instance) is handled during statement caching.

When set to Cache, the prepared statement is included in the statement cache.

When set to Implicit and the Statement Cache Mode connection string option is set to Auto, the prepared statement is included in the statement cache.

When set to DoNotCache, the prepared statement is excluded from the statement cache.

You can use connection statistics to determine the effect that caching specific statements has on performance (see Analyzing Performance With Connection Statistics).

The following topics cover the data types supported by the Zen ADO.NET Data Provider:

The following table lists the data types supported by the Zen ADO.NET data providers and how they are mapped to the .NET Framework types. You can use the table to infer the data types that will be used when a DataSet is filled using a DataAdapter.

This table also identifies the proper accessors for the data when a DataReader object is used directly.

The type of the parameter is specific to each data provider. Zen ADO.NET data providers must convert the parameter value to a native format before sending it to the server. The best way for an application to describe a parameter is to use the data provider-specific type enumeration.

In generic programming circumstances, the data provider-specific type may not be available. When no provider-specific DB type has been specified, the data type will be inferred from either the System.Data.DbType or from the .NET Framework type of the parameter's value.

Zen ADO.NET data providers use the following order when inferring the data type of a parameter:

The following table shows how the data provider infers its types if only the System.Data.DbType is specified.

The following table shows the mapping that the data provider uses to infer a data type if neither the provider-specific data type nor the System.Data.DbType are provided.

Zen ADO.NET data providers support the use of streams as inputs to long data parameters with the data types listed in the following table.

See Using Streams as Input to Long Data Parameters for a discussion of using streams.

Allowing the use of noncontiguous memory to represent a very large binary or text value, such as a video clip or a large document, improves performance, functionality, and scalability.

Stream objects used to read binary data are derived from the System.IO.Stream object and use the Framework data type of byte[]:

Stream objects used to read text data are derived from the System.IO.TextReader object and use the Framework data type of string:

To enable the use of streams, you set the Value property of the PsqlParameter object to a specific instance of the stream (see PsqlParameter Object). When the command is executed, the data provider reads from the stream to extract the value.

The examples shipped with the data provider include a code example on inserting data into LONGVARCHAR and LONGVARBINARY columns using randomly generated data. The example also shows how to use streaming objects as inputs to LONGVARCHAR and LONGVARBINARY columns.

Parameter markers, including parameter markers for stored procedures, are specified in Zen ADO.NET data providers by using the "?" symbol in SQL statements.

Because parameters are not named, the bindings must occur in the order of the parameters in the statement. This means that the calls to the Add() method on the PsqlParameterCollection object (adding the Parameter objects to the collection) must occur in the order of the "?"s in the command text.

Parameter array binding is typically used with INSERT statements to speed up the time needed to fill a table. An application can specify rows of parameter values with a single execution of a command. The values can then be sent to the database server in a single round trip (depending on the native capabilities of the backend database).

Zen ADO.NET data providers support input parameter arrays for INSERT and UPDATE statements.