/// <summary>
/// 批量插入
/// </summary>
/// <param name="context">当前DbContext</param>
/// <param name="tableName">要插入的表名称</param>
/// <param name="dataTable">DataTable</param>
public static int BulkInsert(this MySqlDbContext context, string tableName, DataTable dataTable)
{
var provider = new BulkInsertmmMySqlProvider(context);
var result = provider.BulkInsert(tableName, dataTable);
return(result);
}
/// <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 MySqlDbContext context, string tableName, IList <T> list)
{
var provider = new BulkInsertmmMySqlProvider(context);
var result = provider.BulkInsert(tableName, list);
return(result);
}
public FileGrpcServcie(MySqlDbContext context)
{
context.EnsureNotNull(() => new AngleExceptions {
});
_dBcontext = context;
}
public void PlaySummaryMongoToSql()
{
// Arrange
string testId = MongoJsonTestData.Play.EntityId;
// Act
var playRepository = new GenericMongoRepository<PlayMongo>();
PlayMongo entity = playRepository.GetByComplexIdAsync(testId, MongoJsonTestData.Play.ParentApiId).Result;
var sqlEntity = EntityFactory.CreatePlaySummarySql(entity);
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<PlaySummarySql>(context);
repo.Insert(sqlEntity);
repo.Save();
}
// Assert
PlaySummarySql sqlSavedEntity;
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<PlaySummarySql>(context);
sqlSavedEntity = repo.FindWhere(x => x.Id == testId).FirstOrDefault();
}
Assert.IsNotNull(sqlSavedEntity);
}
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 MySqlDatabaseFixture()
{
Db = new MySqlDbContext("Server=localhost;Uid=root;Pwd=Password12!");
DropDatabase();
InitDb();
}
public AddUserWindow(MySqlDbContext db)
{
InitializeComponent();
this.db = db;
user = new User();
SetUpUi();
}
public async Task Authenticate(string email, string password, HttpContext httpContext)
{
using (var context = new MySqlDbContext())
{
var user = await context.Users
.FirstOrDefaultAsync(u => u.Email == email && u.Password == password);
if (user != null)
{
// Creating claim
var claims = new List <Claim>
{
new Claim(ClaimsIdentity.DefaultNameClaimType, user.Email),
new Claim(ClaimsIdentity.DefaultRoleClaimType, user.UserRole.ToString())
};
var role = user.UserRole.ToString();
// Creating Claim Identity
var id = new ClaimsIdentity(claims, "ApplicationCookie", ClaimsIdentity.DefaultNameClaimType,
ClaimsIdentity.DefaultRoleClaimType);
// Setting up authentication cookies
await httpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme,
new ClaimsPrincipal(id));
}
else
{
throw new CannotAuthenticateUser(email, password);
}
}
}
public static TrainStation StationForName(string stationName)
{
using (var context = new MySqlDbContext())
{
return(context.Stations.FirstOrDefault(station => station.Name == stationName));
}
}
public User FindByEmail(string email)
{
using (var context = new MySqlDbContext())
{
return(context.Users.FirstOrDefault(u => u.Email == email));
}
}
public static void TestMySql()
{
string connectString = "server=localhost;User Id=root;password=123456;Database=testdb;SslMode=None;";
SqlDbContext db = new MySqlDbContext(connectString);
//数据执行回调函数
db.ExecuteDbCallBack = (cmdText, dbParms) => {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("sql:{0}\n", cmdText);
if (dbParms != null)
{
foreach (IDbDataParameter param in dbParms)
{
stringBuilder.AppendFormat("paramName:{0},paramValue:{1}\n", param.ParameterName, param.Value.ToString());
}
}
stringBuilder.Append("\n");
Console.Write(stringBuilder.ToString());
};
var dbTest = new DbTest(db);
dbTest.DeleteAll();
dbTest.Insert();
dbTest.Delete();
dbTest.Update();
dbTest.Query();
dbTest.OrtherQuery();
dbTest.OrtherNoneQuery();
}
public IActionResult Register(string username, string passcode)
{
using (var db = new MySqlDbContext()) {
bool aUserAlready =
(from u in db.Users
where u.Username == username
select u).Any();
if (!aUserAlready)
{
PasswordHasher <User> hasher = new PasswordHasher <User>();
string pepper = "P$t`bSo#yH{}R2o"; //Always the same, not stored in database. Store in appsettings.json
User user = new User {
Username = username
};
string hash = hasher.HashPassword(user, passcode + pepper);
user.Passcode = hash;
db.Users.Add(user);
db.SaveChanges();
return(RedirectToAction("Login"));
}
}
return(View());
}
private void FrmUpdateItem_Load(object sender, EventArgs e)
{
txtTagCode.Text = BaseTool.ConvertUid(_entity.Uid);
txtTagCode1.Text = _entity.Uid;
txtName.Text = _entity.ItemName;
txtType.Text = _entity.ItemType;
txtLocation.Text = _entity.ItemLocation;
try
{
using (var db = new MySqlDbContext())
{
var list = db.MachineType.ToList();
cboMachineName.DataSource = list;
cboMachineName.DisplayMember = "TypeName";
cboMachineName.ValueMember = "Id";
for (var i = 0; i < list.Count; i++)
{
if (list.ElementAt(i).Id == _entity.MachineId)
{
cboMachineName.SelectedIndex = i;
break;
}
}
}
}
catch (Exception ex)
{
#if DEBUG
throw;
#else
Loger.Error(ex);
MessageBox.Show(Resources.FailMessage, @"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
#endif
}
}
private async Task Handle(SaveArticleEvent @event)
{
ArticleRecord article;
using (var context = new MySqlDbContext())
{
article = await context.Articles
.FirstOrDefaultAsync((x => x.Id == @event.Id));
}
if (article != null)
{
var record = new ArticleDetailsRecord
{
Id = article.Id,
Title = article.Title,
Date = article.Date,
Text = article.Text,
ImageUrl = article.ImageUrl
};
await SaveArticle(record);
}
Sender.Tell(new CommandResult(), Self);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, MySqlDbContext mySqlDbContext, ApplicationDbContext applicationDbContext)
{
// var _dBcontext = new MySqlDbContext(app.ApplicationServices.GetRequiredService<DbContextOptions<MySqlDbContext>>());
InitializeDb(mySqlDbContext);
InitializeDb(applicationDbContext);
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseCookiePolicy();
app.UseSession();
app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllerRoute(
name: "default",
pattern: "{controller=Account}/{action=Login}/{id?}");
endpoints.MapControllerRoute(
name: "System",
pattern: "{area:exists}/{controller=Home}/{action=Index}/{id?}");
});
}
public static void Main()
{
//consoledbContext dbctxt = new consoledbContext();
//var query = dbctxt.Robots.AsQueryable().AsNoTracking();
//var result = query.Where(x => x.Name.Contains("SD"));
//Console.WriteLine(result.Count());
//Console.ReadLine();
MySqlDbContext dbctxt = new MySqlDbContext();
var query = dbctxt.Set <MyRobots>().AsQueryable();
query = query.OrderByDescending(x => x.Id);
var result = query.Where(x => x.Name.Contains("aa"));
var a = result.Count();
Console.WriteLine(a);
Console.ReadLine();
}
public EditUserWindow(User user, MySqlDbContext db)
{
InitializeComponent();
this.user = user;
this.db = db;
SetUpUi();
}
public void GameMongoToSql()
{
// Arrange
string testGameId = MongoJsonTestData.Game.EntityId;
// Act
var gameMongoRepository = new GenericMongoRepository<GameMongo>();
var seasonMongoRepository = new GenericMongoRepository<SeasonMongo>();
GameMongo game = gameMongoRepository.GetByComplexIdAsync(testGameId).Result;
SeasonMongo season = seasonMongoRepository.GetByComplexIdAsync(game.SeasonId, game.ParentApiId).Result;
GameSql sqlEntity = EntityFactory.CreateGameSql(game, season);
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<GameSql>(context);
repo.Insert(sqlEntity);
repo.Save();
}
// Assert
GameSql sqlSavedEntity;
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<GameSql>(context);
sqlSavedEntity = repo.FindWhere(x => x.Id == testGameId).FirstOrDefault();
}
Assert.IsNotNull(sqlSavedEntity);
}
private IFrameworkContext CreateContext(ProviderType providerType, IDataStoreScope scope)
{
var optionsBuilder = new DbContextOptionsBuilder <FrameworkContext>();
IFrameworkContext context = null;
switch (providerType)
{
case ProviderType.Sqlite:
var sqliteOptions = scope == null?
optionsBuilder.UseSqlite(_connectionString).Options:
optionsBuilder.UseSqlite(scope.AsDbConnection()).Options;
context = new SqliteDbContext(sqliteOptions);
break;
case ProviderType.MySql:
var mySqlOptions = scope == null?
optionsBuilder.UseMySql(_connectionString).Options:
optionsBuilder.UseMySql(scope.AsDbConnection()).Options;
context = new MySqlDbContext(mySqlOptions);
break;
default:
throw new NotImplementedException($"No provider exists for '{providerType}'.");
}
return(scope != null?
context.UseTransaction(scope.AsTransaction()) :
context);
}
/// <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);
}
private void btnSubmit_Click(object sender, EventArgs e)
{
try
{
using (var db = new MySqlDbContext())
{
var entity = new MachineType
{
TypeName = txtMachineType.Text
};
db.MachineType.Add(entity);
db.SaveChanges();
MessageBox.Show(Resources.SuccecssMessage, @"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
Success?.Invoke(Name);
}
}
catch (Exception ex)
{
#if DEBUG
throw;
#else
Loger.Error(ex);
MessageBox.Show(Resources.FailMessage, @"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
#endif
}
}
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);
}
public MySqlDatabaseFixture()
{
const string connString = "Server=localhost;Uid=root;Pwd=Password12!";
Db = new MySqlDbContext(connString);
InitDb();
}
public CarriageSeat GetSeatById(int seatId)
{
using (var context = new MySqlDbContext())
{
return(context.Seats.Include(seat => seat.Carriage)
.FirstOrDefault(seat => seat.ID == seatId));
}
}
public static void Init()
{
using (var db = new MySqlDbContext(conString))
{
//var aa = db.Delete<SearchHistory>(x => x.HnUserId == 1236425);
Test.Init(db);
}
}
public void ExecuteQuery(string SqlQuery)
{
using (var dbContext = new MySqlDbContext())
{
dbContext.Database.ExecuteSqlCommand(SqlQuery);
dbContext.SaveChanges();
}
}
/// <summary>
/// Creates the vendor repository
/// </summary>
/// <param name="mySqlDbContext"></param>
/// <param name="userManager"></param>
public VendorRepository(MySqlDbContext mySqlDbContext, IUserManager <MyIdentityUser> userManager)
{
_mySqlDbContext = mySqlDbContext;
_identityUserManager = userManager;
vendors = _mySqlDbContext.Vendors.ToList();
vendorUsers = _mySqlDbContext.VendorUsers.Include(vu => vu.Vendor).Include(vu => vu.User).ToList();
}
public Trip GetTripById(int tripId)
{
using (var context = new MySqlDbContext())
{
var trip = context.Trips.Include(t => t.Train).FirstOrDefault(t => t.ID == tripId);
return(trip);
}
}
public int SearchListCount(UserSearchCondition condition)
{
using (var context = new MySqlDbContext())
{
var count = context.UserMySql.Count();
return(count);
}
}
public List <UserMySql> SearchList(UserSearchCondition condition)
{
using (var context = new MySqlDbContext())
{
var list = context.UserMySql.OrderByDescending(m => m.ID).Skip(condition.PageIndex).Take(condition.PageSize).ToList();
return(list);
}
}
public bool Edit(UserMySql model)
{
using (var context = new MySqlDbContext())
{
context.UserMySql.Update(model);
return(context.SaveChanges() > 0);
}
}
/// <summary>
/// 根据RFID标签序列号获取物品信息
/// </summary>
/// <param name="uidList">RFID标签序列号集合</param>
/// <returns>物品集合</returns>
public IEnumerable <Item> QueryList(IEnumerable <string> uidList)
{
using (var db = new MySqlDbContext())
{
return(uidList.Select(uid => db.Item.FirstOrDefault(x => x.Uid == uid) ?? new Item {
Uid = uid
}).ToList());
}
}
private void CleanSqlDatabase()
{
using (var context = new MySqlDbContext())
{
const string tableName = "`test`.`team`";
string removeQuery = string.Format("DELETE FROM {0} WHERE `Id` NOT like '%{1}%'", tableName, "manual");
var sqlRepository = new GenericSqlRepository<TeamSql>(context);
sqlRepository.ExecuteQuery(removeQuery);
}
}
public void GetAll_void_notEmpty()
{
InsertNewGameItem(1);
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<GameSql>(context);
IList<GameSql> result = repo.GetAll();
Assert.IsNotNull(result);
Assert.IsTrue(result.Any());
}
}
public void Insert_item_itemSaved()
{
const string id = "testInsert";
GameSql newEntity = new GameSql()
{
Id = id,
GameDate = DateTime.Now,
Season = 1,
SeasonType = "",
Week = 1,
HomeTeamId = "",
AwayTeamId = "",
WeatherCondition = "",
HomeTeamQ1Score = 1,
HomeTeamQ2Score = 1,
HomeTeamQ3Score = 1,
HomeTeamQ4Score = 1,
AwayTeamQ1Score = 1,
AwayTeamQ2Score = 1,
AwayTeamQ3Score = 1,
AwayTeamQ4Score = 1,
HomeTeamFinalScore = 1,
AwayTeamFinalScore = 1,
};
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<GameSql>(context);
repo.Insert(newEntity);
repo.Save();
}
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<GameSql>(context);
GameSql result = repo.FindWhere(x => x.Id == id).SingleOrDefault();
Assert.IsNotNull(result);
}
}
public void PlayPlayerPartisipantsMontoToSql()
{
// Arrange
string testId = MongoJsonTestData.Play.EntityId;
// Act
var playRepository = new GenericMongoRepository<PlayMongo>();
var playerRepository = new GenericMongoRepository<PlayerMongo>();
PlayMongo play = playRepository.GetByComplexIdAsync(testId, MongoJsonTestData.Play.ParentApiId).Result;
var filter = Builders<PlayerMongo>.Filter.Eq(x => x.PlayId, play.EntityId) &
Builders<PlayerMongo>.Filter.Eq(x => x.ParentApiId, play.ParentApiId);
IList<PlayerMongo> players = playerRepository.FindByFilter(filter).Result;
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<PlayPlayerPartisipantsSql>(context);
IList<PlayPlayerPartisipantsSql> sqlEntity = EntityFactory.CreatePlayPlayerPartisipantsSql(play, players);
foreach (var item in sqlEntity)
{
repo.Insert(item);
}
repo.Save();
}
// Assert
IList<PlayPlayerPartisipantsSql> sqlSavedEntity;
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<PlayPlayerPartisipantsSql>(context);
sqlSavedEntity = repo.FindWhere(x => x.PlayId == play.EntityId).ToList();
}
Assert.IsNotNull(sqlSavedEntity);
Assert.AreEqual(2, sqlSavedEntity.Count());
}
public void TeamMongoToSql()
{
// Arrange
string testId = MongoJsonTestData.Team.EntityId;
// Act
var teamMongoRepository = new GenericMongoRepository<TeamMongo>();
var conferenceMongoRepository = new GenericMongoRepository<ConferenceMongo>();
var divisionMongoRepository = new GenericMongoRepository<DivisionMongo>();
TeamMongo team = teamMongoRepository.GetByComplexIdAsync(testId, MongoJsonTestData.Team.ParentApiId).Result;
var conferenceFilter = Builders<ConferenceMongo>.Filter.Eq(x => x.EntityId, team.ConvferenceId) &
Builders<ConferenceMongo>.Filter.Eq(x => x.ParentApiId, team.ParentApiId);
IList<ConferenceMongo> conference = conferenceMongoRepository.FindByFilter(conferenceFilter).Result;
var divisionFilter = Builders<DivisionMongo>.Filter.Eq(x => x.EntityId, team.DivisionId) &
Builders<DivisionMongo>.Filter.Eq(x => x.ParentApiId, team.ParentApiId);
IList<DivisionMongo> division = divisionMongoRepository.FindByFilter(divisionFilter).Result;
var sqlEntity = EntityFactory.CreateTeamSql(team, conference[0], division[0]);
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<TeamSql>(context);
repo.Insert(sqlEntity);
repo.Save();
}
// Assert
TeamSql sqlSavedEntity;
using (var context = new MySqlDbContext())
{
var repo = new GenericSqlRepository<TeamSql>(context);
sqlSavedEntity = repo.FindWhere(x => x.Id == testId).FirstOrDefault();
}
Assert.IsNotNull(sqlSavedEntity);
}
