PostgreSQL数据库驱动Npgsql体验（.Net core）

简介

Npgsql是.Net环境下，postgresql的数据库驱动，现已支持.Net core。

Npgsql is an open source ADO.NET Data Provider for PostgreSQL, it allows programs written in C#, Visual Basic, F# to access the PostgreSQL database server. It is implemented in 100% C# code, is free and is open source.

官网：http://www.npgsql.org/index.html 相关文档：http://www.npgsql.org/doc/index.html

使用demo

数据库建表

-- ---------------------------- -- Table structure for person -- ---------------------------- DROP TABLE IF EXISTS "public"."person"; CREATE TABLE "public"."person" ( "id" int4 DEFAULT nextval('test_id_seq'::regclass) NOT NULL, "name" varchar(255) COLLATE "default", "gender" varchar(255) COLLATE "default" ) WITH (OIDS=FALSE) ; -- ---------------------------- -- Alter Sequences Owned By -- ---------------------------- -- ---------------------------- -- Primary Key structure for table person -- ---------------------------- ALTER TABLE "public"."person" ADD PRIMARY KEY ("id");

demo源码

using Npgsql; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace NpgsqlDemo { public class Program { public static void Main(string[] args) { Console.ReadKey(); } /// <summary> /// 普通操作 /// </summary> private static void Opt() { using (var conn = new NpgsqlConnection("Host=127.0.0.1;Username=postgres;Password=123456;Database=postgres")) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; // Insert some data cmd.CommandText = "INSERT INTO person (name,gender) VALUES ('zhangsan', 'man')"; cmd.ExecuteNonQuery(); // Retrieve all rows cmd.CommandText = "SELECT * FROM person"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader.GetString(0)); Console.WriteLine(reader.GetString(1)); Console.WriteLine(reader.GetString(2)); } } } } } /// <summary> /// 事务并提交 /// </summary> private static void TransactionAndCommit() { using (var conn = new NpgsqlConnection("Host=127.0.0.1;Username=postgres;Password=123456;Database=postgres")) { conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO person (name,gender) VALUES ('lisi', 'man')"; cmd.ExecuteNonQuery(); } tran.Commit(); } } /// <summary> /// 事务并回滚 /// </summary> private static void TransactionAndRollback() { using (var conn = new NpgsqlConnection("Host=127.0.0.1;Username=postgres;Password=123456;Database=postgres")) { conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO person (name,gender) VALUES ('wangwu', 'man')"; cmd.ExecuteNonQuery(); } tran.Rollback(); } } /// <summary> /// 事务并设置保存点 /// </summary> private static void TransactionAndSavepoint() { using (var conn = new NpgsqlConnection("Host=127.0.0.1;Username=postgres;Password=123456;Database=postgres")) { conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO person (name,gender) VALUES ('zhaoliu', 'man')"; cmd.ExecuteNonQuery(); } tran.Save("zhaoliu"); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO person (name,gender) VALUES ('test', 'man')"; cmd.ExecuteNonQuery(); } //test会被回滚，但是zhaoliu会被正常保存 tran.Rollback("zhaoliu"); tran.Commit(); } } } }

需要注意的是，Npgsql不支持嵌套事务和并发事务，但是支持保存点。

Transactions can be started by calling the standard ADO.NET method NpgsqlConnection.BeginTransaction(). PostgreSQL doesn’t support nested or concurrent transactions - only one transaction may be in progress at any given moment. Calling BeginTransaction() while a transaction is already in progress will throw an exception. Because of this, it isn’t necessary to pass the NpgsqlTransaction object returned from BeginTransaction() to commands you execute - calling BeginTransaction() means that all subsequent commands will automatically participate in the transaction, until either a commit or rollback is performed. However, for maximum portability it’s recommended to set the transaction on your commands. Although concurrent transactions aren’t supported, PostgreSQL supports the concept of savepoints - you may set named savepoints in a transaction and roll back to them later without rolling back the entire transaction. Savepoints can be created, rolled back to, and released via NpgsqlTransaction.Save(name),