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),