Data Provider-specific Classes
The PSQL ADO.NET Data Provider supports all of the .NET public objects. The PSQL ADO.NET Data Provider attaches the provider-specific prefix Psql to the public .NET objects, for example, PsqlCommand.
The following objects are described in this appendix:
•
PsqlBulkCopy
•
PsqlBulkCopyColumnMapping
•
PsqlBulkCopyColumnMappingCollection
•
PsqlCommand Object
•
PsqlCommandBuilder Object
•
PsqlConnection Object
•
PsqlConnectionStringBuilder Object
•
PsqlDataAdapter Object
•
PsqlDataReader Object
•
PsqlError Object
•
PsqlErrorCollection Object
•
PsqlException Object
•
PsqlFactory Object
•
PsqlInfoMessageEventArgs Object
•
PsqlParameter Object
•
PsqlParameterCollection Object
•
PsqlTrace Object
•
PsqlTransaction Object
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, refer to the data provider’s 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
Table 21 describes the public properties of the PsqlCommand object.
 
Table 21
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 PSQL 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.
Table 22 describes the public methods supported by the PsqlCommand object.
 
Table 22
Public Methods of 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 PSQL.
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.
Table 23 describes the public properties supported by the PsqlCommandBuilder object.
 
Table 23
Public Properties of the PsqlCommandBuilder Object  
Property
Description
DataAdapter
Gets or sets the PsqlDataAdapter object associated with this PsqlCommandBuilder.
Table 24 provides the public methods supported for the PsqlCommandBuilder object.
 
Table 24
Public Methods of 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 properties described in Table 25. 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.
 
Table 25
Public Properties of the PsqlConnection Object 
Property
Description
ConnectionString
Gets or sets the string used to open a database. See Table 27 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.
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 PSQL 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.
Table 26 describes the public methods of PsqlConnection.
 
Table 26
Public Methods of the PsqlConnection Object  
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 PSQL ADO.NET Data Provider:
"Server DSN=SERVERDEMO;Host=localhost"
Table 27 lists the properties that correspond to the connection string options supported by the PSQL data providers, and describes each property.
 
Table 27
Connection String Properties 
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 PSQL 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
EnableIPV6
Provides backward compatibility for connecting to the PSQL 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 PSQL.
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 connection string option may adversely affect performance. See the "Performance Considerations" section in the chapter for your data provider for more information.
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 PSQL 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 PSQL 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 PSQL 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" section in the chapter 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" section in the chapter 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 the ADO.NET data provider.
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 the ADO.NET Entity Framework data provider.
Password
Specifies a case-insensitive password used to connect to your PSQL 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" section in the chapter for your data provider for information on how pooling can affect performance.
Port
Specifies the TCP port of the listener running on the PSQL 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 default for the 4.2 provider), 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 for the 4.0 provider), 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 Table 28 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 FixProcedureParamDirection 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 the "Performance Considerations" section in the chapter 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 the ADO.NET Entity Framework data provider.
Timestamp
{DateTime | String}. Specifies whether PSQL timestamps are stored and retrieved as strings in the data provider.
If set to DateTime or not defined (the default), the data provider maps timestamps to the .NET DateTime type. This setting may be appropriate when native precision is required, for example, when using the PsqlCommandBuilder with TimeStamp.
If set to String, the timestamps are returned as strings. The data provider maps PSQL timestamps to the .NET String type.
TimeType
{DateTime | TimeSpan}. Specifies whether PSQL 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 PSQL user name used to connect to your PSQL database.
Alias: UID
Table 28 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.
 
Table 28
PSQL Column Collections 
Name
Hex Value
Collection/Column
ShowColumnDefaults1
0x04
Columns/COLUMN_DEFAULT
ShowParameterDefaults
0x08
ProcedureColumns//PARAMETER_DEFAULT
FixProcedureParamDirection
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.
PsqlDataAdapter Object
The PsqlDataAdapter object uses PsqlCommand objects to execute SQL commands on the PSQL database, to load the DataSet with data, and to reconcile the changed data in the DataSet to the database.
Table 29 describes the public properties of PsqlDataAdapter.
 
Table 29
Public Properties of the PsqlDataAdapter Object
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 PSQL data source.
InsertCommand
Gets or sets a SQL statement used to insert new records into the PSQL database.
SelectCommand
Gets or sets a SQL statement used to select records in the PSQL 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.
Table 30 describes the public properties of PsqlDataReader.
 
Table 30
Public Properties of the PsqlDataReader Object 
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.
Table 31 describes some of the public methods of the PsqlDataReader.
 
Table 31
Supported Methods of the PsqlDataReader Object
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 Table 49 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 PSQL server.
Table 32 describes the public properties supported by PsqlError.
 
Table 32
Public Properties of the PsqlError Object  
Property
Description
Message
Gets the error message text returned from the PSQL server.
Number
Gets the error number returned from the PSQL server.
SQLState
Gets the string representation of the SQLState when an exception is thrown by the PSQL 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 PSQL server.
Table 33 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.
 
Table 33
Public Properties of the PsqlErrorCollection Object 
Property
Description
Count
Gets the number of PsqlError objects generated by the PSQL server.
The PsqlErrorCollection object supports the public methods described in Table 34.
 
Table 34
Public Methods of the PsqlErrorCollection Object 
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 PSQL 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 Table 35 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.
 
Table 35
Public Properties of the PsqlException Object 
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 PSQL server.
Number
Gets or sets the number returned from the PSQL server.
SQLState
Returns the string representation of the SQLState when an exception is thrown by the PSQL 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.
Table 36 lists the static methods used to accommodate choosing the PSQL ADO.NET Data Provider and instantiating its DbProviderFactory.
 
Table 36
Methods of the PsqlFactory Object 
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 PSQL server.
Table 37 describes the public properties for PsqlInfoMessageEventArgs.
 
Table 37
Public Properties of PsqlInfoMessageEventArgs 
Property
Description
Errors
Specifies a PsqlErrorCollection that contains a collection of warnings sent from the PSQL server. See PsqlErrorCollection Object for more information.
Message
Returns the text of the last message returned from the PSQL 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.
Table 38 describes the public properties for PsqlParameter.
 
Table 38
Public Properties of the PsqlParameter Object  
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.
Table 39 describes the public properties for PsqlParameterCollection.
 
Table 39
Public Properties of 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.
Table 40 provides the public methods for PsqlParameterCollection.
 
Table 40
Public Methods of the PsqlParameterCollection Object
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;
Table 41 describes the public properties for the PsqlTrace object.
 
Table 41
Public Properties of PsqlTrace
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.
Table 42 describes the public methods for PsqlTrace.
 
Table 42
Public Methods of the PsqlTrace Object
Method
Description
DumpFootprints
Displays the footprint of all source files in a data provider.
PsqlTransaction Object
Table 43 describes the public properties of PsqlTransaction.
 
Table 43
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.
Table 44 describes the public methods of the PsqlTransaction object.
 
Table 44
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.