/// <summary> /// Сохранение состояния существующего объекта в БД. /// Суть - апдейт, при передаче объекта отсутствующего в базе апдейт ничего не изменит /// </summary> /// <param name="user">Объект класса DBUser</param> /// <exception cref="System.ArgumentNullException">Иcключение выбрасывается когда параметр user равен null</exception> public void update(DBUser user) { if (user == null) { throw new ArgumentNullException("user"); } using (TransactionScope scope = new TransactionScope()) { List <System.Data.SqlClient.SqlCommand> commands = new List <System.Data.SqlClient.SqlCommand>(); string queryUpdate = string.Format("UPDATE [dbo].[User] " + "SET [surname] = @surname ,[first_name] = @first_name ,[patronymic_name] = @patronymic_name " + ",[login] = @login ,[change_password] = @change, [department] = @department, [is_active] = @is_active, [modifier_id] = '{1}' ,[modify_date] = GETDATE() " + "WHERE [id] = '{0}'", user.Id, ((DBUser)CurrentUser.Instance).Id); Dictionary <string, object> paramsDictionaryUpdate = new Dictionary <string, object>(); user.toDictionary(paramsDictionaryUpdate); paramsDictionaryUpdate.Add("@change", user.Password.HaveToChange); commands.Add(dbContext.CreateCommand(queryUpdate, CommandType.Text, paramsDictionaryUpdate)); if (user.groupWasChanged == true) { string deleteRoleQuery = String.Format("DELETE FROM [dbo].[UserRole] WHERE [user_id] = '{0}'", user.Id); commands.Add(dbContext.CreateCommand(deleteRoleQuery, CommandType.Text)); foreach (string role in user.Groups) { string queryRole = String.Format("DECLARE @role_id UNIQUEIDENTIFIER " + "SELECT @role_id = id FROM Role WHERE name = '{0}' " + "INSERT INTO [dbo].[UserRole] " + "([user_id] ,[role_id] ,[creator_id] ,[create_date]) " + "VALUES " + "('{1}' ,@role_id, '{2}', GETDATE()) ", role, user.Id, ((DBUser)CurrentUser.Instance).Id); commands.Add(dbContext.CreateCommand(queryRole, CommandType.Text)); } } dbContext.ExecuteTransaction(commands); user.resetGroupWasChanged(); scope.Complete(); } }
/// <summary> /// Добавление новой записи в таблицу /// </summary> /// <param name="user">Объект класса DBUser</param> /// <returns>ID новой записи</returns> /// <exception cref="System.ArgumentNullException">Исключение выбрасывается когда параметр user равен null</exception> /// <exception cref="ProjectKernel.Classes.User.UserAlreadyExistException">Исключение выбрасывается когда в базе уже существует пользователя с таким логином</exception> public Guid add(DBUser user) { string recordId; try { if (user == null) { throw new ArgumentNullException("user"); } List <System.Data.SqlClient.SqlCommand> comm = new List <System.Data.SqlClient.SqlCommand>(); //Add command for insert user string queryUser = String.Format("DECLARE @id UNIQUEIDENTIFIER " + "SET @id = newid() " + "INSERT INTO [dbo].[User] " + "([id] ,[surname] ,[first_name] ,[patronymic_name] " + ",[login] ,[salt] ,[password], [change_password], [department], [is_active], [creator_id], [create_date]) " + "VALUES " + "(@id ,@surname ,@first_name ,@patronymic_name " + ",@login ,@salt ,@password, @change, @department, @is_active, '{0}', GETDATE()) " + "SELECT @id ", ((DBUser)CurrentUser.Instance).Id); Dictionary <string, object> paramsDictionary = new Dictionary <string, object>(); user.toDictionary(paramsDictionary); paramsDictionary.Add("@salt", user.Password.salt); paramsDictionary.Add("@password", user.Password.hashPassword); paramsDictionary.Add("@change", user.Password.HaveToChange); comm.Add(dbContext.CreateCommand(queryUser, CommandType.Text, paramsDictionary)); //Add command for insert user //Add commands for insert user groups foreach (string role in user.Groups.Distinct()) { string queryRole = String.Format("DECLARE @user_id UNIQUEIDENTIFIER " + "DECLARE @role_id UNIQUEIDENTIFIER " + "SELECT @user_id = id FROM [dbo].[User] WHERE login = '******' " + "SELECT @role_id = id FROM [dbo].[Role] WHERE name = '{1}' " + "INSERT INTO [dbo].[UserRole] " + "([user_id] ,[role_id] ,[creator_id] ,[create_date]) " + "VALUES " + "(@user_id, @role_id, '{2}', GETDATE()) " , user.Login, role, ((DBUser)CurrentUser.Instance).Id); comm.Add(dbContext.CreateCommand(queryRole, CommandType.Text)); } //Add command for insert activity record string queryActivity = String.Format("INSERT INTO [dbo].[Activity] " + "([user_login], [state], [last_login_datetime], [last_login_workstation], [last_logout_datetime]) " + "VALUES " + "('{0}', 'log_out', GETDATE(), '{1}', GETDATE()) ", user.Login, Environment.MachineName); comm.Add(dbContext.CreateCommand(queryActivity, CommandType.Text)); dbContext.ExecuteTransaction(comm); //Get user id string queryId = string.Format("SELECT id FROM [dbo].[User] WHERE login = '******'", user.Login); recordId = dbContext.ExecuteScalar(queryId, CommandType.Text).ToString(); } catch (System.Data.SqlClient.SqlException ex) { if ((ex.Number == 2627) || (ex.Number == 2601)) //if (ex.Message.Contains("\"CK_Users_login\"") && ex.Message.Contains("UNIQUE KEY") && ex.Message.Contains("\"dbo.Users\"") && ex.Message.Contains(user.Login)) { Exception exc = new UserAlreadyExistException(string.Format("User with login '{0}' is already exist in this system", user.Login), ex); throw exc; } throw ex; } return(Guid.Parse(recordId)); }