public Entity ExecuteEntity <Entity>(SqlDbContext DbContext) where Entity : class
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DataSet ds = ExecuteDataSet(DbContext);
return(GetEntityFromDataSet <Entity>(ds));
}
return(default(Entity));
}
public GenericRepository(SqlDbContext context)
{
if (context == null)
{
context = new SqlDbContext();
}
this.Context = context;
this.DbSet = context.Set <TEntity>();
}
public TestSqlSaveChanges()
{
_sqlOptions = this.CreateUniqueClassOptions <SqlDbContext>();
using (var context = new SqlDbContext(_sqlOptions))
{
context.Database.EnsureCreated();
context.WipeAllDataFromDatabase();
}
}
public UnitOfWork(SqlDbContext context)
{
if (context == null || this.disposed)
{
context = new SqlDbContext();
this._repositories = null;
}
this.context = context;
}
public IList <Message> GetMessage()
{
SqlDbContext context = new SqlDbContext();
IQueryable <Message> query = context.Messages
.OrderBy(m => m.HasRead)
.ThenByDescending(m => m.PublishDateTime);
return(query.ToList());
}
public bool CheckUserLogin(string userName, string password)
{
using var context = new SqlDbContext();
var user = context.Set <User>().FirstOrDefault(x => x.UserName == userName && x.Password == password);
//return user != null;
return(user != null);
}
public List <Entity> ExecuteList <Entity>(SqlDbContext DbContext) where Entity : class
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DataSet ds = ExecuteDataSet(DbContext);
return(GetListFromDataSet <Entity>(ds));
}
return(default(List <Entity>));
}
public async Task TestGetCosmosClientOnMainDatabasesOk()
{
//SETUP
var mainConfig = AppSettings.GetConfiguration("../EfCoreSqlAndCosmos/");
var noSQlDatabaseName = mainConfig["database"];
var noSqlOptions = noSQlDatabaseName.GetCosmosDbToEmulatorOptions <NoSqlDbContext>();
using (var noSqlDbContext = new NoSqlDbContext(noSqlOptions))
{
var cosmosClient = noSqlDbContext.Database.GetCosmosClient();
var database = cosmosClient.GetDatabase(noSQlDatabaseName);
var container = database.GetContainer(nameof(NoSqlDbContext));
//ATTEMPT
_output.WriteLine($"SQL count = {noSqlDbContext.Books.Select(_ => 1).AsEnumerable().Count()}");
using (new TimeThings(_output, "NoSQL count, EF Core"))
{
var result = noSqlDbContext.Books.Select(_ => 1).AsEnumerable().Count();
}
using (new TimeThings(_output, "NoSQL count, EF Core"))
{
var result = noSqlDbContext.Books.Select(_ => 1).AsEnumerable().Count();
}
using (new TimeThings(_output, "NoSQL count, via client"))
{
var resultSet = container.GetItemQueryIterator <int>(new QueryDefinition("SELECT VALUE Count(c) FROM c"));
var result = (await resultSet.ReadNextAsync()).First();
}
using (new TimeThings(_output, "NoSQL count, via client"))
{
var resultSet = container.GetItemQueryIterator <int>(new QueryDefinition("SELECT VALUE Count(c) FROM c"));
var result = (await resultSet.ReadNextAsync()).First();
}
}
var sqlConnection = mainConfig.GetConnectionString("BookSqlConnection");
var builder = new DbContextOptionsBuilder <SqlDbContext>()
.UseSqlServer(sqlConnection);
using (var sqlContext = new SqlDbContext(builder.Options))
{
_output.WriteLine($"SQL count = {sqlContext.Books.Count()}");
using (new TimeThings(_output, "SQL count"))
{
var num = await sqlContext.Books.CountAsync();
}
using (new TimeThings(_output, "SQL count"))
{
var num = await sqlContext.Books.CountAsync();
}
}
}
public async Task <DbDataReader> ExecuteReaderAsync(SqlDbContext DbContext)
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DbContext.ParameterInitializes();
DbContext.ConnectionStatusCheck();
return(await DbContext.DbCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection));
}
return(default(DbDataReader));
}
public IPDetailsService(
SqlDbContext ctx,
IIPInfoProvider ipInfoProvider,
IMemoryCacheService memory
)
{
_ctx = ctx;
_memory = memory;
_ipInfoProvider = ipInfoProvider;
}
public async Task <object> ExecuteScalarAsync(SqlDbContext DbContext)
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DbContext.ParameterInitializes();
DbContext.ConnectionStatusCheck();
return(await DbContext.DbCommand.ExecuteScalarAsync());
}
return(default(object));
}
public async Task <List <Entity> > ExecuteListAsync <Entity>(SqlDbContext DbContext) where Entity : class
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DataSet ds = await ExecuteDataSetAsync(DbContext);
return(GetListFromDataSet <Entity>(ds));
}
return(default(List <Entity>));
}
public async void AddServerRole()
{
var serverRoleDto = new ServerRoleDto
{
ServerId = SetupManager.Instance.ServerId,
ServerIP = SetupManager.Instance.IPAddress,
ServerName = Environment.MachineName,
ServerRoleEnum = ServerRoleEnum.Database,
IsActive = true
};
try
{
using (var context = new SqlDbContext(Main.ConnectionInfo.ConnectionString))
{
var oldRoles = context.ServerRoles.Where(x => x.ServerRoleEnum == ServerRoleEnum.Database && x.IsActive == true).ToList();
if (oldRoles.Count == 0)
{
context.ServerRoles.Add(serverRoleDto);
await context.SaveChangesAsync();
}
else if (oldRoles.Count == 1)
{
oldRoles.First().IsActive = false;
context.Entry(oldRoles.First()).State = System.Data.Entity.EntityState.Modified;
context.ServerRoles.Add(serverRoleDto);
await context.SaveChangesAsync();
}
else if (oldRoles.Count > 1)
{
var toDelete = oldRoles.Take(oldRoles.Count - 1).ToList();
foreach (var item in toDelete)
{
item.IsActive = false;
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
oldRoles.Last().ServerId = serverRoleDto.ServerId;
oldRoles.Last().ServerIP = serverRoleDto.ServerIP;
oldRoles.Last().ServerName = serverRoleDto.ServerName;
oldRoles.Last().ServerRoleEnum = serverRoleDto.ServerRoleEnum;
context.Entry(oldRoles.Last()).State = System.Data.Entity.EntityState.Modified;
await context.SaveChangesAsync();
}
}
}
catch (Exception ex)
{
logger.Error(ex, $"Error occured EnsureDatabase.");
TxtErrorNotAdded.Visibility = Visibility.Visible;
}
}
static void Main(string[] args)
{
SqlDbContext context = new SqlDbContext();
//var db = context.Database;
////db.Migrate();
//db.EnsureDeleted();
//db.EnsureCreated();
//context.SaveChanges();
context.Users.Where(u => u.Name.StartsWith("小")).ToList();
}
//public async Task<bool> AddServerRole(ServerRoleEnum serverRoleEnum)
//{
// if (DatabaseHelper.Instance.Status == DatabaseHelperStatusEnum.CelsusDatabaseVersionOk)
// {
// }
// else
// {
// return false;
// }
// var serverRoleDto = new ServerRoleDto
// {
// ServerId = ComputerHelper.Instance.ServerId,
// ServerIP = ComputerHelper.Instance.IPAddress,
// ServerName = Environment.MachineName,
// ServerRoleEnum = serverRoleEnum,
// IsActive = true
// };
// return await AddServerRoleInner(serverRoleEnum, serverRoleDto);
//}
private async Task <bool> AddServerRoleInnerAllowOne(ServerRoleEnum serverRoleEnum, ServerRoleDto serverRoleDto)
{
try
{
using (var context = new SqlDbContext(DatabaseHelper.Instance.ConnectionInfo.ConnectionString))
{
var oldRoles = await context.ServerRoles.Where(x => x.ServerRoleEnum == serverRoleEnum && x.IsActive == true).ToListAsync();
if (oldRoles.Count == 0)
{
context.ServerRoles.Add(serverRoleDto);
await context.SaveChangesAsync();
}
else if (oldRoles.Count == 1)
{
oldRoles.First().IsActive = false;
context.Entry(oldRoles.First()).State = System.Data.Entity.EntityState.Modified;
context.ServerRoles.Add(serverRoleDto);
await context.SaveChangesAsync();
}
else if (oldRoles.Count > 1)
{
var toDelete = oldRoles.Take(oldRoles.Count - 1).ToList();
foreach (var item in toDelete)
{
item.IsActive = false;
context.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
oldRoles.Last().ServerId = serverRoleDto.ServerId;
oldRoles.Last().ServerIP = serverRoleDto.ServerIP;
oldRoles.Last().ServerName = serverRoleDto.ServerName;
oldRoles.Last().ServerRoleEnum = serverRoleDto.ServerRoleEnum;
context.Entry(oldRoles.Last()).State = System.Data.Entity.EntityState.Modified;
await context.SaveChangesAsync();
}
await GetRoles();
NotifyPropertyChanged("");
return(true);
}
}
catch (Exception ex)
{
logger.Error(ex, $"Error occured EnsureDatabase.");
}
return(false);
}
public async Task <IdentityResult> ChangePasswordAsync(IdentityUser user, string currentPassword, string newPassword)
{
var verifyResult = _hasher.VerifyHashedPassword(user.Id, user.PasswordHash, currentPassword);
if (verifyResult != Microsoft.AspNetCore.Identity.PasswordVerificationResult.Success)
{
return(new IdentityResult()
{
Succeeded = false,
Errors = new List <string>()
{
"Incorrect password supplied"
}
});
}
newPassword = newPassword.Trim();
if (string.IsNullOrEmpty(newPassword) || newPassword.Length < 7)
{
return(new IdentityResult()
{
Errors = new List <string> {
"Password must be at least 7 characters"
},
Succeeded = false
});
}
using (var db = new SqlDbContext(_options))
{
try
{
var dbUser = db.IdentityUser.FirstOrDefault(tbl => tbl.Id == user.Id);
if (dbUser == null)
{
user.PasswordHash = _hasher.HashPassword(user.Id, newPassword);
db.Update(user);
}
await db.SaveChangesAsync();
}
catch (Exception exc)
{
}
return(new IdentityResult()
{
Succeeded = true
});
}
}
protected TestWithSqlite()
{
_connection = new SqliteConnection(InMemoryConnectionString);
_connection.Open();
var options = new DbContextOptionsBuilder <SqlDbContext>()
.UseSqlite(_connection)
.Options;
DbContext = new SqlDbContext(options);
DbContext.Database.EnsureCreated();
}
public void Setup()
{
var options = new DbContextOptionsBuilder <SqlDbContext>().UseInMemoryDatabase(databaseName: "UserDatabase").Options;
userContext = new SqlDbContext(options);
foreach (var user in userContext.Users.ToArray())
{
userContext.Remove(user);
}
userManager = new UserManagerSql(userContext);
}
public void StartServices()
{
// DI ready
ServiceLocator.Start = (IServiceLocator locator) =>
{
IProfiler profiler = new WebProfiler();
IApplicationHost host = new WebApplicationHost(profiler);
ILocalizer localizer = new WebLocalizer(host);
IDbContext dbContext = new SqlDbContext(
profiler as IDataProfiler,
host as IDataConfiguration,
localizer as IDataLocalizer,
host as ITenantManager);
IRenderer renderer = new XamlRenderer(profiler);
IWorkflowEngine workflowEngine = new WorkflowEngine(host, dbContext);
IMessaging messaging = new MessageProcessor(host, dbContext);
IDataScripter scripter = new VueDataScripter();
ILogger logger = new WebLogger(host, dbContext);
IMessageService emailService = new EmailService(logger);
ISmsService smsService = new SmsService(dbContext, logger);
IExternalLoginManager externalLoginManager = new ExternalLoginManager(dbContext);
IUserStateManager userStateManager = new WebUserStateManager(host, dbContext);
locator.RegisterService <IDbContext>(dbContext);
locator.RegisterService <IProfiler>(profiler);
locator.RegisterService <IApplicationHost>(host);
locator.RegisterService <IRenderer>(renderer);
locator.RegisterService <IWorkflowEngine>(workflowEngine);
locator.RegisterService <IMessaging>(messaging);
locator.RegisterService <ILocalizer>(localizer);
locator.RegisterService <IDataScripter>(scripter);
locator.RegisterService <ILogger>(logger);
locator.RegisterService <IMessageService>(emailService);
locator.RegisterService <ISmsService>(smsService);
locator.RegisterService <IExternalLoginManager>(externalLoginManager);
locator.RegisterService <IUserStateManager>(userStateManager);
HttpContext.Current.Items.Add("ServiceLocator", locator);
};
ServiceLocator.GetCurrentLocator = () =>
{
if (HttpContext.Current == null)
{
throw new InvalidProgramException("There is no http context");
}
var locator = HttpContext.Current.Items["ServiceLocator"];
if (locator == null)
{
new ServiceLocator();
}
return(HttpContext.Current.Items["ServiceLocator"] as IServiceLocator);
};
}
public TestNoSqlBookUpdaterAsync()
{
_sqlOptions = this.CreateUniqueClassOptions <SqlDbContext>();
using (var context = new SqlDbContext(_sqlOptions))
{
context.Database.EnsureCreated();
var filepath = TestData.GetFilePath(@"..\..\EfCoreSqlAndCosmos\wwwroot\AddUserDefinedFunctions.sql");
context.ExecuteScriptFileInTransaction(filepath);
context.WipeAllDataFromDatabase();
}
}
public EfRepositorySqlServerTests()
{
_options = new DbContextOptionsBuilder <SqlDbContext>()
.UseSqlServer(@"Data Source=.\SqlExpress;Initial Catalog=AnyService_Test_DB;Integrated Security=True")
.Options;
_dbContext = new SqlDbContext(_options);
_logger = new Mock <ILogger <EfRepository <SqlBulkTestClass> > >();
_repository = new EfRepository <SqlBulkTestClass>(_dbContext, _logger.Object);
}
public int ExecuteNonQuery(SqlDbContext DbContext)
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DbContext.ParameterInitializes();
DbContext.ConnectionStatusCheck();
return(DbContext.DbCommand.ExecuteNonQuery());
}
return(default(int));
}
protected override void Execute(NativeActivityContext context)
{
WorkflowDataContext dataContext = context.DataContext;
PropertyDescriptorCollection propertyDescriptorCollection = dataContext.GetProperties();
string sessionId = string.Empty;
int fileSystemItemId = 0;
foreach (PropertyDescriptor propertyDesc in propertyDescriptorCollection)
{
if (propertyDesc.Name == "ArgSessionId")
{
sessionId = propertyDesc.GetValue(dataContext) as string;
break;
}
}
foreach (PropertyDescriptor propertyDesc in propertyDescriptorCollection)
{
if (propertyDesc.Name == "ArgFileSystemItemId")
{
fileSystemItemId = (int)propertyDesc.GetValue(dataContext);
break;
}
}
if (string.IsNullOrWhiteSpace(sessionId))
{
LogHelper.AddGeneralLog(GeneralLogTypeEnum.ActivityError, $"SessionId is null.");
return;
}
if (fileSystemItemId == 0)
{
LogHelper.AddSessionLog(SessionLogTypeEnum.ActivityError, sessionId, $"FileSystemItemId is null.");
return;
}
FileSystemItemDto fileSystemItem = null;
try
{
using (var sqlDbContext = new SqlDbContext())
{
fileSystemItem = sqlDbContext.FileSystemItems.FirstOrDefault(x => x.Id == fileSystemItemId);
}
}
catch (Exception ex)
{
LogHelper.AddSessionLog(SessionLogTypeEnum.ActivityError, sessionId, $"Exception has been thrown when getting fileSystemItem. FileSystemItemId: {fileSystemItemId}", ex);
return;
}
Result.Set(context, fileSystemItem);
}
public static void Seed(SqlDbContext dbContext, RoleManager<IdentityRole> roleManager, UserManager<Customer> userManager)
{
// Create default Users (if there are none)
if (!dbContext.Users.Any())
CreateCustomers(dbContext, roleManager, userManager).GetAwaiter().GetResult();
// create Inventory with auto-generated static data
if (!dbContext.Inventories.Any()) FillInventory(dbContext);
//Uncomment the following line if you need to add some Transactions
//if (!dbContext.Transactions.Any()) AddSomeTransactions(dbContext);
}
public IActionResult ResetDatabase(
[FromServices] SqlDbContext context,
[FromServices] NoSqlDbContext noSqlDbContext,
[FromServices] IWebHostEnvironment env)
{
context.DevelopmentWipeCreated(noSqlDbContext);
var numBooks = context.SeedDatabase(env.WebRootPath);
SetupTraceInfo();
return(View("Message", $"Successfully reset the database and added {numBooks} books."));
}
public void Delete(string id)
{
using (var db = new SqlDbContext(_options))
{
var result = db.Set <T>().FirstOrDefault(x => x.Id == id);
if (result != null)
{
db.Remove(result);
db.SaveChanges();
}
}
}
public async Task <DataSet> ExecuteDataSetAsync(SqlDbContext DbContext)
{
if (DbContext.OpenRealExecutionSaveToDb)
{
var reader = await ExecuteReaderAsync(DbContext);
DataSet ds = await DbDataReaderToDataSetAsync(reader);
return(ds);
}
return(default(DataSet));
}
private async Task <SqlDbContext> CreateContextWithTestData(IEnumerable <MyDto> data)
{
var options = new DbContextOptionsBuilder <SqlDbContext>()
.UseInMemoryDatabase(databaseName: "myTestDb")
.Options;
var context = new SqlDbContext(options);
await context.Data.AddRangeAsync(data);
await context.SaveChangesAsync();
return(context);
}
public void Init()
{
var dbContext = new SqlDbContext();
dbContext.Database.EnsureDeleted();
dbContext.Database.EnsureCreated();
_serviceProvider = new ServiceCollection()
.AddLogging()
.AddLibraryDependencies()
.AddDbContext <SqlDbContext>()
.BuildServiceProvider();
}
public DataSet ExecuteDataSet(SqlDbContext DbContext)
{
if (DbContext.OpenRealExecutionSaveToDb)
{
DbContext.ParameterInitializes();
DbContext.ConnectionStatusCheck();
DataSet ds = new DataSet();
DbContext.DbDataAdapter.Fill(ds);
return(ds);
}
return(default(DataSet));
}
public SqlDbContext Get()
{
return dataContext ?? (dataContext = new SqlDbContext());
}
