Introduction
PostgreSQL, or simply Postgres, is an object-relational database management system (ORDBMS). It is free and open source, and that is good. In this article I aim to give a step by step account on how to integrate Postgres with Visual Studio (the version I use here is Express 2008). The language is C++/CLI
Installing Postgres
First we need to install Postgres. This is a very simple procedure: Find the latest executable at the Postgres website. Click the One Click Installer link and select the link suitable to your platform. As I'm working from a windows 7-64 bit machine, my file was named postgresql-9.0.3-2-windows_x64.exe.
Run the executable. Note the password and port number. Just select <default locale> when prompted for your locale. If you're running ZoneAlarm firewall you will be prompted to give permission several times. Make sure you tick the box to say you will always allow these connections. Next you will be prompted to run Stackbuilder to allow updates etc. Allow this and tick all database driver boxes (we really only are interested in the NpgSql driver but you may as well download the other 2 drivers for future applications). Stackbuilder will proceed downloading each driver in turn (about 7 MB each) and consequently prompt you to install them - which you will agree to.
You may want to view the PostgreSQL 9.0.3 documentation in your All Programs menu (on my machine it is located at C:/Program Files/PostgreSQL/9.0/doc/postgresql/html/index.html).
Checking the PostgreSQL Server
To check whether everything is working ok, we will try to create a database, called mydb. Open up your command prompt and type
"C:\Program Files\PostgreSQL\9.0\bin\createdb" mydbYour path may vary of course, but the result of this command should be a prompt for your password. Enter your password (the one you gave at installation) and you possibly will get an error stating that the authentication failed for user 'User'. Fret not, the solution is simple:
- Start the PgAdmin application which was installed with Postgress. It should be available in the Programs menu
- Highlight the server (displayed in red)
- Connect PgAdmin to the server using Tools→Connect. Enter same password when prompted.
- Click Login Roles and you see that one user is available, called postgres. This is the user that was allocated the password. For the purposes of Postgres, a role is the same as a user
- Right click Login Roles in the object browser and select New Login Role
- Create a user called User and select any password (I used the same password).
- Right click User in the object browser and select Properties. Select Role Priveleges and tick all boxes.
Now try creating a database again using the command line, using the password you gave for User. This time no errors should appear and the command line prompt appears again. Click the Refresh icon in pgAdmin and the database mydb should appear in the Databases folder (don't worry about the red crosses).
You may want to execute more commands as discussed in the manual, but for our purposes, the Postgres server is functional and we are now in a position to interact with Postgres using Visual Studio instead of using the command prompt.
Integrating Postgres with Visual Studio
Visual Studio uses a provider called Npgsql. We installed the driver for this provider. Npsql is .Net provider that allows a .Net client application to send and receive data with a PostgreSQL server. The Npgsql documentation is available here.
Use the following to connect Postgres to Visual Studio:
- Create a basic Form in Visual Studio and add a RichTextBox and a Button.
- Download the Npgsql2.0.11.91-bin-ms.net3.5sp1.zip file (or whatever version is the latest) from the pgFoundry download page, unzip it and copy its top files to the Visual Studio Project folder where the solution file resides in.
- In Visual Studio go to Project→Properties→Common Properties and add new references (using the Browse tab) to the dll files you just added to project: Mono.Security, Npgsql, policy.2.0.Npsql. Also add a .Net reference to System.Data if it is not listed.
Visual Studio should now be ready to talk to Progres. The code excerpts in the following chapter should serve as an example on how to do this:
Code excerpts
Following code is complete except for the automatically generated designer code
namespace Postgres {
using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System::Data;
using namespace System::Drawing;
using namespace Npgsql;
public ref class Form1 : public System::Windows::Forms::Form
{
private:
Npgsql::NpgsqlConnection^ conn; // postgres database connector
public:
Form1(void)
{
InitializeComponent();
conn = gcnew NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=yourPassword;Database=mydb;");
}
protected:
~Form1()
{
if (components){
delete components;
}
}
public: void Createtables()
{
conn->Open();
String^ query = "DROP TABLE IF EXISTS weather; "
+ "CREATE TABLE weather ("
+ "city varchar(80), "
+ "temp_lo int, "
+ "temp_hi int, "
+ "prcp real, "
+ "date date); "
+ "DROP TABLE IF EXISTS cities;"
+ "CREATE TABLE cities ("
+ "name varchar(80), "
+ "location point); ";
Npgsql::NpgsqlCommand ^ command = gcnew NpgsqlCommand(query, conn);
Int32 rowsaffected;
try{
rowsaffected = command->ExecuteNonQuery();
this->richTextBox1->AppendText("RESULT CreateTrables():\nsuccessfully created tables\n\n");
}
finally{
conn->Close();
}
}
public: void InsertData()
{
conn->Open();
String^ query = "INSERT INTO cities VALUES ('Hobart', '(-120.0, 533.0)'); "
+ "INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); "
+ "INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); "
+ "INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); "
+ "INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37); ";
Npgsql::NpgsqlCommand ^ command = gcnew NpgsqlCommand(query, conn);
Int32 rowsaffected;
try{
rowsaffected = command->ExecuteNonQuery();
this->richTextBox1->AppendText("RESULT InsertData():\nadded " + rowsaffected + " lines to tables\n\n");
}
finally{
conn->Close();
}
}
public: void QuerySingleData()
{
conn->Open();
String ^ query = "select version()";
Npgsql::NpgsqlCommand ^ command = gcnew NpgsqlCommand(query, conn);
String ^ serverVersion;
try{
serverVersion = (String^)command->ExecuteScalar();
this->richTextBox1->AppendText("RESULT QuerySingleData():\n" + serverVersion + "\n\n");
}
finally{
conn->Close();
}
}
public: void SelectData()
{
conn->Open();
String ^ query = "select city, temp_lo, date from weather";
Npgsql::NpgsqlCommand ^ command = gcnew NpgsqlCommand(query, conn);
this->richTextBox1->AppendText("RESULT SelectData():\n");
try{
NpgsqlDataReader ^ dr = command->ExecuteReader();
while(dr->Read()){
richTextBox1->AppendText((String^)dr[0] + "\t");
richTextBox1->AppendText(Convert::ToString(dr[1]) + "\t");
richTextBox1->AppendText(((DateTime^)dr[2])->ToString("dd MMM yyyy") + "\n");
}
richTextBox1->AppendText("\n");
}
finally{
conn->Close();
}
}
// Parameters let you dynamically insert values into SQL queries at run-time.
public: void DynamicInsert()
{
conn->Open();
String ^ query = "select temp_lo, temp_hi from weather where temp_lo = :value1";
// declare parameter in query string
Npgsql::NpgsqlCommand ^ command = gcnew NpgsqlCommand(query, conn);
// add parameter to the parameter collection of the command specifying its type
command->Parameters->Add(gcnew NpgsqlParameter("value1", NpgsqlTypes::NpgsqlDbType::Integer));
// add a value to it
command->Parameters[0]->Value = 37; // must exist in the database
// execute the command as usual
try{
NpgsqlDataReader ^ dr = command->ExecuteReader();
richTextBox1->AppendText("RESULT DynamicInsert():\n");
while(dr->Read()){
for(int i = 0; i< dr->FieldCount; i++){
richTextBox1->AppendText(Convert::ToString(dr[i]));
richTextBox1->AppendText("\t");
}
richTextBox1->AppendText("\n");
}
}
finally{
conn->Close();
}
}
private: System::Void button1_Click(System::Object^ sender, System::EventArgs^ e) {
try{
Createtables(); // uses Parameters
InsertData();
QuerySingleData();
SelectData();
DynamicInsert();
}
catch(Exception ^e){
MessageBox::Show(e->Message, "Warning", MessageBoxButtons::OK, MessageBoxIcon::Warning);
}
}
};
}
Additional Info
You can check your tables in pgAdmin (assuming you are connected) by opening the following folders in the object browser:
Databases → mydb → schemas → public → TablesThen right clicking on a table and going
View Data → View All Rows
To Deploy an application using Postgres, read this article. In short:
- Use the Postgres windows installer to install 8.2.7 or latest version
- Import all the application's databases.
- Using the NSIS Installer Generation app:
- Include all files under c:\program files\postgresql... in my project;
- Use NSIS utilities to install the PostgreSQL service in a custom manner.
I do this on a non-standard port so that if the user already has PG, it does not clobber the existing install.
Also, check out: