Dapper 常规用法

安装Neget包

安装Dapper和其他的数据库

.Net FX

Install-Package Dapper      1.50.2
Install-Package MySql.Data  6.9.12
Install-Package Oracle.ManagedDataAccess  19.10.0
Install-Package System.Data.SQLite

.Net Core

Install-Package Dapper 
Install-Package MySql.Data
Install-Package Oracle.ManagedDataAccess.Core
Install-Package System.Data.SQLite.Core

操作数据库

查询数据

 using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                var data = conn.Query<User>("SELECT * from User");
            }

增删改获取返回行数

 using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                int a = conn.Execute("delete from User");
            }

事务

            using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                IDbTransaction transaction = conn.BeginTransaction();
                var result = conn.Execute("delete from User", null, transaction);
                //回滚
                transaction.Rollback();
                //提交
                transaction.Commit();
            }

其他数据库链接

using Dapper;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data.SqlClient;
using System.Data.SQLite;

namespace DBDapperNetCore
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                var data = conn.Query<User>("SELECT * from User");
                int a = conn.Execute("delete from User");
            }

            using (var conn = new OracleConnection("Data Source=127.0.0.1:1521/orcl;User Id=root;Password=123456;"))
            {
            }

            using (var conn = new MySqlConnection("Database=TestDB;Data Source=127.0.0.1;User Id=root;Password=123456;port=30001;pooling=false;SslMode=None;old Guids=true;Charset=utf8;"))
            {
            }

            using (var conn = new SQLiteConnection(@"Data Source=F:/test.db;database=main;"))
            {
            }

            Console.ReadKey();
        }
    }
}

实体类操作数据库

需要安装Dapper.Contrib包

Install-Package Dapper.Contrib
--.NET FX
Install-Package Dapper.Contrib 1.50.0

在Dapper.Contrib中一共以下扩展方法可以调用

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();

特性

主键属性必须存在，都是通过主键去增删改查的。

1. 默认以实体类Id（不区分大小写）作为主键
2. 可以通过[Key]属性标位主键，代表此值是数据库自动生成的数据，一般是int或GUID类型
3. 可以通过[ExplicitKey]属性标位主键，代表此值需要手动赋值，不会自动生成

操作数据库

using Dapper.Contrib.Extensions;
using System;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                Note entity = new Note();
                entity.Text = "564457674";
                entity.Date = DateTime.Now;
                var result = conn.Insert(entity);
                var list = conn.GetAll<Note>();
                conn.Update(new Note() { ID = 1, Text = "Maruti" });
                //SqlMapperExtensions.Insert(conn, entity);
            }
        }
    }

    [Table("Note")]
    public class Note
    {
        //主键
        [Key]
        public int ID { get; set; }

        //不可写
        [Write(false)]
        public string Text { get; set; }

        public DateTime Date { get; set; }
    }
}

通过数据库Table生成实体类

需要安装PocoClassGenerator包

GitHub源码地址 Gitee地址

支持以下类型的数据库

• sqlserver
• oracle
• mysql,
• postgresql

生成案例代码

using System.Data.SqlClient;

namespace ConsoleApp2
{

    class Program
    {
        static void Main(string[] args)
        {

            using (var connection = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
            {
                //生成所有表的实体
                var str = connection.GenerateAllTables();

                //生成summary注释实体
                str = connection.GenerateAllTables(GeneratorBehavior.Comment);

                //生成DapperContrib特性实体
                str = connection.GenerateAllTables(GeneratorBehavior.DapperContrib);

                //生成所有视图实体
                str = connection.GenerateAllTables(GeneratorBehavior.View);

                //生成前面三个所有的实体
                str = connection.GenerateAllTables(GeneratorBehavior.Comment
                                                | GeneratorBehavior.DapperContrib
                                                | GeneratorBehavior.View);

                //获取具体表的实体
                var classCode = connection.GenerateClass("select * from Table");

                classCode = connection.GenerateClass("select * from Table", GeneratorBehavior.Comment
                                                | GeneratorBehavior.DapperContrib
                                                | GeneratorBehavior.View);
            }
        }
    }
}