DbCRUD.Mysql 1.3.1

数据库连接及初始化

 //数据库连接
  IDbCRUD testdb = new MysqlCRUD(@"Server=127.0.0.1;Database=testdb;Uid=root;Pwd=;");

插入数据

    int dbcount = testdb.TableExists(tb_custormer) ? testdb?.Count(tb_custormer) ?? 0 : 0;
    //**同步插入对象数据
    var customer = new CustomerViwe
    {
        Id = dbcount + 1, ////实体类，ID不赋值，默认根据数据类型自动整数编号，支持int,long,objectid
        Name = "对象插入",
        Phones = new string[] { "80000", "90000" },
        IsActive = true,
    };
    var result = testdb.Insert(tb_custormer, customer);
    Assert.IsTrue(result.Stutas);
    int count=testdb?.Count(tb_custormer) ??0;
    Assert.IsTrue(count > 0);
    var findresult = testdb.FindAndResult<CustomerViwe>(tb_custormer, "Name='对象插入'");
    Assert.IsTrue(findresult.ResultData?.Any());
    //**同步插入字典数据
    var dic1 = new Dictionary<string, object>
    {
        //{ "_id", 1 },//***如果不指定ID,插入时会自动编一个int的唯一ID
        { "Name", "mysql自动编号插入" },
        { "Qty", 19},
        { "DDate", DateTime.Now }
    };
    var result11 = testdb.Insert(autoIDData, dic1);
    Assert.IsTrue(result.Stutas);
    int count1 = testdb?.Count(autoIDData) ?? 0;
    Assert.IsTrue(count1 > 0);
    //插入JSON数据
    string jsondata = JsonConvert.SerializeObject(dic1);
    var result12 =testdb.Insert(tb_jsondata, jsondata);
    Assert.IsTrue(result12.Stutas);
    var json_findresult = testdb.FindAndResult<Dictionary<string, object>>(tb_jsondata, "id>0");
    Assert.IsTrue(json_findresult.ResultData?.Count() > 0);
    //**sql命令插入
    var result13 = testdb.Insert($"insert INTO Sqldata (`Name`,`DDATE`) values ('test','2023-05-13')");
    Assert.IsTrue(result13.Stutas);
    var findresult1 = testdb.FindAndResult<Dictionary<string, object>>(sqldata, "name='test'");
    Assert.IsTrue(findresult1.ResultData?.Count() > 0);
    //**批量插入列表
    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", $"批量插入{i}" },
            { "Qty", 19+maxid},
            { "DDate", DateTime.Now }
        };
        listdata.Add(dic2);
    }
    var listResult= testdb.Insert(dictable, listdata);
    Assert.IsTrue(listResult.Stutas);

更新数据

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

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

   //** 更新或插入数据
    var dic1 = new Dictionary<string, object>
    {
        { "_id", 2 },
        { "Name", "插入或更新单条数据" },
        { "Qty", 200},
        { "DDate", DateTime.Now }
    };
    var result= testdb.Upsert(dictable, dic1);
    Assert.IsTrue(result.Stutas);
    //** 批量插入或更新
    var dic3 = new Dictionary<string, object>
    {
        { "_id", 3 },
        { "Name", "批量插入或更新" },
        { "Qty", 300},
        { "DDATE", DateTime.Now }
    };
    List<Dictionary<string,object>> listdata=new List<Dictionary<string, object>> { dic3,dic1};
    var listresult = testdb.Upsert(dictable, listdata);
    Assert.IsTrue(listresult.Stutas);
    //var aa= Assert.ThrowsException<Exception>(async ()=> await testdb.FindAsync<Dictionary<string, object>>("", "_id=2"),"aaaf");
    var getupdatalist=testdb.Find<Dictionary<string, object>>(dictable, "_id=3");
    var getupdata1=  getupdatalist.FirstOrDefault();
    Assert.AreEqual(300, getupdata1.GetValueOrDefault("Qty", 0));//根据值验证是否更新成功
    //** 不存在就插入
    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);
    var get_insertdata = testdb.Find<Dictionary<string, object>>(dictable, $"_id={maxid}");
    Assert.IsNotNull(get_insertdata);

查询数据

   //查找id=2的数据
    var databyid = testdb.FindByID<Dictionary<string, object>>(dictable,3);
    Assert.IsNotNull(databyid);
    //查找Qty>10的数据
    var wheredata = testdb.Find<Dictionary<string, object>>(dictable, "Qty>10");
    Assert.IsNotNull(wheredata);
    //sql语句查找的数据
    string sqlcmd = $"select * from {dictable}";
    var sqldata = testdb.Find<Dictionary<string, object>>(sqlcmd);
    Assert.IsNotNull(sqldata);
    //分页查找的数据
    var pagedata = testdb.GetPagingData<Dictionary<string, object>>(dictable, "Qty>10",pageindex:1,pagenumber:10);
    Assert.IsNotNull(pagedata);

删除数据

   //**删除_id=2的数据
    var result = testdb.Delete(dictable,2);
    Assert.IsTrue(result.Stutas);
    //**删除qty<30的数据
    var wherresult = testdb.Delete(dictable, "Qty<200");
    Assert.IsTrue(wherresult.Stutas);
    //**使用sql语句删除_id=30的数据
    string sql = $"delete from {dictable} where _id=30";
    var sqlresult = testdb.Delete(sql);
    Assert.IsTrue(sqlresult.Stutas);