基于.NET C#的 sqlite 数据库 ORM 【Easyliter】

 

因为工作原因经常用到SQLITE数据库，但又找不到好用的ORM所以自个整理了一个简单好用的轻量极ORM框架：Easyliter

 

功能介绍：

1、支持SQL语句操作

2、支持 List<T>和DataTable两种返回格式

3、支持拉姆达表达示进行数据查询和分页

4、内置创建实体类函数

5、依赖于 System.Data.SQLite

使用方法如下：

 

复制代码using Sqlite.Model; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Easyliter; namespace test { class Program { static void Main(string[] args) { var connstr = "DataSource=" + System.AppDomain.CurrentDomain.BaseDirectory + "mapping.sqllite"; //reference System.Data.SQLite //引用 System.Data.SQLite Client e = new Client(connstr); //Generate entity classes from a database //从数据库生成实体类 //CreateClassFile(e); //Delete operation //删除操作 DeleteData(e); //update operation //更新操作 UpdateData(e); //insert operation //插入数据 InsertData(e); //Search operation //查询操作 Search(e); //基本操作 //Basic operation BasicOperation(e); } //search operation //查询操作 private static void Search(Client e) { ////By sql List<Product> list = e.Select<Product>("select * from product where id>@num", new { num = 100 }); //No parameter List<Category> list2 = e.Select<Category>(); //Single parameter List<Product> list3 = e.Select<Product>(x => x.id > 200); //Multiple parameter List<Product> list4 = e.Select<Product>(x => x.id > 200, x => x.sku == "skx" || x.sku == null); //By page int count = 0; List<Product> list5 = e.SelectPage<Product>(1, 10, ref count, " id desc", x => x.id > 10,//条件1 x => true);//条件2 ...条件N //Query extenions var extObj = e.Query<Product>().Where(x => x.id > 10).Where(x => x.id > 2).Select("id,sku") .OrderBy(El_Sort.asc, "id") .OrderBy(El_Sort.desc, "sku").Take(100); //get list var list6 = extObj.ToList(); //get dataTable var dataTable = extObj.ToDataTable(); //get single int num = 500; var item = e.Query<Product>().Where(c=>c.id==num).Single(); //get first var first = extObj.First(); //get sql string sql = extObj.ToSql(); //left join List<V_Product> VProductList = e.Query<Product>() .Join<Product, Category>(false /* true is inner join*/ ) .On(" Product.category_id=Category.id ") .OrderBy(El_Sort.desc, " Product.id") .Select("Product.*,Category.name as category_name").JoinWhere("Product.id>300").ToNewList<Product, V_Product>(); //left join string VProductSql = e.Query<Product>() .Join<Product, Category>(false /* true is inner join*/ ) .On(" Product.category_id=Category.id ") .OrderBy(El_Sort.desc, " Product.id") .Select("Product.*,Category.name as category_name").JoinWhere("Product.id>300").ToSql(); } //insert operation //插入数据 private static void InsertData(Client e) { //添加 Product p = new Product() { category_id = 2, sku = "sku", title = "title" }; e.Insert<Product>(p); } //update operation //更新操作 private static void UpdateData(Client e) { e.Update<Product>(new { sku = "AGA123101", category_id = 1 } /*update columns*/, new { id = 434 }/*where columns*/); } //Delete operation //删除操作 private static void DeleteData(Client e) { e.Delete<Product>(100);//primary key e.Delete<Product>(new int[] { 1, 2, 3 }); } //Generate entity classes from a database //从数据库生成实体类 private static void CreateClassFile(Client e) { //by database var createCalss1 = e.CreateClass("Sqlite.Model"/*命名空间*/, @"D:TFSEmailBackupEasyliterTestmodel"/*路径*/); //by sql var createCalss2 = e.CreateClassBySql("Sqlite.Model", @"D:TFSEmailBackupEasyliterTestmodel1", "viewproduct", "select id,sku from product "); } //基本操作 //Basic operation private static void BasicOperation(Client e) { var dt = e.GetDataTable("select * from product"); var intVal = e.GetInt("select count(*) from product"); var stringVal = e.GetString("select sku from product where id=500 "); //e.ExecuteNonQuery("inset into .."); } } }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
using Sqlite.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Easyliter;
namespace test
{
class Program
{
static void Main(string[] args)
{
var connstr = "DataSource=" + System.AppDomain.CurrentDomain.BaseDirectory + "mapping.sqllite";
//reference System.Data.SQLite
//引用 System.Data.SQLite
Client e = new Client(connstr);
//Generate entity classes from a database
//从数据库生成实体类
//CreateClassFile(e);
//Delete operation
//删除操作

DeleteData(e);
//update operation
//更新操作

UpdateData(e);
//insert operation
//插入数据

InsertData(e);
//Search operation
//查询操作

Search(e);
//基本操作
//Basic operation

BasicOperation(e);
}
//search operation
//查询操作
private static void Search(Client e)
{
////By sql
List<Product> list = e.Select<Product>("select * from product where
id>@num", new { num = 100 });
//No parameter
List<Category> list2 = e.Select<Category>();
//Single parameter
List<Product> list3 = e.Select<Product>(x => x.id > 200);
//Multiple parameter
List<Product> list4 = e.Select<Product>(x => x.id > 200,
x => x.sku == "skx" || x.sku == null);
//By page
int count = 0;
List<Product> list5 = e.SelectPage<Product>(1, 10, ref count, " id
desc",
x => x.id > 10,//条件1
x => true);//条件2 ...条件N
//Query extenions
var extObj = e.Query<Product>().Where(x => x.id > 10).Where(x => x.id > 2).Select("id,sku")
.OrderBy(El_Sort.asc, "id")
.OrderBy(El_Sort.desc, "sku").Take(100);
//get list
var list6 = extObj.ToList();
//get dataTable
var dataTable = extObj.ToDataTable();
//get single
int num = 500;
var item = e.Query<Product>().Where(c=>c.id==num).Single();
//get first
var first = extObj.First();
//get sql
string sql = extObj.ToSql();
//left join
List<V_Product> VProductList = e.Query<Product>()
.Join<Product, Category>(false /* true is
inner join*/ )
.On(" Product.category_id=Category.id ")
.OrderBy(El_Sort.desc, " Product.id")
.Select("Product.*,Category.name as category_name").JoinWhere("Product.id>300").ToNewList<Product, V_Product>();
//left join
string VProductSql = e.Query<Product>()
.Join<Product, Category>(false /* true is
inner join*/ )
.On(" Product.category_id=Category.id ")
.OrderBy(El_Sort.desc, " Product.id")
.Select("Product.*,Category.name as category_name").JoinWhere("Product.id>300").ToSql();
}
//insert operation
//插入数据
private static void InsertData(Client e)
{
//添加
Product p = new Product()
{
category_id = 2,
sku = "sku",
title = "title"
};
e.Insert<Product>(p);
}
//update operation
//更新操作
private static void UpdateData(Client e)
{
e.Update<Product>(new { sku = "AGA123101", category_id = 1 } /*update columns*/, new { id = 434 }/*where columns*/);
}
//Delete operation
//删除操作
private static void DeleteData(Client e)
{
e.Delete<Product>(100);//primary key
e.Delete<Product>(new int[] { 1, 2, 3 });
}
//Generate entity classes from a database
//从数据库生成实体类
private static void CreateClassFile(Client e)
{
//by database
var createCalss1 = e.CreateClass("Sqlite.Model"/*命名空间*/, @"D:TFSEmailBackupEasyliterTestmodel"/*路径*/);
//by sql
var createCalss2 = e.CreateClassBySql("Sqlite.Model", @"D:TFSEmailBackupEasyliterTestmodel1", "viewproduct", "select id,sku from product ");
}
//基本操作
//Basic operation
private static void BasicOperation(Client e)
{
var dt = e.GetDataTable("select * from product");
var intVal = e.GetInt("select count(*) from product");
var stringVal = e.GetString("select sku from product where id=500 ");
//e.ExecuteNonQuery("inset into ..");

}
}
}

 

 

源码地址：http://git.oschina.net/sunkaixuan/easyliter