//public async Task<bool> RemoveUser(UserInfo user)
//{
// using (var dc = new SqlServerDbContext())
// {
// dc.Users.Remove(user);
// var result = await dc.SaveChangesAsync();
// return result == 1;
// }
//}
//public async Task<bool> UpdateUser(UserInfo user)
//{
// using (var dc = new SqlServerDbContext())
// {
// try
// {
// var et= dc.Users.Attach(user);
// DbEntityEntry<UserInfo> entry = dc.Entry(user);
// var result = await dc.SaveChangesAsync();
// if (result > 0)
// user.Timestamp = et.Timestamp;
// return result == 1;
// }
// catch (System.Data.Entity.Core.OptimisticConcurrencyException)
// {
// throw new InvalidOperationException("无法提交修改的内容，此对象已被修改："+user.UserName);
// }
// }
//}
public async Task <bool> ModifyPassword(int id, string newPassword)
{
using (var dc = new SqlServerDbContext())
{
try
{
var user = dc.Users.Where(x => x.Id == id)
.FirstOrDefault();
dc.Configuration.ValidateOnSaveEnabled = false; //关闭全局校验
user.Password = newPassword;
dc.Users.Attach(user);
DbEntityEntry <UserInfo> entry = dc.Entry(user); //部分修改，只对需要修改的字段的ismodify置true
// entry.Property(e => e.LoginName).IsModified = false;
entry.Property(e => e.Password).IsModified = true;
//entry.Property(e => e.Roles).IsModified = false;
var result = await dc.SaveChangesAsync();
return(result == 1);
}
catch (System.Data.Entity.Core.OptimisticConcurrencyException)
{
throw new InvalidOperationException("无法提交修改的内容，此对象已被修改，用户id：" + id);
}
}
}
public static void TestSqlServer()
{
string connectString = "server=localhost;user id=sa;password=123456;database=testdb;pooling=true;min pool size=5;max pool size=512;connect timeout = 60;";
SqlDbContext db = new SqlServerDbContext(connectString);
//数据执行回调函数
db.ExecuteDbCallBack = (cmdText, dbParms) => {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("sql:" + cmdText);
if (dbParms != null)
{
foreach (IDbDataParameter param in dbParms)
{
stringBuilder.Append("paramName:" + param.ParameterName + ",paramValue:" + param.Value.ToString());
}
}
stringBuilder.Append("\n\n");
Console.Write(stringBuilder.ToString());
};
var dbTest = new DbTest(db);
dbTest.DeleteAll();
dbTest.Insert();
dbTest.Delete();
dbTest.Update();
dbTest.Query();
dbTest.OrtherQuery();
dbTest.OrtherNoneQuery();
}
/// <summary>
/// 添加SQL语句操作数据的上下文注入
/// </summary>
/// <param name="services"></param>
/// <returns></returns>
public static IServiceCollection AddSqlEnginePack(this IServiceCollection services)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append($"初始化SQL引擎实例 \r\n");
services.AddScoped <ISqlServerDbContext, SqlServerDbContext>();
services.AddScoped <IMySqlDbContext, MySqlDbContext>();
services.AddScoped <IDataBaseDbContextManager, DataBaseDbContextManager>();
sb.Append($"正在读取配置文件 \r\n");
//自动构建表
var dbconfig = ConfigerManager.Instance.Configuration.GetDLDbSetting();
if (dbconfig.AutoAdoNetMiagraionEnable)
{
sb.Append($"已启动ADO.NET自动迁移，Auto【{dbconfig.AutoAdoNetMiagraionEnable}】");
ISqlServerDbContext service = new SqlServerDbContext();
var conn = ConfigerManager.Instance.Configuration.GetConStrSetting();
sb.Append($"正在创建SQL数据库连接。。 \r\n");
var context = service.CreateDbConnection(conn.Default ?? conn.SqlDefault);
sb.Append($"正在初始化数据表。。。。\r\n");
service.CreateSqlServerTable();
service.Dispose();
sb.Append($"迁移成功。。。\r\n");
}
logger.Info($"ADO.NET数据表迁移完毕");
}
catch (Exception ex)
{
logger.Error($"ADO.NET迁移数据表发生异常，异常信息:{ex.Message},stack：{ex.StackTrace}");
throw ex;
}
return(services);
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="context">当前DbContext</param>
/// <param name="tableName">要插入的表名称</param>
/// <param name="dataTable">DataTable</param>
public static int BulkInsert(this SqlServerDbContext context, string tableName, DataTable dataTable)
{
var provider = new BulkInsertSqlServerProvider(context);
var result = provider.BulkInsert(tableName, dataTable);
return(result);
}
//public async Task<bool> Remove(int id)
//{
// using (var db = new SqlServerDbContext())
// {
// var test= db.TorqueWrenchMeasures.Find(id);
// // TorqueWrenchMeasureDao test = new TorqueWrenchMeasureDao { Id = id }; //这里不能简单创建指定id的实例，因为有引用的其他对象，ef会抛出relationship异常
// db.TorqueWrenchMeasures.Attach(test);
// db.TorqueWrenchMeasures.Remove(test);
// var count = await db.SaveChangesAsync();
// return count >0; //返回的不能简单的判断是否为1，如果有关联的对象，则删除数为1+关联数
// }
//}
public Task <SearchResult <TorqueWrenchMeasure> > Find(string wrenchSN, int pagesize = 3, int pageIdx = 0)
{
return(Task.Run(() =>
{
using (var db = new SqlServerDbContext())
{
db.Configuration.LazyLoadingEnabled = false;
var total = db.TorqueWrenchMeasures
.Where(x => x.Measurand.SerialNumber.Contains(wrenchSN)).Count();
var tests = db.TorqueWrenchMeasures
.Where(x => x.Measurand.SerialNumber.Contains(wrenchSN))
.OrderByDescending(x => x.Id)
.Skip(pagesize * pageIdx)
.Take(pagesize)
.Include(x => x.Environment)
.Include(x => x.Measurand.Product)
.Include(x => x.Measurand.Owner)
.ToList()
.Select(x => new TorqueWrenchMeasure {
Id = x.Id, TestCode = x.TestCode, Environment = x.Environment, Measurand = x.Measurand
});
return new SearchResult <TorqueWrenchMeasure>(tests, total);
}
}));
}
//public override async Task<bool> Update(Customer customer)
//{
// using (var dc = new SqlServerDbContext())
// {
// dc.Configuration.ValidateOnSaveEnabled = false;
// var original = dc.Customers.Include(x => x.Contacts).Single(x => x.Id == customer.Id);
// //删除不存在的contact
// original.Contacts.Where(x => !customer.Contacts.Contains(x)).ToList().ForEach(x =>
// {
// dc.CustomerContacts.Attach(x);
// dc.Entry(x).State = EntityState.Deleted;
// });
// //添加新增的contact
// customer.Contacts.Where(x => x.Id == 0).ToList()
// .ForEach(x =>
// {
// dc.CustomerContacts.Add(x);
// // dc.Entry(x).State = EntityState.Added;
// });
// original.Contacts.ToList().ForEach(x =>
// {
// dc.Entry(x).State = EntityState.Detached;
// });
// dc.Entry(original).CurrentValues.SetValues(customer);
// var result = await dc.SaveChangesAsync();
// return result > 0;
// }
//}
public override async Task <bool> Update(Customer customer)
{
using (var dc = new SqlServerDbContext())
{
dc.Configuration.ValidateOnSaveEnabled = false;
var original = dc.Customers.Include(x => x.Contacts).Single(x => x.Id == customer.Id);
var addContacts = customer.Contacts.Where(x => x.Id == 0).ToList(); //此处一定要转成tolist！！
var deletedContacts = original.Contacts.Where(x => !customer.Contacts.Contains(x)).ToList(); //此处一定要转成tolist！！
var updatedContacts = original.Contacts.Where(x => customer.Contacts.Contains(x)).ToList(); //此处一定要转成tolist！！
addContacts.ForEach(t => //添加新建的
{
t.Company = original; //此处必须要置上existing，否则，ef会重新创建一个customer
dc.Entry(t).State = EntityState.Added;
});
foreach (var t in updatedContacts) //更新
{
var entry = dc.Entry(t);
entry.CurrentValues.SetValues(customer.Contacts.FirstOrDefault(x => x.Id == t.Id));
entry.State = EntityState.Modified;
}
deletedContacts.ToList().ForEach(t => dc.CustomerContacts.Remove(t)); //删除不存在的
var nwtry = dc.Entry(original);
nwtry.CurrentValues.SetValues(customer);
nwtry.State = EntityState.Modified;
var result = await dc.SaveChangesAsync();
AutoMapper.Mapper.Map(original, customer);
return(result > 0);
}
}
public override async Task <Customer> Find(int id)
{
using (var db = new SqlServerDbContext())
{
return(await db.Customers.Include(x => x.Contacts).FirstOrDefaultAsync(x => x.Id == id));
}
}
public HomeController(RoleManager <ApplicationRole> roleManager, UserManager <ApplicationUser> userManager, SignInManager <ApplicationUser> signInManager, SqlServerDbContext context)
{
_roleManager = roleManager;
_userManager = userManager;
_context = context;
_signInManager = signInManager;
}
private IDbContextCore GetDbContext(DatabaseType dbType, DbContextOption option)
{
IDbContextCore dbContext;
switch (dbType)
{
case DatabaseType.MySQL:
dbContext = new MySqlDbContext(option);
break;
case DatabaseType.PostgreSQL:
dbContext = new PostgreSQLDbContext(option);
break;
case DatabaseType.Oracle:
dbContext = new OracleDbContext(option);
break;
default:
dbContext = new SqlServerDbContext(option);
break;
}
return(dbContext);
}
/// <summary>
/// 批量插入,不建议使用List插入、性能会受一定影响
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context">当前DbContext</param>
/// <param name="tableName">要插入的表名称</param>
/// <param name="list">List列明必须与数据表列一致,严格大小写区分</param>
public static int BulkInsert <T>(this SqlServerDbContext context, string tableName, IList <T> list)
{
var provider = new BulkInsertSqlServerProvider(context);
var result = provider.BulkInsert(tableName, list);
return(result);
}
public async Task <IEnumerable <TablesModel> > GetModelsAsync()
{
IDbConnection conn = null;
var dbconn = StaticConfig.AppSettings.DbConnection;
var querySql = string.Empty;
switch (dbconn.DbType)
{
case Models.Enums.EnumDbType.MySql:
{
conn = new MySqlDbContext(dbconn.MySqlConnection).CreateConnection();
querySql = $"select table_name,table_comment from information_schema.tables where table_schema='{StaticConfig.AppSettings.DbConnection.DbName}'";
//return await StaticConfig.DbContext.GetModelsAsync<TablesModel, object>(sqlServer, new { dbName = StaticConfig.AppSettings.DbConnection.DbName });
}
break;
case Models.Enums.EnumDbType.SqlServer:
{
conn = new SqlServerDbContext(dbconn.SqlServerConnection).CreateConnection();
querySql = $"select name as table_name,name table_comment from {StaticConfig.AppSettings.DbConnection.DbName}.sys.tables a where 1=1 {StaticConfig.AppSettings.Template.TableSqlWhere}"; //排序不需要生成的表名; and a.name not like '%temp%' and a.name like 'tb_%'
}
break;
default:
break;
}
return(await conn.QueryAsync <TablesModel>(querySql));
}
public static void AddFromTest()
{
List <Developer> devs = RandomThings.GetDevelopers().ToList();
var sqlSever = new SqlServerDbContext();
var rng = new Random();
foreach (var dev in devs)
{
if (sqlSever.Developers.Any(d => d.UserName == dev.UserName))
{
}
else
{
var devToAdd = new Developer();
devToAdd.UserName = dev.UserName;
devToAdd.FirstName = dev.FirstName;
devToAdd.LastName = dev.LastName;
devToAdd.Age = dev.Age;
devToAdd.isTeamLeader = dev.isTeamLeader;
devToAdd.SpecialityId = rng.Next(1, 10);
devToAdd.TeamId = rng.Next(1, 6);
sqlSever.Developers.Add(devToAdd);
}
}
sqlSever.SaveChanges();
}
public override async Task <bool> Add(TorqueWrench wrench)
{
using (var dc = new SqlServerDbContext())
{
try
{
if (wrench.Owner.Id == 0)
{
dc.Customers.Add(wrench.Owner);
}
if (wrench.Product.Id == 0)
{
dc.TorqueWrenchProducts.Add(wrench.Product);
}
dc.TorqueWrenchs.Attach(wrench); //必须先attach，否则ef会自动插入新的userinfo而不是之前存在的userinfo
var result = dc.TorqueWrenchs.Add(wrench);
var count = await dc.SaveChangesAsync();
return(count > 0);
}
catch (DbEntityValidationException dbEx)
{
dbEx.Dump();
throw dbEx;
}
}
}
/// <summary>
/// 获取指定的数据库上下文
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public IDataBaseContext GetDataBaseDbContext(DataBaseType type)
{
IDataBaseContext _dbContext = null;
if (_typecontexts.ContainsKey(type))
{
return(_typecontexts[type]);
}
switch (type)
{
case DataBaseType.SqlServer:
_dbContext = new SqlServerDbContext();
break;
case DataBaseType.MySql:
_dbContext = new MySqlDbContext();
break;
case DataBaseType.Oracle:
_dbContext = new OracleDbContext();
break;
}
_typecontexts.TryAdd(type, _dbContext);
return(_dbContext);
}
public IDataBaseContext GetDataBaseDbContext(Type type)
{
IDataBaseContext dbContext = null;
if (_contexts.ContainsKey(type))
{
return(_contexts[type]);
}
var instance = Activator.CreateInstance(type) as IDataBaseContext;
if (instance == null)
{
throw new Exception($"创建上下文实例发生异常，请检查当前上下文 “{type}”是否实现 IDataBaseContext");
}
switch (instance.Type)
{
case DataBaseType.SqlServer:
dbContext = new SqlServerDbContext();
break;
case DataBaseType.MySql:
dbContext = new MySqlDbContext();
break;
case DataBaseType.Oracle:
dbContext = new OracleDbContext();
break;
}
_contexts.TryAdd(type, dbContext);
return(dbContext);
}
public BackstageController(SqlServerDbContext context, UserManager <ApplicationUser> userManager, IHostingEnvironment hostingEnvironment, RoleManager <ApplicationRole> roleManager, SignInManager <ApplicationUser> signInManager)
{
_roleManager = roleManager;
_userManager = userManager;
_context = context;
_signInManager = signInManager;
_hostingEnvironment = hostingEnvironment;
}
/// <summary>
/// 批量插入(SqlServer)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="destinationTableName"></param>
/// <param name="list">List列明必须与数据表列一致,严格大小写区分</param>
/// <param name="batchSize"></param>
public static void BulkInsertSqlBulkCopy <T>(this SqlServerDbContext context, string destinationTableName, IEnumerable <T> list, SqlBulkCopyOptions options,
int batchSize = DefaultBatchSize) where T : class
{
var provider = new BulkInsertSqlServerProvider(context);
var table = list.ToDataTable();
BulkInsert(context, table, destinationTableName, options, batchSize);
}
private static void PagedTest(SqlServerDbContext db)
{
int total = 0;
var a = db.Query <User>(x => x.Id > 0)
.OrderBy(x => x.Id)
.ThenByDescending(x => x.Gender)
.ToPageList(1, 10, out total);
}
public static void BulkInsert(this SqlServerDbContext context, IDataReader dataReader, string destinationTableName, SqlBulkCopyOptions options, int batchSize)
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(context.Connection as SqlConnection, options, context.Transaction as SqlTransaction))
{
sqlBulkCopy.BatchSize = batchSize;
sqlBulkCopy.DestinationTableName = destinationTableName;
sqlBulkCopy.WriteToServer(dataReader);
}
}
private static void SqlServerDbCreate()
{
var sqlServerDbContext = new SqlServerDbContext();
using (sqlServerDbContext)
{
sqlServerDbContext.Database.CreateIfNotExists();
}
}
//private readonly IRepository<faceTab> _repository;
public IndexController(IHubContext <ChatHouService> hubContext,
SqlServerDbContext dbContext
//IRepository<faceTab> repository
)
{
this._hubContext = hubContext;
this._dbContext = dbContext;
//this._repository = repository;
}
public IndexController(UserManager <ApplicationUser> userManager,
RoleManager <ApplicationRole> roleManager,
SignInManager <ApplicationUser> signInManager, SqlServerDbContext context)
{
_signInManager = signInManager;
_userManager = userManager;
_roleManager = roleManager;
_context = context;
}
public static void UseSqlServerAutoMigrations(this IApplicationBuilder app, string connectionString)
{
var builder = new DbContextOptionsBuilder <SqlServerDbContext>();
builder.UseSqlServer(connectionString);
var context = new SqlServerDbContext(builder.Options);
context.Database.Migrate();
}
public CleanerRunner()
{
context = CreateDbContext();
cleaners.AddRange(new List <ICleaner>()
{
new ConcertCleaner(context),
new TheatreCleaner(context),
new FilmCleaner(context)
});
}
public async Task <bool> AppendUser(UserInfo user)
{
using (var dc = new SqlServerDbContext())
{
dc.Users.Add(user);
var result = await dc.SaveChangesAsync();
return(result == 1);
}
}
public override async Task <bool> Add(Customer customer)
{
using (var db = new SqlServerDbContext())
{
var ev = db.Customers.Add(customer);
var count = await db.SaveChangesAsync();
return(count > 0);
}
}
public virtual Task <T> Find(int id)
{
return(Task.Run(() =>
{
using (var db = new SqlServerDbContext())
{
//return db.GetDbSet<T>().Find(id); //如果对应的实体被其他表引用会抛出Sequence contains more than one element的异常
return db.GetDbSet <T>().FirstOrDefault(x => x.Id == id);
}
}));
}
private static void Main(string[] args)
{
ISqlServerDbContext context = new SqlServerDbContext();
context.CreateDbConnection("Data Source=.;Initial Catalog=CodeFormDB;Persist Security Info=True;User ID=sa;Password=0103");
var sql = string.Format("SELECT * FROM USERINFO");
var list = context.GetDataTable(sql, CommandType.Text).ToObjectList <UserInfo>();
Console.ReadKey();
}
public override async Task <bool> Remove(int id)
{
using (var db = new SqlServerDbContext())
{
//考虑到级联删除，如果使用基类方法仅attachcustomer的话ef会报错
Customer customer = db.Customers.Include(x => x.Contacts).FirstOrDefault(x => x.Id == id);
db.Customers.Remove(customer);
var count = await db.SaveChangesAsync();
return(count > 0);
}
}
public override IDbContext CreateDbContext()
{
// 直接用无参构造函数时会使用默认配置项 XFrameworkConnString
// new SqlDbContext();
var context = new SqlServerDbContext(connString)
{
IsDebug = base.IsDebug,
IsolationLevel = System.Data.IsolationLevel.Serializable
};
return(context);
}
