Dapper试用

以下代码摘自imfunny的<给力分享新的ORM => Dapper> http://www.cnblogs.com/imfunny/archive/2011/09/16/2178829.html

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Dapper;
namespace ConsoleApplication1
{
    class Program
    {
        public static readonly string sqlconnectionString = "Data Source=localhost;Initial Catalog=Test;User Id=admindsl;Password=a4w8c3k5;";
        static void Main(string[] args)
        {
            SqlConnection connection = GetOpenConnection(sqlconnectionString);
            //插入100000条数据到数据库
            //for (int i = 0; i < 100000; i++)
            //{
            //    connection.Execute("INSERT INTO dog (Age,Name,Weight) VALUES (@age,@name,@Weight)",
            //      new { @age = i, @name = Guid.NewGuid().ToString(), @Weight = i });//System.Guid.NewGuid().ToString()全球唯一标识符 (GUID) 是一个字母数字标识符，用于指示产品的唯一性安装。
            //}

            //获取单条记录
            //var edog = connection.Query<Dog>("select * from dog where id = @Id", new { Id = 2 }).Single<Dog>();
            var edog = connection.Query<Dog>("select * from dog where id = @Id", new { Id = 3 }).SingleOrDefault<Dog>();
            Console.WriteLine("{0} : {1}", edog.Name, edog.Id);
            Console.WriteLine();

            //获取前十条记录
            IList<Dog> dogs = connection.Query<Dog>("select * from dog where id <= 10", null).ToList();
            foreach (Dog dg in dogs)
            {
                Console.WriteLine("{0} : {1}", dg.Name, dg.Id);
            }


            Console.Read();
        }
        public static SqlConnection GetOpenConnection(string x)
        {
            SqlConnection connection = new SqlConnection(x);
            connection.Open();
            return connection;
        }
    }
    public class Dog
    {
        public int Age { get; set; }

        public int Id { get; set; }

        public string Name { get; set; }

        public int Weight { get; set; }

        public int IgnoredProperty
        {
            get
            {
                return 1;
            }
        }

    }

}

Dapper保持目前的API,保持精确的定位是非常正确, 非常必须的.否则又是一个大而全的重型机器,失去它的优势.
如Connection, Transaction不由Dapper内部管理, 这个定位真是太绝了

 

想写linq的话使用这个扩展。地址这里http://sqlinq.codeplex.com/
SQLinq.Dapper
语法如下。

IEnumerable<Person> data = null;
using(IDbConnection con = GetDbConnection())
{
    con.Open();
    data = con.Query(
        from p in SQLinq<Person>()
        where p.FirstName.StartsWith("C") && p.Age > 21
        orderby p.FirstName
        select p
    );
    con.Close();
}

单表的映射是这样的Query<Dog>(sqlquery,parmar);
多表的是这样Query<Dog，Content,Dog>(sqlquery,(Content,Dog) => Dog.dd = Content.id，。。。。);

 

更加简化的事务机制

ar trans = conn.BeginTransaction(); 
              conn.Execute(query, info);
              trans.Commit();

数据库的关闭

const string query = "DELETE FROM `ex_admin` WHERE adminid = {0};";
           using (var conn = Database.DbService())
           {
               conn.Execute(string.Format(query, adminid), null);
           }

测试速度代码一

private void TestDapper()
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("SELECT EmployeeID,EmployeeNO,EmployeeName,DepartmentID,LoginNo,LoginPWD,IsLogin,GroupID,IsDeleted,Operator,InputDate,EditDate ");
    strSql.Append("FROM Employee ");
    IList<EmployeeInfo> list;
    IDbConnection conn = new SqlConnection(connectionString);
 
    conn.Open();
    sw.Reset();
    sw.Start();
    list = conn.Query<EmployeeInfo>(strSql.ToString(), null).ToList();
    conn.Close();
    sw.Stop();
 
    MessageBox.Show(list.Count.ToString());
}
 
private void TestPeta()
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("SELECT EmployeeID,EmployeeNO,EmployeeName,DepartmentID,LoginNo,LoginPWD,IsLogin,GroupID,IsDeleted,Operator,InputDate,EditDate ");
    strSql.Append("FROM Employee ");
    PetaPoco.Database mcc = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
    sw.Reset();
    sw.Start();
    IList<EmployeeInfo> list = mcc.Query<EmployeeInfo>(strSql.ToString(), null).ToList();
    sw.Stop();
    MessageBox.Show(list.Count.ToString());
 
}