DbCRUD.SqlServer 1.3.1

安装

Install-Package DbCRUD.SqlServer 安装包

数据库连接及初始化

 //数据库连接 Database connection
 IDbCRUD testdb = new SqlServerCRUD(@"Data Source=xxx;Initial Catalog=xxx;User ID=sa;Password=xxx;Encrypt=True;TrustServerCertificate=True;");

插入数据

 //获取最大ID,用于ID编号
            int dbcount = testdb.TableExists(tb_custormer) ? testdb?.Count(tb_custormer) ?? 0 : 0;
            //**同步插入对象数据 Insert object data synchronously
            var customer = new CrudTestModel
            {
                ID = dbcount + 1, //如果要使用DB自身编号功能，ID不要赋值即可
                Name = "objectData",
                Phones = new string[] { "80000", "90000" },
                IsActive = true,
            };
            var result = testdb.Insert(tb_custormer, customer);

//**同步插入字典数据 Insert dictionary data synchronously
           var dic1 = new Dictionary<string, object>
           {
               //{ "ID", 1 },//***如果不指定ID,插入时会自动编一个int的唯一ID
               { "Name", "mysql自动编号插入" },
               { "Qty",random.Next(1,10000) },
               { "DDate", DateTime.Now }
           };
           var result11 = testdb.Insert(autoIDData, dic1);
      //**批量插入列表 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 updatepre = testdb.Find<Dictionary<string, object>>(dictable, "ID=2")?.FirstOrDefault();
          
            var updata = new Dictionary<string, object>
            {
                { "Name", "更新指定字段数据" },
                { "Qty", 600}
            };
            var upresult = testdb.UpDate(dictable, updata, "ID=2");   //更新ID=2的数据
            Assert.IsTrue(upresult.Status);
            //更新后
            var getupdata = testdb.Find<Dictionary<string, object>>(dictable, "ID=2")?.FirstOrDefault();
            Assert.AreEqual(300, getupdata.GetValueOrDefault("Qty", 0));

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

//** 更新或插入数据 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);
//** 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,排除id列的最新一条数据
    var one_result = testdb.FindOne<Dictionary<string, object>>(dictable, "Qty>10","!id", "!DDATE");
    
    //查找Qty>10的数据
    var wheredata = testdb.Find<Dictionary<string, object>>(dictable, "Qty>10");
    
    //【简写语法】查找Qty>10,排除ID列，按DDATE倒序的数据
    var simple_where = testdb.FindAndResult<Dictionary<string, object>>(dictable, "Qty>10","!id","!DDATE");
    
    //【SQL语法】查找Qty>10,排除ID列，按DDATE倒序的数据
    var sql_where = testdb.FindAndResult<Dictionary<string, object>>(dictable, "Qty>10", "name,qty,ddate", "order by DDATE desc");

    //sql语句查找的数据
    string sqlcmd = $"select * from {dictable}";
    var sqldata = testdb.Find<Dictionary<string, object>>(sqlcmd);
   
    //分页查找的数据
    var pagedata = testdb.GetPagingData<Dictionary<string, object>>(dictable, "Qty>10", "!id", "!DDATE", pageindex:1,pagecount:10);
    
    //分页查找的数据,返回DataTable
    var dt_resutl = testdb.GetPagingData(dictable,"Qty>10", pageindex: 1, pagecount: 10);
     //【委托查询】查找_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=3的数据
   var result = testdb.Delete(dictable,3);
   //**删除qty<30的数据
   var wherresult = testdb.Delete(dictable, "Qty<30");
   //**使用sql语句删除_id=30的数据
   string sql = $"delete {dictable} where _id=30";
   var sqlresult = testdb.Delete(sql);

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

    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;

    }