private void MapWholeInstanceDataToContext(MsSqlMonitorEntities context, InstanceInfo[] newInfo)
{
context.Configuration.AutoDetectChangesEnabled = false;
// Link InstanceInfo to instance, information for which it contains.
var linkedInstanceInfo = LinkInstancesByAssociatedId(newInfo, context);
// Get rid of offline instances.
linkedInstanceInfo = MarkAndRemoveOfflineInstances(linkedInstanceInfo);
// Update instance information
SetNewInstanceInfo(linkedInstanceInfo);
// Clear instance before adding new information.
ClearInstances(linkedInstanceInfo, context);
// Add to instances logins and roles.
AddPrincipalsToInstance(linkedInstanceInfo);
// Add to instances databases with their users and roles.
AddDatabasesAndTheirPrincipals(linkedInstanceInfo);
// Set permissions for instance principals.
SetInstancePrincipalsPermissions(linkedInstanceInfo, context);
// Set permissions for database principals.
SetDatabasePrinciaplsPermissions(linkedInstanceInfo, context);
// Map principals (users to roles)
MapPrincipals(linkedInstanceInfo);
context.ChangeTracker.DetectChanges();
context.SaveChanges();
}
public async Task SaveStatusOnlyAsync(InstanceInfo[] newInfo)
{
MsSqlMonitorEntities context = new MsSqlMonitorEntities();
MapBaseInstanceDataToContext(newInfo, context);
await context.SaveChangesAsync().ConfigureAwait(false);
}
public void SaveStatusOnly(InstanceInfo[] newInfo)
{
MsSqlMonitorEntities context = new MsSqlMonitorEntities();
MapBaseInstanceDataToContext(newInfo, context);
context.SaveChanges();
}
public async void RemoveInstanceForeverAsync(CollectionResult result, ISLogger logger)
{
logger.Debug("Removing forever instance id=" + result.InstanceID);
try
{
MsSqlMonitorEntities context = new MsSqlMonitorEntities();
Instance instance = context.Instances.Find(result.InstanceID);
instance.Assigns.ToList().ForEach(i => context.Assigns.Remove(i));
instance.Roles.ToList().ForEach(r => { r.RefuseAllLogins(); context.Entry(r).State = EntityState.Deleted; });
// Delete all users before update.
instance.Logins.ToList().ForEach(l => context.Entry(l).State = EntityState.Deleted);
// Delete all roles from databases
instance.Databases.ToList().ForEach(db => db.Roles.ToList().ForEach(role => { role.RefuseAllUsers(); context.Entry(role).State = EntityState.Deleted; }));
instance.Databases.ToList().ForEach(db => db.Users.ToList().ForEach(user => { user.RefuseAllRoles(); context.Entry(user).State = EntityState.Deleted; }));
// Delete all databases
instance.Databases.ToList().ForEach(db => context.Entry(db).State = EntityState.Deleted);
context.Entry(instance).State = EntityState.Deleted;
await context.SaveChangesAsync().ConfigureAwait(false);
}
catch (Exception e)
{
logger.Error(e.StackTrace);
}
}
public async Task GrantInstanceAccess(int instanceId, int userId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Assign assign = await context.Assigns.FirstOrDefaultAsync(a => a.InstanceId == instanceId && a.UserId == userId);
if (assign != null)
{
throw new Exception($"User '{userId}' already has assign to instance '{instanceId}'");
}
Instance instance = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instance == null)
{
throw new EntityNotExistException($"There is no instance with id '{instanceId}' in the database");
}
User user = await context.Users.FirstOrDefaultAsync(u => u.Id == userId);
if (user == null)
{
throw new EntityNotExistException($"There is no user with id '{userId}' in the database");
}
context.Assigns.Add(new Assign()
{
InstanceId = instanceId, UserId = userId
});
await context.SaveChangesAsync();
}
}
public async Task SaveInstancesAsync(InstanceInfo[] newInfo)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
MapWholeInstanceDataToContext(context, newInfo);
await context.SaveChangesAsync().ConfigureAwait(false);
}
}
public void SaveInstances(InstanceInfo[] newInfo)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
MapWholeInstanceDataToContext(context, newInfo);
context.SaveChanges();
}
}
public UserRepository(MsSqlMonitorEntities context, IAssignRepository assignRepository
, IInstanceRepository instanceRepository, ApplicationUserManager userManager) : base(context)
{
this.assignRepository = assignRepository;
this.instanceRepository = instanceRepository;
this.context = context;
this.userManager = userManager;
CheckIsAnyUser();
}
public async Task <InstanceViewModel> GetInstanceById(int instanceId)
{
using (var context = new MsSqlMonitorEntities())
{
Instance result = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (result == null)
{
throw new EntityNotExistException($"Instance with id {instanceId} doesn't exist.");
}
return(result.ToViewModel());
}
}
public void SaveStatusOnly(InstanceInfo[] newInfo, ISLogger logger)
{
try
{
MsSqlMonitorEntities context = new MsSqlMonitorEntities();
MapBaseInstanceDataToContext(newInfo, context);
context.SaveChanges();
}catch (Exception e)
{
logger.Error("LocalDb.SaveSatusOnly " + e.Message);
logger.Error(e.StackTrace);
}
}
public async Task ShowInstance(int instanceId, int userId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Assign assign = await context.Assigns.FirstOrDefaultAsync(a => a.InstanceId == instanceId && a.UserId == userId);
if (assign == null)
{
throw new EntityNotExistException($"Assignment of user '{userId}' to instance '{instanceId}' doesn't exist");
}
assign.IsHidden = false;
await context.SaveChangesAsync();
}
}
public async Task <IEnumerable <PermissionViewModel> > GetDatabasePrincipalPermissions(int principalId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
DbPrincipal dbPrincipal = await context.DbPrincipals.FirstOrDefaultAsync(princ => princ.Id == principalId);
if (dbPrincipal == null)
{
throw new EntityNotExistException($"Database principal with Id '{principalId}' doesn't exist.");
}
return((await context.DbPermissions.Where(perm => perm.Principals.Select(princ => princ.Id).Contains(principalId)).ToListAsync())
.Select(perm => perm.ToViewModel()).ToList());
}
}
public async Task <IEnumerable <DatabaseViewModel> > GetDatabases(int instanceId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Instance instance = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instance == null)
{
throw new EntityNotExistException($"Instance with Id '{instanceId}' doesn't exist.");
}
return((await context.Databases.Where(db => db.InstanceId == instanceId)
.ToListAsync()).Select(db => db.ToViewModel()).ToList());
}
}
public async Task RevokeInstanceAccess(int instanceId, int userId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Assign assign = await context.Assigns.FirstOrDefaultAsync(a => a.InstanceId == instanceId && a.UserId == userId);
if (assign == null)
{
throw new EntityNotExistException($"Assignment of user '{userId}' to instance '{instanceId}' doesn't exist");
}
context.Entry(assign).State = EntityState.Deleted;
await context.SaveChangesAsync();
}
}
public async Task <IEnumerable <UserViewModel> > GetNotAssignedUsers(int instanceId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Instance instance = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instance == null)
{
throw new EntityNotExistException($"Instance with Id '{instanceId}' doesn't exist.");
}
var assignedUsersIds = await(from assign in context.Assigns where assign.InstanceId == instanceId select assign.UserId).ToListAsync();
return((await context.Users.Where(user => !assignedUsersIds.Contains(user.Id)).ToListAsync())
.Select(user => user.ToViewModel()).ToList());
}
}
public async Task <IEnumerable <DatabaseRoleViewModel> > GetDatabaseRoles(int databaseId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Database database = await context.Databases.FirstOrDefaultAsync(db => db.Id == databaseId);
if (database == null)
{
throw new EntityNotExistException($"Database with Id '{databaseId}' doesn't exist.");
}
return((await context.DbRoles.Where(role => role.DatabaseId == databaseId)
.Include(role => role.Users).ToListAsync())
.Select(role => role.ToViewModel()).ToList());
}
}
public async Task <IEnumerable <InstanceLoginViewModel> > GetLogins(int instanceId)
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
Instance instance = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instance == null)
{
throw new EntityNotExistException($"Instance with Id '{instanceId}' doesn't exist.");
}
return((await context.InstLogins.Where(login => login.InstanceId == instanceId)
.Include(login => login.Roles)
.ToListAsync()).Select(login => login.ToViewModel()).ToList());
}
}
public void SaveInstances(InstanceInfo[] newInfo, ISLogger logger)
{
try
{
using (MsSqlMonitorEntities context = new MsSqlMonitorEntities())
{
MapWholeInstanceDataToContext(context, newInfo);
context.SaveChanges();
}
}
catch (Exception e)
{
logger.Error("LocalDb.SaveSatusOnly " + e.Message);
logger.Error(e.StackTrace);
}
}
public async Task <InstanceViewModel> RecoverInstance(int instanceId)
{
using (var context = new MsSqlMonitorEntities())
{
Instance instToRecover = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instToRecover == null)
{
throw new EntityNotExistException($"Instance with id '{instanceId}' that you are trying to recover doesn't exist");
}
instToRecover.IsDeleted = false;
await context.SaveChangesAsync();
return(instToRecover.ToViewModel());
}
}
public async Task <InstanceViewModel> DeleteInstance(int instanceId)
{
using (var context = new MsSqlMonitorEntities())
{
Instance instToDelete = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == instanceId);
if (instToDelete == null)
{
throw new EntityNotExistException($"Instance with id '{instanceId}' that you try to delete doesn't exist");
}
instToDelete.IsDeleted = true;
instToDelete.IsDeletedTime = DateTime.Now;
await context.SaveChangesAsync();
return(instToDelete.ToViewModel());
}
}
public async Task SaveStatusOnlyAsync(InstanceInfo[] newInfo, ISLogger logger)
{
try
{
MsSqlMonitorEntities context = new MsSqlMonitorEntities();
MapBaseInstanceDataToContext(newInfo, context);
await context.SaveChangesAsync().ConfigureAwait(false);
}
catch (AggregateException e)
{
foreach (Exception ex in e.InnerExceptions)
{
logger.Error("LocalDb.SaveSatusOnly " + ex.Message);
}
logger.Error(e.StackTrace);
}
}
private InstPermission GetInstPermission(string name, string state, MsSqlMonitorEntities context)
{
InstPermission permission = context.InstPermissions.FirstOrDefault(perm => perm.Name == name && perm.State == state);
if (permission == null)
{
permission = context.InstPermissions.Local.FirstOrDefault(perm => perm.Name == name && perm.State == state);
}
if (permission == null)
{
permission = new InstPermission()
{
Name = name, State = state
};
context.InstPermissions.Add(permission);
}
return(permission);
}
public async Task <InstanceViewModel> UpdateInstance(InstanceViewModel source)
{
if (!source.Id.HasValue)
{
throw new InvalidModelException("Instance id is required.");
}
using (var context = new MsSqlMonitorEntities())
{
Instance instToUpdate = await context.Instances.FirstOrDefaultAsync(inst => inst.Id == source.Id);
if (instToUpdate == null)
{
throw new EntityNotExistException($"Instance with id '{source.Id}' that you try to update is not exist");
}
if (!String.IsNullOrEmpty(source.ServerName))
{
instToUpdate.ServerName = source.ServerName;
}
if (!String.IsNullOrEmpty(source.InstanceName))
{
instToUpdate.InstanceName = source.InstanceName;
}
if (!String.IsNullOrEmpty(source.Login))
{
instToUpdate.Login = source.Login;
}
if (!String.IsNullOrEmpty(source.Password))
{
string encryptionKey;
instToUpdate.Password = encryptionManager.Encrypt(out encryptionKey, source.Password);
instToUpdate.EncryptionKey = encryptionKey;
}
if (source.IsWindowsAuthentication.HasValue)
{
instToUpdate.Authentication = source.IsWindowsAuthentication.Value
? AuthenticationType.Windows
: AuthenticationType.Sql;
}
await context.SaveChangesAsync();
return(instToUpdate.ToViewModel());
}
}
public async Task <InstanceViewModel> AddInstance(InstanceViewModel newInstance)
{
if (String.IsNullOrEmpty(newInstance.InstanceName) ||
String.IsNullOrEmpty(newInstance.ServerName) ||
String.IsNullOrEmpty(newInstance.Login) ||
String.IsNullOrEmpty(newInstance.Password) ||
!newInstance.IsWindowsAuthentication.HasValue)
{
throw new InvalidModelException("Instance name, Server name, Login, Password, and authentication type are required.");
}
string encryptionKey;
var encryptedPassword = encryptionManager.Encrypt(out encryptionKey, newInstance.Password);
using (var context = new MsSqlMonitorEntities())
{
if (null != await context.Instances.FirstOrDefaultAsync(inst => inst.ServerName.ToUpper() == newInstance.ServerName.ToUpper() &&
inst.InstanceName.ToUpper() == newInstance.InstanceName.ToUpper()))
{
throw new ObjectAlreadyExistException("Instance already exist in the system.");
}
Instance result = new Instance()
{
ServerName = newInstance.ServerName,
InstanceName = newInstance.InstanceName,
Password = encryptedPassword,
EncryptionKey = encryptionKey,
Login = newInstance.Login,
Authentication = newInstance.IsWindowsAuthentication.Value ? AuthenticationType.Windows : AuthenticationType.Sql
};
context.Instances.Add(result);
await context.SaveChangesAsync();
return(result.ToViewModel());
}
}
private List <Tuple <Instance, InstanceInfo> > LinkInstancesByAssociatedId(InstanceInfo[] infos, MsSqlMonitorEntities context)
{
var result = new List <Tuple <Instance, InstanceInfo> >(infos.Length);
int toFind = infos.Length;
foreach (InstanceInfo info in infos)
{
foreach (Instance inst in context.Instances)
{
if (inst.Id == info.NativeId)
{
result.Add(new Tuple <Instance, InstanceInfo>(inst, info));
toFind--;
break;
}
}
if (toFind == 0)
{
break;
}
}
return(result);
}
private void ClearInstances(List <Tuple <Instance, InstanceInfo> > linkedInfo, MsSqlMonitorEntities context)
{
// Delete all roles before update.
linkedInfo.ForEach(li => li.Item1.Roles.ToList().ForEach(r => { r.RefuseAllLogins(); context.Entry(r).State = EntityState.Deleted; }));
// Delete all users before update.
linkedInfo.ForEach(li => li.Item1.Logins.ToList().ForEach(l => context.Entry(l).State = EntityState.Deleted));
// Delete all roles from databases
linkedInfo.ForEach(li => li.Item1.Databases.ToList().ForEach(db => db.Roles.ToList().ForEach(role => { role.RefuseAllUsers(); context.Entry(role).State = EntityState.Deleted; })));
linkedInfo.ForEach(li => li.Item1.Databases.ToList().ForEach(db => db.Users.ToList().ForEach(user => { user.RefuseAllRoles(); context.Entry(user).State = EntityState.Deleted; })));
// Delete all databases
linkedInfo.ForEach(li => li.Item1.Databases.ToList().ForEach(db => context.Entry(db).State = EntityState.Deleted));
}
private void SetDatabasePrinciaplsPermissions(List <Tuple <Instance, InstanceInfo> > linkedInfo, MsSqlMonitorEntities context)
{
linkedInfo.ForEach(li => // In every instance
{
foreach (DatabaseInfo dbInfo in li.Item2.Databases) // in every database in the instance
{
foreach (PermissionInfo permInfo in li.Item2.Permissions)
{
// Permission should be unique, so we take one from database if it exists otherwise create new.
DbPermission permission = GetDbPermission(permInfo.Name, permInfo.State, context);
int toFind = permInfo.AssociatedIds.Count; // count of associated with permission principals
foreach (DbRoleInfo role in dbInfo.Roles)
{
if (permInfo.AssociatedIds.Contains(role.NativeId))
{
role.Entity.Permissions.Add(permission);
toFind--;
}
if (toFind == 0)
{
break;
}
}
if (toFind == 0)
{
continue;
}
foreach (DbUserInfo user in dbInfo.Users)
{
if (permInfo.AssociatedIds.Contains(user.NativeId))
{
user.Entity.Permissions.Add(permission);
toFind--;
}
if (toFind == 0)
{
break;
}
}
}
}
});
}
private void MapBaseInstanceDataToContext(InstanceInfo[] newInfo, MsSqlMonitorEntities context)
{
var linkedInstanceInfo = LinkInstancesByAssociatedId(newInfo, context);
SetNewInstanceInfo(linkedInstanceInfo);
}
static void addDataForTesting()
{
List <BrowsableInstance> list = CommonLib.BrowsableInstance.GetInstances();
MsSqlMonitorEntities db = new MsSqlMonitorEntities();
//db.SaveChanges();
/*
* User user;
* if (db.Users.LongCount<User>() == 0)
* {
* user = new User();
* user.Login = "******";
* user.Password = "******";
* user.Role = UserRole.Admin;
* db.Users.Add(user);
* db.SaveChanges();
*
* user = new User();
* user.Login = "******";
* user.Password = "******";
* user.Role = UserRole.User;
* db.Users.Add(user);
* db.SaveChanges();
*
*
* } else
* {
* user = db.Users.First<User>();
* }
*/
foreach (var browsableInstance in list)
{
if (db.Instances.Where <Instance>(x => x.InstanceName.Equals(browsableInstance.InstanceName)).LongCount() != 0)
{
continue;
}
Instance newInst = new Instance();
newInst.Authentication = AuthenticationType.Sql;
newInst.InstanceName = browsableInstance.InstanceName;
newInst.ServerName = browsableInstance.ServerName;
newInst.Login = "******";
newInst.Password = "******";
newInst.IsDeleted = false;
db.Instances.Add(newInst);
// db.
///////////////////////////////////////////////////
/*
* Assign assign = new Assign();
* assign.User = user;
* assign.UserId = user.Id;
* assign.Instance = newInst;
* assign.InstanceId = newInst.Id;
*
* db.Assigns.Add(assign);
*/
db.SaveChanges();
//////////////////////////////////////////////
/*
* InstanceUpdateJob updateJob = new InstanceUpdateJob();
* updateJob.Instance = newInst;
* updateJob.InstanceId = newInst.Id;
*
* JobType jobType = db.JobTypes.Where<JobType>(i => i.Type == JobType.UpdateInfoType.Full).First<JobType>();
*
* updateJob.JobType = jobType;
* updateJob.JobTypeId = jobType.Type;
*
*
* db.InstanceUpdateJobs.Add(updateJob);
*
*
* db.SaveChanges();
*
* //////////////////////////////////////////////
*
* InstanceUpdateJob updateJob2 = new InstanceUpdateJob();
* updateJob2.Instance = newInst;
* updateJob2.InstanceId = newInst.Id;
*
* JobType jobType2 = db.JobTypes.Where<JobType>(i => i.Type == JobType.UpdateInfoType.CheckStatus).First<JobType>();
*
* updateJob2.JobType = jobType2;
* updateJob2.JobTypeId = jobType.Type;
*
*
* db.InstanceUpdateJobs.Add(updateJob2);
*
*/
db.SaveChanges();
//////////////////////////////////////////////
}
// db.SaveChanges();
// db.SaveChanges();
}
public DatabaseRepository(MsSqlMonitorEntities context) : base(context)
{
table = context.Databases;
}
