通用的CRUD之MongoDB - ToB企服应用市场:ToB评测及商务社交产业平台

//数据库连接 Database connection
IDbCRUD testdb =new MongoDbCRUD("mongodb://localhost:27017/testdb");
//带连接选项初始化数据库连接对象
MongoDbCRUD testdb = new MongoDbCRUD("mongodb://localhost:27017/testdb", opt => { opt.AutoCreateIndex = true;opt.AutoOpen = true; });

复制代码

//*****插入对象数据 Insert object data synchronously
int id = testdb.Max<int>(tb_custormer);
var customer = new CrudTestModel
{
ID = id + 1,
Name = "objectData",
Phones = new string[] { "80000", "90000" },
FFloat=random.NextDouble(),
IsActive = true,
Dic = new Dictionary<string, object>
{
{ "Name", "Embed_Data" },
{ "DDate", DateTime.Now }
}
};
var result = testdb.Insert(tb_custormer, customer);
//*****插入字典数据 Insert dictionary data synchronously
var dic1 = new Dictionary<string, object>
{
//{ "_id", 1 },//***如果不指定ID,插入时会自动编一个objectid的唯一ID
{ "Name", "MongodbCRUD" },
{ "Qty",random.Next(1,10000) },
{ "DDATE", DateTime.Now }
};
var dicresult = testdb.Insert(autoIDData, dic1);
//*****插入JSON数据 Insert JSON data
string jsondata = JsonConvert.SerializeObject(dic1);
var result12 =testdb.Insert(tb_jsondata, jsondata);
//*****SQL语句插入 sql command insert 大小写敏感
var result13 = testdb.Insert($"insert into {sqldata}('name','date') values ('test1','{DateTime.Now.ToString("yyyy-MM-dd HH: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", "更新指定字段数据 Updates the specified field data" },
{ "Qty", 600}
};
var upresult = testdb.UpDate(dictable, updata, "_id=6"); //更新_id=2的数据
//【SQL语句更新】更新_id=1的数据，mongodb对大小写敏感，所有sql语句中的表名和字段名大小写要与DB一致。
var sql_up_result = testdb.UpDate("UPDATE dicdata SET Name='zzw',Qty=188 where _id=1");

复制代码

//***** 更新或插入数据 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);

复制代码

///查找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, "_id=2")?.FirstOrDefault();
///查找id>2的数据,返回按DDATE排序,并排除dic列的最新一条数据
var ondresult = testdb.FindOne<CrudTestModel>(tb_custormer, "_id>2", project: "!dic", sort: "!DDATE");
//查找Qty>10的数据
var wheredata = testdb.Find<Dictionary<string, object>>(dictable, "Qty>10");
//【SQL语法,查找开头】，查找name中'Mongodbi'开头的数据,条件不区分大小写，字段名称区分大小写
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%'");
//【MongoDB语法】，查找name中'Mongodbi'开头的数据,不区分大小写
var bsonwheredata = testdb.Find<Dictionary<string, object>>(autoIDData, "{Name:/^Mongodb/i}");
//****SQL语法和Mongodb查询语法不能混用，简单查询使用SQL语法，书写简单，复杂查询只能使用原生语法。
//【简写语法】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
var pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "Qty>10",project:"!_id",sort: "!DDATE", pageindex: 1, pagecount: 10);
//【Mongodb语法】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
var mgdb_pagedata = testdb.GetPagingData<Dictionary<string, object>>(tb_jsondata, "{Qty:{$gt:10}}", project: "{_id:0}", sort: "{DDATE:-1}", pageindex: 1, pagecount: 10);
//【返回DataTable】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
var datatable_pagedata = testdb.GetPagingData(autoIDData, "Qty>10", project: "!_id", sort: "!DDATE", pageindex: 1, pagecount: 10);
//【多条件查询】分页查找Qty>10,返回除_id列，按DDATE倒序排序的数据，返回第一页10条数据。
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<='2023-06-05 13:28:48'", sort: "!DDATE", pageindex: 1, pagecount: 10);
//【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'^Batch'", sort: "!DDATE", pageindex: 1, pagecount: 10);s
//【sql语句查询】查找_id=1的数据
string sqlcmd = $"select _id,Name,DDATE from {dictable} where _id=1";
var sqldata = testdb.Find<Dictionary<string, object>>(sqlcmd);
//【委托查询】查找_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=8的数据
var result = testdb.Delete(dictable, 8);
//**删除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);

复制代码

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}");
}

复制代码