I needed to connect to a PostgreSQL database from C# code.
I am using
- Visual Studio 2015 and
- Npgsql (http://www.npgsql.org) library from Nuget
In your project opened in Visual Studio, using “Manage Nuget Packages” add Npgsql to your project.
Once added, here is the code to run a query, get the results and read them using NpgsqlDataReader.
using Npgsql;
using System.Collections.Generic;
using System.Configuration;
using System.Web.Http;
namespace WebApi.Controllers
{
public class MyController : ApiController
{
// GET api/items
public IEnumerable<Item> Get()
{
var items = new List<Item>();
var connection = new NpgsqlConnection(Server=10.xxx.xxx.xxx;Port=5432;
User Id=user;Password=pwd;Database=dbname);
using (connection)
{
connection.Open();
const string query = "select name from items;";
Npgsql.NpgsqlCommand command = new NpgsqlCommand(query, connection);
Npgsql.NpgsqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
items.Add(new Item() { name = dataReader[0].ToString() });
}
dataReader.Close();
}
connection.Close();
return items;
}
}
}
To pass a parameter in the query and get results, here is the code:
using (NpgsqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select distinct count(serial_number) from data where
name=@value1";
cmd.Parameters.AddWithValue("@value1", paramvalue);
var result = cmd.ExecuteScalar().ToString();
if (!string.IsNullOrEmpty(result)) {
value = double.Parse(result);
}
}
Instead of having the database connection string in the code,
it could be added to appSettings in the config file and access it
using ConfigurationManager in code:
<appSettings>
<add key="DbConnectionString" value="Server=10.xxx.xxx.xxx;Port=5432;
User Id=user;Password=pwd;Database=dbname" />
</appSettings>
var connection = new NpgsqlConnection(ConfigurationManager.AppSettings.Get("DbConnectionString"));
Code Snippets 