基於.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:\TFS\EmailBackup\Easyliter\Test\model"/*路徑*/);

            
            //by sql
            var createCalss2 = e.CreateClassBySql("Sqlite.Model", @"D:\TFS\EmailBackup\Easyliter\Test\model1", "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