关于 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)); //
} }
}
谢谢浏览！