If you need to choose which data provider for PostgreSQL to use with dotConnect Universal, we recommend using dotConnect for PostgreSQL because of its obvious advantages. This article provides a brief overview of dotConnect for PostgreSQL, describes some useful features and helps you get started quickly.
dotConnect for PostgreSQL is a full-featured 100% managed ADO.NET data provider for the .NET Framework and the .NET Compact Framework. dotConnect for PostgreSQL is acknowledged as the leading ADO.NET data provider for PostgreSQL, and it has many advantages over any rival product. The main features of dotConnect for PostgreSQL are:
The full list of dotConnect for PostgreSQL features can be found on the product page.
The Professional Edition of dotConnect Universal includes dotConnect for PostgreSQL as a bundled provider. The Trial Edition includes trial dotConnect for PostgreSQL Data Provider as well. For Standard Edition of dotConnect Universal, the dotConnect for PostgreSQL Data Provider should be purchased and installed separately.
dotConnect Universal is compatible with dotConnect for PostgreSQL version 4.00 and above.
To deploy applications written with dotConnect Universal, copy the run-time assemblies Devart.Data.Universal.dll and Devart.Data.Universal.PostgreSql.dll to target machine. If you are using the standalone (not bundled) dotConnect for PostgreSQL Data Provider, you will also have to deploy its Devart.Data.PostgreSql.dll assembly.
These assemblies can be registered at the Global Assembly Cache (GAC) for the appropriate .NET Framework or placed in the folder of your application.
For web projects, you may also need to deploy the Devart.Data.Universal.Web.dll, Devart.Data.PostgreSql.Web.dll and App_Licenses.dll assemblies. The *.Web.dll assemblies are required for using the ASP.NET Provider Model and the App_Licenses.dll assembly is a part of licensing mechanism.
Web projects are usually run as partially trusted code. dotConnect Universal and dotConnect for PostgreSQL require medium trust plus ReflectionPermission and SocketPermission.
Suppose you want to connect to a PostgreSQL server running on the host server, at the port 5432, with the user name postgres and the password mypassword. Suppose also that you would like to work within schema Public and use postgres as the default database. In this case your connection string would look like this:
Provider=PostgreSQL;host=server;port=5432;user=postgres;password=mypassword;initial schema=Public;database=postgres
The initial schema parameter is Public by default; the port parameter defaults to 5432 as well. If the database parameter is not specified, it is assumed that name of the database is same as user name. So basically you need only host name, user name and password:
Provider=PostgreSQL;host=server;user=postgres;password=mypassword
The following table enumerates most important connection string parameters.
Name | Description |
---|---|
Connection Lifetime | When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0. |
Connect Timeout -or- Connection Timeout | The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15. |
Data Source -or- Host -or- Server | The name or IP address of host of PostgreSQL database to which to connect. |
Database | The name of the database. |
Initial Schema | The name of the schema to be used once a connection is opened. You can change it later if you need. By default it is schema 'Public'. |
Max Pool Size | The maximum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 100. |
Min Pool Size | The minimum number of connections allowed in the pool. The default value is 0. |
Password | The password for the PostgreSQL login account. |
Pooling | If true, by default, the PgSqlConnection object is drawn from the appropriate pool or is created and added to the appropriate pool. |
Port | The port of PostgreSQL database to which to connect. The default value is 5432. |
Protocol | The Frontend/Backend Protocol version. Available values are 2 and 3. Set the parameter to 2 for the protocol version 2.0 or to 3 for protocol version 3.0. Can be applied only for PostgreSQL server versions 7.4 or higher, for PostgreSQL earlier versions this parameter is ignored. To execute several statements in the same query set procol to 2 version. The default value is 3. |
SSH Authentication Type | Client's SSH authentication methods. |
SSH Cipher List | The list of ciphers that client agrees to use, separated by colons. |
SSH Host | The name or IP address of SSH server. |
SSH Host Key | The location of the public key on the client side to verify the server host key during establishing connection. OpenSSH or IETF SECSH key files are supported. You may use such utilities as 'ssh-keygen.exe' or 'puttygen.exe' for generation of the corresponding public/private key pair. |
SSH Passphrase | The client key passphrase. |
SSH Password | The user password on SSH server. |
SSH Port | The number of port on SSH server to connect. |
SSH Private Key | The location of the client private key. |
SSH Strict Host Key Check | Indicates whether the host key is verified during establishing connection. |
SSH User | The user id on SSH server. |
SSH Private Key | Location of the private key, which is used by the client to sign data and by server to authenticate the user. |
SSL CA Cert | The location of authority certificate. |
SSL Cert | The location of client certificate. |
SSL Cipher List | The list of ciphers that client agrees to use. |
SSL Key | The location of user's private key. |
SSL TLS Protocol | The preferred TLS protocol version, reported to the server when establishing a secure connection. |
SSLMode | SSL connection priority. May be Disable, Allow, Prefer, and Require. The default value is Disable, which means that only an unencrypted SSL connection will be attempted. |
Unicode | If true, by default, sets client charset to UTF8 and converts client data according to this charset. |
User ID -or- User | The PostgreSQL login account. |
To get up and running quickly, you have to perform the following steps:
That's all you need to start executing queries on the server. Below is a code fragment that illustrates steps 2-5. It also shows how to perform INSERT and SELECT operations.
dotConnect Universal supports handling PostgreSQL BLOBs. For detailed information on how to work with BLOB objects, refer to the Working with BLOB Data article.
dotConnect Universal supports using the UniDecimal structure with PostgreSQL. Use the reference to find out how to manage big numbers on client side.
It is also possible to take advantage of special network capabilities of dotConnect for PostgreSQL by setting up the connection string parameters. This way you can enable:
dotConnect for PostgreSQL supports all features of UniSQL, except for the DATEDIFF unified function name. Use PostgreSQL and PostgreSQLDirect predefined macros to build conditional UniSQL statements. For more information refer to the UniSQL topic.
The following table shows how the UniDbType enumeration maps to the PostgreSQL database type.
UniDbType | PostgreSQL Type |
---|---|
BigInt | BIGINT |
Binary | BYTEA |
Bit | BIT |
Blob | BYTEA |
Boolean | BOOLEAN |
Byte | SMALLINT |
Char | CHAR |
Clob | TEXT |
Currency | CURRENCY |
Date | DATE |
DateTime | TIMESTAMP |
Decimal | NUMERIC |
Double | DOUBLE |
Guid | VARCHAR |
Int | INTEGER |
IntervalDS | INTERVAL |
IntervalYM | INTERVAL |
NChar | CHAR |
NClob | TEXT |
NVarChar | VARCHAR |
Single | REAL |
SmallInt | SMALLINT |
TinyInt | SMALLINT |
Time | TIME |
TimeStamp | TIMESTAMP |
VarChar | VARCHAR |
Xml | VARCHAR |
For more information on dotConnect for PostgreSQL Data Provider please visit the following locations:
Using dotConnect Universal with SQL Client | Using dotConnect Universal with dotConnect for Oracle | Using dotConnect Universal with dotConnect for MySQL | Using dotConnect Universal with dotConnect for SQLite