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); }