DbCRUD.LiteDB 1.6.4

DbCRUD

介绍

开发项目过程中，不同的开发项目可能会用到不同的数据库，初次接触一门新的数据库，要投入不少的学习成本和踩不少的坑。本库就是基于此背景开发，集 SQL 和 NOSQL 常用数据库增删改查，并尽量保持一样的输入输出，减少你学习成本投入。我踩过的坑，不用你再踩。

软件架构

使用 c# .core(现在统一建.net)开发，可自行实现 IDbCRUD 接口自由扩展。

安装教程

1. :tw-1f1f1: 安装 DbCRUD.LiteDB
   Install-Package DbCRUD.LiteDB 安装包

使用说明

LiteDB

• 数据库连接及初始化

  //数据库连接 Database connection
  IDbCRUD testdb = new LiteDbCRUD(@"filename=CRUDTestDB.db;Mode=Shared"); //共享模式访问数据库
  //IDbCRUD testdb = new LiteDbCRUD(@"filename=CRUDTestDB.db");//相对路径([程序目录]\DATA)
  
  //IDbCRUD testdb = new LiteDbCRUD(@"filename=C:\CRUDTestDB.db");//绝对路径指定数据库保存路径
  

插入数据

    //**同步插入对象数据 Insert object data synchronously 
    var customer = new CrudTestModel
    {
        //Id = id + 50, ////实体类，ID不赋值，默认根据数据类型自动整数编号，支持int,long,objectid
        Name = "objectData",
        Phones = new string[] { "80000", "90000" },
        IsActive = true,
        Dic = new Dictionary<string, object>
        {
            { "Name", "Embed_Data" },
            { "DDate", DateTime.Now }
        },
        FFloat = random.NextDouble(),
    };
  var result = testdb.Insert(tb_custormer, customer);
 //**同步插入字典数据 Insert dictionary data synchronously
    var dic1 = new Dictionary<string, object>
    {
        //{ "_id", 1 },//***如果不指定ID,插入时会自动编一个objectid的唯一ID
        { "Name", "LitedbCRUD" },
        { "Qty",random.Next(1,10000) },
        { "DDate", DateTime.Now }
    };
    var result11 = testdb.Insert(autoIDData, dic1);

    //插入JSON数据
    string jsondata = JsonConvert.SerializeObject(dic1);
    var result12 =testdb.Insert(tb_jsondata, jsondata);
    //**sql命令插入
     testdb.Insert($"insert into {sqldata}('name','date') values ('test','{DateTime.Now.ToString("yyyy-MM-dd")}'");
    //**批量插入列表 Batch insert
    List<Dictionary<string, object>> listdata = new List<Dictionary<string, object>>();
    int maxid = testdb.Max<int>(dictable);
    for (int i = 0; i < 10; i++)
    {
        maxid++;
        var dic2 = new Dictionary<string, object>
        {
            { "_id",maxid },
            { "Name", "Batch insert" },
            { "Qty",random.Next(1,10000) },
            { "DDate", DateTime.Now }
        };
        listdata.Add(dic2);
    }
    var listResult= testdb.Insert(dictable, listdata);

更新数据

    var updata = new Dictionary<string, object>
    {
        { "Name", "更新指定字段数据" },
        { "Qty", 300}
    };
    var upresult = testdb.UpDate(dictable, updata, "_id=2");   //更新_id=2的数据

更新及插入数据（数据存在更新，不存在插入）

    //** 更新或插入数据 Update or insert data
    var dic1 = new Dictionary<string, object>
    {
        { "_id", 2 },
        { "Name", "Insert or update" },
        { "Qty", 200},
        { "DDate", DateTime.Now }
    };
    var result= testdb.Upsert(dictable, dic1);
    Assert.IsTrue(result.Stutas);
    //** Batch insert or update
    var dic3 = new Dictionary<string, object>
    {
        { "_id", 3 },
        { "Name", "Batch insert or update" },
        { "Qty", 300},
        { "DDATE", DateTime.Now }
    };
    List<Dictionary<string,object>> listdata=new List<Dictionary<string, object>> { dic3,dic1};
    var listresult = testdb.Upsert(dictable, listdata);

查询数据

   //查找id=2的数据
   var databyid = testdb.FindByID<Dictionary<string, object>>(dictable,2);
   //查找Qty>10的数据
   var wheredata = testdb.Find<Dictionary<string, object>>(dictable, "Qty>10");
   //sql语句查找的数据
   string sqlcmd = $"select * from {dictable}";
   var sqldata = testdb.Find<Dictionary<string, object>>(sqlcmd);
   //查找id>2的数据,返回按DDATE排序,并排除dic列的最新一条数据
    var ondresult = testdb.FindOne<CrudTestModel>(tb_custormer, "_id>2", project: "!dic", sort: "!DDATE");
    //【SQL语法模糊查询】，查找name中'Litedb'开头的数据,不区分大小写
    var wheredata1 = testdb.Find<Dictionary<string, object>>(autoIDData, "Name like'Litedb%'");
    //【简写语法】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);

    //【SQL语法】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var mgdb_pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "Qty>10", project: "name,ddate", sort: "DDATE desc", pageindex: 1, pagecount: 10);
  
    Assert.AreEqual(pagedata.Count(), mgdb_pagedata.Count());
    //【返回DataTable】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var datatable_pagedata = testdb.GetPagingData(autoIDData, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);
 
    //【多条件查询】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var mu_where = testdb.GetPagingData<Dictionary<string, object>>(dictable, "_id>=6 and Qty>10", sort: "!DDATE", pageindex: 1, pagecount: 10);
   
    //【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
    var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");
    
    //【SQL语法in查询】查找Qty=200和300的数据。
    var sql_in_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Qty in(200,300)");
    
    //【LiteDB in查询】查找Qty=200和300的数据。
    var lite_in_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Qty in[200,300]");
    
    //【in模糊查询】查找Name=Batch insert和data结尾的数据。不支持模糊匹配。
    var in_fuzzy = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Name in('Batch insert','data')");
    Assert.IsNotNull(in_fuzzy.Data);
    //【委托查询】查找_id>=6 and Qty>10的数据。数据在委托中返回，方便进行数据处理
    var action_result = testdb.GetPagingDataAction<CrudTestModel1>(dictable, "_id>=6 and Qty>10", datalist => 
    {
        double sum = datalist.Sum(s => s.FFloat);
                
    });

删除数据

   //**删除_id=2的数据
   var result = testdb.Delete(dictable,2);
   //** 删除_id等于'ID82'的记录 注意_id列是字符串时，如果要删除ID值，必须具名参数，即增加'id:'.否则会当成条件，导致删除失败
    var result22 = testdb.Delete(tb_stringID,id:"ID82");
   //**删除qty<30的数据
   var wherresult = testdb.Delete(dictable, "Qty<30");
   //**使用sql语句删除_id=30的数据
   string sql = $"delete {dictable} where _id=30";
   var sqlresult = testdb.Delete(sql);

//单值key-value string key = "test"; var v = DateTime.Now; //保存 var result = testdb.SaveKeyValue(key, v); //判断是否存在 bool isExists= testdb.KeyValueExists(key) //获取 var getv = testdb.GetKeyValue<DateTime>(key); //删除 bool ok= testdb.DelKeyValue(key) //对象key-value var crud_result = testdb.FindAndResult<CrudTestModel>(tb_custormer, $"{testdb.IdName}=1"); if (crud_result.Data != null) { //单对象 string objkey = "object_keyvalue"; var obj_result = testdb.SaveKeyValue(objkey, crud_result.Data.FirstOrDefault()); Assert.IsTrue(obj_result.State); Assert.IsTrue(testdb.KeyValueExists(objkey)); var objv = testdb.GetKeyValue<CrudTestModel>(objkey); Assert.IsTrue(objv.ID>0); //数组列表 string arraykey = "array_keyvalue"; var arraykey_result = testdb.SaveKeyValue(arraykey, crud_result.Data); Assert.IsTrue(arraykey_result.State); Assert.IsTrue(testdb.KeyValueExists(arraykey)); var arraykeyv = testdb.GetKeyValue<IEnumerable<CrudTestModel>>(arraykey); Assert.IsTrue(arraykeyv.Count()>0); }

消息事件绑定

    public DbTest() {
    t estdb.Message += Testdb_Message;
    }

    private void Testdb_Message((string Message, string Level, DateTime Time) obj)
    {
       Debug.WriteLine($"{obj.Time}|{obj.Level}|{obj.Message}");
    }

增删改查系列包

• 🍁 LiteDB: Install-Package DbCRUD.LiteDB
• 🍃 MongoDB: Install-Package DbCRUD.MongoDB
• 🍀 Mysql: Install-Package DbCRUD.Mysql
• 🌿 Sqlite: Install-Package DbCRUD.Sqlite
• 🍂 SQL SERVER: Install-Package DbCRUD.SqlServer

一致的增删改查语法

LiteDB

IDbCRUD testdb = new LiteDbCRUD(@"filename=CRUDTestDB.db");
//【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");

MongoDB

IDbCRUD testdb =new MongoDbCRUD("mongodb://localhost:27017/testdb");
//【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");

Mysql

IDbCRUD testdb = new MysqlCRUD(@"Server=127.0.0.1;Database=testdb;Uid=root;Pwd=;");

//【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");

Sqlite

IDbCRUD testdb = new SqliteCRUD($@"Data Source=sqlitedb.db; Cache=Shared")

//【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");

SQL SERVER

IDbCRUD testdb = new SqlServerCRUD(@"Data Source=xxx;Initial Catalog=xxx;User ID=sa;Password=xxx;Encrypt=True;TrustServerCertificate=True;");
//【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<='2023-06-05 13:28:48'的数据。
var date_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12:24' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");

实体模型

 public class CrudTestModel
    {
        [BsonId]
        public int ID { get; set; }
        public string Name { get; set; }
        public string[] Phones { get; set; }
        public bool IsActive { get; set; }
        public Dictionary<string, object> Dic { get; set; }
        public double FFloat { get; set; } = 0.118;
        public DateTime DDATE { get; set; } = DateTime.Now;

    }