.net core EF 连接 PostgreSQL

引入包

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql.EntityFrameworkCore.PostgreSQL;

EF 所需的Context

public class PostgreSQLContent : DbContext
{
    public PostgreSQLContent(DbContextOptions<PostgreSQLContent> options) : base(options)
    {
 
    }
 
    public DbSet<TestTable> TestTable { get; set; }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestTable>(entity =>
        {
            entity.ToTable("test_table");
        });
    }
}

实体Entity

public class TestTable
{
    public int id { get; set; }
    public DateTime updatetime { get; set; }
    public String name { get; set; }
}

连接

 string conStr = "Host=xxxx;Username=postgres;Password=xxxx;Database=test_pg";
 
//不用注入
            DbContextOptions<PostgreSQLContent> dbContextOption = new DbContextOptions<PostgreSQLContent>();
            DbContextOptionsBuilder<PostgreSQLContent> dbContextOptionBuilder = new DbContextOptionsBuilder<PostgreSQLContent>(dbContextOption);
            var db = new PostgreSQLContent(dbContextOptionBuilder.UseNpgsql(conStr).Options);
 
 
//使用注入
            //IServiceCollection services = new ServiceCollection();
            //services.AddDbContext<PostgreSQLContent>(option => option.UseNpgsql(conStr));
            //var db = services.BuildServiceProvider().GetService<PostgreSQLContent>();
 
 
//插入
    db.TestTable.Add(new PostgreSQLTest.Models.TestTable() { id = 21, name = "teset_xiajun", updatetime = DateTime.Now });
            db.SaveChanges();
//获取数据
            var ent = db.TestTable.Where(s => s.id == 21).FirstOrDefault();

---

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