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();
        }
    }
}

连接不同类型的数据库

 public class UserRepository : DbConnectionMSSQLRepositoryBase
    {
        public UserRepository(IDbConnectionFactory dbConnectionFactory)
            : base(dbConnectionFactory) { }

        public IEnumerable<int> GetAll()
        {
            const string sql = @"SELECT * FROM TABLE";

            return base.DbConnection.Query<int>(sql);
        }
    }
    public class DapperDbConnectionFactory : IDbConnectionFactory
    {
        public IDbConnection CreateDbConnection(DatabaseConnectionName connectionName)
        {
            string connectionString = null;
            if (connectionName.Equals(DatabaseConnectionName.MSSQL))
            {
                return new SqlConnection(connectionString);
            }
            if (connectionName.Equals(DatabaseConnectionName.MYSQL))
            {
                return new MySqlConnection(connectionString);
            }
            throw new ArgumentNullException();
        }
    }

    public abstract class DbConnectionMSSQLRepositoryBase
    {
        public IDbConnection DbConnection { get; private set; }

        public DbConnectionMSSQLRepositoryBase(IDbConnectionFactory dbConnectionFactory)
        {
            // Now it's the time to pick the right connection string!
            // Enum is used. No magic string!
            this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.MSSQL);
        }
    }

    public abstract class DbConnectionMYSQLRepositoryBase
    {
        public IDbConnection DbConnection { get; private set; }

        public DbConnectionMYSQLRepositoryBase(IDbConnectionFactory dbConnectionFactory)
        {
            this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.MYSQL);
        }
    }

    public interface IDbConnectionFactory
    {
        IDbConnection CreateDbConnection(DatabaseConnectionName connectionName);
    }
    public enum DatabaseConnectionName
    {
        MSSQL,
        MYSQL
    }

实体类操作数据库

需要安装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>();

特性

  • Table:指定实体对应地数据库表名
  • Key:指定此列为主键(自动增长主键),可忽略,忽略后默认查找以实体类Id(不区分大小写)作为主键
  • ExplicitKey:指定此列为主键(不自动增长类型例如guid,ExplicitKey与Key地区别下面会详细讲)
  • Computed:计算属性,打上此标签,对象地insert,update等操作会忽略此列
  • Write:需穿一个bool值,false时insert,update等操作会忽略此列

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

  1. 默认以实体类Id(不区分大小写)作为主键
  2. 可以通过[Key]属性标位主键,代表此值是数据库自动生成的数据,一般是intGUID类型
  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
  • SqLite

生成案例代码

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);
            }
        }
    }
}

其他扩展包

其他链接

posted @   雨水的命运  阅读(436)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· Blazor Hybrid适配到HarmonyOS系统
· Obsidian + DeepSeek:免费 AI 助力你的知识管理,让你的笔记飞起来!
· 分享4款.NET开源、免费、实用的商城系统
· 解决跨域问题的这6种方案,真香!
· 一套基于 Material Design 规范实现的 Blazor 和 Razor 通用组件库
点击右上角即可分享
微信分享提示