Developer Reference > Data Access Methods > Data Provider for .NET > A. .NET Objects Supported
A. .NET Objects Supported
ADO.NET 2.0 introduced a new set of classes that provided an additional, more generic interface between applications and data sources.
Predecessors of ADO.NET 2.0 opted for a tighter factoring of data providers into each specific instance of the data provider used by an application. In contrast, ADO.NET 2.0 and higher deliver a set of base classes that permit applications to handle a heterogeneous set of data sources with a single API, much like is done with ODBC and JDBC today. This means that in ADO.NET 2.0 and higher, all data classes derive from base classes, and exist in a specific dedicated namespace, System.Data.Common.
The data provider supports:
.NET Base Classes
The interfaces on which ADO.NET 1.0 and ADO.NET 1.1 data providers were built were retained for application compatibility. The base classes of ADO.NET 2.0 and higher provide additional functionality:
• DbCommand
• DbCommandBuilder
• DbConnection
• DbDataAdaptor
• DbDataReader
• DBDataPermission
• DbParameter
• DbParameterCollection
• DbConnectionStringBuilder
• DbTransaction
From a day-to-day programming perspective, these classes are provided as abstract implementation. This means they cannot be instantiated directly, but must be used with Provider factories. Each data provider must supply a Factory class, such as PsqlFactory, that derives from the DbFactory class, which contains a set of static methods. Each of these static methods is a factory method for producing an instance of the base classes.
When a data provider is installed, it is registered with the .NET Framework. This allows the common .NET Framework DbFactory to locate any registered data provider that an application requires and provide a common mechanism to establish a connection to a data source. Ultimately, the .NET Framework provides a fully fledged common programming API for ADO.NET data sources.
Data Provider-Specific Classes
The ADO.NET Data Provider supports all of the .NET public objects. The ADO.NET Data Provider attaches the provider-specific prefix Psql to the public .NET objects, for example, PsqlCommand.
The following objects are described:
For more information on public objects, refer to the Microsoft .NET Framework Version 2.0 SDK documentation.
PsqlBulkCopy
The PsqlBulkCopy object uses an API pattern similar to the ADO.NET Bulk API patterns, and has no provider-specific properties or methods. For information about the properties and methods supported, see the data provider online help and the Microsoft .NET Framework SDK documentation.
PsqlBulkCopyColumnMapping
The PsqlBulkCopyColumnMapping object uses an API pattern similar to the ADO.NET Bulk API patterns, and has no provider-specific properties or methods. For information about the properties and methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
PsqlBulkCopyColumnMappingCollection
The PsqlBulkCopyColumnMappingCollection object follows an API pattern similar to the Microsoft SqlBulkCopyColumnMappingCollection class, and has no provider-specific properties or methods. For information about the properties and methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
PsqlCommand Object
The following table describes the public properties of the PsqlCommand object.
Property
Description
AddRowID
Adds the ROWID as part of the Select list of a SQL statement.
If set to true, the values returned in the ROWID column are used to generate more efficient Insert, Delete, and Update commands when using the PsqlCommandBuilder.
If set to false (the initial default), the data provider does not add the ROWID column to the Select list.
ArrayBindCount
Specifies the number of rows of parameters that will be used. The application must set this property before executing a command that uses parameter array binding. The count must equal the length of each of the arrays that is set for each parameter value.
The initial default value is 0. The application does not use parameter array binding.
ArrayBindStatus
Returns an array of row status values. This property enables the application to inspect the per row status after executing a command that uses parameter array binding. The property's type is an array of PsqlRowStatus.
Parameter array binding is performed as a single atomic operation. This means that if the operation succeeds, every entry will be set to OK; if the operation fails, none of the entries will be set to OK.
The PsqlRowStatus enumeration has the following possible values:
• OK. The operation succeeded. All entries are marked as OK.
• Failed. The operation failed. The data provider assigns this value to all status entries except for the row that caused the failure.
• SchemaViolation. When an operation fails, the data provider assigns this value to the row that caused the failure.
BindByName
Specifies how the data provider processes named parameters when executing a stored procedure. The application can use named parameters or use default values for parameters to the stored procedure.
If set to true, the data provider uses the names of parameters supplied in the PsqlParameter objects for the parameter bindings to the Zen server. See example for CommandText.
Alternatively, the user can specify a default value for a named parameter using either of the following methods:
• The application binds the parameters using named parameters, but does not add a PsqlParameter object to the PsqlParameterCollection for the parameters for which the application wants to use the default value.
• The application sets the Value property of the PsqlParameter object to null. The data provider does not send this parameter to the server and uses the parameter’s default value when executing the stored procedure.
When BindByName is set to true and the Parameter Mode connection string option is defined as BindByName or BindByOrdinal, those values defined in the connection string are overridden for the lifetime of the Command object.
If set to false (the initial default), the data provider ignores the names for the parameters supplied in the PsqlParameter objects, and assumes that the parameters are in the same order as they were specified in the Create Procedure statement.
CommandText
Gets or sets the text command to run against the data source.
When using stored procedures, set CommandText to the name of the stored procedure, for example:
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "call EnrollStudent(!!Stud_id!!,!!Class_Id!!, !!GPA!!)";
cmd.BindByName = true;
PsqlParameter Class_Id = new PsqlParameter();
Class_Id.Value = 999;
Class_Id.ParameterName = "!!Class_Id!!";
PsqlParameter Stud_id = new PsqlParameter();
Stud_id.Value = 1234567890;
Stud_id.ParameterName = "!!Stud_id!!";
PsqlParameter GPA = new PsqlParameter();
GPA.Value = 3.2;
GPA.ParameterName = "!!GPA!!";
cmd.Parameters.Add(Class_Id);
cmd.Parameters.Add(Stud_id);
cmd.Parameters.Add(GPA);
CommandTimeout
Gets or sets the wait time before terminating the attempt to execute a command and generating an error.
The initial default is 30 seconds.
We recommend that the application sets the CommandTimeout property to a value that is greater than the largest default timeout value on the server. This ensures that the application gets a more meaningful reply in case of a timeout.
CommandType
Indicates or specifies how the CommandText property is interpreted.
To use stored procedures, set CommandType to StoredProcedure.
Connection
Gets or sets the IDbConnection used by this instance of the IDbCommand.
Parameters
Gets the PsqlParameterCollection.
RowSetSize
Limits the number of rows returned by any query executed on this Command object to the value specified at execute time. The data type for the Read-Write property is signed integer.
Valid values are 0 to 2147483647.
If set to 0 (the initial default), the data provider does not limit the number of rows returned.
StatementCacheBehavior
Retrieves the statement cache behavior or sets the statement cache behavior to one of the values in the PsqlStatementCacheBehavior enumeration. See Enabling Statement Caching for more information.
If set to Implicit (the default) and the Statement Cache Mode connection string option is set to Auto, statement caching occurs implicitly.
If set to Cache and the Statement Cache Mode connection string option is set to ExplicitOnly, statements identified as Cache are cached.
If set to DoNotCache, statement caching does not occur.
Transaction
Gets or sets the transaction in which the PsqlCommand object executes.
UpdatedRowSource
Gets or sets how command results are applied to the DataRow, when used by the Update method of a DataAdapter.
When the UpdateBatchSize property is set to a value other than 1, the UpdatedRowSource property for UpdateCommand, DeleteCommand, and InsertCommand must be set to None or OutputParameters.
If set to None, any returned parameters or rows are ignored.
If set to OutputParameters, output parameters are mapped to the changed row in the DataSet.
The following table describes the public methods supported by the PsqlCommand object.
Method
Description
Cancel
Attempts to cancel the execution of an IDbCommand.
CreateParameter
Creates a new instance of an IDbDataParameter object.
Dispose
Releases the resources used by the component. Overloaded.
ExecuteNonQuery
Executes a SQL statement against the PsqlConnection object, and returns the number of rows affected. This method is intended for commands that do not return results.
ExecuteReader
Executes the CommandText against the connection and builds an IDataReader.
ExecuteScalar
Executes the query, and returns the first row of the resultset that the query returns. Any additional rows or columns are ignored.
Prepare
Creates a prepared version of the command on an instance of Zen.
Note: The Prepare method has no effect in this release of the data provider.
PsqlCommandBuilder Object
Using a PsqlCommandBuilder object can have a negative effect on performance. Because of concurrency restrictions, the PsqlCommandBuilder can generate highly inefficient SQL statements. The end user can often write more efficient update and delete statements than those that the PsqlCommandBuilder generates.
The following table describes the public properties supported by the PsqlCommandBuilder object.
Property
Description
DataAdapter
Gets or sets the PsqlDataAdapter object associated with this PsqlCommandBuilder.
The following table describes the public methods supported by the PsqlCommandBuilder object.
Method
Description
DeriveParameters
Populates the specified PsqlCommand object's Parameters collection with parameter information for a stored procedure specified in the PsqlCommand.
GetDeleteCommand
Gets the automatically generated PsqlCommand object required to perform deletions on the database when an application calls Delete on the PsqlDataAdapter.
GetInsertCommand
Gets the automatically generated PsqlCommand object required to perform inserts on the database when an application calls Insert on the PsqlDataAdapter.
GetUpdateCommand
Gets the automatically generated PsqlCommand object required to perform updates on the database when an application calls Update on the PsqlDataAdapter.
QuoteIdentifier
Given an unquoted identifier in the correct catalog case, returns the correct quoted form of that identifier, including properly escaping any embedded quotes in the identifier.
UnquoteIdentifier
Given a quoted identifier, returns the correct unquoted form of that identifier, including properly un-escaping any embedded quotes in the identifier.
PsqlConnection Object
The PsqlConnection object supports the public constructors described in the following table.
Property
Description
PsqlConnection()
Initializes a new instance of the PsqlConnection class.
PsqlConnection(string connectionString)
Initializes a new instance of the PsqlConnection class when given a string that contains the connection string.
PsqlConnection(string connectionString, PsqlCredential credential)
Initializes a new instance of the PsqlConnection class given a connection string and a PsqlCredential object that contains the user ID and password.
The PsqlConnection object supports the public properties described in the following table. Some properties return the values specified for the corresponding connection string option. Unlike the connection string options, the PsqlConnection property names do not include a space.
Property
Description
ConnectionString
Gets or sets the string used to open a database. See Connection String Properties for a description of the values you can set.
ConnectionTimeout
Gets the time to wait while trying to establish a connection before the data provider terminates the attempt and generates an error.
You can set the amount of time a connection waits to time out by using the ConnectTimeout property or the Connection Timeout connection string option.
If connection failover is enabled (the AlternateServers property defines one or more alternate database servers), this property applies to each attempt to connect to an alternate server. If connection retry is also enabled (the Connection Retry Count connection string option is set to an integer greater than 0), the ConnectionTimeout property applies to each retry attempt.
Credential
Provides a more secure way to specify the password for connecting to a Zen server. PsqlCredential is comprised of a user ID and a password that will be used for connecting to a Zen server. The SecureString object which holds the password should be marked read-only.
Database
Gets the name of the current database or the database to be used when a connection is open.
Host
Returns the value specified for the Host connection string option. Read-only.
Port
Returns the value specified for the Port connection string option. Read-only.
ServerDSN
Returns the value specified for the Server DSN connection string option. Read-only.
ServerName
Returns the value specified for the Server Name connection string option. Read-only.
ServerVersion
Returns a string containing the version of the Zen server to which this object is currently connected.
If the PsqlConnection object is not currently connected, the data provider generates an InvalidOperation exception.
State
Gets the current state of the connection.
StatisticsEnabled
Enables statistics gathering.
If set to True, enables statistics gathering for the current connection.
The following table describes the public methods of PsqlConnection.
Method
Description
BeginTransaction
Begins a database transaction.
When using the overloaded BeginTransaction(IsolationLevel) method, the data provider supports isolation levels ReadCommitted and Serializable. See Isolation Levels for more information.
ChangeDatabase
Changes the current database for an open Connection object.
ClearAllPools
Empties the connection pools for the data provider.
ClearPool
Clears the connection pool that is associated with connection.
If additional connections associated with the connection pool are in use at the time of the call, they are marked appropriately and are discarded when Close is called on them.
Close
Closes the connection to the database.
CreateCommand
Creates and returns a PsqlCommand object associated with the PsqlConnection.
Dispose
Releases the resources used by the PsqlConnection object.
Open
Opens a database connection with the settings specified by the ConnectionString property of the PsqlConnection object.
ResetStatistics
Resets all values to zero in the current statistics gathering session on the connection.
When the connection is closed and returned to the connection pool, statistics gathering is switched off and the counts are reset.
RetrieveStatistics
Retrieves a set of statistics for a connection that is enabled for statistics gathering (see the StatisticsEnabled property). The set of name=value pairs returned forms a "snapshot in time" of the state of the connection when the method is called.
You can use the InfoMessage event of the PsqlConnection object to retrieve warnings and informational messages from the database. If the database returns an error, an exception is thrown. Clients that want to process warnings and informational messages sent by the database server should create a PsqlInfoMessageEventHandler delegate to register for this event.
The InfoMessage event receives an argument of type PsqlInfoMessageEventArgs containing data relevant to this event.
PsqlConnectionStringBuilder Object
PsqlConnectionStringBuilder property names are the same as the connection string option names of the PsqlConnection.ConnectionString property. However, the connection string option name can have spaces between the words. For example, the connection string option name Min Pool Size is equivalent to the property name MinPoolSize.
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"
Connection String Properties
The following table lists the properties that correspond to the connection string options supported by the Zen data providers, and describes each property.
Property
Description
AlternateServers
Specifies a list of alternate database servers to which the data provider will try to connect if the primary database server is unavailable. Specifying a value for this connection string option enables connection failover for the data provider.
The value you specify must be in the form of a string that defines connection information for each alternate server. You must specify the name or the IP address of each alternate server and the port number, if you are not using the default port value of 1583. The string has the format:
"Host=hostvalue;Port=portvalue[, ...]"
For example, the following Alternate Servers value defines two alternate servers for connection failover:
Alternate Servers="Host=AcctServer;Port=1584, Host=123.456.78.90;Port=1584"
See Using Connection Failover for a discussion of connection failover and information about other connection string options that you can set for this feature.
ConnectionPoolBehavior
{LeastRecentlyUsed | MostRecentlyUsed | LeastFrequentlyUsed | MostFrequentlyUsed}. Specifies the order in which a connection is removed from the connection pool for reuse, based on how frequently or how recently the connection has been used.
If set to MostRecentlyUsed, the data provider uses a Last In First Out (LIFO) approach to return the connection that was returned to the pool most recently.
If set to LeastRecentlyUsed (the initial default), the data provider uses a First In First Out (FIFO) approach to return the connection that has been in the pool for the longest time. This value ensures a balanced use of connections in the pool.
If set to MostFrequentlyUsed, the data provider returns the connection with the highest use count. This value enables applications to give preference to the most seasoned connection.
If set to LeastFrequentlyUsed, the data provider returns the connection with the lowest use count. This value ensures a balanced use of connections in the pool.
ConnectionReset
{True | False}. Specifies whether a connection that is removed from the connection pool for reuse by an application will have its state reset to the initial configuration settings of the connection. Resetting the state impacts performance because the new connection must issue additional commands to the server, for example, resetting the current database to the value specified at connect time.
If set to False (the initial default), the data provider does not reset the state of the connection.
ConnectionRetryCount
Specifies the number of times the data provider tries to connect to the primary server, and, if specified, the alternate servers after the initial unsuccessful attempt.
The value can be any integer from 0 to 65535.
If set to 0 (the initial default), the data provider does not try to reconnect after the initial unsuccessful attempt.
If a connection is not established during the retry attempts, the data provider returns an error that is generated by the last server to which it attempted to connect.
This option and Connection Retry Delay, which specifies the wait interval between attempts, can be used in conjunction with connection failover. See Using Connection Failover for a discussion of connection failover and for information about other connection string options that you can set for this feature.
ConnectionRetryDelay
Specifies the number of seconds the data provider waits after the initial unsuccessful connection attempt before retrying a connection to the primary server, and, if specified, the alternate servers.
The value can be any integer from 0 to 65535.
The initial default is 3 (seconds). If set to 0, there is no delay between retrying the connection.
Note: This option has no effect unless the Connection Retry Count connection string option is set to an integer value greater than 0.
This option and the Connection Retry Count connection string option, which specifies the number of times the data provider attempts to connect after the initial attempt, can be used in conjunction with connection failover. See Using Connection Failover for a discussion of connection failover and for information about other connection string options that you can set for this feature.
ConnectionTimeout
Specifies the number of seconds after which the attempted connection to the server will fail if not yet connected. If connection failover is enabled, this option applies to each connection attempt.
If set to 0, the data provider never times out on a connection attempt.
The initial default is 15 seconds.
DatabaseName
Specifies the internal name of the database to which you want to connect. Use this option when you need to connect to a Zen data source for which a ServerDSN has not been defined.
The default value is an empty string.
Note: Do not combine the Database Name and Server DSN connection string options in a connection string.
Alias: DBQ
DbFileDirectoryPath
Note: This option is supported only for Zen ADO.NET Entity Framework Core data providers.
It determines in which directory on the database server the database files are created.
The default value is an empty string.
EnableIPV6
Provides backward compatibility for connecting to a Zen server using an IPv4 address.
If set to True, a client with IPv6 protocol installed can identify itself to the server using either an IPv4 address or an IPv6 address.
If set to False, the clients runs in the backward compatibility mode. The client always identifies itself to the server using an IPv4 address.
The default value for 4.0 is set to True.
For more information about IPv6 formats, see IPv6 in Getting Started with Zen.
EnableTrace
{0 | 1}. Specifies whether tracing is enabled.
If set to 0 (the initial default), tracing is not enabled.
Encoding
Specifies an IANA name or Windows code page number to be used for translating the string data stored in the database.
The default value is an empty string; the current Windows Active Code Page (ACP) is used.
Encrypt
{If Needed | Always | Never}. Determines whether the data provider uses Encrypted Network Communications, also known as wire encryption.
If set to Always, the data provider uses encryption, or, if the server does not allow wire encryption, returns an error.
If set to Never, the data provider does not use encryption and returns an error if wire encryption is required by the server.
If set to IfNeeded (the default), the data provider uses the default setting on the server.
Note: This option may adversely affect performance because of the additional overhead, mainly CPU usage, required to encrypt and decrypt data.
Encryption
{Low | Medium | High}. Determines the minimum level of encryption allowed by the data provider.
The initial default is Medium.
The meaning of these values depends on the encryption module used. With the default encryption module, these values correspond to 40-, 56-, and 128-bit encryption, respectively.
Enlist
{True | False}. Specifies whether the data provider automatically attempts to enlist the connection in creating the thread’s current transaction context.
Note: Because Zen does not support distributed transactions, any attempt to enlist the connection in the thread’s current transaction context will fail.
If set to False, the data provider does not automatically attempt to enlist the connection.
If set to True (the initial default), the data provider returns an error on the connection if a current transaction context exists. If a current transaction context does not exist, the data provider raises a warning.
Host
Specifies the name or the IP address of the Zen database server to which you want to connect. For example, you can specify a server name such as Accountingserver. Or, you can specify an IP address such as 199.226.22.34 (IPv4) or 1234:5678:0000:0000:0000:0000:9abc:def0 (IPv6).
The initial default value is an empty string.
Alias: Server, Server Name
InitialCommandTimeout
Specifies the default wait time (timeout in seconds) before the data provider terminates the attempt to execute the command and generates an error. This option provides the same functionality as the PsqlCommand object’s CommandTimeout property without the need to make changes to the application code. Subsequently, an application can use the CommandTimeout property to override the Initial Command Timeout connection string option.
The initial default value is 30. If set to 0, the query never times out.
For example, in the following C# code fragment, the connection string instructs the application to wait 60 seconds before terminating the attempt to execute the command. The application then specifies a CommandTimeout of 45 seconds, which overrides the value set in the connection string:
PsqlCommand command = new PsqlCommand();
PsqlConnection conn = new PsqlConnection("…; Initial Command Timeout=60; …");
conn .Open();
command.Connection = connection;
// command.CommandTimeout returns 60;
command.CommandTimeout = 45;
// command.CommandTimeout returns 45
command = new PsqlCommand();
command.CommandTimeout = 45;
command.Connection = conn;
// command.CommandTimeout still returns 45
Note: Set the Initial Command Timeout option to a value that is greater than the largest default deadlock detection and timeout value on the server. This ensures that the application receives a more meaningful reply in case of a timeout.
InitializationString
Specifies one statement that will be issued immediately after connecting to the database to manage session settings.
The initial default is an empty string.
Example: To handle CHAR columns that are padded with NULLs, set the value to:
Initialization String=SET ANSI_PADDING ON
Note: If the statement fails to execute for any reason, the connection to the Zen server fails. The data provider throws an exception that contains the errors returned from the server.
LoadBalanceTimeout
Specifies the number of seconds to keep connections in a connection pool. The pool manager periodically checks all pools, and closes and removes any connection that exceeds its lifetime. The MinPoolSize option can cause some connections to ignore this value. See Removing Connections from a Pool for a discussion of connection lifetimes.
The value can be any integer from 0 to 65 335.
If set to 0, (the initial default), the connections have the maximum timeout.
Alias: Connection Lifetime
LoadBalancing
{True | False}. Determines whether the data provider uses client load balancing in its attempts to connect to primary and alternate database servers. The list of alternate servers is specified by the Alternate Servers connection option.
If set to True, the data provider attempts to connect to the database servers in random order. See Using Client Load Balancing for more information about load balancing.
If set to False (the initial default), client load balancing is not used and the data provider connects to each server based on its sequential order (primary server first, then, alternate servers in the order they are specified).
Note: This option has no effect unless alternate servers are defined for the Alternate Servers connection string option.
The Load Balancing connection string option is an optional setting that you can use in conjunction with connection failover. See Using Connection Failover for more information for a discussion of connection failover and for information about other connection options that you can set for this feature.
MaxPoolSize
Specifies the maximum number of connections within a single pool. When the maximum number is reached, no additional connections can be added to the connection pool. The Max Pool Size Behavior connection string option can cause some connections to ignore this value temporarily.
The value can be any integer from 1 to 65335.
The initial default is 100.
MaxPoolSizeBehavior
{SoftCap | HardCap}. Specifies whether the data provider can exceed the number of connections specified by the Max Pool Size option when all connections in the connection pool are in use.
If set to SoftCap, the number of connections created can exceed the value set for Max Pool Size, but the number of connections pooled does not. When the maximum connections for the pool are in use and a a connection request is received, the data provider creates a new connection. If a connection is returned to a pool that is full and contains idle connections, the pooling mechanism selects a connection to be discarded so the connection pool never exceeds the Max Pool Size.
If set to HardCap (the initial default), when the maximum number of connections allowed in the pool are in use, any new connection requests wait for an available connection until the Connection Timeout is reached.
MaxStatementCacheSize
Specifies the maximum number of statements that can be held in the statement cache. The value can be 0, or any integer greater than 1.
Setting the cache size to 0 disables statement caching.
The initial default is 10.
In most cases, using statement caching results in improved performance. See the "Performance Considerations" topic for your data provider for information on how this option can affect performance.
MinPoolSize
Specifies the number of connections created when a connection pool is initialized and the minimum number of connections that will be kept in the pool. The connection pool retains this number of connections even when some connections have exceeded their LoadBalanceTimeout value.
The value can be any integer from 0 to 65335.
If set to 0 (the initial default), when the connection is closed and sent to the connection pool, the pool retains only the original connection used to create the pool.
If set to an integer from 1 to 65535, the specified number of duplicates of the connection are placed in the pool.
See the "Performance Considerations" topic for your data provider for information on how pooling can affect performance.
ParameterMode
Specifies the behavior of native parameter markers and binding. This allows applications to reuse provider-specific SQL code and simplifies migration to Zen ADO.NET data providers.
If set to ANSI (the initial default), the ? character is processed as a parameter marker and bound as ordinal. Applications can toggle the behavior of the BindByName property on a per-command basis.
If set to BindByOrdinal, native parameter markers are used and are bound as ordinal for stored procedures and standard commands.
If set to BindByName, native parameter markers are used and are bound by name for stored procedures and standard commands.
Note:  This option is not supported for Zen ADO.NET Entity Framework data providers.
Password
Specifies a case-insensitive password used to connect to your Zen database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
Alias: PWD
PersistSecurityInfo
{True | False}. Specifies whether to display security information in clear text in the ConnectionString property.
If set to True, the value of the Password connection string option is displayed in clear text.
If set to False (the initial default), the data provider does not display the password in the connection string.
Pooling
{True | False}. Specifies whether connections are pooled. See Using Connection Pooling for more information about connection pooling.
If set to True (the initial default), connection pooling is enabled.
See the "Performance Considerations" topic for your data provider for information on how pooling can affect performance.
Port
Specifies the TCP port of the listener running on the Zen database.
The default port number is 1583.
PVTranslate
{Auto | Nothing}. Specifies whether the client should negotiate a compatible encoding with the server.
If set to Auto, the data provider will set the Encoding connection property to the database code page. In addition, SQL query text will be sent to the engine using UTF-8 encoding instead of the data encoding. This preserves NCHAR string literals in the query text.
If set to Nothing (the default), the setting for the Encoding connection property is used.
SchemaCollectionTimeout
Specifies the number of seconds after which an attempted schema collection operation fails if it is not yet completed.
If set to 0, the data provider never times out on a schema collection operation attempt.
The initial default is 120.
SchemaOptions
Specifies additional database metadata that can be returned. By default, the data provider prevents the return of some available performance-expensive database metadata to optimize performance. If your application needs this database metadata, specify the name or hexadecimal value of the metadata.
This option can affect performance.
See Connection String Properties for the name and hexadecimal value of the database metadata that the data provider can add.
If set to ShowColumnDefaults or 0x04, column defaults are returned.
If set to ShowParameterDefaults or 0x08, column defaults are returned.
If set to FixParameterDirections or 0x10, procedure definitions are returned.
If set to ShowProcedureDefinitions or 0x20, procedure definitions are returned.
If set to ShowViewDefinitions or 0x40, view definitions are returned.
If set to ShowAll or 0xFFFFFFFF (the initial default), all database metadata is returned.
For example, to return descriptions of procedure definitions, specify Schema Options=ShowProcedureDefinitions or Schema Options=0x20.
To show more than one piece of the omitted database 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 restrict. For example, to return descriptions of procedure definitions and view definitions (hexadecimal values 0x20 and 0x40, respectively), specify Schema Options=ShowProcedureDefinitions, ShowViewDefinitions or Schema Options=0x60.
Note: This connection string option may adversely affect performance. See documentation on performance considerations for your data provider for more information.
ServerDSN
Specifies the name of the data source on the server, such as Server DSN=SERVERDEMO.
The default value is DEMODATA.
Note: Do not combine the Database Name and Server DSN connection string options in a connection string.
StatementCacheMode
Specifies the statement cache mode. The statement cache mode controls the behavior of the statement cache. Statements can be cached automatically or only cached when a command is explicitly marked.
If set to Auto, statement caching is enabled for statements marked as Implicit by the PsqlCommand property StatementCacheBehavior. These commands have a lower priority than that of explicitly marked commands, that is, if the statement pool reaches its maximum number of statements, the statements marked Implicit are removed from the statement pool first to make room for statements marked Cache.
If set to ExplicitOnly (the initial default), only statements that are marked Cache by the StatementCacheBehavior property are cached.
In most cases, enabling statement caching results in improved performance. See Performance Considerations for information on how this option can affect performance of the ADO.NET data provider.
Note: This option is not supported for Zen ADO.NET Entity Framework data providers.
Timestamp
{DateTime | String}. Specifies whether Zen time stamps are stored and retrieved as strings in the data provider.
If set to DateTime or not defined (the default), the data provider maps time stamps to the .NET DateTime type. This setting may be appropriate when native precision is required, for example, when using the PsqlCommandBuilder with a time stamp.
If set to String, the time stamps are returned as strings. The data provider maps Zen time stamps to the .NET String type.
TimeType
{DateTime | TimeSpan}. Specifies whether Zen Times are retrieved as Timespan or DataTime in the ADO.NET data provider.
If set to As DateTime, the data provider maps the SQL type TIME to the .NET type System.DateTime.
If set to As TimeSpan, the data provider maps the SQL type TIME to the .NET type System.DateTimespan.
TraceFile
Specifies the path and file name of the trace file.
The initial default is an empty string. If the specified file does not exist, the data provider creates it.
UseCurrentSchema
This connection string option is not supported. Setting it will cause the data provider to throw an exception.
UserID
Specifies the default Zen user name used to connect to your Zen database.
Alias: UID
The following table lists the name and the hexadecimal value of the column collection that the data provider will omit from the returned data. To specify multiple values, specify a comma-separated list of the names, or the sum of the hexadecimal values of the column collections that you want to return.
Name
Hex Value
Collection/Column
ShowColumnDefaults1
0x04
Columns/COLUMN_DEFAULT
ShowParameterDefaults
0x08
ProcedureColumns//PARAMETER_DEFAULT
FixParameterDirections
0x10
ProcedureColumns/PARAMETER_TYPE
ShowProcedureDefinitions
0x20
Procedures/PROCEDURE_DEFINITION
ShowViewDefinitions
0x40
Views/VIEW_DEFINITION
ShowAll
0x7F
All
1. COLUMN_HAS_DEFAULT is always reported with a value of null.
The PsqlConnectionStringBuilder object has no provider-specific methods. For information about the methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
PsqlCredential Object
The PsqlCredential object provides a secure way to log in using Zen server authentication. PsqlCredential is comprised of a user ID and a password recognized by the Zen server.
The password in a PsqlCredential object is of type SecureString, unlike Connection String where the password is unsecure until the provider reads it and converts it to SecureString. The password is handled in a secure way without writing it to memory. The string that stores the password is cleaned after use.
Note:  Use PsqlCredential only when the authentication method requires the user ID and password. Also, if you are using Kerberos or Client, you should not use PsqlCredential. Finally, the Connection String should not include the user ID and password when the Credential object is being used.
The following code snippet shows how the PsqlCredential class can be used. The method used to convert a string into a SecureString in this example is one of many possible methods.
PsqlConnection con = null;
PsqlCredential lobjCredential = null;
string userId = "ABCD";
SecureString password = ConvertToSecureString("XYXYX");
private static SecureString ConvertToSecureString(string value)
{
var securePassword = new SecureString();
foreach (char c in value.ToCharArray())
securePassword.AppendChar(c);
securePassword.MakeReadOnly();
return securePassword;
}
try
{
lobjCredential = new PsqlCredential(userId, password);
con = new PsqlConnection("Host=nc-xxx;Port=xxxx;Database Name=xxxx"",
lobjCredential);
con.Open();
Console.WriteLine("Connection Successfully Opened...");
con.Close();
}
catch (Exception e)
{
Console.Write(e.Message)
}
finally
{
if (null != con)
{
con.Close();
con = null;
}
if (null != lobjCredential)
{
lobjCredential = null;
}
}
The following table lists the provider-specific implementation of the public properties of the PsqlCredential object.
Property
Description
User ID
Returns the user ID component of the PsqlCredential object.
Uses String data type. NULL and empty are invalid values.
Password
Returns the password component of the PsqlCredential object.
Uses SecureString data type. NULL is an invalid value.
If you use the PsqlCredential object while opening the connection and want to use the same pooled connection, you need to reference the same PsqlCredential object so that the same connection is fetched from the available connection pool.
If you create a new credential object for each connection, the driver treats them separately and puts them into different connection pools, even if the same user ID and password are used.
PsqlDataAdapter Object
The PsqlDataAdapter object uses PsqlCommand objects to execute SQL commands on the Zen database, to load the DataSet with data, and to reconcile the changed data in the DataSet to the database.
The following table describes the public properties of PsqlDataAdapter.
Property
Description
UpdateBatchSize
Gets or sets a value that specifies the number of commands that can be executed in a batch.
If your application uses disconnected DataSets and updates those DataSets, you can positively influence performance by setting this property to a value greater than 1. By default, the data provider attempts to use the largest batch size possible. However, this may not equate to optimal performance for your application. Set the value based on the number of rows you typically update in the DataSet. For example, if you are updating less than 50 rows, a suggested setting for this property is 25.
If set to 0, the PsqlDataAdapter uses the largest batch size the data source can support. The UpdatedRowSource property for the InsertCommand, UpdateCommand, and DeleteCommand must be set to None or OutputParameters.
If set to 1, batch updating is disabled.
If set to a value greater than 1, the specified number of commands are executed in a batch. The UpdatedRowSource property for the InsertCommand, UpdateCommand, and DeleteCommand must be set to None or OutputParameters.
DeleteCommand
Gets or sets a SQL statement for deleting records from the Zen data source.
InsertCommand
Gets or sets a SQL statement used to insert new records into the Zen database.
SelectCommand
Gets or sets a SQL statement used to select records in the Zen database.
UpdateCommand
Gets or sets a SQL statement used to update records in the data source.
PsqlDataReader Object
The PsqlDataReader object is a forward-only cursor that retrieves read-only records from a database. Performance is better than using PsqlDataAdapter, but the result set cannot be modified.
The following table describes the public properties of PsqlDataReader.
Property
Description
Depth
Gets a value indicating the depth of nesting for the current row.
HasRows
Gets a value indicating whether the PsqlDataReader contains one or more rows.
IsClosed
Gets a value indicating whether the data reader is closed.
RecordsAffected
Gets the number of rows that were changed, inserted, or deleted by execution of the SQL statement.
The following table describes some of the public methods of the PsqlDataReader.
Method
Description
Close
Closes the DataReader. Always call the Close method when you finish using the DataReader object.
GetSchemaTable
Returns a DataTable that describes the column metadata of the PsqlDataReader. See PsqlCredential Object for more information.
NextResult
Advances the data reader to the next result when reading the results of batch SQL statements.
Read
Advances the IDataReader to the next result.
PsqlError Object
The PsqlError object collects information relevant to errors and warnings generated by the Zen server.
The following table describes the public properties supported by PsqlError.
Property
Description
Message
Gets the error message text returned from the Zen server.
Number
Gets the error number returned from the Zen server.
SQLState
Gets the string representation of the SQLState when an exception is thrown by the Zen data provider, or 0 if the exception is not applicable to the error. This property is read-only.
Note: For all of the ADO.NET client error messages which do not have any SQLstate information, S1000 is used as the default SQLState.
PsqlErrorCollection Object
The PsqlErrorCollection object is created by a PsqlException to contain all the errors generated by the Zen server.
The following table provides the public provider-specific properties supported for the PsqlErrorCollection object. For information about other properties and methods supported, refer to the data provider's online help and the Microsoft .NET Framework SDK documentation.
Property
Description
Count
Gets the number of PsqlError objects generated by the Zen server.
The PsqlErrorCollection object supports the public methods described in the following table.
Method
Description
CopyTo
Copies the PsqlError objects from the ErrorCollection to the specified array.
GetEnumerator
Returns the IEnumerator interface for a given array.
PsqlException Object
Provider-specific exceptions are derived directly from the System.Data interface. Only the public properties and methods, for example, the Message property, are directly available on the System.Exception object in a generic sense. The SQLState and Number properties are only accessible through provider-specific code or by using reflection.
ADO.NET 2.0 introduced a new property on the DbException class, Data. This property returns a collection of key-value pair tuples that provide additional user-defined information about an exception. The ADO.NET Data Provider gets a collection of key/value pairs such as SQLState, Number, and ErrorPosition.
The Psql.Data.SqlClient prefix is applied to each key, for example:
Psql.Data.SqlClient.Data["SQLState"] = 28000;
The properties described in the following table apply to the last error generated, if multiple errors exist. The application should check the Count property of the PsqlErrorCollection returned in the Errors property of this object to determine whether multiple errors occurred. See PsqlErrorCollection Object for more information.
Property
Description
Errors
Gets or sets a PsqlErrorCollection of one of more PsqlError objects.
Message
Specifies the error message text that is returned from the Zen server.
Number
Gets or sets the number returned from the Zen server.
SQLState
Returns the string representation of the SQLState when an exception is thrown by the Zen data provider, or 0 if the exception is not applicable to the error. This property is read-only.
PsqlFactory Object
Provider Factory classes allow users to program to generic objects. Once instantiated from DbProviderFactory, the factory generates the proper type of concrete class.
The following table lists the static methods used to accommodate choosing the ADO.NET Data Provider and instantiating its DbProviderFactory.
Method
Description
CreateCommand
Returns a strongly typed DbCommand instance.
CreateCommandBuilder
Returns a strongly typed DbCommandBuilder instance.
CreateConnection
Returns a strongly typed DbConnection instance.
CreateConnectionStringBuilder
Returns a strongly typed DbConnectionString instance.
CreateDataAdapter
Returns a strongly typed DbDataAdapter instance.
CreateDataSourceEnumerator
Returns a strongly typed PsqlDataSourceEnumerator instance.
CreateParameter
Returns a strongly typed DbParameter instance.
PsqlInfoMessageEventArgs Object
The PsqlInfoMessageEventArgs object is passed as an input to the PsqlInfoMessageEventHandler and contains information relevant to a warning generated by the Zen server.
The following table describes the public properties for PsqlInfoMessageEventArgs.
Property
Description
Errors
Specifies a PsqlErrorCollection that contains a collection of warnings sent from the Zen server. See PsqlErrorCollection Object for more information.
Message
Returns the text of the last message returned from the Zen server. The application should check the Count property of the PsqlErrorCollection returned in the Errors property of this object to determine whether multiple warnings occurred.
PsqlParameter Object
The PsqlParameter object represents a parameter to a PsqlCommand object.
The following table describes the public properties for PsqlParameter.
Property
Description
ArrayBindStatus
Determines whether any values in the array of PsqlParameterStatus entries should be bound as null. The PsqlParameterStatus enumeration contains the entry NullValue.
When this property is not set, then no values are null. The length of the array should match the amount specified by the PsqlCommand object's ArrayBindCount property (see PsqlCommand Object).
The initial default is null.
DbType
Gets or sets the DbType of the parameter.
Direction
Gets or sets a value that indicates whether the parameter is input-only, output-only, bidirectional, or the return value parameter of a stored procedure.
IsNullable
Gets or sets a value that indicates whether the parameter accepts null values.
ParameterName
Gets or sets the name of the PsqlParameter object.
Precision
Gets or sets the maximum number of digits used to represent the Value property.
Scale
Gets or sets the number of decimal places to which the Value property is resolved.
Size
Gets or sets the maximum size, in bytes, of the data within the column.
SourceColumn
Gets or sets the name of the source column that is mapped to the DataSet and used for loading or returning the Value property.
SourceColumnNullMapping
Sets or gets a value that indicates whether the source column is nullable.
SourceVersion
Gets or sets the DataRowVersion to use when loading the Value property.
Value
Gets or sets the value of the parameter.
The initial default value is null.
Note: When array binding is enabled (see the ArrayBindCount property of the PsqlCommand Object), this property is specified as an array of values. Each array's length must match the value of the ArrayBindCount property. When specifying the array's values for binary type columns, the data will actually be specified as byte[]. This is an array of arrays of bytes. The data provider anticipates a "jagged" array as such when using parameter array binding with parameters.
If set to null for a stored procedure parameter, the data provider does not send the parameter to the server. Instead, the default value for the parameter is used when executing the stored procedure.
PsqlParameterCollection Object
The PsqlParameterCollection object represents a collection of parameters relevant to a PsqlCommand, and includes their mappings to columns in a DataSet.
The following table describes the public properties for PsqlParameterCollection.
Property
Description
Count
Gets the number of PsqlParameter objects in the collection.
IsFixedSize
Gets a value that indicates whether the PsqlParameterCollection has a fixed size.
IsReadOnly
Gets a value that indicates whether the PsqlParameterCollection is read-only.
IsSynchronized
Gets a value that indicates whether the PsqlParameterCollection is thread-safe.
Item
Gets the parameter at the specified index. In C#, this property is the indexer for the IDataParameterCollection class.
SynchRoot
Gets the object used to synchronize access to the PsqlParameterCollection.
The following table provides the public methods for PsqlParameterCollection.
Method
Description
Contains
Gets a value that indicates whether a parameter in the collection has the specified source table name.
IndexOf
Gets the location of the IDataParameter within the collection.
RemoveAt
Removes the IDataParameter from the collection.
PsqlTrace Object
The PsqlTrace object is created by the application to debug problems during development. Setting the properties in the PsqlTrace object overrides the settings of the environment variables. For your final application, be sure to remove references to the PsqlTrace object.
The following code fragment creates a Trace object named MyTrace.txt. All subsequent calls to the data provider will be traced to that file.
PsqlTrace MyTraceObject = new PsqlTrace();
MyTraceObject.TraceFile="C:\\MyTrace.txt";
MyTraceObject.RecreateTrace = 1;
MyTraceObject.EnableTrace = 1;
The following table describes the public properties for the PsqlTrace object.
Property
Description
EnableTrace
If set to 1 or higher, enables tracing.
The initial default value is 0. Tracing is disabled.
RecreateTrace
If set to 1, recreates the trace file each time the application restarts.
If set to 0 (the initial default), the trace file is appended
TraceFile
Specifies the path and name of the trace file.
The initial default is an empty string. If the specified file does not exist, the data provider creates it.
Note: Setting EnableTrace starts the tracing process. Therefore, you must define the property values for the trace file before setting EnableTrace. Once the trace processing starts, the values of TraceFile and RecreateTrace cannot be changed.
The following table describes the public methods for PsqlTrace.
Method
Description
DumpFootprints
Displays the footprint of all source files in a data provider.
PsqlTransaction Object
The following table describes the public properties of the PsqlTransaction object.
Property
Description
Connection
Specifies the PsqlConnection object associated with the transaction. See PsqlConnection Object for more information.
IsolationLevel
Defines the isolation level for the entire transaction. If the value is changed, the new value is used at execution time.
The following table describes the public methods of the PsqlTransaction object.
Method
Description
Commit
When overridden in a derived class, returns the Exception that is the root cause of one or more subsequent exceptions.
Rollback
Cancels modifications made in a transaction before the transaction is committed.
Zen Common Assembly Classes
Zen ADO.NET data providers support additional classes that provide enhanced functionality, such as bulk load. All classes are created with 100% managed code. The following classes are provided in the Pervasive.Data.Common.dll assembly:
The classes used for bulk loading implement the generic programming model. They can be used with any DataDirect Technologies ADO.NET data provider or ODBC driver that supports Zen Bulk Load and any supported database.
CsvDataReader
The CsvDataReader class provides the DataReader semantics for the CSV file format defined by Zen Bulk Load.
The following table lists the public properties for the CsvDataReader object.
Property
Description
BulkConfigFile
Specifies the CSV bulk configuration file that is produced when the WriteToFile method is called. A bulk load configuration file defines the names and data types of the columns in the bulk load data file in the same way as the table or result set from which the data was exported. A bulk load configuration file is supported by an underlying XML schema.
The path may be fully qualified. Otherwise, the file is considered to exist in the current working directory.
Note: This property can only be set prior to the Open() call and after the Close() call; otherwise, an InvalidOperationException is thrown.
BulkFile
Specifies the bulk load data file that contains the CSV-formatted bulk data. The file name is used for writing (exporting) and reading (importing) the bulk data. If the file name provided does not contain an extension, the ".csv" extension is assumed.
The path may be fully qualified. Otherwise, the file is considered by default to exist in the current working directory. An InvalidOperationException is thrown if this value is not set.
Note: This property can only be set prior to the Open() call and after the Close() call; otherwise, an InvalidOperationException is thrown.
ReadBufferSize
Specifies the size of the read buffer when using bulk load to import data from a data source.
The initial default is 2048 KB.
Values equal to or less than zero cause a System.ArgumentOutOfRangeException to be thrown.
RowOffset
Specifies the row from which to start the bulk load read. The RowOffset is relative to the first (1) row.
The initial default is 1.
Values equal to or less than zero cause a System.ArgumentOutOfRangeException to be thrown.
Note: This property can only be set prior to the Open() call and after the Close() call; otherwise, an InvalidOperationException is thrown.
SequentialAccess
Determines whether columns are accessed in order for improved performance.
The initial default is False.
Note: This property can only be set prior to the Open() call and after the Close() call; otherwise, an InvalidOperationException is thrown.
The following table lists the public methods for the CsvDataReader object.
Method
Description
Open
Opens the bulk file instance and associated metadatafile for processing.
CsvDataWriter
The CsvDataWriter class provides the DataWriter semantics of the CSV file format that is written by Zen Bulk Load.
For more information, refer to the data provider’s online help.
The following table lists the public properties for the CsvDataWriter object.
Property
Description
BinaryThreshold
Specifies the threshold (in KB) at which separate files are generated to store binary data during a bulk unload.
The Initial default is 32.
Values less than zero cause a System.ArgumentOutOfRangeException to be thrown.
CharacterThreshold
Specifies the threshold (in KB) at which separate files are generated to store character data during a bulk unload.
The initial default is 64.
Values less than zero cause a System.ArgumentOutOfRangeException to be thrown.
CsvCharacterSetName
Specifies any of the supported IANA code page names that may be used as values. See IANA Code Page Mappings for the supported values.
Applications can obtain the database character that was set using the PsqlConnection.DatabaseCharacterSetName property.
If an unrecognized CharacterSetName is used, an exception is thrown, declaring that invalid character set has been used.
The initial default value is UTF-16.
Note this property enforces the character set used in the CSV data file and overflow files added.
The following table lists the public methods for the CsvDataWriter object.
Method
Description
Open
Opens the bulk file instance and associated metadatafile for processing.
WriteToFile
Writes the contents of the IDataReader to the bulk data file.
DbBulkCopy
The DbBulkCopy class facilitates copying rows from one data source to another.
The DbBulkCopy object follows the de facto standard defined by the Microsoft SqlBulkCopy class, and has no provider-specific properties or methods. For information about the properties and methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
DbBulkCopyColumnMapping
The DbBulkCopyColumnMapping class represents a column mapping from the data sources table to a destination table.
The DbBulkCopyColumnMapping object follows the de facto standard defined by the Microsoft SqlBulkCopyColumnMapping class, and has no provider-specific properties or methods. For information about the properties and methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
DbBulkCopyColumnMappingCollection
The DbBulkCopyColumnMappingCollection class is a collection of DbBulkCopyColumnMapping objects.
The DbBulkCopyColumnMappingCollection object follows the de facto standard defined by the Microsoft SqlBulkCopyColumnMappingCollection class, and has no provider-specific properties or methods. For information about the properties and methods supported, refer to the data provider’s online help and the Microsoft .NET Framework SDK documentation.
 
Last modified date: 11/04/2024