Dapper框架

Dapper是一个轻量级的ORM框架，它扩展了IDbConnection接口的功能，适合多种数据库。

先定义一个类，注意一定要使用属性

class Student { public Guid ID { get; set; } public string Name { get; set; } }

Sql语句，注意GUID，必须设置为char(36)不能设置为varchar(36).

CREATE TABLE `Student` ( `Name` varchar(63), `ID` char(36) NOT NULL, PRIMARY KEY(ID) );

连接

public static SQLiteConnection GetDBConnection() { try { SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder(); sb.DataSource = System.Configuration.ConfigurationManager.ConnectionStrings["dbPath"].ConnectionString; SQLiteConnection conn = new SQLiteConnection(sb.ToString()); return conn; } catch (Exception ex) { throw ex; } }

由于SQLiteConnection继承了IDisposable接口，于是每次调用都可以用

using (SQLiteConnection con = DBHelper.GetDBConnection()) { //代码写这 }

insert

//GUID在数据库中我是以char(36)保存的，因此不能保存guid类型，应该转换为string类型 string sqlStr = "insert into Student(ID,Name) Values(@ID,@Name)"; con.Execute(sqlStr, new { ID= Guid.NewGuid().ToString("N") , Name = "张三" });

当然了，也可以插入已经实例化的Student类

Student student = New Student（）{ ID= Guid.NewGuid().ToString("N") , Name = "张三" }; string sqlStr = "insert into Student(ID,Name) Values(@ID,@Name)"; con.Execute(sqlStr, student );

delete

string sqlStr = "delete from Student WHERE id =@id"; con.Execute(sqlStr , student );

update

string sqlStr = "update Student set Name=@name WHERE id =@id"; con.Execute(sqlStr , student );

select

string sqlStr = "select ID,Name from Student"; List<Student> studentLists = con.Query<Student>(sqlStr).ToList(); //注意：会报错，类中的guid类型跟数据库中的string类型不符合

应该改sql语句加上这个Student类

string sqlStr = "select ID as IDString,Name from Student";//通过别名将值保存为IDString List<Student> studentLists = con.Query<Student>(sqlStr).ToList(); class Student { public Guid ID { get; set; } public string Name { get; set; } private string IDString//设置为private比较安全，除了select以外没有需要这个属性的地方 { get { return ID.ToString("N"); } set { ID = new Guid(value); } } }

抽象类的select

类为抽象类

abstract class Student { public Guid ID { get; set; } public string Name { get; set; } private string IDString//设置为private比较安全，除了select以外没有需要这个属性的地方 { get { return ID.ToString("N"); } set { ID = new Guid(value); } } } class A:Student { } class B:Student { }

如果按照上面select的方法会说抽象类无法实例化，那么就下面这个方法解决，先增加两个扩展方法

public static Student ConvertToStudent(object value) { var dapperRowProperties = value as IDictionary<string, object>; //从查询来的Name中来判断实例化为A还是B switch (dapperRowProperties["Name"].ToString()) { case “A”: return GetObject<A>(dapperRowProperties); case "B": return GetObject<B>(dapperRowProperties); default: return null; } } private static T GetObject<T>(IDictionary<string, object> dict) { Type type = typeof(T); var obj = Activator.CreateInstance(type); foreach (var kv in dict) { //反射，非public属性不会被找到,所以要传入BindingFlags参数来获取protected属性 type.GetProperty(kv.Key,System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).SetValue(obj, kv.Value); } return (T)obj; }

然后就可以查询了

string sqlStr = "select ID as IDString,Name from Student"; List<Student> studentLists = con.Query(sqlStr).Select(ConvertToStudent).ToList();

保存到datatable

添加一个扩展方法

public static DataTable ToDataTable(this IEnumerable<dynamic> items) { var data = items.ToArray(); if (data.Count() == 0) return null; var dt = new DataTable(); foreach (var key in ((IDictionary<string, object>)data[0]).Keys) { dt.Columns.Add(key); } foreach (var d in data) { dt.Rows.Add(((IDictionary<string, object>)d).Values.ToArray()); } return dt; }

然后就能查找出datatable类型的数据了

string sqlStr = "select ID as IDString,Name from Student"; DataTable dt = DBHelper.ToDataTable(con.Query(sqlStr));

其实还有些多表查询，多结果查询等没讲，等之后再补充