Library > Products > Pervasive.SQL > Pervasive.SQL 9.1 > Data Provider for .NET Guide > Using the Pervasive.SQL Data Provider > Connecting with a Data Provider

PreviousData Provider for .NET Guide (9.1 revision 1) Next

Connecting with a Data Provider

Show this topic in Library frames

The Pervasive.SQL data providers attaches a data provider-specific prefix to the public .NET objects. For example, the Connection object for the Pervasive.SQL data provider is PsqlConnection.

You connect to a database through the PsqlConnection object, using the method PsqlConnection.Open().

The following example uses the PsqlConnection object to connect to Pervasive.SQL while passing the user name and password: 

PsqlConnection mycon = new 
PsqlConnection("ServerDSN=Demodata; 
	UID=test; 
	PWD=test; 
	ServerName=localhost;"); 
mycon.Open();

Connection String Options for the Pervasive.SQL Provider

Table 2-1 lists the connection options supported by the Pervasive.SQL data provider, and describes each option. The connection strings have the form: 

"option=value;option=value;option=value"

For example, the following connection string contains the minimum connection options needed to make the initial connection to the Pervasive.SQL server SERVERDEMO. 

"ServerDSN=SERVERDEMO;UID=test;PWD=test;ServerName=localhost;
"

NOTES:

Using Connection Pooling

The Pervasive.SQL data provider automatically enables connection pooling for your .NET client application. Connection pooling allows you to reuse connections rather than create a new one every time the data provider needs to establish a connection to the underlying database.

You can control connection pooling behavior by using connection string options (see Modifying the Connection Object ). For example, you can define the number of connection pools, the number of connections in a pool, and the number of seconds before a connection is discarded.

Creating a Connection Pool

Each connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique, case-sensitive, connection string is closed and needs to be added to a connection pool. The connection pool remains active as long any connections remain open, either in the connection pool, or held explicitly by the application that has references to a connection object that holds an open backend connection.

NOTE: If a new connection is opened and the connection string does not match an existing pool, a new pool must be created when the connection is closed. By using the same connection string, you can enhance the performance and scalability of your application.

In the following C# code fragments, three new PsqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ only by the value assigned for User ID and Password. 

PsqlConnection conn1 = new PsqlConnection(); 
conn.ConnectionString = "ServerDSN=DEMODATA; 
		UID=test; 
		PWD=test; 
		Server=localhost;"
conn1.Open(); 
// Pool A is created.  
PsqlConnection conn2 = new PsqlConnection(); 
conn2.ConnectionString = "ServerDSN=DEMODATA2; 
		UID=lucy;
		PWD=quake; 
		Server=localhost;"
conn2.Open(); 
// Pool B is created because the connection strings 
differ. 
PsqlConnection conn3 = new PsqlConnection(); 
conn3.ConnectionString = "ServerDSN=DEMODATA; 
		UID=test;
		PWD=test; 
		Server=localhost;"
conn3.Open(); 
// conn3 goes into Pool A with conn1.

Adding Connections

A connection is eligible to be added to a connection pool when an application explicitly calls the Close method for a Connection object. If no corresponding pool exists for the connection string, a pool is created and populated with connections up to the minimum pool size. Additional connections can be added until the maximum pool size is reached.

Only the resources required to maintain the connection to the backend database, for example, the IP socket and connection string information are added to the pool. The Connection object itself is not added.

Note
Close the connection when you are finished using it, using the Close() method of the PsqlConnection object. If you do not explicitly close the connection, it will not be added or returned to the pool.

Removing Connections

A connection is removed from a connection pool when it either exceeds its lifetime as determined by the Connection Lifetime connection string option, or when a new connection that has a matching connection string is initiated by the application (PsqlConnection.Open() is called). The connection must currently be unused, and have a valid link to the server.

Before returning a connection from the connection pool to an application, the pool manager checks to see if the connection has been closed at the server. If the connection is no longer valid, the pool manager discards it, and returns another connection from the pool, if one is available and valid.

NOTE: By default, if discarding an invalid connection causes the number of connections to drop below the number specified in the Min Pool Size attribute, a new connection will not be created until an application needs one.

Modifying the Connection Object

You can modify a connection by specifying connection options. See PsqlConnection Object for information about specifying options through the data provider's PsqlConnection object.

Chapter contents
Publication contents

Prev topic: About the Data Provider
Next topic: Understanding Connection Failover and Client Load Balancing