This article shows how we can use PostgreSQL V 3.1.1 with ASP.NET Core with plain SQL queries. Before start anything we need local or remote postgres database server.We've build this example on ASP.Net core RC2.
We need to have npgsql nuget package into our project.
We need to configure asp.net core and Npgsql. This is how your project.json file's dependencies section should look like. You can see that we’ve used V 3.1.1 for npgsql.
"dependencies": {
"Microsoft.NETCore.App": {
"version": "1.0.0-rc2-3002702",
"type": "platform"
},
"Microsoft.AspNetCore.Mvc": "1.0.0-rc2-final",
"Microsoft.AspNetCore.Server.IISIntegration": "1.0.0-rc2-final",
"Microsoft.AspNetCore.Server.Kestrel": "1.0.0-rc2-final",
"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0-rc2-final",
"Microsoft.Extensions.Configuration.FileExtensions": "1.0.0-rc2-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc2-final",
"Microsoft.Extensions.Logging": "1.0.0-rc2-final",
"Microsoft.Extensions.Logging.Console": "1.0.0-rc2-final",
"Microsoft.Extensions.Logging.Debug": "1.0.0-rc2-final",
"Npgsql": "3.1.1"
},
This is the example code which is consuming the Npgsql library and doing the DB communication job for us.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Npgsql;
namespace webapisample
{
public class Postgresql
{
private static Postgresql _Instance;
private static NpgsqlConnection Connection { get; set; }
private Postgresql()
{
Postgresql.Connect();
}
public static Postgresql Instance => _Instance ?? (_Instance = new Postgresql());
private static void Connect()
{
Postgresql.Connection = new NpgsqlConnection();
Postgresql.Connection.ConnectionString = "HOST=localhost;PORT=5432;DATABASE=webapisample;USER ID=your_db_user;PASSWORD=your_db_user_password;TIMEOUT=30";
Postgresql.Connection.Open();
}
public void Disconnect()
{
Postgresql.Connection.Close();
Postgresql.Connection = null;
Postgresql._Instance = null;
}
public NpgsqlDataReader ExecuteReader(string SQL)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = Postgresql.Connection;
cmd.CommandText = SQL;
return cmd.ExecuteReader();
}
}
public object ExecuteScalar(string SQL)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = Postgresql.Connection;
cmd.CommandText = SQL;
return cmd.ExecuteScalar();
}
}
public int ExecuteNonQuery(string SQL)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = Postgresql.Connection;
cmd.CommandText = SQL;
return cmd.ExecuteNonQuery();
}
}
}
}
You can modify the code as per your requirement. I’ve learned from this wonderful example from here. http://senvichet.com/blog/asp-net-core-npgsql-connect-to-postgres-database/
Here is the example, I have used in my API controller.
public IEnumerable Get()
{
List books = new List();
var db = Postgresql.Instance;
string sql = $"SELECT * from book";
using (var reader = db.ExecuteReader(sql))
{
while (reader.Read())
{
books.Add(new Book()
{
Id = Convert.ToInt32(reader["id"]),
Title = (string)reader["title"],
});
}
}
db.Disconnect();
return books;
}
The controller API can be called from browser or fiddler. Here is the API result.
Links