Dapper数据库相关操作

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Dapper
{
    public class b_base
    {
        public IDbConnection Connection = null;
        public b_base()
        {
            Connection = new SqlConnection(ConfigurationManager.AppSettings["dapper"]);       
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Dapper;

namespace Dapper
{
    public class crud:b_base
    {
        public crud()
        {
                
        }

        #region 手写Sql插入数据
        /// <summary>
        /// 手写Sql插入数据
        /// </summary>
        public int InsertWithSql()
        {
            using (var conn = Connection)
            {
                string _sql ="INSERT INTO t_department(departmentname,introduce,[enable])VALUES('应用开发部SQL','应用开发部主要开始公司的应用平台',1)";
                conn.Open();
                return conn.Execute(_sql);
            }
        }
        #endregion

        #region 实体插入数据
        /// <summary>
        /// 实体插入数据
        /// </summary>
        public int? InsertWithEntity()
        {
            using (var conn = Connection)
            {
                var _entity = new t_department { departmentname = "应用开发部ENTITY", introduce = "应用开发部主要开始公司的应用平台"};
                conn.Open();
                return conn.Insert(_entity);
            }
        }
        #endregion

        #region 在IDBconnection中使用事务
        /// <summary>
        /// 在IDBconnection中使用事务
        /// </summary>
        /// <returns></returns>
        public bool InsertWithTran()
        {
            using (var conn = Connection)
            {
               int _departmentid = 0, _employeeid = 0,_rnum=0;
                var _departmentname = new t_department { departmentname = "应用开发部ENTITY", introduce = "应用开发部主要开始公司的应用平台" };
                var _employee = new t_employee {displayname = "Micro",email ="1441299@qq.com",loginname ="Micro",password = "66778899",mobile = "123456789"};
                conn.Open();
                var _tran=conn.BeginTransaction();
                try
                {
                    _departmentid=conn.Insert(_departmentname, transaction: _tran).Value;
                    ++_rnum;
                    _employeeid = conn.Insert(_employee, transaction: _tran).Value;
                    ++_rnum;
                    conn.Insert(new t_derelation { departmentid = _departmentid, employeeid = _employeeid }, transaction: _tran);
                    ++_rnum;
                    _tran.Commit();
                }
                catch
                {
                    _rnum = 0;
                    _tran.Rollback();
                }
                return _rnum > 0;
            }
        }
        #endregion

        #region 在存储过程中使用事务
        /// <summary>
        /// 在存储过程中使用事务
        /// </summary>
        /// <returns></returns>
        public bool InsertWithProcTran()
        {
            var _parameter = new DynamicParameters();
            _parameter.Add("departmentname","外网开发部门");
            _parameter.Add("introduce","外网开发部门负责外部网站的更新");
            _parameter.Add("displayname","夏季冰点");
            _parameter.Add("loginname","Micro");
            _parameter.Add("password","123456789");
            _parameter.Add("mobile","1122334455");
            _parameter.Add("email","123456789@qq.com");
            using (var _conn = Connection)
            {
                _conn.Open();
                return
                    _conn.Query<bool>("p_Insertdata", _parameter, commandType: CommandType.StoredProcedure)
                        .FirstOrDefault();
            }
        }
        #endregion

        #region 查询所有员工信息方法一
        /// <summary>
        /// 查询所有员工信息方法一
        /// </summary>
        /// <returns></returns>
        public IEnumerable<t_employee> GetemployeeListFirst()
        {
            string _sql = "SELECT * FROM t_employee";
            using (var _conn = Connection)
            {
                _conn.Open();
                return _conn.Query<t_employee>(_sql);
            }
        }
        #endregion 

        #region 查询所有员工信息方法二
        /// <summary>
        /// 查询所有员工信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<t_employee> GetemployeeListSecond()
        {
            using (var _conn = Connection)
            {
                _conn.Open();
                return _conn.GetList<t_employee>();
            }
        }
        #endregion 

        #region 获取某位员工的信息方法一
        /// <summary>
        /// 获取某位员工的信息方法一
        /// </summary>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        public t_employee GetemployeeFirst(int employeeid)
        {
            string _sql = "SELECT * FROM t_employee where employeeid=@pemployeeid";
            using (var _conn = Connection)
            {
                _conn.Open();
                return _conn.Query<t_employee>(_sql, new { pemployeeid = employeeid }).FirstOrDefault();
            }
        }
        #endregion 

        #region 获取某位员工的信息方法二
        /// <summary>
        /// 获取某位员工的信息方法二
        /// </summary>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        public t_employee GetemployeetSecond(int employeeid)
        {
            using (var _conn = Connection)
            {
                _conn.Open();
                return _conn.Get<t_employee>(employeeid);
            }
        }
        #endregion 

        #region 获取某位员工的信息方法三
        /// <summary>
        /// 获取某位员工的信息方法三
        /// </summary>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        public t_employee Getemployeethird(int pemployeeid)
        {
            using (var _conn = Connection)
            {
                _conn.Open();
                return _conn.GetList<t_employee>(new { employeeid = pemployeeid }).FirstOrDefault();
            }
        }
        #endregion

        #region 多表查询(获取部门&员工信息)
        /// <summary>
        /// 多表查询(获取部门&员工信息)
        /// </summary>
        public void GetMultiEntity()
        {
            string _sql = "SELECT * FROM t_department AS a;SELECT * FROM t_employee AS a";
            using (var _conn = Connection)
            {
                var _grid = _conn.QueryMultiple(_sql);
                var _department = _grid.Read<t_department>();
                var _employee = _grid.Read<t_employee>();
            }
        }
        #endregion

        #region 父子关系查询
        /// <summary>
        /// 父子关系查询
        /// </summary>
        public IEnumerable<t_department> GetPCEntity()
        {
            string _sql = "SELECT * FROM t_department AS a;SELECT * FROM t_employee AS a;SELECT * FROM t_derelation;";
            using (var _conn = Connection)
            {
                var _grid = _conn.QueryMultiple(_sql);
                var _department = _grid.Read<t_department>();
                var _employee = _grid.Read<t_employee>();
                var _derelation = _grid.Read<t_derelation>();
                foreach (var tDepartment in _department)
                {
                    tDepartment.ListEmployees = _employee.Join(_derelation.Where(v => v.departmentid == tDepartment.departmentid), p => p.employeeid, r => r.employeeid, (p, r) => p);
                }
                return _department;
            }
        }
        #endregion

        #region 简单分页查询
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pstart"></param>
        /// <param name="pend"></param>
        /// <returns></returns>
        public IEnumerable<t_employee> GetPaging(int pstart=0,int pend=5)
        {
            string _sql = "SELECT * FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY a.employeeid) rownum FROM t_employee as a ) b WHERE b.rownum BETWEEN @start AND @end ORDER BY b.rownum";
            using (var _conn = Connection)
            {
                return _conn.Query<t_employee>(_sql, new {start = pstart, end = pend});
            }
        }
        #endregion

        #region 通用分页
        /// <summary>
        /// 通用分页
        /// </summary>
        /// <returns></returns>
        public int GetPaging()
        {
            实际开发可以独立出来处理/
            var _ppaging = new p_PageList<t_employee>();
            _ppaging.Tables = "t_employee";
            _ppaging.OrderFields = "employeeid asc";
            ///
            var _dy = new DynamicParameters();
            _dy.Add("Tables", _ppaging.Tables);
            _dy.Add("OrderFields", _ppaging.OrderFields);
            _dy.Add("TotalCount",dbType:DbType.Int32,direction: ParameterDirection.Output);
            using (var _conn= Connection)
            {
                _conn.Open();
                _ppaging.DataList=_conn.Query<t_employee>("p_PageList", _dy, commandType: CommandType.StoredProcedure);
            }
            _ppaging.TotalCount = _dy.Get<int>("TotalCount");
            return _ppaging.PageCount;
        }
        #endregion

        #region 存储过程Demo
        /// <summary>
        /// 存储过程Demo
        /// </summary>
        public Tuple<string,string> ProceDemo()
        {
            int employeeid = 1;
            var _mobile = "";
            var _dy = new DynamicParameters();
            _dy.Add("employeeid", employeeid);
            _dy.Add("displayname", string.Empty, dbType: DbType.String, direction: ParameterDirection.Output);
            using (var _conn = Connection)
            {
                _conn.Open();
                _mobile= _conn.Query<string>("p_Procedemo", _dy, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
            return Tuple.Create(_mobile, _dy.Get<string>("displayname"));
        }
        #endregion

    }
}

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

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

            //父子表测试  
            StringBuilder builder = new StringBuilder();
            var dlist = new crud().GetPCEntity();
            foreach (var de in dlist)
            {
                builder.AppendLine(de.departmentid + "---->" + de.departmentname);
                var elist = de.ListEmployees;
                foreach (var em in elist)
                {
                    builder.AppendLine(em.displayname + "---->" + em.email);
                }
            }
            Console.Write(builder.ToString());
            Console.Read();
        }
    }
}

CREATE PROCEDURE p_PageList
	@Tables VARCHAR(200),
	@Fields VARCHAR(500) = '*',
	@OrderFields VARCHAR(100),
	@Where VARCHAR(100) = NULL,
	@PageIndex INT = 1 ,
	@PageSize INT = 20,
	@GroupBy VARCHAR(100) = NULL,
	@TotalCount INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @sql NVARCHAR(4000);
	DECLARE @PageCount INT;
	IF (@GroupBy = '' OR @GroupBy IS NULL)
	BEGIN
	    SET @sql = 'select @RecordCount = count(*) from ' + @Tables
	    IF (@Where <> '' AND @Where IS NOT NULL)
	        SET @sql = @sql + ' where ' + @Where
	END
	ELSE
	BEGIN
	    SET @sql = 'select @Recordcount=count(*) from(select 1 as total from ' + 
	        @Tables
	    
	    IF (@Where <> '' AND @Where IS NOT NULL)
	        SET @sql = @sql + ' where ' + @Where
	    
	    SET @sql = @sql + ' group by ' + @GroupBy + ') as t'
	END
	EXEC sp_executesql @sql,
	     N'@RecordCount int OUTPUT',
	     @TotalCount OUTPUT
	
	SELECT @PageCount = CEILING((@TotalCount + 0.0) / @PageSize)
	SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderFields 
	    + ') as rowId,' + @Fields + ' from ' + @Tables
	
	IF (@Where <> '' AND @Where IS NOT NULL)
	    SET @sql = @sql + ' where ' + @Where
	
	IF (@GroupBy <> '' AND @GroupBy IS NOT NULL)
	    SET @sql = @sql + ' group by ' + @GroupBy
	
	IF @PageIndex <= 0
	    SET @PageIndex = 1
	
	IF @PageIndex > @PageCount
	    SET @PageIndex = @PageCount
	
	DECLARE @StartRecord     INT,
	        @EndRecord       INT
	
	SELECT @StartRecord = (@PageIndex -1) * @PageSize + 1,
	       @EndRecord = @StartRecord + @PageSize - 1
	
	SET @Sql = @Sql + ') as ' + @Tables + ' where rowId between ' + CONVERT(VARCHAR(50), @StartRecord) 
	    + ' and ' + CONVERT(VARCHAR(50), @EndRecord)
	
	EXEC (@Sql)
	---------------------------------------------------
	SET NOCOUNT OFF;
END

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

namespace Dapper
{
    class Program
    {
        private static readonly string connString = ConfigurationManager.AppSettings["northwind"].ToString();
        static void Main(string[] args)
        {
            /*
            using (var cn = new SqlConnection(ConfigurationManager.AppSettings["northwind"]))
            {
                //查询类别为2的商品
                var list = cn.Query(
                    "SELECT * FROM Products WHERE CategoryID=@catg", new { catg = 2 });
                foreach (var item in list)
                {
                    Console.WriteLine("{0}.{1}({2})",
                        item.ProductID, item.ProductName, item.QuantityPerUnit);
                }
            }
            */


            using (var cn = new SqlConnection(connString))
            {
                //1) 将SELECT結果转换成指定的类别(属性和字段名要保持一致)
                //2) 直接传数字组合作为WHERE IN比對参数
                //   =>自动转换成WHERE col in (@arg1,@arg2,@arg3)
                var list = cn.Query<SimpProduct>(
                    "SELECT * FROM Products WHERE CategoryID IN @catgs",
                    new { catgs = new int[] { 1, 4 } });
                foreach (var item in list)
                {
                    Console.WriteLine("{0}.{1} category:{2}",
                        item.ProductID, item.ProductName, item.CategoryID);
                }
            }

            using (var cn = new SqlConnection(connString))
            {
                //1) 可执行SQL命令，支持参数
                //2) 提供多组参数，可批量执行，适合批量插入，修改，删除操作
                cn.Execute(@"INSERT INTO Test VALUES (@username, @userage)",
                    new[] {
                        new { username = "张三", userage = 20 },
                        new { username = "李四", userage = 25 }
                    });
            }
            Console.Read();
        }
    }

    public class SimpProduct
    {
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public int CategoryID { get; set; }
    }
}

//书和书评是1---n的关系。（沿用Entity Framework的实体类，virtual表示延迟加载，此处忽略）


//书
public class Book
{
	public Book()
	{
		Reviews = new List<BookReview>();
	}
	public int Id { get; set; }
	public string Name { get; set; }
	public virtual List<BookReview> Reviews { get; set; }
	public override string ToString()
	{
		return string.Format("[{0}]------《{1}》", Id, Name);
	}
}

//书评
public class BookReview
{
	public int Id { get; set; }
	public int BookId { get; set; }
	public virtual string Content { get; set; }
	public virtual Book AssoicationWithBook { get; set; }
	public override string ToString()
	{
		return string.Format("{0})--[{1}]\t\"{3}\"", Id, BookId, Content);
	}
}

//基本的增删改查操作
//由于Dapper ORM的操作实际上是对IDbConnection类的扩展，所有的方法都是该类的扩展方法。所以在使用前先实例化一个IDBConnection对象。

IDbConnection conn = new SqlConnection(connString);

Insert


Book book = new Book();
book.Name="C#本质论";
string query = "INSERT INTO Book(Name)VALUES(@name)";
//对对象进行操作
conn.Execute(query, book);
//直接赋值操作
conn.Execute(query, new {name = "C#本质论"});

update

string query = "UPDATE Book SET  Name=@name WHERE id =@id";
conn.Execute(query, book);


delete

string query = "DELETE FROM Book WHERE id = @id";
conn.Execute(query, book);
conn.Execute(query, new { id = id });


query


string query = "SELECT * FROM Book";
//无参数查询，返回列表，带参数查询和之前的参数赋值法相同。
conn.Query<Book>(query).ToList();

//返回单条信息
string query = "SELECT * FROM Book WHERE id = @id";
book = conn.Query<Book>(query, new { id = id }).SingleOrDefault();     

数据库表对应关系操作

//查询图书时，同时查找对应的书评，并存在List中。实现1--n的查询操作
string query = "SELECT * FROM Book b LEFT JOIN BookReview br ON br.BookId = b.Id WHERE b.id = @id";
Book lookup = null;
//Query<TFirst, TSecond, TReturn>
var b = conn.Query<Book, BookReview, Book>(query,
　　(book, bookReview) =>
　　{
　　　　//扫描第一条记录，判断非空和非重复
　　　　if (lookup == null || lookup.Id != book.Id)
　　　　　　lookup = book;
　　　　//书对应的书评非空，加入当前书的书评List中，最后把重复的书去掉。
　　　　if (bookReview != null)
　　　　　　lookup.Reviews.Add(bookReview);
　　　　return lookup;
　　}, new { id = id }).Distinct().SingleOrDefault();
return b;


//1--1操作 
BookReview br;
string query = "SELECT * FROM BookReview WHERE id = @id";
using (conn)
{
　　br = conn.Query<BookReview, Book, BookReview>(query,
　　(bookReview, book) =>
　　{
　　　　bookReview.AssoicationWithBook = book;
　　　　return bookReview;
　　 }, new { id = id }).SingleOrDefault();
　　return br;
}

事务操作

using (conn)
{
	//开始事务
	IDbTransaction transaction = conn.BeginTransaction();
　　try
　　{
　　　　string query = "DELETE FROM Book WHERE id = @id";
　　　　string query2 = "DELETE FROM BookReview WHERE BookId = @BookId";
　　　　conn.Execute(query2, new { BookId = id }, transaction, null, null);
　　　　conn.Execute(query, new { id = id }, transaction, null, null);
　　　　//提交事务
　　　　transaction.Commit();
　　}
　　catch (Exception ex)
　　{
　　　　//出现异常，事务Rollback
　　　　transaction.Rollback();
　　　　throw new Exception(ex.Message);
　　}
}

Dapper-Extensions基本使用

//实体类
DemoEntity entity = new DemoEntity();

//根据实体主键删除
this.Delete<DemoEntity>(entity);

//根据主键ID删除
this.Delete<DemoEntity>(1);

//增加
this.Insert<DemoEntity>(entity);

//更新
bool result = this.Update<DemoEntity>(entity);

//根据主键返回实体
entity = this.GetById<DemoEntity>(1);

//返回 行数
this.Count<DemoEntity>(new { ID = 1 });

//查询所有
IEnumerable<DemoEntity> list = this.GetAll<DemoEntity>();

IList<ISort> sort = new List<ISort>();
sort.Add(new Sort { PropertyName = "ID", Ascending = false });


//条件查询
list = this.GetList<DemoEntity>(new { ID = 1, Name = "123" }, sort);

//orm 拼接条件 查询
IList<IPredicate> predList = new List<IPredicate>();
predList.Add(Predicates.Field<DemoEntity>(p => p.Name, Operator.Like, "不知道%"));
predList.Add(Predicates.Field<DemoEntity>(p => p.ID, Operator.Eq, 1));
IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, predList.ToArray());



list = this.GetList<DemoEntity>(predGroup);


//分页查询
long allRowsCount = 0;
this.GetPageList<DemoEntity>(1, 10, out allRowsCount, new { ID = 1 }, sort);


//Dapper测试


新增数据     
//RBAC.Model.SY_ADMIN model = new RBAC.Model.SY_ADMIN();
//model.UserName = "16bb21";
//model.RealName = "张三";
//int id = RBAC.Bll.SY_ADMIN.Insert(model);
//str += string.Format("数据新增成功,ID={0}", id);

修改数据
//RBAC.Model.SY_ADMIN model2 = new RBAC.Model.SY_ADMIN();
//model2.UserID = 10111;
//model2.UserName = "li5551222211";
//model2.RealName = "李1四";
//bool flag = RBAC.Bll.SY_ADMIN.Update(model2);
//if (flag)
//{
//    str += string.Format("更新成功");
//}
//else
//{
//    str += string.Format("更新失败，无此数据");
//}

删除（一条数据）
//bool flag = RBAC.Bll.SY_ADMIN.Delete(10065);
//if (flag)
//{
//    str += string.Format("删除成功");
//}
//else
//{
//    str += string.Format("删除失败，无此数据");
//}

获取一条数据
//var model = RBAC.Bll.SY_ADMIN.Get(10075);
//if (model == null)
//{
//    str += string.Format("用户ID{0}的数据不存在", 10075);
//}
//else
//{
//    str += string.Format("用户名{0}密码{1}真实姓名{2}", model.UserName, model.UserPwd, model.RealName);
//}


获取一组数据
//var pgMain = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
//var pg1 = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
//pg1.Predicates.Add(Predicates.Field<RBAC.Model.SY_ADMIN>(f => f.RealName, Operator.Eq, "张三"));

//var pg2 = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
//pg2.Predicates.Add(Predicates.Field<RBAC.Model.SY_ADMIN>(f => f.UserName, Operator.Like, "%bb%"));

//pgMain.Predicates.Add(pg1);
//pgMain.Predicates.Add(pg2);

///*
// * 自动生成sql语句
// * SELECT * FROM [SY_ADMIN] WHERE ((([SY_ADMIN].[RealName] = @RealName_0)) OR (([SY_ADMIN].[UserName] LIKE @UserName_1)))
//*/
//var list = RBAC.Bll.SY_ADMIN.Get(pgMain);
//foreach (var item in list)
//{
//    str += string.Format("{0},用户名{1},密码{2},真实姓名{3}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName);
//}

//获取分页数据
int totalRecord = 0;
int totalPage = 0;
var list = RBAC.Bll.SY_ADMIN.GetPage("*", "userID asc", 1, 2,
	"", out totalRecord, out totalPage);
foreach (var item in list)
{
	str += string.Format("{0},用户名{1},密码{2},真实姓名{3}-{4}-{5}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName, totalRecord, totalPage);
}

扩展自定义方法
//var list = RBAC.Bll.SY_ADMIN.GetAllByExt2();
//foreach (var item in list)
//{
//    str += string.Format("{0},用户名{1},密码{2},真实姓名{3}<br/>", item.UserID, item.UserName, item.UserPwd, item.RealName);
//}


事务处理 
//using (TransactionScope scope = new TransactionScope())
//{
//    //主程序区域
//    scope.Complete();
//} 


using Dapper;
using DapperExtensions;  //Install-Package DapperExtensions
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace RBAC.Dal
{
    public partial class SY_ADMIN
    {
        public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; 
        
        #region 新增数据
        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(Model.SY_ADMIN model)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                int a = conn.Insert(model);
                conn.Close();
                return a;
            }
        }
        #endregion        
 
        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns> 
        public bool Update(Model.SY_ADMIN model)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                Model.SY_ADMIN model1 = conn.Get<Model.SY_ADMIN>(model.UserID);
                if (model1 != null)
                {                
				    if (null != model.UserName) {  model1.UserName = model.UserName ; }   
				    if (null != model.UserPwd) {  model1.UserPwd = model.UserPwd ; }   
				    if (null != model.SafeCode) {  model1.SafeCode = model.SafeCode ; }   
				    if (null != model.RealName) {  model1.RealName = model.RealName ; }   
				    if (null != model.CreateDate) {  model1.CreateDate = model.CreateDate ; }   
				    if (null != model.LastDate) {  model1.LastDate = model.LastDate ; }   
				    if (null != model.LastIP) {  model1.LastIP = model.LastIP ; }   
				    if (null != model.CurrIP) {  model1.CurrIP = model.CurrIP ; }   
				    if (null != model.LoginCount) {  model1.LoginCount = model.LoginCount ; }   
				    if (null != model.GroupID) {  model1.GroupID = model.GroupID ; }   
                    var a = conn.Update(model1);
                    conn.Close();
                    return a;
                }
                else
                {
                    conn.Close();
                    return false;
                }                
            }
        }
        #endregion        
    
        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns>        
        public bool Delete(int id)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                Model.SY_ADMIN model = new Model.SY_ADMIN();
                model.UserID = id;                
                bool a = conn.Delete(model);
                conn.Close();
                return a;
            }
        }
        
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="model">实体类条件</param>
        /// <returns></returns>        
        public bool Delete(Model.SY_ADMIN model)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                StringBuilder sqlStr = new StringBuilder();
				if (null != model.UserName) { sqlStr.Append(" AND [UserName] = @UserName "); }
				if (null != model.UserPwd) { sqlStr.Append(" AND [UserPwd] = @UserPwd "); }
				if (null != model.SafeCode) { sqlStr.Append(" AND [SafeCode] = @SafeCode "); }
				if (null != model.RealName) { sqlStr.Append(" AND [RealName] = @RealName "); }
				if (null != model.CreateDate) { sqlStr.Append(" AND [CreateDate] = @CreateDate "); }
				if (null != model.LastDate) { sqlStr.Append(" AND [LastDate] = @LastDate "); }
				if (null != model.LastIP) { sqlStr.Append(" AND [LastIP] = @LastIP "); }
				if (null != model.CurrIP) { sqlStr.Append(" AND [CurrIP] = @CurrIP "); }
				if (null != model.LoginCount) { sqlStr.Append(" AND [LoginCount] = @LoginCount "); }
				if (null != model.GroupID) { sqlStr.Append(" AND [GroupID] = @GroupID "); }
                  
                string sql = string.Format("DELETE FROM [{0}] WHERE 1=1 {1}",
                                           "SY_ADMIN",
                                           sqlStr.ToString().TrimEnd(','));                                            
                var a = conn.Execute(sql,
                new { UserName = model.UserName,UserPwd = model.UserPwd,SafeCode = model.SafeCode,RealName = model.RealName,CreateDate = model.CreateDate,LastDate = model.LastDate,LastIP = model.LastIP,CurrIP = model.CurrIP,LoginCount = model.LoginCount,GroupID = model.GroupID,UserID = model.UserID });
                conn.Close();
                return a > 0;
            }
        }
        #endregion        
 
        #region 获取数据
        /// <summary>
        /// 获取数据(一条)
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns></returns> 
        public Model.SY_ADMIN Get(int id)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var a = conn.Get<Model.SY_ADMIN>(id);
                conn.Close();
                return a;
            }
        }
        
        /// <summary>
        /// 获取数据(实体类条件)
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns> 
        public IList<Model.SY_ADMIN> Get(PredicateGroup pg)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();                                          
                var a = conn.GetList<Model.SY_ADMIN>(pg).ToList();
                conn.Close();
                return a;
            }
        }
        
        /// <summary>
        /// 获取数据(全部)
        /// </summary>
        /// <returns></returns>        
        public IList<Model.SY_ADMIN> GetAll()
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var a = conn.GetList<Model.SY_ADMIN>().ToList();
                conn.Close();
                return a;
            }
        }
        #endregion          

        #region 获取分页数据
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="fields">字段,如*或逗号分隔</param>
        /// <param name="orderField">排序,如id desc</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="whereStr">条件</param>
        /// <param name="totalRecord">总记录数</param>
        /// <param name="totalPage">总页数</param>
        /// <returns></returns> 
        public IList<Model.SY_ADMIN> GetPage(string fields, string orderField, int pageIndex,
            int pageSize, string whereStr, out int totalRecord, out int totalPage)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                totalRecord = conn.ExecuteScalar<int>(string.Format("SELECT count(1) FROM SY_ADMIN where 1=1 {0}", whereStr));
                totalPage = (totalRecord % pageSize != 0) ? (totalRecord / pageSize + 1) : totalRecord / pageSize;
                string sql = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS ROWID, "
                    + "{0} FROM {2} where 1=1 {3} ) AS t WHERE ROWID BETWEEN {4} AND {5}",
                    fields,
                    orderField,
                    "SY_ADMIN",
                    whereStr,
                    (pageIndex - 1) * pageSize + 1,
                    pageIndex * pageSize);
                var a = conn.Query<Model.SY_ADMIN>(sql).ToList();
                conn.Close();
                return a;
            } 
        }
        #endregion        
    }
}

class Program
    {
        //项目中建议尽量用强类型，虽然麻烦点，但后期好维护
        static void Main(string[] args)
        {
            string connStr = "Data Source=.;Initial Catalog=DapperDB;User ID=sa;Password=xxx";

            #region 强类型
            //public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                //无参查询
                //var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel"); 
                //带参查询
                var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel where Id in @id and Count>@count", new { id = new int[] { 1, 2, 3, 4, 5, 6 }, count = 1 });
                foreach (var item in qqModeList)
                {
                    Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
                }
            }
            #endregion

            #region 动态类型
            //逆天动态类型用的比较多[可能是MVC ViewBag用多了]
            public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    var qqModeList = conn.Query("select Id,Name,Count from QQModel").ToList();
            //    foreach (var item in qqModeList)
            //    {
            //        Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
            //    }
            //}
            #endregion

            #region 多映射
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    string sqlStr = @"select A.Id,A.Title,S.SeoKeywords from Article A
            //                    inner join SeoTKD S on A.SeoId=S.Id
            //                    where A.Id in @ids";
            //    conn.Open();
            //    var articleList = conn.Query(sqlStr, new { ids = new int[] { 41, 42, 43, 44, 45, 46, 47, 48 } });
            //    foreach (var item in articleList)
            //    {
            //        Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
            //    }
            //}
            #endregion

            #region 多返回值
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    string sqlStr = @"select Id,Title,Author from Article where Id = @id 
            //                      select * from QQModel where Name = @name 
            //                      select * from SeoTKD where Status = @status";
            //    conn.Open();
            //    using (var multi = conn.QueryMultiple(sqlStr, new { id = 11, name = "打代码", status = 99 }))
            //    {
            //        //multi.IsConsumed   reader的状态 ，true 是已经释放
            //        if (!multi.IsConsumed)
            //        {
            //            //注意一个东西，Read获取的时候必须是按照上面返回表的顺序 （article，qqmodel，seotkd）
            //            //强类型
            //            var articleList = multi.Read<Temp>();//类不见得一定得和表名相同
            //            var QQModelList = multi.Read<QQModel>();
            //            var SeoTKDList = multi.Read<SeoTKD>();

            //            //动态类型
            //            //var articleList = multi.Read();
            //            //var QQModelList = multi.Read();
            //            //var SeoTKDList = multi.Read();

            //            #region 输出
            //            foreach (var item in QQModelList)
            //            {
            //                Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
            //            }
            //            foreach (var item in SeoTKDList)
            //            {
            //                Console.WriteLine(item.Id + " | " + item.SeoKeywords);
            //            }
            //            foreach (var item in articleList)
            //            {
            //                Console.WriteLine(item.Author);
            //            }
            //            #endregion
            //        }

            //    }
            //}
            #endregion

            #region 增删改等
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    //增
            //    int count = conn.Execute("insert into Article values(@title,@content,@author,961,1,2,2,N'2015-11-23 11:06:36.553',N'2015-11-23 11:06:36.553',N'5,103,113',91,N'3,5,11',0,N'/Images/article/16.jpg')", new { title = "Title1", content = "TContent1", author = "毒逆天" });

            //    //改
            //    //int count = conn.Execute("update Article set Title=@title where Id=@id", new { title = "么么哒", id = 274 });
            //    if (count > 0)
            //    {
            //        Console.WriteLine(count + "条操作成功");
            //    }
            //}
            #endregion

            #region 存储过程
            查询
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    //参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可）
            //    //动态类型
            //    //var list = conn.Query("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
            //    //强类型
            //    var list = conn.Query<TitleAndKeyWords>("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
            //    foreach (var item in list)
            //    {
            //        Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
            //    }
            //}

            插入
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    int count = conn.Execute("usp_insertArticle", new { title = "Title11", content = "TContent1", author = "毒逆天" }, commandType: CommandType.StoredProcedure);
            //    if (count > 0)
            //    {
            //        Console.WriteLine(count + "条操作成功");
            //    }
            //}

            更新
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    int count = conn.Execute("usp_updateArticle", new { id = 276, title = "Dapper使用" }, commandType: CommandType.StoredProcedure);
            //    if (count > 0)
            //    {
            //        Console.WriteLine(count + "条操作成功");
            //    }
            //}
            #endregion

            Console.ReadKey();
        }
    }

转载于:https://my.oschina.net/smartsmile/blog/815444