DbCRUD.LiteDB 1.8.2

dotnet add package DbCRUD.LiteDB --version 1.8.2                
NuGet\Install-Package DbCRUD.LiteDB -Version 1.8.2                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="DbCRUD.LiteDB" Version="1.8.2" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DbCRUD.LiteDB --version 1.8.2                
#r "nuget: DbCRUD.LiteDB, 1.8.2"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install DbCRUD.LiteDB as a Cake Addin
#addin nuget:?package=DbCRUD.LiteDB&version=1.8.2

// Install DbCRUD.LiteDB as a Cake Tool
#tool nuget:?package=DbCRUD.LiteDB&version=1.8.2                

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.Status);
    //** 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倒序排序的数据,返回第一�?0条数据�?
    var pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);

    //【SQL语法】分页查找Qty>10,返回除_id列,按DDATE倒序排序的数据,返回第一�?0条数据�?
    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倒序排序的数据,返回第一�?0条数据�?
    var datatable_pagedata = testdb.GetPagingData(autoIDData, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);
 
    //【多条件查询】分页查找Qty>10,返回除_id列,按DDATE倒序排序的数据,返回第一�?0条数据�?
    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�?00的数据�?
    var sql_in_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Qty in(200,300)");
    
    //【LiteDB in查询】查找Qty=200�?00的数据�?
    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

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;

    }
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 is compatible.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.8.2 74 6/24/2024
1.8.1 108 4/27/2024
1.8.0 102 4/8/2024
1.7.4 104 4/6/2024
1.7.3 126 3/22/2024
1.7.2 122 3/21/2024
1.7.1 590 11/13/2023
1.7.0 468 11/10/2023
1.6.9 508 10/31/2023
1.6.8 594 9/3/2023
1.6.7 610 8/27/2023
1.6.6 725 8/4/2023
1.6.5 734 7/31/2023
1.6.4 676 7/27/2023
1.6.3 696 7/24/2023
1.6.2 711 7/23/2023
1.6.1 740 7/20/2023
1.6.0 714 7/14/2023
1.5.9 732 7/9/2023
1.5.8 704 7/7/2023
1.5.7 727 7/6/2023
1.5.6 753 6/26/2023
1.5.5 721 6/18/2023
1.5.4 703 6/13/2023
1.5.3 698 6/11/2023
1.5.1 719 6/9/2023
1.5.0 728 6/8/2023
1.4.0 725 6/6/2023
1.3.0 723 5/14/2023
1.2.4 745 5/11/2023
1.2.3 747 5/10/2023
1.2.2 721 5/8/2023
1.2.1 746 5/7/2023
1.2.0 729 5/6/2023
1.1.0 709 5/4/2023
1.0.0 712 4/25/2023

优化数据库连接