C#.NET and Greenplum

This article is about connecting to greenplum (GP) database using C#.NET. Greenplum DB is based on the open source database PostgreSQL.

Greenplum database is optimized for faster searches. For searching data in tables having millions of rows, Greenplum is really fast.

Given below is a C# example for opening a connection to GP database and executing a search query.

---

1) The first step is to download the dll which connects to GP. It is called Npgsql.dll. It can be downloaded from http://npgsql.projects.postgresql.org/docs/manual/UserManual.html

2) Create a C#.NET console application using Visual Studio.


3) Create a new file for GP connection. Lets name it GPDData.cs

4) Copy the dll to a local folder in the project and add a reference to the dll from project. Or you can register it in Global Assembly Cache using "gacutil".

5) Include the namespace

using Npgsql;

6) In the GPData class create method to execute query in greenplum as given below.

public DataSet FillDataSet(string strCommand,string strConnection)
{
NpgsqlConnection oConnection = new NpgsqlConnection(strConnection);
DataSet ds = new DataSet();

NpgsqlCommand oCommand = new NpgsqlCommand(strCommand, oConnection);
NpgsqlDataAdapter oAdp = new NpgsqlDataAdapter();
oCommand.CommandTimeout = 100;
oAdp.SelectCommand = oCommand;

oConnection.Open();
oAdp.Fill(ds);
CloseConnection();

return ds;
}

public void CloseConnection()
{
if (oConnection.State == System.Data.ConnectionState.Open)
{
oConnection.Close();
}
}

7) Now you can execute query by calling the method as given below.

GPData oData = new GPData();
DataSet ds = oData.FillDataSet([command],[connectionstring]);