Creating an Npgsql-enabled VB Project

Client 1Connecting to the Server

Connecting a VB.NET application to a PostgreSQL database is easy. Simply add an NpgsqlConnection object to your application and set the ConnectionString property. You can open the connection programmatically, or let another Npgsql object manage the connection as needed.

To create an NpgsqlConnection object, open the Data tab (in the Toolbox) and double-click the NpgsqlConnection toolVisual Studio adds an NpgsqlConnection object to the form that you're editing. If you see a message that states "The .NET assembly 'Npgsql' could not be found.", you forgot to add an Npgsql reference to your project. See the previous section ("Creating an Npgsql-enabled VB.NET Project") to learn how to fix the problem.

To define the ConnectionString property, click the NpgsqlConnection object (when it appears below the form) and then click the ConnectionString property in the Properties window (see Figure 18.1).

Figure 18.1. VB Properties window.

There are two ways to define the ConnectionString. First, you can use a helper dialog (see Figure 18.2) by clicking on the ". . ." button next to the ConnectionString property. After you've filled in the server name, username, and password, you can select a database from the drop-down list box (the NpgsqlConnection object connects to the server that you've specified and retrieves a list of database names for you to choose from). When you click Ok, the dialog converts the values that you entered into a connection string and copies that string into the ConnectionString property.

Figure 18.2. PostgreSQL Data Connection Properties.

The Data Connection Properties dialog is very pickyit does its best to ensure that you create a connection string that actually works. The dialog connects to the server that you specify, verifies the user ID and password, and won't let you connect to a database that doesn't actually exist. That means that you can't use the Data Connection Properties dialog unless your PostgreSQL server is running (and accessible), and you've already created the user account and database that you want to use.

If you don't want to use the Data Connection Properties dialog, or you need to create a connection string at run-time, you can build one by hand. An Npgsql connection string is semicolon separated list of property=value pairs. You can include any of the keywords shown in Table 18.1. You must specify a SERVER and USER ID. (Npgsql does not search for any environment variables to satisfy missing connection properties.)

Table 18.1. Npgsql Connection String Keywords

Keyword	Synonyms	Description
SERVER	HOST	Specifies the hostname or IP address of the PostgreSQL server.
PORT		Specifies the TCP port number (typically 5432) where the postmaster is listening for client requests.
PROTOCOL		Determines which version of the client/server protocol the connection will use when talking to the server. This property is automatically negotiated at the time the NpgsqlConnection object connects to the server.
DATABASE	DB	Specifies the name of the PostgreSQL database that you want to connect to.
USER ID	USER, USERID, USER NAME, USERNAME, UID	Specifies the PostgreSQL user account used by the connection.
PASSWORD	PSW, PWD	Specifies the password provided to the PostgreSQL server (if required by the authentication used by the server). At the time we are writing this (Npgsql version 0.7), Npgsql supports clear-text password authentication and MD5-encrpyted authentication.
SSL		If TRue, the NpgsqlConnection object tries to create an SSL-secured connection to the server. If the server does not support SSL connections, Npgsql will try to create an insecure connection.
ENCODING		Determines the encoding (character set) reported to the server. This property defaults to SQL_ASCII.
TIMEOUT		Specifies the number of seconds to wait for the connection to complete before throwing an exception.

If you are creating an NpgsqlConnection object at run-time (as opposed to design-time), you can set ConnectionString property by hand or you can pass the connection string to the NpgsqlConnection constructor:

...
Dim conn_1 AS Npgsql.NpgsqlConnection
Dim conn_2 AS Npgsql.NpgsqlConnection

conn_1 = New Npgsql.NpgsqlConnection
conn_1.ConnectionString = "SERVER=cows;USER=bruce"

conn_2 = New Npgsql.NpgsqlConnection("SERVER=cows;USER=bruce" )
...

When you create an NpgsqlConnection, the object doesn't actually connect to the database until you call the Open() method. In some cases, some other object will invoke Open() for you. For example, when you use a DataAdapter object to fill a DataSet, the DataAdapter will Open() a connection on your behalf. It's not easy to find out which objects automatically Open() a connectionyou have to slog through the .NET documentation to know for sure. It's usually safer to Open() the connection yourself.

The Open() method throws an exception if anything goes wrong, so you should only call Open() inside of a try/catch block (if you don't, your program will die a fiery death should something go awry).

To wrap up your first Npgsql client, double-click on the background of the form you're designing and add the code shown in Listing 18.1 to the Form1_Load() method.

Listing 18.1. The client1 Form1_Load() Subroutine

1 Private Sub Form1_Load(...) Handles MyBase.Load
2 Try
3 NpgsqlConnection1.Open()
4 MessageBox.Show(NpgsqlConnection1.ConnectionString, "Connected!")
5 Catch ex As Exception
6 MessageBox.Show(ex.Message, "Can't connect")
7 End Try
8 Application.Exit()
9 End Sub

When you build and run this program, you'll see a MessageBox appear. If the NpgsqlConnection object successfully connects to the server you specify, the code at line 4 displays a MessageBox (titled "Connected!") that displays the ConnectionString property. If the connection attempt fails, the code at line 6 extracts an error message from the Exception object thrown by Open() and displays that message in a MessageBox (titled "Can't Connect").