The PSQL ADO.NET data provider (the ADO.NET data provider) provides data access to any .NET-enabled application or application server. The ADO.NET data provider delivers high-performance point-to-point and n-tier access to industry-leading data stores across the Internet and intranets. Because it is optimized for the .NET environment, the ADO.NET data provider allows you to incorporate .NET technology and extend the functionality and performance of your existing system.

This chapter describes features that pertain to the PSQL ADO.NET data provider. It contains the following sections:

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

The PSQL ADO.NET data provider is compliant with the Microsoft .NET Framework 2.0, 3.0, 3.5, 3.5 SP1, 4.0, 4.5, 4.5.1, 4.5.2, 4.6.1, and 4.6.2 Preview. The data provider is built with 100% managed code; it 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 ADO.NET data provider is Pervasive.Data.SqlClient. When connecting to the PSQL database, you use the PsqlConnection and PsqlCommand objects in the Pervasive.Data.SqlClient namespace.

The following code fragment shows how to include the ADO.NET data provider’s namespace in your applications:

The strongly named assembly for the 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 PSQL ADO.NET Data Provider:

See Using Connection Strings for guidelines on specifying connection strings.

See Table 27 for a description of the supported connection string options.

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. Table 27 lists the connection string properties, and describes each property.

The connection string options have the 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 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).

Table 5 through Table 8 list the data types supported by the PSQL ADO.NET Data Provider.

Table 5 lists the data types supported by the ADO.NET data provider 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 accessing the data when a DataReader object is used directly.

The type of the parameter is specific to each data provider. The ADO.NET data provider 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.

The ADO.NET data provider uses the following order when inferring the data type of a parameter:

Table 6 shows how the data provider infers its types if only the System.Data.DbType is specified.

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

The ADO.NET data provider supports the use of streams as inputs to long data parameters with the data types listed in Table 8.

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 the ADO.NET data provider 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).

The ADO.NET data provider supports input parameter arrays for INSERT and UPDATE statements.