Dapper 基础用法

Dapper是.Net下的一个简单orm框架，具有以下特点：

1、简单，只需要一个文件即可（SqlMapper.cs）

2、快速，下面是一个查询结果集在500以上的运行速度对比

3、不要求特定的db provider，即不论是sqlite, sqlce, firebird, oracle, MySQL 还是 SQL Server，都能使用它。

项目的github地址是：https://github.com/SamSaffron/dapper-dot-net 可以从这里下载SqlMapper.cs，然后添加到自己的项目中即可。

下面通过一个例子来介绍Dapper的基本用法。

假设有个数据库test，test包含一张表users用来存储id, nickname, email, role字段。

在vs中新建一个控制台项目，添加SqlMapper.cs到项目中，下面是program.cs的内容，注释中介绍了dapper的常用方法：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;

namespace DapperTest
{
    class Program
    {
        public const string ConnectionString = "Data Source=.;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=test";

        public const string OleDbConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI";

        public static SqlConnection GetOpenConnection()
        {
            var connection = new SqlConnection(ConnectionString);
            connection.Open();
            return connection;
        }

        static void Main(string[] args)
        {
            var connection = GetOpenConnection();

            //1、返回动态对象列表的查询
            Console.WriteLine("1、返回动态对象列表的查询");
            var users = connection.Query("select * from users");
            foreach (var user in users)
            {
                //动态对象的名称与数据库字段的名称一模一样
                Console.WriteLine(user.id+"\t"+user.nickname+"\t"+user.email);
            }
            Console.WriteLine(users.First().nickname);

            //2、返回强类型列表的查询
            Console.WriteLine("2、返回强类型列表的查询");
            var users2 = connection.Query<User>("select * from users");
            foreach (var user in users2)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }

            //3、传递参数的查询
            Console.WriteLine("3、传递参数的查询");
            var users3 = connection.Query<User>("select * from users where email=@email",
                new {email = "liulixiang1988@gmail.com"});
            foreach (var user in users3)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }

            //4、执行不返回结果的命令（更新、删除等）
            Console.WriteLine("4、执行不返回结果的命令（更新、删除等）");
            connection.Execute(@"
              IF EXISTS(SELECT 1 FROM users WHERE nickname=@nickname)
                RETURN;
              insert into users(nickname, email, role) values(@nickname, @email, @role);
            ", new {nickname = "王二", email = @"wanger@test.com", role = 0});
            var users4 = connection.Query<User>("select * from users");
            Console.WriteLine("执行不返回结果后的结果集合");
            foreach (var user in users4)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }

            //5、插入或更新集合或者列表(多条记录）
            Console.WriteLine("5、插入或更新集合或者列表(多条记录）");
            var userList = new List<User>
            {
                new User() {NickName = "李四", Email = "lisi@test.com", Role = 0},
                new User() {NickName = "张三", Email = "zhangsan@test.com", Role = 0}
            };
            connection.Execute(@"
              IF EXISTS(SELECT 1 FROM users WHERE nickname=@nickname)
                RETURN;
              insert into users(nickname, email, role) values(@nickname, @email, @role);
            ", userList);
            //执行查询
            var users5 = connection.Query<User>("select * from users");
            Console.WriteLine("执行不返回结果后的结果集合");
            foreach (var user in users5)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }
            //6、dapper支持列表自动分解
            Console.WriteLine("6、dapper支持列表自动分解");
            var users6 = connection.Query<User>(
                @"SELECT * FROM users
                WHERE nickname IN @nicknames", new {nicknames=new string[]{"张三", "李四"}})
            ;
            foreach (var user in users6)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }

            //7、执行存储过程
            Console.WriteLine("7、执行存储过程");
            var users7 = connection.Query<User>("sp_get_user", new {nickname = "张三"},
                commandType: CommandType.StoredProcedure);
            foreach (var user in users7)
            {
                //注意强类型返回的是User类实例
                Console.WriteLine(user.Id + "\t" + user.NickName + "\t" + user.Email);
            }
            Console.ReadKey();
            if(connection.State != ConnectionState.Closed)
                connection.Close();
        }
    }

    public class User
    {
        public int? Id { get; set; }
        public string NickName { get; set; }
        public string Email { get; set; }
        public int? Role { get; set; }
    }
}