dapper 结合postgresql完成数据映射操作

环境准备：
1 安装Npgsql
2 安装Dapper

设置连接字符串

  <connectionStrings>
    <add name="connect" connectionString="User ID=postgres;Password=postgres;Host=localhost;Port=5432;Database=postgres;"/>
  </connectionStrings>

数据库类（没有做成泛型）
需要添加
using Dapper;

using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    public class Db
    {
        public static NpgsqlConnection OpenConnection(string connStr)
        {
            NpgsqlConnection conn = new NpgsqlConnection(connStr);
            conn.Open();
            return conn;
        }
        /// <summary>  
        /// print the data  
        /// </summary>  
        public static IList<Kenny> PrintData(string _connStr)
        {
            IList<Kenny> list;
            //2.query  
            using (var conn = OpenConnection(_connStr))
            {
                string querySQL = @"SELECT id, number FROM public.kenny;";
                list = conn.Query<Kenny>(querySQL).ToList();
            }
            return list;
        }


        public static Kenny selectOne(string id,string _connStr)
        {
            string sql = "select id, number from public.kenny where id=@id";
            Kenny kenny = null;
            using (var conn = OpenConnection(_connStr))
            {

                kenny = conn.Query<Kenny>(sql,new { id = id }).FirstOrDefault();
            }
            return kenny;
        }


        public static void update(Kenny kenny,string _connStr)
        {
            string sql = "update public.kenny set number=@number where id=@id";

           
            using (var conn = OpenConnection(_connStr))
            {
                conn.Execute(sql, kenny);
            }
        }

        public static void insert(Kenny kenny,string connstring)
        {
            string sql = "insert into public.kenny (id,number) values(@id,@number)";
            using (var conn = OpenConnection(connstring))
            {
                conn.Execute(sql, kenny);
            }
        }
    }
}

如上的sql中使用了参数化，避免sql注入

实体类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    public class Kenny
    {
        public string id { get; set; }

        public string number { get; set; }
    }
}

demo

class Program
    {
        static void Main(string[] args)
        {
            string connectString = ConfigurationManager.ConnectionStrings["connect"].ToString();

            IList<Kenny> kennies = Db.PrintData(connectString);

            Kenny kenny1 = Db.selectOne("1", connectString);

            Kenny kenny = new Kenny
            {
                id = "1",
                number = "2"
            };

            //Db.insert(kenny, connectString);

            Db.update(kenny, connectString);
        }
    }