Developer Reference : Data Access Methods : Data Provider for .NET : The ADO.NET Data Provider
 
The ADO.NET Data Provider
The Zen 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.
The following topics describe features that pertain to the Zen ADO.NET data provider:
About the Zen ADO.NET Data Provider
Using Connection Strings with the ADO.NET Data Provider
Performance Considerations
Data Types
Parameter Arrays
Note: See The ADO.NET Entity Framework Data Provider for information about using the data provider with the ADO.NET Entity Framework.
About the Zen ADO.NET Data Provider
The Zen 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.
Namespace
The namespace for the 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 the ADO.NET data provider’s namespace in your applications:
C#
// Access Zen
using System.Data;
using System.Data.Common;
using Pervasive.Data.SqlClient;
Visual Basic
' Access Zen
Imports System.Data
Imports System.Data.Common
Imports Pervasive.Data.SqlClient
Assembly Name
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.
Using Connection Strings with the ADO.NET Data Provider
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 ADO.NET Data Provider:
"Server DSN=SERVERDEMO;Host=localhost"
See Using Connection Strings for guidelines on specifying connection strings.
See Table 35 for a description of the supported connection string options.
Constructing a Connection String
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 35 lists the connection string properties, and describes each property.
The connection string options have the following form:
option name=value
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:
"Server DSN=SERVERDEMO;Host=localhost"
Performance Considerations
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.
Connection String Options that Affect Performance
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:
Load Balance Timeout: You can define how long to keep connections in the pool. The pool manager checks a connection's creation time when it is returned to the pool. The creation time is compared to the current time, and if the timespan exceeds the value of the Load Balance Timeout option, the connection is destroyed. The Min Pool Size option can cause some connections to ignore this value.
Connection Reset: Resetting a re-used connection to the initial configuration settings impacts performance negatively because the connection must issue additional commands to the server.
Max Pool Size: Setting the maximum number of connections that the pool can contain too low might cause delays while waiting for a connection to become available. Setting the number too high wastes resources.
Min Pool Size: A connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size, if one has been specified. The connection pool retains this number of connections, even when some connections exceed their Load Balance Timeout value.
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:
Schema Option=ShowProcedureDefinitions, ShowViewDefinitions
Schema Options=0x60
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.
Properties that Affect Performance
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).
Data Types
Table 5 through Table 8 list the data types supported by the ADO.NET Data Provider.
Table 5 maps the Zen data types to the .NET Framework types.
Table 6 maps the data types the data provider uses if only the System.Data.DbType is specified.
Table 7 maps the data types the data provider uses to infer a data type if neither the provider-specific type nor the System.Data.DbType are provided.
Table 8 maps the data types the data provider uses when streams are used as inputs to Long data parameters.
Mapping Zen Data Types to .NET Framework Data Types
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 the data when a DataReader object is used directly.
The Zen Data Type column refers to the native type name.
The PsqlDbType column refers to the ADO.NET Data Provider’s type enumeration. Generally, there is a one to one mapping between the native type and the PsqlDbType. The Zen NUMBER data type, which can be either a decimal or a double, is an exception to this rule.
The .NET Framework Type column refers to the base data types available in the Framework.
The .NET Framework Typed Accessor column refers to the method that must be used to access a column of this type when using a DataReader.
 
Table 5 Mapping of Zen Data Types  
Zen Data Type
PsqlDbType
.NET Framework Type
.NET Framework Typed Accessor
AUTOTIMESTAMP
Timestamp
DateTime
GetDateTime()
BFLOAT4
BFloat4
Single
GetSingle()
BFLOAT8
BFloat8
Double
GetDouble()
BIGIDENTITY
BigInt
Int64
GetInt64()
BIGINT
BigInt
Int64
GetDecimal()
BINARY
Binary
Byte[]
GetBytes()
BIT
Bit
Byte[]
GetBytes()
CHAR
Char
String
Char[]
GetString()
GetChars()
CURRENCY
Currency
Decimal
GetDecimal()
DATE
Date
DateTime
GetDateTime()
DATETIME
DateTime11
DateTime
GetDateTime()
DECIMAL
Decimal
Decimal
GetDecimal()
DOUBLE
Double
Double
GetDouble()
FLOAT
Float
Double
GetDouble()
IDENTITY
Identity
Int32
GetInt32()
INTEGER
Integer
Int32
GetInt32()
LONGVARBINARY
LongVarBinary
Byte[]
GetBytes()
LONGVARCHAR
LongVarChar
Byte[]
GetBytes()
MONEY
Money
Decimal
GetDecimal()
NCHAR
NChar
String
Char[]
GetString()
GetChars()
NLONGVARCHAR
NLongVarChar
String
Char[]
GetString()
GetChars()
NUMERIC
Decimal
Decimal
GetDecimal()
NUMERICSA
DecimalSA
Decimal
GetDecimal()
NUMERICSTS
DecimalSTS
Decimal
GetDecimal()
NVARCHAR
NVarChar
String
Char[]
GetString()
GetChars()
REAL
Real
Single
GetSingle()
SMALLIDENTITY
SmallIdentity
Int16
GetInt16()
SMALLINT
SmallInt
Int16
GetInt16()
TIME
Time
Timespan2
GetValue()
TIMESTAMP, TIMESTAMP2
Timestamp
DateTime
GetDateTime()
TINYINT
TinyInt
SByte
GetByte()
UBIGINT
UBigInt
UInt64
GetUInt64()
UNIQUE_IDENTIFIER
UniqueIdentifier1
String
GetString()
UINTEGER
UInteger
UInt32
GetUInt32()
USMALLINT
USmallInt
UInt16
GetUInt16()
UTINYINT
UTinyInt
Byte
GetByte()
VARCHAR
VarChar
String
Char[]
GetString()
GetChars()
1 Supported in Zen 9.5 and higher
2 Depends on the setting of the timetype connect option.
Mapping Parameter Data Types
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:
The data provider uses the provider-specific data type if it has been specified.
The data provider infers the data type from the System.Data.DbType if it has been specified, but the provider-specific data type has not been specified.
The data provider infers the data type from the .NET Framework type if neither the provider-specific data type nor the System.Data.DbType have been specified.
Table 6 shows how the data provider infers its types if only the System.Data.DbType is specified.
 
Table 6 Mapping System.Data.DbTypes to PsqlDbTypes 
System.Data.DbType
PsqlDbType
AnsiString
VarChar
AnsiStringFixedLength
Char
Binary
Binary
Boolean
Integer
Byte
Integer
Currency
Currency
Date
Date
DateTime
DateTime1
Decimal
Decimal or Money
Double
Double
Float
Float
GUID
UniqueIdentifier*
Int16
SmallInt
Int32
Integer
Int64
BigInt
Int64
BigIdentity
Sbyte
Integer
Single
BFloat4
String
NVarChar
StringFixedLength
NChar
Time
Time
Uint16
USmallInt
Uint32
UInteger
Uint64
UBigInt
VarNumeric
Decimal
1 Supported in PSQL 9.5 and higher.
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.
 
Table 7 Mapping .NET Framework Types to PsqlDbType 
.NET Framework Type
PsqlDbType
Boolean
Integer
Byte
Integer
Byte[]
Binary
DateTime
Timestamp
Decimal
Decimal
Double
Double
Int16
SmallInt
Int32
Integer
Int64
BigInt
Single
BFloat4
String
NVarChar
VarChar (if PvTranslate=Nothing)
Uint16
USmallInt
Uint32
UInteger
Uint64
UBigInt
Data Types Supported with Stream Objects
The ADO.NET data provider supports the use of streams as inputs to long data parameters with the data types listed in Table 8.
 
Table 8 Supported Stream Objects
Provider Data Type
Stream Type Supported
LONGVARBINARY
Stream
LONGVARCHAR
TextReader
See Using Streams as Input to Long Data Parameters for a discussion of using streams.
Using Streams as Input to Long Data Parameters
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[]:
System.IO.BufferedStream
System.IO.FileStream
System.IO.MemoryStream
System.Net.Sockets.NetworkStream
System.Security.Cryptography.CryptoStream
Stream objects used to read text data are derived from the System.IO.TextReader object and use the Framework data type of string:
System.IO.StreamReader
System.IO.StringReader
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
Parameter markers, including parameter markers for stored procedures, are specified in the ADO.NET data provider by using the "?" symbol in SQL statements.
UPDATE emp SET job = ?, sal = ? WHERE empno = ?
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 Arrays
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.