Using the Data Providers
The PSQL data providers provide data access to any .NET-enabled application or application server. The data providers delivers high-performance point-to-point and n-tier access to industry-leading data stores across the Internet and intranets. Because they are optimized for the .NET environment, the data providers allow you to incorporate .NET technology and extend the functionality and performance of your existing system.
See
Advanced Features for information on advanced features such as connection pooling, statement caching, configuring security, PSQL Bulk Load, and diagnostic support.
See
The ADO.NET Data Provider for information about using the ADO.NET data provider in the standard ADO.NET environment.
See
The ADO.NET Entity Framework Data Provider for information about using the data provider with the ADO.NET Entity Framework.
About the Data Providers
The PSQL data providers are compliant with the Microsoft .NET Framework 2.0, 3.0, 3.5, 3.5 SP1, 4.0, 4.5, 4.5.1, 4.5.2, 4.6.1, and 4.6.2. The data providers are built with 100% managed code; they can run and connect to the database entirely within the common language runtime (CLR).
Code that runs in the native operating system, such as client libraries and COM components, is called unmanaged code. You can mix managed and unmanaged code within a single application. However, unmanaged code reaches outside the CLR, which means that it effectively increases complexity, reduces performance, and opens possible security risks.
Using Connection Strings
You can define the behavior of a connection using a connection string or the properties of the PsqlConnection object.
However, values set in the connection string cannot be changed by the connection properties.
The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The following example shows the keywords and values for a simple connection string for the PSQL data provider:
"Server DSN=SERVERDEMO;Host=localhost"
Guidelines
Use the following guidelines when specifying a connection string:
•The spaces in the connection string option names are required.
•All connection string option names are case-insensitive. For example, Password is the same as password. However, the values of options such as User ID and Password may be case-sensitive.
•To include values that contain a semicolon, single quote, or double quotes, enclose the value in double quotes. If the value contains both a semicolon and double quotes, use single quotes to enclose the value.
•You can also use single quotes when the value starts with a double quote. Conversely, double quotes can be used if the value starts with a single quote. If the value contains both single quotes and double quotes, the character used to enclose the value must be doubled every time it occurs within the value.
•To include leading or trailing spaces in the string value, the value must be enclosed in either single quotes or double quotes. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in single or double quotes. However, spaces within a string literal keyword or value are preserved. Single or double quotes can be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless it is the first or last character in the value.
•Special characters can be used in the value of the connection string option. To escape special characters, surround the value in single or double quotes.
•The Equals character (=) can also be repeated within the connection string. For example:
Initialization String=update mytable set col1 == 'foo'"
•If the connection string contains invalid connection string options, the connection attempt returns an error. For example, an error is returned if you specify a value for Load Balancing when Alternate Servers has not been defined.
•If the connection string contains duplicated connection string options, the data provider uses the connection string option that appears last in the connection string. For example, Connection Timeout appears twice in the following connection string, with different values. The data provider uses the second value and waits 35 seconds before terminating an attempted connection:
"Server DSN=SERVERDEMO;Host=localhost;Connection Timeout=15;Min Pool Size=50;Connection Timeout=35"
See Table
27 for a list of the supported connection string options.
Using the PSQL Performance Tuning Wizard
You can use the Performance Wizard to select the optimal connection string options for both the ADO.NET data provider or the ADO.NET Entity Framework data provider.
Stored Procedures
To enable stored procedures in the application, do the following:
•Set the CommandText property in the PsqlCommand object to the stored procedure name.
MyCommand.CommandText = "GetEmpSalary";
•Set the CommandType property in the PsqlCommand object to StoredProcedure.
MyCommand.CommandType = CommandType.StoredProcedure;
•Specify parameter arguments, if needed. The application should add the parameters to the parameter collection of the PsqlCommand object in the order of the arguments to the stored procedure. The application does not need to specify the parameter markers in the CommandText property of the PsqlCommand object.
To retrieve the return value from a stored procedure, the application should add an extra parameter to the parameter collection for the PsqlCommand object. This parameter’s ParameterDirection property should be set to ParameterDirection.ReturnValue. The return value parameter can be anywhere in the parameter collection because it does not correspond to a specific parameter marker in the Text property of the PsqlCommand object.
If the stored procedure does not produce a return value, parameters bound with the ParameterDirection property as ReturnValue are ignored.
If the stored procedure returns a ReturnValue from the database and the application has not bound a parameter for it, the data provider discards the value.
Note for ADO.NET Entity Framework Users: The PsqlConnection object includes properties and methods that provide enhanced statistics functionality. The methods and properties are standard in the ADO.NET data provider but are not available at the ADO.NET Entity Framework layer. Instead, the ADO.NET Entity Framework data provider exposes the same functionality through "pseudo" stored procedures. See
Using Stored Procedures with the ADO.NET Entity Framework for more information.
Using IP Addresses
The data providers support Internet Protocol (IP) addresses in IPv4 and IPv6 formats. If your network supports named servers, the server name specified in the data source can resolve to an IPv4 or an IPv6 address.
The EnableIPv6 connection string option, when set to True, allows a client with IPv6 protocol installed to connect to the server using either an IPv4 address or an IPv6 address. For more information about IPv6 formats, see
IPv6 in
Getting Started with PSQL.
Transaction Support
The data provider uses only 100% managed code to support the transactions, which are implemented entirely within the .NET Framework.
Using Local Transactions
Local transactions use the internal transaction manager of the underlying database.
The application creates a PsqlTransaction object by calling BeginTransaction on the PsqlConnection object. Subsequent operations, such as committing or aborting the transaction, are performed on the PsqlTransaction object.
Thread Support
The PsqlConnection object is thread-safe. Multiple PsqlCommand objects, each accessed on a separate thread, can simultaneously use a single connection.
Accessing other public and data provider-specific objects simultaneously on separate threads is not thread-safe.
Unicode Support
The data provider supports Unicode as specified in the .NET Framework SDK. Effectively, this means that the data provider uses Unicode UTF-16 encoding to represent characters.
The data provider converts UTF-16 characters to the format used by the database, and returns .NET Framework strings to the application. For example, if a PSQL database code page is in extended ASCII format, the data provider uses extended ASCII to represent characters sent to the database. The data provider then converts the extended ASCII characters returned before sending them back to the application.
For more information about the .NET Framework implementation of Unicode and international characters, refer to the Microsoft .NET Framework SDK documentation.
Isolation Levels
PSQL supports the ReadCommitted and Serializable isolation levels. It supports record-level locking. See
Locking and Isolation Levels for details.
SQL Escape Sequences
See
SQL Escape Sequences for .NET for information about the SQL escape sequences supported by the PSQL data provider.
Event Handling
The event handler receives an argument of type PsqlInfoMessageEventArgs, which contains data relevant to an event. See
PsqlInfoMessageEventArgs Object for more information.
This event is defined as:
public event PsqlInfoMessageEventHandler InfoMessage;
Clients that want to process warnings and informational messages sent by the database server should create an PsqlInfoMessageEventHandler delegate to listen to this event.
You can use these events to capture failures that can occur when creating packages, stored procedures, or stored functions, which all create commands. If PSQL encounters errors when compiling a command created by a package, stored procedure, or stored function, the object is created, even though it is not valid. An event will be sent, indicating the failure.
The following code fragment defines a delegate that represents the method that handles the InfoMessage event of a PsqlConnection object:
[Serializable]
public delegate void PsqlInfoMessageEventHandler(
object sender
PsqlInfoMessageEventArgs e
);
where sender is the object that generated the event and e is an PsqlInfoMessageEventArgs object that describes the warning. For more information on Events and Delegates, refer to the .NET Framework SDK documentation.
Error Handling
The PsqlError object collects information relevant to errors and warnings generated by the PSQL server. See
PsqlError Object for more information.
The PsqlException object is created and thrown when the PSQL server returns an error. Exceptions generated by the data provider are returned as standard run time exceptions. See
PsqlException Object for more information.
Using .NET Objects
The data provider supports the .NET public objects, exposing them as sealed objects.
See
.NET Objects Supported for more information.
Developing Applications for .NET
Developers of data consumer applications must be familiar with the Microsoft .NET specification and object-oriented programming techniques.
Microsoft also provides extensive information about ADO.NET on its World Wide Web site, including the following articles: