DbCRUD.Mysql 1.3.0

数据库连接及初始化

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

插入数据

 //testdb.CheckConnect();
             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);