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");
}
}
}
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 bool IsEmailPasswordValid(LogOnViewModel viewModel, out string messageOrId)
{
viewModel.GeoLocation = GetGeolocationInfo();
DataSet identityDataSet = new DataSet();
string usersTable = "[dbo].[ApplicationUsers]";
string userPasswordTable = "[dbo].[ApplicationUserPasswordHistories]";
string userSignInTable = "[dbo].[ApplicationUserSignInHistories]";
string userId = string.Empty;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["IdentityDbConnectionString"].ConnectionString))
{
connection.Open();
string selectByEmail = $"select * from {usersTable} where [Email]='{viewModel.Email}'";
using (SqlDataAdapter adapter = new SqlDataAdapter(selectByEmail, connection))
{
//проверяем, есть ли емейл в базе
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
adapter.Fill(identityDataSet);
if (identityDataSet.Tables[0].Rows.Count == 0)
{
messageOrId = $"There is no email in database like {viewModel.Email}";
return(false);
}
userId = identityDataSet.Tables[0].Rows[0]["Id"].ToString();
//проверяем, подходит ли пароль емейлу
identityDataSet.Clear();
string selectByEmailAndPassword = $"select * from {usersTable} u, {userPasswordTable} p " +
$"where u.[Id]=p.[ApplicationUserId] " +
$"and u.[Email]='{viewModel.Email}' and p.[PasswordHash]= '{viewModel.Password}' and p.[InvalidatedDate] is null";
adapter.SelectCommand = new SqlCommand(selectByEmailAndPassword, connection);
commandBuilder = new SqlCommandBuilder(adapter);
adapter.Fill(identityDataSet);
if (identityDataSet.Tables[0].Rows.Count == 0)
{
DataSet tmp = new DataSet();
adapter.SelectCommand = new SqlCommand(selectByEmail, connection);
commandBuilder = new SqlCommandBuilder(adapter);
adapter.Fill(tmp);
DataTable table = tmp.Tables[0];
int cnt = 0;
Int32.TryParse(table.Rows[0]["FailedSignInCount"].ToString(), out cnt);
table.Rows[0]["FailedSignInCount"] = cnt + 1;
adapter.Update(tmp);
messageOrId = "Invalid password, try again";
return(false);
}
//добавляем строку нового входа в таблице ApplicationUserSignInHistories в БД
identityDataSet.Clear();
ApplicationUserSignInHistories userSignIn = new ApplicationUserSignInHistories()
{
Id = Guid.NewGuid().ToString(),
ApplicationUserId = userId,
SignInTime = DateTime.Now.ToString("yyyy-MM-dd"),
MachineIp = viewModel.GeoLocation.ip,
IpToGeoCountryCode = viewModel.GeoLocation.country_name,
IpToGeoCityName = viewModel.GeoLocation.city,
IpToGeoLatitude = viewModel.GeoLocation.latitude,
IpToGeoLongitude = viewModel.GeoLocation.longitude
};
string userSignInSql = $"select * from {userSignInTable}";
adapter.SelectCommand = new SqlCommand(userSignInSql, connection);
commandBuilder = new SqlCommandBuilder(adapter);
adapter.Fill(identityDataSet);
DataRow dataRow = identityDataSet.Tables[0].NewRowWithData(userSignIn);
identityDataSet.Tables[0].Rows.Add(dataRow);
adapter.Update(identityDataSet);
}
}
messageOrId = userId;
return(true);
}
