public static void Init()
{
var db = new SqlSugarScope(new SqlSugar.ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
db.Aop.OnLogExecuted = (s, p) =>
{
Console.WriteLine(s);
};
//建表
if (!db.DbMaintenance.IsAnyTable("User_Test001", false))
{
db.CodeFirst.InitTables <User_Test001>();
}
if (!db.DbMaintenance.IsAnyTable("UserRole_Test001", false))
{
db.CodeFirst.InitTables <UserRole_Test001>();
}
//用例代码
var result = db.Queryable <User_Test001, UserRole_Test001>((u, ur) => new object[] {
JoinType.Left, u.ID == ur.UserID
}).Select((u, ur) => new
{
customName = SqlFunc.Subqueryable <User_Test001>().Where(s => s.UserName == u.UserName).Select(s => s.UserName + "")
}).ToPageList(1, 10);
}
public static void Init()
{
var db = new SqlSugarScope(new SqlSugar.ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = SqlSugar.DbType.PostgreSQL,
IsAutoCloseConnection = true
});
db.Aop.OnLogExecuted = (s, p) =>
{
Console.WriteLine(s);
};
//db.CodeFirst.InitTables<User_Test001>();
var list = db.Queryable <User_Test001>().ToList();
var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("UserName");
dt.TableName = "public.unitUser_Test001"; //设置表名
var addRow = dt.NewRow();
addRow["ID"] = 1;
addRow["UserName"] = "******";
dt.Rows.Add(addRow); //添加娄据
var x = db.Storageable(dt).WhereColumns("id").ToStorage(); //id作为主键
}
public LogRecordService(TimeSpan duration, ILogger <LogRecordService> logger, LogRecordQueue queue, SqlSugarScope db, IFreeSql fsql)
{
this.logger = logger;
this.queue = queue;
this.db = db;
this.duration = duration;
this.fsql = fsql;
}
/// <summary>
/// 功能描述:构造函数
/// 作 者:Blog.Core
/// </summary>
public MyContext(ISqlSugarClient sqlSugarClient)
{
if (string.IsNullOrEmpty(_connectionString))
{
throw new ArgumentNullException("数据库连接字符串为空");
}
_db = sqlSugarClient as SqlSugarScope;
}
public virtual bool Insert(T entity, SqlSugarScope Db = null)
{
if (entity == null)
{
return(false);
}
SetCreate(entity);
return((Db ?? _dbContext.GetInstance()).Insertable(entity).ExecuteCommand() > 0);
}
public virtual Task <bool> InsertAsync(T entity, SqlSugarScope Db = null)
{
if (entity == null)
{
return(Task.FromResult(false));
}
SetCreate(entity);
return(Task.FromResult((Db ?? _dbContext.GetInstance()).Insertable(entity).ExecuteCommandAsync().Result > 0));
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
public RecordManager(SqlSugarScope db, ILogger <RecordManager> logger)
{
this.db = db;
records = new RecordTask(logger);
tasks = new RecordTask(logger);
last = DateTime.Now;
duration = TimeSpan.FromSeconds(2);
worker = new Thread(() =>
{
var watch = new Stopwatch();
var count = 0;
watch.Start();
while (true)
{
try
{
watch.Restart();
lock (records)
{
if (records.Count > 0)
{
last = DateTime.Now;
records.Switch(tasks);
}
}
count = tasks.Count;
if (tasks.Count > 0)
{
tasks.WriteDown(db);
logger.LogInformation("write down final");
}
watch.Stop();
if (count > 0)
{
logger.LogInformation("down: {0}ms {1}", watch.ElapsedMilliseconds, count);
}
}
catch (Exception e)
{
logger.LogError("write down: {0}", e);
}
Thread.Sleep(duration);
}
});
worker.Start();
logger.LogInformation("Start Worker.");
this.logger = logger;
}
public static void AddSqlsugarSetup(this IServiceCollection services, IConfiguration configuration, string dbName = "db_master")
{
SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
{
DbType = SqlSugar.DbType.MySql,
ConnectionString = configuration.GetConnectionString(dbName),
IsAutoCloseConnection = true,
},
db =>
{
//单例参数配置，所有上下文生效
db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql); //输出sql
Console.WriteLine(string.Join(",", pars?.Select(it => it.ParameterName + ":" + it.Value))); //参数
};
});
services.AddSingleton <ISqlSugarClient>(sqlSugar);
}
private static void Fastest2()
{
var db = new SqlSugarScope(new SqlSugar.ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
db.CodeFirst.InitTables <Test2>();
db.DbMaintenance.TruncateTable <Test2>();
//用例代码
db.Insertable(new Test2()
{
p = "1"
}).ExecuteCommand(); //用例代码
db.Insertable(new Test2()
{
p = "2", delPer = 1
}).ExecuteCommand(); //用例代码
var updateList = db.Queryable <Test2>()
.ToList();
db.Fastest <Test2>().BulkCopy(updateList);
int index = 0;
foreach (var update in updateList)
{
update.p = index.ToString();
index++;
}
db.Fastest <Test2>().BulkUpdate(updateList);
Console.WriteLine("用例跑完");
}
public SqlSugarScope GetInstance()
{
//创建数据库对象
SqlSugarScope db = new SqlSugarScope(new ConnectionConfig()
{
ConnectionString = DbOptions.ConnectionString,//连接符字串
DbType = DbOptions.DbType,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
});
if (DbOptions.IsPintLog)
{ //添加Sql打印事件，开发中可以删掉这个代码
db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine("[执行脚本]:" + sql + "\r\n" + "[执行参数]:" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
Console.WriteLine();
};
}
return(db);
}
public virtual Task <bool> InsertRangeAsync(List <T> list, SqlSugarScope Db = null)
{
BatchSetCreate(list);
return(Task.FromResult((Db ?? _dbContext.GetInstance()).Insertable(list).ExecuteCommandAsync().Result > 0));
}
public virtual bool InsertRange(List <T> list, SqlSugarScope Db = null)
{
BatchSetCreate(list);
return((Db ?? _dbContext.GetInstance()).Insertable(list).ExecuteCommand() > 0);
}
public virtual List <T> GetList(SqlSugarScope Db = null)
{
return(_dbContext.GetInstance().Queryable <T>().ToList());
}
public virtual Task <bool> ExistsAsync(Expression <Func <T, bool> > whereExpression, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().AnyAsync(whereExpression));
}
public virtual Task <bool> ExistsByIdAsync(dynamic id, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().InSingleAsync(id) != null);
}
public List <T> GetPageList(List <IConditionalModel> conditionalList, PageModel page, SqlSugarScope Db = null)
{
int totalNumber = 0;
List <T> result = (Db ?? _dbContext.GetInstance()).Queryable <T>().Where(conditionalList).ToPageList(page.PageIndex, page.PageSize, ref totalNumber);
page.TotalCount = totalNumber;
return(result);
}
public virtual int GetCount(SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().Count());
}
public virtual T First(Expression <Func <T, bool> > whereExpression, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().First(whereExpression));
}
public List <T> GetPageList(Expression <Func <T, bool> > whereExpression, PageModel page, SqlSugarScope Db = null)
{
int totalNumber = 0;
List <T> result = (Db ?? _dbContext.GetInstance()).Queryable <T>().Where(whereExpression).ToPageList(page.PageIndex, page.PageSize, ref totalNumber);
page.TotalCount = totalNumber;
return(result);
}
public virtual Task <T> FirstAsync(dynamic id, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().InSingleAsync(id));
}
public virtual Task <int> GetCountAsync(Expression <Func <T, bool> > whereExpression, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().Where(whereExpression).CountAsync());
}
public virtual Task <List <T> > GetListAsync(SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().ToListAsync());
}
public virtual bool DeleteById(dynamic id, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Deleteable <T>().In(id).ExecuteCommand() > 0);
}
public List <T> GetPageList(List <IConditionalModel> conditionalList, PageModel page, Expression <Func <T, object> > orderByExpression = null, OrderByType orderByType = OrderByType.Asc, SqlSugarScope Db = null)
{
int totalNumber = 0;
List <T> result = (Db ?? _dbContext.GetInstance()).Queryable <T>().OrderByIF(orderByExpression != null, orderByExpression, orderByType).Where(conditionalList)
.ToPageList(page.PageIndex, page.PageSize, ref totalNumber);
page.TotalCount = totalNumber;
return(result);
}
public virtual Task <int> GetCountAsync(SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().CountAsync());
}
public virtual List <T> GetList(Expression <Func <T, bool> > whereExpression, SqlSugarScope Db = null)
{
return((Db ?? _dbContext.GetInstance()).Queryable <T>().Where(whereExpression).ToList());
}