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").

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net