5 Schritte um ASP.net Core mit Postgresql zu verwenden
In dem Blogpost Microsoft.NET - Schneller an der Lösung dank Open Source habe ich dir gezeigt wie du - unter anderem - von der Offenlegung (Open Source) von dem ASP.net MVC 6.0 Quellcode profitieren kannst. Wie erwähnt sind bereits etliche Teile aus dem Microsoft .NET Umfeld Open Source verfügbar. Eine verbreitete Komponente wo das (noch?) nicht zutrifft ist der Microsoft SQL Server. Gerade für die Realisierung von Webapplikationen mit Microsoft ASP.net wird jedoch meistens eine Datenbank im Hintergrund benötigt. Ist in dieser Kombination nun "Ende Gelände" für Open Source? Natürlich nicht! Ein Weg besteht darin, dass man den Microsoft SQL Server mit einer alternativen Datenbanktechnologie ersetzt. Der Eintrag von The Windows Club gibt dir eine kleine und sicherlich nicht abschliessende Übersicht über verfügbare Alternativen (siehe http://www.thewindowsclub.com/open-source-database).
Seit geraumer Zeit setze ich mich mit Postgresql auseinander. Dies natürlich auch in Kombination mit ASP.net. Die Verwendung von Postgresql ist dabei denkbar einfach und - sofern man nicht irgendwelche Spezialfunktionen von Microsoft SQL Server benötigt - eine gute Alternative. Gerne zeige ich dir in diesem Blogpost wie du vorgehen musst um ein neues ASP.net Core Projekt mit Postgresql von Grund auf realisieren musst.
Nachfolgende Schritte sind notwendig
- Setup von Postgresql auf Windows
- Erstellen einer neuen Postgresql Datenbank
- Neues ASP.net Core Web Application Projekt erstellen
- Realisieren des Zugriffs auf die Postgresql Datenbank
- Implementation des ASP.net Controllers
Wichtig: Du findest den ganzen Source Code zu diesem Repository auf Github: https://github.com/retschgi/PostgresqlUndDapper
1. Setup von Postgresql auf Windows 10
Postgresql lässt sich auf einem Windows System mittels einfachem Installer installieren. Du findest den Installer hier. Nach dem Download kannst du diesen Installer ganz normal ausführen. Zusätzlich zu der Postgresql empfehle ich dir PgAdmin zu installieren. PgAdmin ist quasi das Microsoft SQL Server Management Studio für Postgresql. PgAdmin bekommst du hier.
2. Erstellen einer neuen Postgresql Datenbank
Wir arbeiten in diesem Tutorial mit einer kleinen Testdatenbank. Um diese zu erstellen gehst du wie folgt vor:
Starte PgAdmin und erstelle eine neue Datenbank mit dem Namen RetschgisPostgresqlTest.
Die Datenbank RetschgisPostgresqlTest wird eine einzige Tabelle mit dem Namen Employees enthalten. Mittels einem Datenbankstatement werden wir diese Tabelle anlegen und gleich noch Testdaten abfüllen. Das zugehörigen Datenbankstatement lautet wie folgt:
CREATE TABLE Employees (
Id serial primary key not null,
Name varchar(255) not null,
YearOfContract integer not null
);
INSERT INTO Employees(Name, YearOfContract)
VALUES
('Hans Scheff', 2008),
('Fränzi Finanzi', 2009),
('Klaus Werber', '2011'),
('Fabienne Schaffer', 2009);Du kannst diese Query ausführen indem du in PgAdmin das Query Tool verwendest. Das bekommst du entweder über das Menü via Tools>Query Tool oder durch die Eingabe von ctrl+E auf deiner Tastatur:
Anschliessend kannst du mittels einem einfachen SQL Query prüfen ob die Daten erfolgreich angelegt wurden.
3. Neues ASP.net Core Projekt anlegen
Ich gehe davon aus, dass du bereits etwas weisst wie du ein neues ASP.net Core Projekt in Visual Studio 2015 anlegen musst. Falls das für dich absolutes Neuland ist, kann ich dir folgende Anleitung ans Herz legen: http://ef.readthedocs.org/en/latest/getting-started/index.html
In unserem Beispiel machen wir ein neues ASP.net Core Web Application Projekt mit dem Namen PostgresqlUndDapper. Da wir keine Authentifizierung benötigen, wählen wir No Authentication aus.
Nach dem Erstellen wird dein Projekt in dem Solution Explorer in etwa wie folgt aussehen:
4. Datenbankzugriff realisieren
Für den Zugriff auf eine Datenbank gibt es im Kontext von ASP.net seit längerem verschiedene Konzepte und Ansätze. Microsoft selber pushed sehr stark die Verwendung von ihrem eigenen Entity Framework. Die Version 7 (aktuell noch beta) ist sogar Open Source. Ich persönlich habe zum Entity Framework eine etwas gespaltene Beziehung. Gerade bei umfangreicheren Projekten bin ich in der Vergangenheit mit dem Entity Framework immer wieder an Punkte geraten wo ich mir flexiblere Eingriffsmöglichkeiten in die Datenbankabfragen erwünscht hätte. Entity Framework und postgresql haben bereits einige Leute versucht. (Siehe zum Beispiel den Beitrag von Rob Conery). Die Begeisterung scheint sich da aber in Grenzen zu halten. Zeit also mal einen anderen Weg zu gehen. Die etwas krassere Alternative wäre das Verwenden von reinem ADO.net. Das wäre mir aber auch gerade etwas zu aufwändig. Als Zwischenweg bietet sich die Verwendung von einem Micro ORM an. Einer der bekannteren Micro ORM ist Dapper. Durch Dapper wird eine geringere Datenbankabstraktion erziehlt als zum Beispiel beim Entity Framework so, dass man stärker die Kontrolle übernehmen kann. Trotzdem findet - im Vergleich zu reinem ADO.net - noch eine Abstraktion statt. So, dass der Code nicht all zu crappy wird.
4.1 Notwendige Zusatz-Libraries hinzufügen
Um mit Dapper arbeiten zu können, brauchen wir zwei zusätzliche NuGET-Pakete:
- Dapper (Install-Package dapper). Für weitere Informationen siehe offizielle Dokumentation von Dapper
- Npgsql (Install-Package Npgsql) Für weitere Informationen siehe offizielle Dokumentation von Npgsql
Nach dem Hinzufügen der Pakete wirst du feststellen, dass die Packages für DNX 5 nicht aufgelöst werden können. Das liegt daran, dass die Implementationen für dieses .NET Execution Environment noch nicht vorliegt. Der Weg führt im Moment nur darüber, dass das dnx5 Framework entfernt wird. Das machst du indem du im project.json File den entsprechenden Eintrag entfernst:
{
"version": "1.0.0-*",
"compilationOptions": {
"emitEntryPoint": true
},
"dependencies": {
"Dapper": "1.42.0",
"Microsoft.AspNet.Diagnostics": "1.0.0-rc1-final",
"Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
"Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
"Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
"Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
"Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
"Microsoft.AspNet.Tooling.Razor": "1.0.0-rc1-final",
"Microsoft.Extensions.Configuration.FileProviderExtensions": "1.0.0-rc1-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
"Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final",
"Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0-rc1-final",
"Npgsql": "3.0.5"
},
"commands": {
"web": "Microsoft.AspNet.Server.Kestrel"
},
"frameworks": {
"dnx451": { }
},
"exclude": [
"wwwroot",
"node_modules"
],
"publishExclude": [
"**.user",
"**.vspscc"
],
"scripts": {
"prepublish": [ "npm install", "bower install", "gulp clean", "gulp min" ]
}
}4.2 Implementieren des Repository-Patterns
Für die Realisierung der Datenbankabfragen aus dem Source Code heraus gibt es verschiedene Ansätze. Ein bekannter ist die Verwendung des Repository-Patterns. Mehr zum Repository-Pattern kannst du hier nachlesen: http://www.dotnetcurry.com/aspnet-mvc/1155/aspnet-mvc-repository-pattern-perform-database-operations
Kurz formuliert geht es darum, dass pro Entität in der Datenbank (in unserem Beispiel Employee) ein entsprechendes Repository erstellt wird um mit dieser Entität zu arbeiten. (z.B neuer Employee hinzufügen, Employee entfernen usw.). Das bedeutet also, dass wir im Endeffekt eine EmployeeRepository-Klasse schreiben würden, welche wie folgt aussehen könnte:
public class EmployeeRepository
{
IEnumerable<Employee> Get() { };
Employee Get(int id) { };
void Add(Employee employee) { };
void Update(Employee employee) { };
void Remove(Employee employee) { };
}Würden wir der obige Ansatz wählen, so wäre das vermutlich nicht so gut. Eine solche Implementation ist nicht sehr generisch! Man läuft schnell die Gefahr, dass bei dem hinzukommen einer neuen Entität (z.B. Entität Project) ein komplett neues Repository (ProjectRepository) impelmentiert wird, welches Code Duplikationen von dem EmployeeRepository enthält. Ein meines Erachtens besserer Ansatz ist das Einführen von Abstraktionen. Dazu machen wir folgendes:
4.2.1 Neue Interfaces für die Repositories und Employee Model
Die Interfaces für die Repositories leben im Namespace Repositories. Somit erstellen wir die entsprechenden Ordnerstruktur in unserem Projekt.
Als erstes Interface definieren wir das Interface IRepository:
using System.Collections.Generic;
namespace PostgresqlUndDapper.Repositories
{
public interface IRepository<TEntity, in TPrimaryKey> where TEntity : BaseEntity<TPrimaryKey>
{
IEnumerable<TEntity> Get();
TEntity Get(TPrimaryKey id);
void Add(TEntity entity);
void Update(TEntity entity);
void Remove(TEntity entity);
}
}Dieses Interface ist eine generische Implementation eines Repositories, welches mit einer Klasse arbeiten kann, die von der abstrakten Klasse BaseEntity erbt:
using System.ComponentModel.DataAnnotations;
namespace PostgresqlUndDapper.Repositories
{
public abstract class BaseEntity<TPrimaryKey>
{
[Key]
public TPrimaryKey Id { get; set; }
}
}Die BaseEntity stellt sicher, dass jede Klasse wo von ihr eerbt über ein Property Id verfügt. Das Property entspricht dem Primary Key in der Datenbank. Nun ist es ander Zeit unser Model (Entität) für die Datenbanktabelle Employee zu erstellen.
Dazu erstelle ich im Namespace Models (normaler MVC-Namespace) eine neue Klasse Employee. Jene sieht wie folgt aus:
using PostgresqlUndDapper.Repositories;
namespace PostgresqlUndDapper.Models
{
public class Employee : BaseEntity<int>
{
public string Name { get; set; }
public int YearOfContract { get; set; }
}
}4.2.2 Generische IRepository-Implementation für postgresql
Die unter 4.2.1 erstellten Repository-Interfaces brauchen nun noch eine konkrete Implementation für postgresql. Wir wählen auch hier wieder den Ansatz von einer zusätzlichen Abstraktion. Dazu erstellen wir eine neue Klasse AbstractPostgresqlRepository im neu zuerstellenden Namespace RepositoriesPostgresql:
using Dapper;
using Npgsql;
using System.Collections.Generic;
using PostgresqlUndDapper.Repositories;
using System;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
namespace PostgresqlUndDapper.RepositoriesPostgresql
{
public abstract class AbstractPostgresqlRepository<TEntity,TPrimaryKey> : IRepository<TEntity, TPrimaryKey> where TEntity : BaseEntity<TPrimaryKey>
{
private string _connectionString;
public AbstractPostgresqlRepository(string connectionString)
{
_connectionString = connectionString;
}
protected abstract string TableName { get; }
}
}Diese Klasse implementiert unser IRepository Interface, bleibt dabei aber auch noch generisch. Darum arbeiten wir auch hier mit TEntity und TPrimaryKey. Zusätzlich wird dieser Klasse ein Connection-String übergeben. Dies ist später der Connection-String um auf unsere postgresql-Datenbank zuzugreifen.
Innerhalb dieser Klasse implementieren wir nun die einzelnen Methoden aus dem Interface IRepository. Dazu verwenden wir - für meinen Geschmack - etwas viel String-Magic. Alternativ könnten weitere Frameworks wie zum Beispiel die Dapper Extensions eingesetzt werden. Für erste Gehversuche habe ich das aber mal aussen vor gelassen.
using Dapper;
using Npgsql;
using System.Collections.Generic;
using PostgresqlUndDapper.Repositories;
using System;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
namespace PostgresqlUndDapper.RepositoriesPostgresql
{
public abstract class AbstractPostgresqlRepository<TEntity,TPrimaryKey> : IRepository<TEntity, TPrimaryKey> where TEntity : BaseEntity<TPrimaryKey>
{
private string _connectionString;
public AbstractPostgresqlRepository(string connectionString)
{
_connectionString = connectionString;
}
public IEnumerable<TEntity> Get()
{
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
string query = string.Format("SELECT * FROM {0}", TableName);
return connection.Query<TEntity>(query);
}
}
public TEntity Get(TPrimaryKey id)
{
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
string query = string.Format("SELECT * FROM {0} WHERE Id = @Id LIMIT 1", TableName);
return connection.Query<TEntity>(query, new { Id = id }).First();;
}
}
public void Add(TEntity entity)
{
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
IEnumerable<KeyValuePair<string, string>> RowsAndValues = ResolveProperties(entity);
IEnumerable<string> keys = RowsAndValues.Select(c => c.Key);
IEnumerable<string> values = RowsAndValues.Select(c => c.Value);
string query = string.Format("INSERT INTO {0} ({1}) VALUES ({2});", TableName, string.Join(",",keys), string.Join(",", values));
connection.Execute(query);
}
}
public void Update(TEntity entity)
{
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
IEnumerable<KeyValuePair<string, string>> RowsAndValues = ResolveProperties(entity);
IEnumerable<string> keys = RowsAndValues.Select(c => c.Key);
IEnumerable<string> values = RowsAndValues.Select(c => c.Value);
string query = string.Format("UPDATE {0} SET ({1}) = ({2}) WHERE Id = @Id;", TableName, string.Join(",", keys), string.Join(",", values));
connection.Execute(query, new { Id = entity.Id });
}
}
public void Remove(TEntity entity)
{
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
string query = string.Format("DELETE FROM {0} WHERE Id = @Id", TableName);
connection.Execute(query, new { Id = entity.Id });
}
}
private IEnumerable<KeyValuePair<string, string>> ResolveProperties(TEntity entity)
{
List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();
PropertyInfo[] infos = entity.GetType().GetProperties();
foreach (PropertyInfo info in infos)
{
if(info.GetCustomAttribute<KeyAttribute>() == null)
{
result.Add(new KeyValuePair<string, string>(info.Name, string.Format("'{0}'", info.GetValue(entity))));
}
}
return result;
}
protected abstract string TableName { get; }
}
}Besonders interessant in der obigen Klasse ist wohl die Methode ResolveProperties. Diese wird benötigt für das auflösen von der Properties einer BaseEntity-Implementation in eine entsprechende KeyValue-Struktur.
zum Beispiel wird aus einem solchen Employee-Objekt:
new Employee(){ Name="Retschgi", YearOfContract=2004 }new Employee(){ Name="Retschgi", YearOfContract=2004 }ein IEnumerable von KeyValuePairs à la:
new List<KeyValuePair<string,string>>()
{
new KeyValuePair(){Key = "Name", Value="Retschgi"},
new KeyValuePair(){Key = "YearOfContract", Value="2004"}
};Diese Strukturen können dann genutzt werden um zum Beispiel ein Insert-Query:
INSERT INTO Employees (Name,YearOfContract) VALUES ('Retschgi','2004');
dynamisch zu generieren
4.2.3 Implementation für IEmployeeRepository
Nun fehlt uns noch die Implementation für das IEmployeeRepository. Dabei machen wir uns nun die generische AbstractPostgresqlRepository-Klasse zu nutzen:
using PostgresqlUndDapper.Repositories;
using PostgresqlUndDapper.Models;
namespace PostgresqlUndDapper.RepositoriesPostgresql
{
public class EmployeeRepository : AbstractPostgresqlRepository<Employee, int>, IEmployeeRepository
{
public EmployeeRepository(string connectionString) : base(connectionString)
{
}
protected override string TableName
{
get
{
return "Employees";
}
}
}
}4.3 Dependency-Injection konfigurieren
Nun müssen wir noch sicherstellen, dass wir in unserem Code IEmployeeRepository Instanzen anfordern können. Dazu verwenden wir das DI von ASP.net Core selber. Dazu werden in der Klasse Startup.cs entsprechende Codezeilen hinzugefügt:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNet.Builder;
using Microsoft.AspNet.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using PostgresqlUndDapper.Repositories;
using PostgresqlUndDapper.RepositoriesPostgresql;
namespace PostgresqlUndDapper
{
public class Startup
{
....
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddMvc();
var connection = @"Server = 127.0.0.1; Port = 5432; Database = RetschgisPostgresqlTest; User Id = postgres; Password = {your password};";
services.AddSingleton<IEmployeeRepository, EmployeeRepository>(parameter => new EmployeeRepository(connection));
}
....
}
}Der Connection-String musst du natürlich entsprechend anpassen. Noch besser: Du lagerst ihn gleich noch ins appsettings.json aus!
5. Implementation des ASP.net Controllers
Nun kann es los gehen mit dem ASP.net Code. Zum Beispiel:
using System.Collections.Generic;
using PostgresqlUndDapper.Repositories;
using Microsoft.AspNet.Mvc;
using PostgresqlUndDapper.Models;
namespace PostgresqlUndDapper.Controllers
{
public class EmployeeController : Controller
{
private IEmployeeRepository _employeeRepository;
public EmployeeController(IEmployeeRepository employeeRepository)
{
_employeeRepository = employeeRepository;
}
public IActionResult Index()
{
IEnumerable<Employee> result = _employeeRepository.Get();
return View(result);
}
}
}mit der zugehörigen View
Sind Sie auf der Suche nach Microsoft .NET Experten?