DbCRUD.ElasticSearch 1.1.0

Release Note

数据库连接及初始化 Database connection and initialization

 //数据库连接 Database connection
  ElasticSearchCRUD testdb = new ElasticSearchCRUD("http://127.0.0.1:9200");

插入数据 Insert data

    int id = testdb.Max<int>(tb_custormer, "ID");
    var customer = new CrudTestModel
    {
        ID = id + 1,
        Name = $"objectData{id}",
        Phones = new string[] { "80000", "90000" },
        FFloat = random.NextDouble(),
        IsActive = true,
        objData = new CrudTestModel { Name = "test", ID = id },
        Dic = new Dictionary<string, object>
        {
            { "Name", $"Embed_Data{id}" },
            { "DDate", DateTime.Now }
        }
    };          
    //**同步插入字典数据 Insert dictionary data synchronously
    var dic1 = new Dictionary<string, object>
    {
        { "Name", "CRUD" },
        { "Qty", random.Next(1, 10000)},
        { "QTY", random.Next(1, 10000)},
        { "DDATE", DateTime.Now }
    };
    var dicresult = testdb.Insert(autoIDData, dic1);
    var dic3 = new Dictionary<string, object>
    {
        { "ID",id.ToString() },
        { "Name", "String ID" },
    };
    testdb.Insert("iddata", dic3);
    //插入JSON数据
    string jsondata = JsonConvert.SerializeObject(dic1);
    var result12 = testdb.Insert(tb_jsondata, jsondata);
    //**sql命令插入
    var result13 = testdb.Insert($"insert into {sqldata}('name','date') values ('test1','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:dd")}')");
    Assert.IsTrue(result13.Status);

更新数据 update data

   //【实体对象更新】 更新_id=3的数据 ，字段大小写非敏感，数据大小写敏感。
    CrudTestModel crud = new CrudTestModel() { ID = 3, Name = "Update" };
    var id_result = testdb.UpDate(tb_custormer, crud,"3");
    Assert.IsTrue(id_result.Status);
    //【实体对象根据指定条件更新】 更新_id=1的数据 ，字段大小写非敏感，数据大小写敏感。
    CrudTestModel crud1 = new CrudTestModel() { Name = "UpatePart" };
    var where_result = testdb.UpDate(tb_custormer, crud1, "_id=0");
    Assert.IsTrue(where_result.Status);
    //【字典数据更新】更新_id=6的数据，字段和数据大小写敏感
    var updata = new Dictionary<string, object>
    {
        { "Name", "更新指定字段数据" },
        { "Qty", 600}
    };
    var upresult = testdb.UpDate(dictable, updata, "_id=6");
    Assert.IsTrue(upresult.Status);
    //更新后
    var getupdata = testdb.Find<Dictionary<string, object>>(dictable, "_id=6")?.FirstOrDefault();
    Assert.AreEqual(600, getupdata.GetValueOrDefault("Qty", 0).Object2DataType<int>());
    //【SQL语句更新】 更新_id=1的数据 ，mongodb对大小写敏感，所有sql语句中的表名和字段名大小写要与DB一致。
    var sql_up_result = testdb.UpDate("UPDATE dicdata SET Name='zzw',Qty=188 where _id=1");
    Assert.IsTrue(sql_up_result.Status);

更新及插入数据（数据存在更新，不存在插入） Update and insert data (there is an update of the data, but there is no insertion)

    //***** 更新或插入数据 Update or insert data
    var dic1 = new Dictionary<string, object>
    {
        { "_id", 2 },
        { "Name", "Inserts or updates a single piece of data" },
        { "Qty", 200},
        { "DDATE", DateTime.Now }
    };
    var result= testdb.Upsert(dictable, dic1);

   //*****Batch insert or update 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);

   //*****不存在就插入 Insert if it doesn't exist
    int maxid = testdb.Max<int>(dictable)+1;
    var dic4 = new Dictionary<string, object>
    {
        { "_id", maxid },
        { "Name", "根据_id不存在插入值" },
        { "Qty", 8000},
        { "DDATE", DateTime.Now }
    };
    testdb.Upsert(dictable, dic4);

查询数据 Query data

   ///查找id=2的数据
    var databyid = testdb.FindByID<Dictionary<string, object>>(dictable, 2);

    //查找id=2的数据,返回模型数据。 
    //【注意】模型和数据库中的列数不一致时，需要在模型上加 [BsonIgnoreExtraElements]特性，
    //        mongodb默认时UTC时间，如果要转本地时间，在模型时间属性上加[BsonDateTimeOptions(Kind =DateTimeKind.Local)]特性
    var modeldata = testdb.Find<CrudTestModel1>(tb_custormer, q=>q.ID==4)?.FirstOrDefault();

    ///查找id>2的数据,返回按DDATE排序,并排除dic列的最新一条数据
    var ondresult = testdb.FindOne<CrudTestModel>(tb_custormer, "_id>2", project: "!dic", sort: "!dDATE");
 
    //查找Qty>10的数据
    var wheredata = testdb.FindAndResult<Dictionary<string, object>>(dictable, "Qty>10");

    //【SQL语法,查找开头】，查找name中'Mongodb'开头的数据,条件不区分大小写，字段名称区分大小写
    var like_result = testdb.FindAndResult<Dictionary<string, object>>(autoIDData, "Name like'Mongodb%'");
   
    //【SQL语法,查找结尾】，查找name中'crud'开头的数据,条件不区分大小写，字段名称区分大小写
    var like_result1 = testdb.FindAndResult<Dictionary<string, object>>(autoIDData, "Name like'%crud'");
   
    //【SQL语法，包含】，查找name中包含'odb'的数据,条件不区分大小写，字段名称区分大小写
    var like_result2 = testdb.FindAndResult<Dictionary<string, object>>(autoIDData, "Name like'%odb%'");

    //【ES语法】，查找name中'Mongodbi'开头的数据,条件不区分大小写，字段名称区分大小写
    var bsonwheredata = testdb.FindAndResult<Dictionary<string, object>>(autoIDData, "name:Mongodb*");

    //****SQL语法和Mongodb查询语法不能混用，简单查询使用SQL语法，书写简单，复杂查询只能使用原生语法。
    //【简写语法】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "Qty>1", project: "!Name", sort: "!DDATE", pageindex: 1, pagecount: 10);
   
    //【返回DataTable】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
    var datatable_pagedata = testdb.GetPagingDataResult(autoIDData, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);
    
    //【多条件查询】分页查找_id>=6 and Qty>10,按DDATE倒序排序的数据，返回第一页10条数据。
    var mu_where = testdb.GetPagingData<Dictionary<string, object>>(dictable, "_id>=6 and Qty>10", sort: "!DDATE", pageindex: 1, pagecount: 10);
    
    //【ES语法日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<=当前时间的数据。
    var esdate_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, $"dDATE:[2022-06-01T00:00:00.000Z TO {DateTime.Now.ToUniversalTime():yyyy-MM-ddTHH:mm:ss.FFFZ}]", sort: "!DDATE", pageindex: 1, pagecount: 10);

    //【日期范围查询】查找DDATE>='2023-06-05 09:12:24' 和 DDATE<=当前时间的数据。
    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}'", sort: "!DDATE", pageindex: 1, pagecount: 10);

    var date_result1 = testdb.FindAndResult<Dictionary<string, object>>(dictable, $"DDATE>='2023-06-05 09:12' and DDATE<='{DateTime.Now:yyyy-MM-dd HH:mm}'", "Name,DDATE", sort: "!DDATE");
  
    //【in查询】查找Qty=200和300的数据。
    var in_result = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Qty in(200,300)", sort: "!DDATE", pageindex: 1, pagecount: 10);

    //【in模糊查询】查找Name=Batch insert和data结尾的数据。
    var in_fuzzy = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Name in('Batch insert','%data')", sort: "!DDATE", pageindex: 1, pagecount: 10);

    //【正则表达式查询】查找Name=Batch开头的数据。
    var in_regex = testdb.GetPagingDataAndResult<Dictionary<string, object>>(dictable, "Name reg'.+data'", sort: "!DDATE", pageindex: 1, pagecount: 10);
  
    //sql语句查找_id=1的数据
    string sqlcmd = $"select !_id from {dictable} where Name='zzw'";
    var sqldata = testdb.Find<Dictionary<string, object>>(sqlcmd);

    //【委托查询】查找_id>=6 and Qty>10的数据。数据在委托中返回，方便进行数据处理
    var action_result = testdb.GetPagingDataAction<CrudTestModel1>(tb_custormer, "_id>=1 and _id<=10", datalist =>
    {
        double sum = datalist.Sum(s => s.FFloat);
        Assert.IsTrue(sum > 0);
    });

删除数据 delete data

    var databyid1 = testdb.FindByID<Dictionary<string, object>>(dictable,9);
    //**删除ID=8的数据
    var result = testdb.Delete(dictable, 9);
    var databyid = testdb.FindByID<Dictionary<string, object>>(dictable, 9);

           
    object strid = "10";
    var result1 = testdb.Delete("IDDATA", strid);

    var result2 = testdb.Delete("IDDATA", "9");

    //**删除qty>10的数据
    var wherresult = testdb.Delete(dictable, "_id>=10");

    //**使用sql语句删除ID = 7的数据
    string sql = $"delete from {dictable} where _id=7";
    var sqlresult = testdb.Delete(sql);

Key-Value增删改查

//单值key-value
string key = "date";
var v = DateTime.Now;
var result = testdb.SaveKeyValue(key, v);
var getv = testdb.GetKeyValue<DateTime>(key);
var Data = testdb.FindByID<CrudTestModel>(tb_custormer, 6);
if (Data != null)
{
    //单对象
    string objkey = "objectkeyvalue";
    var obj_result = testdb.SaveKeyValue(objkey, Data);

    Assert.IsTrue(testdb.KeyValueExists(objkey));
    var objv = testdb.GetKeyValue<CrudTestModel>(objkey);

    //数组列表
    string arraykey = "array_keyvalue";
    var list = new List<CrudTestModel>();
    list.Add(Data);
    objv.DDATE = DateTime.Now;
    list.Add(objv);
    var arraykey_result = testdb.SaveKeyValue(arraykey, list);

    var arraykeyv = testdb.GetKeyValue<IEnumerable<CrudTestModel>>(arraykey);

}  }

消息事件绑定（可日志输出）

    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
    {
    
        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; }
        //[PropertyName("my_field")]
        public object objData { get; set; }
        public double? FFloat { get; set; } = 0.118;

        public DateTime? DDATE { get; set; } = DateTime.Now;

    }