public void ChangeUserPassword(ChangePasswordViewModel viewModel)
{
DataSet identityDataSet = new DataSet();
string usersTable = "[dbo].[ApplicationUsers]";
string userPasswordHistoriesTable = "[dbo].[ApplicationUserPasswordHistories]";
string userSignInHistoriesTable = "[dbo].[ApplicationUserSignInHistories]";
using (TransactionScope transactionScope = new TransactionScope())
{
try
{
using (SqlConnection identityConnection = new SqlConnection(ApplicationSettings.IDENTITY_CONNECTION_STRING))
{
identityConnection.Open();
string selectPasswordByEmail = $"select u.Email, u.Id, p.SetupDate, p.InvalidatedDate, p.PasswordHash" +
$" from {usersTable} u, {userPasswordHistoriesTable} p where u.Id=p.ApplicationUserId" +
$" and u.Email='{viewModel.Email}' order by p.SetupDate desc";
using (SqlDataAdapter identityAdapter = new SqlDataAdapter(selectPasswordByEmail, identityConnection))
{
identityAdapter.Fill(identityDataSet);
SqlCommandBuilder identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
var tablePasswordsSelected = identityDataSet.Tables[0];
var rows = tablePasswordsSelected.Rows;
var rowsNum = tablePasswordsSelected.Rows.Count;
int rowsCount = 0;
if (rowsNum < 5)
{
rowsCount = rowsNum;
}
else
{
rowsCount = 5;
}
for (int i = 0; i < rowsCount; i++)
{
if (rows[i][4].ToString() == viewModel.newPassword)
{
throw new ApplicationException($"{viewModel.newPassword} was before. Choose another password");
}
}
var userId = tablePasswordsSelected.Rows[0]["Id"];
ApplicationUserPasswordHistories PasswordHistories = new ApplicationUserPasswordHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = userId.ToString(),
SetupDate = DateTime.Now.ToString("yyyy-MM-dd"),
InvalidatedDate = DateTime.Now.AddMonths(3).ToString("yyyy-MM-dd"),
PasswordHash = viewModel.newPassword
};
string userPasswordSql = $"select * from {userPasswordHistoriesTable}";
identityAdapter.SelectCommand = new SqlCommand(userPasswordSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet, userPasswordHistoriesTable);
var dataRow = identityDataSet.Tables[userPasswordHistoriesTable].NewRowWithData(PasswordHistories);
identityDataSet.Tables[userPasswordHistoriesTable].Rows.Add(dataRow);
identityAdapter.Update(identityDataSet, userPasswordHistoriesTable);
}
}
transactionScope.Complete();
}
catch (Exception ex)
{
throw ex;
}
}
}
public void ChangeUserPassword(ChangeUserPasswordViewModel viewModel)
{
//if (viewModel.newPassword != viewModel.newPasswordConfirmation)
// throw new ApplicationException("Passwords do not match");
string identityConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Auction.IdentityDb;Data Source=DM-ПК\SQLEXPRESS";
DataSet identityDataSet = new DataSet();
string userTable = "[dbo].[ApplicationUsers]";
//string userSignInTable = "[dbo].[ApplicationUserSignInHistories]";
string userPasswordTable = "[dbo].[ApplicationUserPasswordHistories]";
string userId = string.Empty;
using (SqlConnection identityConnection = new SqlConnection(identityConnectionString))
{
identityConnection.Open();
string selectEmailPasswordSql = $"select u.Email, u.Id, p.SetupDate, p.PasswordHash " +
$"from {userTable} u, {userPasswordTable} p " +
$"where u.Id=p.ApplicationUserId and u.Email='{viewModel.Email}'" +
$"order by p.SetupDate desc";
using (SqlDataAdapter identityAdapter = new SqlDataAdapter(selectEmailPasswordSql, identityConnection))
{
//проверим, есть ли новый пароль в предыдущих 5
identityAdapter.Fill(identityDataSet);
SqlCommandBuilder identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
DataTable dataTable = identityDataSet.Tables[0];
userId = dataTable.Rows[0][1].ToString();
int cnt = 0;
if (dataTable.Rows.Count <= 5)
{
cnt = dataTable.Rows.Count;
}
else if (dataTable.Rows.Count > 5)
{
cnt = 5;
}
for (int i = 0; i < cnt; i++)
{
if (viewModel.newPassword == dataTable.Rows[i][3].ToString())
{
throw new ApplicationException($"{viewModel.newPassword} has already used. Choose another password");
}
}
//изменяем InvalidatedDate в таблице ApplicationUserPasswordHistories
identityDataSet.Clear();
string selectUserPassword = $"select * from {userPasswordTable} " +
$"where ApplicationUserId = '{userId}' " +
$"and InvalidatedDate is null";
identityAdapter.SelectCommand = new SqlCommand(selectUserPassword, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet);
DataTable table = identityDataSet.Tables[0];
table.Rows[0]["InvalidatedDate"] = DateTime.Now.ToString("yyyy-MM-dd");
identityAdapter.Update(identityDataSet);
//добавляем строку в ApplicationUserPasswordHistories в БД
identityDataSet.Clear();
ApplicationUserPasswordHistories userPassword = new ApplicationUserPasswordHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = userId,
SetupDate = DateTime.Now.ToString("yyyy-MM-dd"),
PasswordHash = viewModel.newPassword
};
string userPasswordSql = $"select * from {userPasswordTable}";
identityAdapter.SelectCommand = new SqlCommand(userPasswordSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet, userPasswordTable);
var dataRow = identityDataSet.Tables[userPasswordTable].NewRowWithData(userPassword);
identityDataSet.Tables[userPasswordTable].Rows.Add(dataRow);
identityAdapter.Update(identityDataSet, userPasswordTable);
}
}
}
public void OpenOrganization(OpenOrganizationViewModel viewModel)
{
DataSet applicationDataSet = new DataSet();
DataSet identityDataSet = new DataSet();
string organizationsTable = "[dbo].[Organizations]";
string employeeTable = "[dbo].[Employees]";
Employee employee = new Employee();
using (TransactionScope transactionScope = new TransactionScope())
{
try
{
using (SqlConnection applicationConnection = new SqlConnection(
ApplicationSettings.APPLICATION_CONNECTION_STRING))
{
applicationConnection.Open();
string selectOrganizationByIdentificatorsSql = $"select * from {organizationsTable} " +
$"where [IdentificationNumber] = {viewModel.OrganizationIdentificationNumber}";
string selectOrganizationsSql = $"select * from {organizationsTable}";
using (SqlDataAdapter applicationAdapter = new SqlDataAdapter(selectOrganizationByIdentificatorsSql,
applicationConnection))
{
applicationAdapter.Fill(applicationDataSet, organizationsTable);
SqlCommandBuilder applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
bool isOrganizationAlreadyExist = applicationDataSet.Tables[organizationsTable].Rows.Count != 0;
if (isOrganizationAlreadyExist)
{
throw new ApplicationException($"Already has an organization with IdentificationNumber = {viewModel.OrganizationIdentificationNumber}");
}
applicationDataSet.Clear();
Organization organization = new Organization()
{
Id = Guid.NewGuid().ToString(),
FullName = viewModel.OrganizationFullName,
IdentificationNumber = viewModel.OrganizationIdentificationNumber,
OrganizationTypeId = viewModel.OrganizationTypeId,
RegistrationDate = DateTime.Now.ToString("yyyy-MM-dd")
};
applicationAdapter.SelectCommand = new SqlCommand(selectOrganizationsSql, applicationConnection);
applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
applicationAdapter.Fill(applicationDataSet, organizationsTable);
var dataRow = applicationDataSet.Tables[organizationsTable].NewRowWithData(organization);
applicationDataSet.Tables[organizationsTable].Rows.Add(dataRow);
applicationAdapter.Update(applicationDataSet, organizationsTable);
employee.Id = Guid.NewGuid().ToString();
employee.FirstName = viewModel.CeoFirstName;
employee.LastName = viewModel.CeoLastName;
employee.MiddleName = viewModel.CeoMiddleName;
employee.Email = viewModel.Email;
employee.DoB = viewModel.DoB;
employee.OrganizationId = Guid.NewGuid().ToString();
string selectEmployeeSql = $"select * from {employeeTable}";
applicationAdapter.SelectCommand = new SqlCommand(selectEmployeeSql, applicationConnection);
applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
applicationAdapter.Fill(applicationDataSet, employeeTable);
dataRow = applicationDataSet.Tables[employeeTable].NewRowWithData(employee);
applicationDataSet.Tables[employeeTable].Rows.Add(dataRow);
applicationAdapter.Update(applicationDataSet, employeeTable);
}
}
using (SqlConnection identityConnection = new SqlConnection(
ApplicationSettings.IDENTITY_CONNECTION_STRING))
{
identityConnection.Open();
string usersTable = "[dbo].[ApplicationUsers]";
string selectUserByEmail = $"select * from {usersTable} where [Email]='{viewModel.Email}'";
using (SqlDataAdapter identityUserAdapter = new SqlDataAdapter(selectUserByEmail, identityConnection))
{
identityUserAdapter.Fill(identityDataSet, usersTable);
SqlCommandBuilder identityCommandBuilder = new SqlCommandBuilder(identityUserAdapter);
bool isUserAlreadyExist = identityDataSet.Tables[usersTable].Rows.Count != 0;
if (isUserAlreadyExist)
{
throw new ApplicationException($"Already has user with email = {viewModel.Email}");
}
identityDataSet.Clear();
ApplicationUser user = new ApplicationUser()
{
Id = Guid.NewGuid().ToString(),
Email = viewModel.Email,
IsActivatedAccount = true,
FailedSigninCount = 0,
IsBlockedBySystem = false,
AssociatedEmployeeId = employee.Id,
CreationDate = DateTime.Now.ToString("yyyy-MM-dd")
};
string selectUsersSql = $"select * from {usersTable}";
identityUserAdapter.SelectCommand = new SqlCommand(selectUsersSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityUserAdapter);
identityUserAdapter.Fill(identityDataSet, usersTable);
var dataRow = identityDataSet.Tables[usersTable].NewRowWithData(user);
identityDataSet.Tables[usersTable].Rows.Add(dataRow);
identityUserAdapter.Update(identityDataSet, usersTable);
identityDataSet.Clear();
ApplicationUserPasswordHistories userPassword = new ApplicationUserPasswordHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = user.Id,
SetupDate = DateTime.Now.ToString("yyyy-MM-dd"),
InvalidatedDate = DateTime.Now.AddMonths(3).ToString("yyyy-MM-dd"),
PasswordHash = viewModel.Password
};
string usersPasswordsTable = "[dbo].[ApplicationUserPasswordHistories]";
string SelectUserPasswordSql = $"select * from {usersPasswordsTable}";
identityUserAdapter.SelectCommand = new SqlCommand(SelectUserPasswordSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityUserAdapter);
identityUserAdapter.Fill(identityDataSet, usersPasswordsTable);
dataRow = identityDataSet.Tables[usersPasswordsTable].NewRowWithData(userPassword);
identityDataSet.Tables[usersPasswordsTable].Rows.Add(dataRow);
identityUserAdapter.Update(identityDataSet, usersPasswordsTable);
identityDataSet.Clear();
GeoLocationInfo geoLocationInfo = GetGeolocationInfo();
ApplicationUserSignInHistories userSignIn = new ApplicationUserSignInHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = user.Id,
SignInTime = DateTime.Now.ToString("yyyy-MM-dd"),
MachineIp = geoLocationInfo.ip,
IpToGeoCountryCode = geoLocationInfo.country_name,
IpToGeoCityName = geoLocationInfo.city,
IpToGeoLatitude = geoLocationInfo.latitude,
IpToGeoLongitude = geoLocationInfo.longitude
};
string userSignInTable = "[dbo].[ApplicationUserSignInHistories]";
string userSignInSql = $"select * from {userSignInTable}";
identityUserAdapter.SelectCommand = new SqlCommand(userSignInSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityUserAdapter);
identityUserAdapter.Fill(identityDataSet, userSignInTable);
dataRow = identityDataSet.Tables[userSignInTable].NewRowWithData(userSignIn);
identityDataSet.Tables[userSignInTable].Rows.Add(dataRow);
identityUserAdapter.Update(identityDataSet, userSignInTable);
}
}
transactionScope.Complete();
}
catch (Exception ex)
{
throw ex;
}
}
}
public void OpenOrganization(OpenOrganizationViewModel viewModel)
{
DataSet applicationDataSet = new DataSet();
DataSet identityDataSet = new DataSet();
string organizationsTable = "[dbo].[Organizations]";
string employeeTable = "[dbo].[Employees]";
string userTable = "[dbo].[ApplicationUsers]";
string userSignInTable = "[dbo].[ApplicationUserSignInHistories]";
string userPasswordTable = "[dbo].[ApplicationUserPasswordHistories]";
string associatedEmpId = string.Empty;
string applicationConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Auction.ApplicationDb;Data Source=DM-ПК\SQLEXPRESS";
string identityConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Auction.IdentityDb;Data Source=DM-ПК\SQLEXPRESS";
GeoLocationInfo geoLocationInfo = GetGeolocationInfo();
using (TransactionScope transactionScope = new TransactionScope())
{
try
{
using (SqlConnection applicationConnection = new SqlConnection(applicationConnectionString))
{
applicationConnection.Open();
string selectOrganizationByIdentificatorsSql = $"select * from {organizationsTable} " +
$"where [IdentificationNumber] = {viewModel.OrganizationIdentificationNumber}";
//формируем адаптер для выгрузки данных в датасет
using (SqlDataAdapter applicationAdapter = new SqlDataAdapter(selectOrganizationByIdentificatorsSql, applicationConnection))
{
//заполняем в датасет таблицу organizationsTable и формируем для него SqlCommandBuilder для последующего добавления данных
applicationAdapter.Fill(applicationDataSet, organizationsTable);
SqlCommandBuilder applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
//проводим проверку, есть ли уже такая организация в БД
bool isOrganizationAlreadyExist = applicationDataSet
.Tables[organizationsTable].Rows.Count != 0;
if (isOrganizationAlreadyExist)
{
throw new ApplicationException($"Already has an organization with IdentificationNumber = {viewModel.OrganizationIdentificationNumber}");
}
//очищаем датасет для дальнейших манипуляций с БД
applicationDataSet.Clear();
Organization organization = new Organization()
{
Id = Guid.NewGuid().ToString(),
FullName = viewModel.OrganizationFullName,
IdentificationNumber = viewModel.OrganizationIdentificationNumber,
OrganizationTypeId = viewModel.OrganizationTypeId,
RegistrationDate = DateTime.Now.ToString("yyyy-MM-dd")
};
//делаем новую команду для SqlCommandBuilder - выбрать все из таблицы organizationsTable
string selectOrganizationsSql = $"select * from {organizationsTable}";
applicationAdapter.SelectCommand = new SqlCommand(selectOrganizationsSql, applicationConnection);
applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
applicationAdapter.Fill(applicationDataSet, organizationsTable); //заполняем датасет новыми данными
var dataRow = applicationDataSet.Tables[organizationsTable].NewRowWithData(organization); //формируем новую строку для таблицы organizationsTable, запихиваем в нее данные объекта organization
applicationDataSet.Tables[organizationsTable].Rows.Add(dataRow); //добавляем строку в organizationsTable
applicationAdapter.Update(applicationDataSet, organizationsTable); //обновляем таблицу Organizations в БД
//добавляем новые данные в таблицу Employees в БД
applicationDataSet.Clear();
Employee employee = new Employee()
{
Id = Guid.NewGuid().ToString(),
FirstName = viewModel.CeoFirstName,
LastName = viewModel.CeoLastName,
MiddleName = viewModel.CeoMiddleName,
Email = viewModel.Email,
DoB = viewModel.DoB.ToString("yyyy-MM-dd"),
OrganizationId = Guid.NewGuid().ToString()
};
associatedEmpId = employee.Id;
string selectEmployeeSql = $"select * from {employeeTable}";
applicationAdapter.SelectCommand = new SqlCommand(selectEmployeeSql, applicationConnection);
applicationCommandBuilder = new SqlCommandBuilder(applicationAdapter);
applicationAdapter.Fill(applicationDataSet, employeeTable);
dataRow = applicationDataSet.Tables[employeeTable].NewRowWithData(employee);
applicationDataSet.Tables[employeeTable].Rows.Add(dataRow);
applicationAdapter.Update(applicationDataSet, employeeTable);
//transactionScope.Complete();
}
}
using (SqlConnection identityConnection = new SqlConnection(identityConnectionString))
{
identityConnection.Open();
string selectUserByEmailSql = $"select * from {userTable} " +
$"where [Email] = '{viewModel.Email}'";
//формируем адаптер и вытаскиваем данные о юзерах с таким емейлом
using (SqlDataAdapter identityAdapter = new SqlDataAdapter(selectUserByEmailSql, identityConnection))
{
identityAdapter.Fill(identityDataSet, userTable);
SqlCommandBuilder identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
//проверяем есть ли юзеры с таким мейлом
bool isUserAlreadyExist = identityDataSet
.Tables[userTable].Rows.Count != 0;
if (isUserAlreadyExist)
{
throw new ApplicationException($"Already has a user with Email = {viewModel.Email}");
}
//добавляем юзера в ApplicationUser в БД
identityDataSet.Clear();
ApplicationUser user = new ApplicationUser()
{
Id = Guid.NewGuid().ToString(),
Email = viewModel.Email,
IsActivatedAccount = true,
FailedSigninCount = 0,
IsBlockedBySystem = false,
AssociatedEmployeeId = associatedEmpId,
CreationDate = DateTime.Now.ToString("yyyy-MM-dd")
};
string selectUserSql = $"select * from {userTable}";
identityAdapter.SelectCommand = new SqlCommand(selectUserSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet, userTable);
var dataRow = identityDataSet.Tables[userTable].NewRowWithData(user);
identityDataSet.Tables[userTable].Rows.Add(dataRow);
identityAdapter.Update(identityDataSet, userTable);
//добавляем строку в ApplicationUserPasswordHistories в БД
identityDataSet.Clear();
ApplicationUserPasswordHistories userPassword = new ApplicationUserPasswordHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = user.Id,
SetupDate = DateTime.Now.ToString("yyyy-MM-dd"),
PasswordHash = viewModel.Password
};
string userPasswordSql = $"select * from {userPasswordTable}";
identityAdapter.SelectCommand = new SqlCommand(userPasswordSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet, userPasswordTable);
dataRow = identityDataSet.Tables[userPasswordTable].NewRowWithData(userPassword);
identityDataSet.Tables[userPasswordTable].Rows.Add(dataRow);
identityAdapter.Update(identityDataSet, userPasswordTable);
//добавляем строку в ApplicationUserSignInHistories в БД
identityDataSet.Clear();
//GeoLocationInfo geoLocationInfo = GetGeolocationInfo();
ApplicationUserSignInHistories userSignIn = new ApplicationUserSignInHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = user.Id,
SignInTime = DateTime.Now.ToString("yyyy-MM-dd"),
MachineIp = geoLocationInfo.ip,
IpToGeoCountryCode = geoLocationInfo.country_name,
IpToGeoCityName = geoLocationInfo.city,
IpToGeoLatitude = geoLocationInfo.latitude,
IpToGeoLongitude = geoLocationInfo.longitude
};
string userSignInSql = $"select * from {userSignInTable}";
identityAdapter.SelectCommand = new SqlCommand(userSignInSql, identityConnection);
identityCommandBuilder = new SqlCommandBuilder(identityAdapter);
identityAdapter.Fill(identityDataSet, userSignInTable);
dataRow = identityDataSet.Tables[userSignInTable].NewRowWithData(userSignIn);
identityDataSet.Tables[userSignInTable].Rows.Add(dataRow);
identityAdapter.Update(identityDataSet, userSignInTable);
}
}
transactionScope.Complete();
}
catch (Exception)
{
throw new ApplicationException("No connection");
}
}
}