Opening a database connection v8.0.2.1

An EDBConnection object is responsible for handling the communication between an instance of EDB Postgres Advanced Server and a .NET application. Before you can access data stored in an EDB Postgres Advanced Server database, you must create and open an EDBConnection object.

Creating an EDBConnection object

You can open a connection using one of the following approaches. In either case, you must import the namespace EnterpriseDB.EDBClient.

Connection with a data source

1. Create an instance of the EDBDataSource object using a connection string as a parameter to the create method of the EDBDataSource class.

2. To open a connection, call the OpenConnection method of the EDBDataSource object.

This example shows how to open a connection using a data source:

await using var dataSource = EDBDataSource.Create(ConnectionString);
var connection = dataSource.OpenConnection();

Connection without a data source

1. Create an instance of the EDBConnection object using a connection string as a parameter to the constructor of the EDBConnection class.

2. Call the Open method of the EDBConnection object to open the connection.

This example shows how to open a connection without a data source:

EDBConnection conn = new EDBConnection(ConnectionString);
conn.Open();

Connection string parameters

A valid connection string specifies location and authentication information for an EDB Postgres Advanced Server instance. You must provide the connection string before opening the connection. A connection string must contain:

• The name or IP address of the server
• The name of the EDB Postgres Advanced Server database
• The name of an EDB Postgres Advanced Server user
• The password associated with that user

You can include the following parameters in the connection string:

CommandTimeout

CommandTimeout specifies the length of time (in seconds) to wait for a command to finish executing before throwing an exception. The default value is 20.

ConnectionLifeTime

Use ConnectionLifeTime to specify the length of time (in seconds) to wait before closing unused connections in the pool. The default value is 15.

Database

Use the Database parameter to specify the name of the database for the application to connect to. The default is the name of the connecting user.

Encoding

The Encoding parameter is obsolete. The parameter always returns the string unicode and silently ignores attempts to set it.

Integrated Security

Specify a value of true to use Windows Integrated Security. By default, Integrated Security is set to false, and Windows Integrated Security is disabled.

Load Role Based Tables

Use Load Role Based Tables to load table OIDs based on role. This change affects only the loading of table type OID and not the composite type. Setting this parameter to true triggers the new functionality. The default value is false.

MaxPoolSize

MaxPoolSize instructs EDBConnection to dispose of pooled connections when the pool exceeds the specified number of connections. The default value is 20.

MinPoolSize

MinPoolSize instructs EDBConnection to preallocate the specified number of connections with the server. The default value is 1.

Password

When using clear text authentication, specify the password to use to establish a connection with the server.

Pooling

Specify a value of false to disable connection pooling. By default, Pooling is set to true to enable connection pooling.

No Reset On Close

When Pooling is enabled and the connection is closed, reopened, and the underlying connection is reused, then some operations are executed to discard the previous connection resources. You can override this behavior by enabling No Reset On Close.

Port

The Port parameter specifies the port for the application to connect to.

Protocol

The specific protocol version to use (instead of automatic). Specify an integer value of 2 or 3.

SearchPath

Use the SearchPath parameter to change the search path to named and public schemas.

Server

The name or IP address of the EDB Postgres Advanced Server host.

SSL

Specify a value of true to attempt a secure connection. By default, SSL is set to false.

sslmode

Use sslmode to specify an SSL connection control preference. sslmode can be:

• prefer — Use SSL if possible.

• require — Throw an exception if an SSL connection can't be established.

• allow — Connect without SSL. This parameter isn't supported.

• disable — Don't attempt an SSL connection. This is the default behavior.

SyncNotification

Use the SyncNotification parameter to specify for EDBDataprovider to use synchronous notifications. The default value is false.

Timeout

Timeout specifies the length of time (in seconds) to wait for an open connection. The default value is 15.

User Id

The User Id parameter specifies the user name to use for the connection.

Example: Opening a database connection using ASP.NET

This example shows how to open a connection to an instance of EDB Postgres Advanced Server and then close the connection. The connection is established using the credentials specified in the DB_CONN_STRING configuration parameter. See Using the .Net Connector for an introduction to connection information. Also see Connection string parameters for connection parameters.

<% @ Page Language="C#" %>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
	var strConnectionString = ConfigurationManager.AppSettings["DB_CONN_STRING"];
	try
	{
		await using var dataSource = EDBDataSource.Create(strConnectionString);
		var conn = dataSource.OpenConnection();
		Response.Write("Connection opened successfully");
		conn.Close();
	}
	catch(EDBException exp)
	{
		Response.Write(exp.ToString());
	}
}
</script>

If the connection is successful, a message appears indicating that the connection opened successfully.

Example: Opening a database connection from a console application

This example opens a connection with an EDB Postgres Advanced Server database using a console-based application.

Before writing the code for the console application, create an app.config file that stores the connection string to the database. Using a configuration file makes it convenient to update the connection string if the information changes.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
      <add key="DB_CONN_STRING" value = "Server=127.0.0.1;Port=5444;
      User Id=enterprisedb;Password=enterprisedb;Database=edb"/>
  </appSettings>
</configuration>

Enter the following code sample into a file:

using System;
using System.Data;
using EnterpriseDB.EDBClient;
using System.Configuration;
namespace EnterpriseDB
{
	class EDB
	{
		static void Main(string[] args)
		{
			var strConnectionString = ConfigurationManager.AppSettings["DB_CONN_STRING"];
			try
			{
				await using var dataSource = EDBDataSource.Create(strConnectionString);
				var conn = dataSource.OpenConnection();
				Console.WriteLine("Connection Opened Successfully");
				conn.Close();
			}
			catch(Exception exp)
			{
			throw new Exception(exp.ToString());
			}
		}
	}
}

Save the file as EDBConnection-Sample.cs and compile it with the following command:

csc /r:EnterpriseDB.EDBClient.dll /out:Console.exe EDBConnection-Sample.cs`

Compiling the sample generates a Console.exe file. You can execute the sample code by entering Console.exe. When executed, the console verifies that it opened successfully.

Example: Opening a database connection from a Windows form application

This example opens a database connection using a .NET WinForm application. To use the example, save the following code as WinForm-Example.cs in a directory that contains the library files:

using System;
using System.Windows.Forms;
using System.Drawing;
using EnterpriseDB.EDBClient;
namespace EDBTestClient
{
	class Win_Conn
	{
		static void Main(string[] args)
		{
			Form frmMain = new Form();
			Button btnConn = new Button();
			btnConn.Location = new System.Drawing.Point(104, 64);
			btnConn.Name = "btnConn";
			btnConn.Text = "Open Connection";
			btnConn.Click += new System.EventHandler(btnConn_Click);
			frmMain.Controls.Add(btnConn);
			frmMain.Text = "EnterpriseDB";
			Application.Run(frmMain);
		}
		private static void btnConn_Click(object sender, System.EventArgs e)
		{
			try
			{
				var strConnectionString = "Server=localhost;port=5444;username=edb;password=edb;database=edb";
				await using var dataSource = EDBDataSource.Create(strConnectionString);
				var conn = dataSource.OpenConnection();
				MessageBox.Show("Connection Opened Successfully");
				conn.Close();
          }
          catch(EDBException exp)
          {
              MessageBox.Show(exp.ToString());
          }
		}
	}
}

Change the database connection string to point to the database that you want to connect to. Then compile the file with the following command:

csc /r:EnterpriseDB.EDBClient.dll /out:WinForm.exe WinForm-Example.cs

This command generates a WinForm.exe file in the same folder that the executable was compiled under. Invoking the executable displays a message that the connection was successful.