一小段 Dapper 的简单示例

关于 Dapper 的介绍，请参考：Dapper - 一款轻量级对象关系映射（ORM）组件，DotNet 下

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

namespace SqlMapperDemo.ConApp
{
    public interface IUser
    {
        string Key { get; set; }
        string Name { get; set; }
    }

    public class UserInfo : IUser
    {
        public string Key { get; set; }
        public string Name { get; set; }
    }

    public class DbContext
    {
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;

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

        public static int GetAccountCount(string key)
        {
            using (var db = GetConnection())
            {
                return db.Query<int>("select count(*) from Account where UserKey = @key", new { key }).FirstOrDefault();
            }
        }

        public static void Add(IUser user)
        {
            using (var db = GetConnection())
            {
                db.Execute("if not exists(select 1 from Account where UserKey = @key) " +
                           "insert into Account(UserKey, UserName) values (@key, @name);" +
                           "else " +
                           "update Account set UserName = @name, deleted = null where UserKey = @key;",
                    new
                    {
                        name = user.Name,
                        key = user.Key
                    });
            }
        }

        public static void Remove(string key)
        {
            using (var db = GetConnection())
            {
                db.Execute("delete Account where UserKey = @key",
                    new
                    {
                        key
                    });
            }
        }

        public static IList<IUser> GetList()
        {
            using (var db = GetConnection())
            {
                return db.Query<UserInfo>("select UserKey as [Key], UserName as Name from Account " +
                                      "where 1=1").ToList().Cast<IUser>().ToList();
            }
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string userKey = Guid.NewGuid().ToString();

            // demo-01 - 得到
            Console.WriteLine("accountCount：" + DbContext.GetAccountCount(userKey)); // 0

            // demo-02 - 新增
            DbContext.Add(new UserInfo() { Key = userKey, Name = "小张" });

            // demo-03 - 再次得到
            Console.WriteLine("accountCount：" + DbContext.GetAccountCount(userKey)); // 1

            // demo-04 - 得到所有记录
            foreach (IUser userItem in DbContext.GetList())
            {
                Console.WriteLine("userKey: " + userItem.Key + ", userName: " + userItem.Name);
                //userKey: f0cbf560-9c41-41a7-9ccf-31026adbfa91, userName: 小张
            }

            // demo-05 - 删除
            DbContext.Remove(userKey);

            // demo-06 - 删除后，得到
            Console.WriteLine("accountCount：" + DbContext.GetAccountCount(userKey)); // 0
        }

        
    }
}

 

谢谢浏览！