Dapper使用入门

Dapper介绍

Dapper是一款轻量级ORM工具
Dapper的速度接近与IDataReader，取列表的数据超过了DataTable，Dapper支持多数据库。诸如：Mysql、SqlLite、Mssql系列、Oracle等一系列的数据库。Dapper支持多表并联的对象。支持一对多，多对多的关系。并且没侵入性，想用就用，不想用就不用，无XML无属性，代码以前怎么写现在还怎么写。Dapper原理通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能高，语法十分简单，并且无须迁就数据库的设计。

Dapper的使用

1、在NuGet引用

Visual Studio IDE

2、Github下载源代码,添加到项目当中去

Dapper 项目地址 https://github.com/StackExchange/Dapper

个人感觉添加源代码到项目当中比较可靠，遇到问题可以直接跟踪调试Dapper的源代码，而且更容易学习理解它的原理。

CURD操作

创建数据库

create table People (
    Id INT,
    Name VARCHAR(50),
    Gender VARCHAR(50),
    Phone VARCHAR(50),
    Email VARCHAR(50),
    Ip VARCHAR(20)
);

写入测试数据
测试数据生成工具 https://www.mockaroo.com

insert into People (Id, Name, Gender, Phone, Email, Ip) values (1, 'Northrop', 'Male', '7175680361', 'nconkie0@reference.com', '108.29.105.168');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (2, 'Dov', 'Male', '6634536549', 'diskov1@1und1.de', '6.167.150.165');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (3, 'Friedrich', 'Male', '6357670903', 'fpoints2@oakley.com', '22.150.105.129');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (4, 'Lisle', 'Male', '4404970881', 'llazonby3@linkedin.com', '228.74.155.156');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (5, 'Roch', 'Female', '7381176390', 'rfilippazzo4@arstechnica.com', '124.180.218.65');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (6, 'Eadmund', 'Male', '5323111949', 'eshallo5@adobe.com', '73.115.179.20');
insert into People (Id, Name, Gender, Phone, Email, Ip) values (7, 'Raffaello', 'Male', '3975300055', 'rlinay6@businessinsider.com', '62.69.243.67');

预览表

image.png

Insert

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    People people = new People
    {
        Name = "越本山",
        Gender = "男",
        Phone = "15110834789"
    };
    string sql = "insert into People (Name,Gender,Phone)values(@Name,@Gender,@Phone)";
    var result = db.Execute(sql, people);
}

或者

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    People people = new People
    {
        Name = "越本山",
        Gender = "男",
        Phone = "15110834789"
    };
    string sql = "insert into People (Name,Gender,Phone)values(@Name,@Gender,@Phone)";
    var result = db.Execute(sql, new {
                    Name = "越本山",
                    Gender = "男",
                    Phone = "15110834789"
                });
}

Update

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    People people = new People
    {
        Id = 1,
        Name = "越本山",
        Gender = "男",
        Phone = "15110834789"
    };
    string sql = "update People set Name=@Name,Gender=@Gender,Phone=@Phone where Id=@Id";
    var result = db.Execute(sql, people);
}

Select

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    string sql = "select * from People";
    List<People> peoples = db.Query<People>(sql).ToList();
}

或者

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    string sql = "select * from People where Id=@Id";
    List<People> peoples = db.Query<People>(sql,new { Id = 2 }).ToList();
}

Delete

using (IDbConnection db = new SqlConnection("SqlServerConnection"))
{
    string sql = "delete from People where Id=@Id";
    var result = db.Execute(sql,new { Id = 2 });
}