Note:有时候连接Mongodb时,解决C#连接Mongo报Unable to authenticate using sasl protocol mechanism SCRAM-SHA-1错误
1.设置Nlog配置文件
<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
autoReload="true"
internalLogLevel="Warn"
internalLogFile="internal-nlog.txt">
<!-- enable asp.net core and mongodb layout renderers -->
<extensions>
<add assembly="NLog.Web.AspNetCore"/>
<add assembly="NLog.Mongo"/>
</extensions>
<targets>
<target name="file" xsi:type="AsyncWrapper" queueLimit="5000" overflowAction="Discard">
<target xsi:type="File" fileName="${basedir}/logs/${shortdate}.log" layout="${longdate} ${level:uppercase=true} ${event-context:item=Action} ${message} ${event-context:item=Amount} ${stacktrace}" />
</target>
<target type="Database" name="database" connectionstring="Data Source=192.168.5.220;Initial Catalog=MPWMS;User ID=sa;Password=F123456789f;Persist Security Info=True;Max Pool Size=50;Min Pool Size=0;Connection Lifetime=300;">
<commandText>
insert into nlog (
Application, Logged, Level, Message,
Logger, CallSite, Exception
) values (
@Application, @Logged, @Level, @Message,
@Logger, @Callsite, @Exception
);
</commandText>
<parameter name="@application" layout="MPWMS.API.Admin" />
<parameter name="@logged" layout="${date}" />
<parameter name="@level" layout="${level}" />
<parameter name="@message" layout="${message}" />
<parameter name="@logger" layout="${logger}" />
<parameter name="@callSite" layout="${callsite:filename=true}" />
<parameter name="@exception" layout="${exception:tostring}" />
</target>
<!-- write log to mongodb-->
<target xsi:type="Mongo"
name="mongo" databaseName="MPWMSAPILog"
collectionName="Logs"
connectionString="mongodb://kevin:sa123@localhost:27017/MPWMSAPILog"
cappedCollectionSize="26214400" IncludeDefaults="false">
<!--<property name="LongDate" layout="${longdate}" bsonType="DateTime" />
<property name="Level" layout="${level}" />
<property name="Logger" layout="${logger}"/>
<property name="Message" layout="${message}" />
<property name="Exception" layout="${exception:format=tostring}" />
<property name="Url" layout="${aspnet-request-url}" />
<property name="Action" layout="${aspnet-mvc-action}" />
<property name="UserName" layout="${windows-identity}" />-->
<Field name="Date" layout="${date:format=yyyy-MM-dd HH\:mm\:ss}" />
<Field name="Level" layout="${level}" />
<Field name="Logger" layout="${logger}"/>
<Field name="Url" layout="${aspnet-request-url}" />
<Field name="Message" layout="${message}" />
<Field name="Exception" layout="${exception:format=tostring}" />
<Field name="CallSite" layout="${callsite:filename=true}"/>
<Field name="StackTrace" layout="${stacktrace}"/>
</target>
</targets>
<rules>
<!--Add logs to mongodb-->
<logger name="WMSPAPI" writeTo="mongo"/>
<!--表示过滤日志 tip:name 是在日志界面定义-->
<logger name="WMSPAPI" writeTo="database"/>
<!--DEBUG,INFO,WARN,ERROR,FATAL-->
<logger name="*" minlevel="Warn" writeTo="file" />
</rules>
</nlog>
2.创建实体类
/// <summary>
/// kevin
/// 2020-03-25 11:17:44
///
/// </summary>
public class Logs
{
public ObjectId _id { get; set; }
/// <summary>
///
/// </summary>
public string Date { get; set; }
/// <summary>
///
/// </summary>
public string Level { get; set; }
/// <summary>
///
/// </summary>
public string Logger { get; set; }
/// <summary>
///
/// </summary>
public string Url { get; set; }
/// <summary>
/// 登录id:001登录密码:123456
/// </summary>
public string Message { get; set; }
/// <summary>
///
/// </summary>
public string CallSite { get; set; }
/// <summary>
///
/// </summary>
public string StackTrace { get; set; }
}
3.创建mongodb辅助类
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using MongoDB.Bson;
using MongoDB.Driver;
namespace MPWMS.API.Core.Helper
{
public class MongoDBHelper
{
#region 构造函数(初始化集合,子类需重写集合名)
/// <summary>
/// 集合
/// </summary>
public string _collName { get; set; }
public MongoDBHelper(string collName)
{
this._collName = collName;
}
#endregion
#region 连接配置
/// <summary>
/// 链接
/// </summary>
private static readonly string conneStr = "mongodb://kevin:sa123@localhost:27017/MPWMSAPILog";
/// <summary>
/// 数据库
/// </summary>
private static readonly string dbName = "MPWMSAPILog";
#endregion
#region 单例创建链接
private static IMongoClient _mongoclient { get; set; }
private static IMongoClient CreateClient()
{
if (_mongoclient == null)
{
_mongoclient = new MongoClient(conneStr);
}
return _mongoclient;
}
#endregion
#region 获取链接和数据库
private IMongoClient client = CreateClient();
public IMongoDatabase _database { get { return _mongoclient.GetDatabase(dbName); } }
public IMongoDatabase GetDatabase()
{
return _database;
}
public IMongoCollection GetClient() where T : class, new()
{
return _database.GetCollection<T>(_collName);
}
#endregion
#region +InsertMany 批量插入
/// <summary>
/// 批量插入
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="t">实体集合</param>
/// <returns></returns>
public int InsertMany(List t) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
client.InsertMany(t);
return 1;
}
catch (Exception ex)
{
return 0;
}
}
#endregion
#region +InsertManyAsync 异步批量插入
/// <summary>
/// 异步批量插入
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="t">实体集合</param>
/// <returns></returns>
public async Task<int> InsertManyAsync(List t) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
await client.InsertManyAsync(t);
return 1;
}
catch
{
return 0;
}
}
#endregion
#region +Add 添加一条数据
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="t">添加的实体</param>
/// <param name="host">mongodb连接信息</param>
/// <returns></returns>
public int Add(T t) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
client.InsertOne(t);
return 1;
}
catch (Exception ex)
{
return 0;
}
}
#endregion
#region +AddAsync 异步添加一条数据
/// <summary>
/// 异步添加一条数据
/// </summary>
/// <param name="t">添加的实体</param>
/// <param name="host">mongodb连接信息</param>
/// <returns></returns>
public async Task<int> AddAsync(T t) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
await client.InsertOneAsync(t);
return 1;
}
catch
{
return 0;
}
}
#endregion
#region +Update 修改一条数据
/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="t">添加的实体</param>
/// <param name="host">mongodb连接信息</param>
/// <returns></returns>
public UpdateResult Update(T t, string id, bool isObjectId = true) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//修改条件
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id));
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
//要修改的字段
var list = new List<UpdateDefinition<T>>();
foreach (var item in t.GetType().GetProperties())
{
if (item.Name.ToLower() == "id") continue;
list.Add(Builders<T>.Update.Set(item.Name, item.GetValue(t)));
}
var updatefilter = Builders<T>.Update.Combine(list);
return client.UpdateOne(filter, updatefilter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region +UpdateAsync 异步修改一条数据
/// <summary>
/// 异步修改一条数据
/// </summary>
/// <param name="t">添加的实体</param>
/// <param name="host">mongodb连接信息</param>
/// <returns></returns>
public async Task UpdateAsync(T t, string id, bool isObjectId) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//修改条件
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id));
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
//要修改的字段
var list = new List<UpdateDefinition<T>>();
foreach (var item in t.GetType().GetProperties())
{
if (item.Name.ToLower() == "id") continue;
list.Add(Builders<T>.Update.Set(item.Name, item.GetValue(t)));
}
var updatefilter = Builders<T>.Update.Combine(list);
return await client.UpdateOneAsync(filter, updatefilter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region +UpdateManay 批量修改数据
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="dic">要修改的字段</param>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">修改条件</param>
/// <returns></returns>
public UpdateResult UpdateManay<T>(Dictionary<string, string> dic, FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
T t = new T();
//要修改的字段
var list = new List<UpdateDefinition<T>>();
foreach (var item in t.GetType().GetProperties())
{
if (!dic.ContainsKey(item.Name)) continue;
var value = dic[item.Name];
list.Add(Builders<T>.Update.Set(item.Name, value));
}
var updatefilter = Builders<T>.Update.Combine(list);
return client.UpdateMany(filter, updatefilter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region +UpdateManayAsync 异步批量修改数据
/// <summary>
/// 异步批量修改数据
/// </summary>
/// <param name="dic">要修改的字段</param>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">修改条件</param>
/// <returns></returns>
public async Task<UpdateResult> UpdateManayAsync<T>(Dictionary<string, string> dic, FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
T t = new T();
//要修改的字段
var list = new List<UpdateDefinition<T>>();
foreach (var item in t.GetType().GetProperties())
{
if (!dic.ContainsKey(item.Name)) continue;
var value = dic[item.Name];
list.Add(Builders<T>.Update.Set(item.Name, value));
}
var updatefilter = Builders<T>.Update.Combine(list);
return await client.UpdateManyAsync(filter, updatefilter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Delete 删除一条数据
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="id">objectId</param>
/// <returns></returns>
public DeleteResult Delete<T>(string id, bool isObjectId = true) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id));
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
return client.DeleteOne(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region DeleteAsync 异步删除一条数据
/// <summary>
/// 异步删除一条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="id">objectId</param>
/// <returns></returns>
public async Task<DeleteResult> DeleteAsync<T>(string id, bool isObjectId = true) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//修改条件
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id));
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
return await client.DeleteOneAsync(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region DeleteMany 删除多条数据
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">删除的条件</param>
/// <returns></returns>
public DeleteResult DeleteMany(FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
return client.DeleteMany(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region DeleteManyAsync 异步删除多条数据
/// <summary>
/// 异步删除多条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">删除的条件</param>
/// <returns></returns>
public async Task DeleteManyAsync(FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
return await client.DeleteManyAsync(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindOne 根据id查询一条数据
/// <summary>
/// 根据id查询一条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="id">objectid</param>
/// <param name="field">要查询的字段,不写时查询全部</param>
/// <returns></returns>
public T FindOne<T>(string id, bool isObjectId = true, string[] field = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id)); //默认id为objectid类型
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
//不指定查询字段
if (field == null || field.Length == 0)
{
return client.Find(filter).FirstOrDefault<T>();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
return client.Find(filter).Project<T>(projection).FirstOrDefault<T>();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindOneAsync 异步根据id查询一条数据
/// <summary>
/// 异步根据id查询一条数据
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="id">objectid</param>
/// <returns></returns>
public async Task<T> FindOneAsync<T>(string id, bool isObjectId = true, string[] field = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
FilterDefinition<T> filter;
if (isObjectId)
{
filter = Builders<T>.Filter.Eq("_id", new ObjectId(id));
}
else
{
filter = Builders<T>.Filter.Eq("_id", id);
}
//不指定查询字段
if (field == null || field.Length == 0)
{
return await client.Find(filter).FirstOrDefaultAsync();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
return await client.Find(filter).Project<T>(projection).FirstOrDefaultAsync();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindList 查询集合
/// <summary>
/// 查询集合
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">查询条件</param>
/// <param name="field">要查询的字段,不写时查询全部</param>
/// <param name="sort">要排序的字段</param>
/// <returns></returns>
public List FindList(FilterDefinition filter = null, string[] field = null, SortDefinition sort = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//不指定查询字段
if (field == null || field.Length == 0)
{
if (sort == null)
return client.Find(filter).ToList();
//进行排序
return client.Find(filter).Sort(sort).ToList();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
if (sort == null) return client.Find(filter).Project<T>(projection).ToList();
//排序查询
return client.Find(filter).Sort(sort).Project<T>(projection).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindListAsync 异步查询集合
/// <summary>
/// 异步查询集合
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">查询条件</param>
/// <param name="field">要查询的字段,不写时查询全部</param>
/// <param name="sort">要排序的字段</param>
/// <returns></returns>
public async Task> FindListAsync(FilterDefinition filter, string[] field = null, SortDefinition sort = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//不指定查询字段
if (field == null || field.Length == 0)
{
if (sort == null) return await client.Find(filter).ToListAsync();
return await client.Find(filter).Sort(sort).ToListAsync();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
if (sort == null) return await client.Find(filter).Project<T>(projection).ToListAsync();
//排序查询
return await client.Find(filter).Sort(sort).Project<T>(projection).ToListAsync();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindListByPage 分页查询集合
/// <summary>
/// 分页查询集合
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">查询条件</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页容量</param>
/// <param name="count">总条数</param>
/// <param name="field">要查询的字段,不写时查询全部</param>
/// <param name="sort">要排序的字段</param>
/// <returns></returns>
public List FindListByPage(FilterDefinition filter, int pageIndex, int pageSize, out long count, string[] field = null, SortDefinition sort = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
count = client.CountDocuments(filter);
//不指定查询字段
if (field == null || field.Length == 0)
{
if (sort == null) return client.Find(filter).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
//进行排序
return client.Find(filter).Sort(sort).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
//不排序
if (sort == null) return client.Find(filter).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
//排序查询
return client.Find(filter).Sort(sort).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region FindListByPageAsync 异步分页查询集合
/// <summary>
/// 异步分页查询集合
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">查询条件</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页容量</param>
/// <param name="field">要查询的字段,不写时查询全部</param>
/// <param name="sort">要排序的字段</param>
/// <returns></returns>
public async Task> FindListByPageAsync(FilterDefinition filter, int pageIndex, int pageSize, string[] field = null, SortDefinition sort = null) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
//不指定查询字段
if (field == null || field.Length == 0)
{
if (sort == null) return await client.Find(filter).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToListAsync();
//进行排序
return await client.Find(filter).Sort(sort).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToListAsync();
}
//制定查询字段
var fieldList = new List<ProjectionDefinition<T>>();
for (int i = 0; i < field.Length; i++)
{
fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
}
var projection = Builders<T>.Projection.Combine(fieldList);
fieldList?.Clear();
//不排序
if (sort == null) return await client.Find(filter).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToListAsync();
//排序查询
return await client.Find(filter).Sort(sort).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToListAsync();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Count 根据条件获取总数
/// <summary>
/// 根据条件获取总数
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">条件</param>
/// <returns></returns>
public long Count(FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
return client.CountDocuments(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region CountAsync 异步根据条件获取总数
/// <summary>
/// 异步根据条件获取总数
/// </summary>
/// <param name="host">mongodb连接信息</param>
/// <param name="filter">条件</param>
/// <returns></returns>
public async Task<long> CountAsync(FilterDefinition filter) where T : class, new()
{
try
{
var client = _database.GetCollection<T>(_collName);
return await client.CountDocumentsAsync(filter);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
}
4.调用
namespace MPWMS.API.Services
{
public class MongoDBLogService : IMongoDBLogService
{
[Obsolete]
public AjaxResult GetLogList()
{
AjaxResult result = new AjaxResult();
//方式二
MongoDBHelper mongoDBHelper = new MongoDBHelper("Logs");
FilterDefinitionBuilder<Logs> builderFilter = Builders<Logs>.Filter;
//约束条件筛选条件
FilterDefinition<Logs> filter = builderFilter.And(builderFilter.Lt("Date", DateTime.Now));
//排序专用
SortDefinitionBuilder<Logs> sortdoc = Builders<Logs>.Sort;
SortDefinition<Logs> sortbuilder = sortdoc.Descending(t => t.Date);
long count = 0;
List<Logs> logs= mongoDBHelper.FindListByPage<Logs>(filter, 1,10,out count,sort: sortbuilder);
var data = new
{
total = logs.Count,
rows = logs.ToJson()
};
result.status = true;
result.data = data;
return result;
}
}
}