예제 #1
0
        public void Create (USER User)
        {
            if (User == null)
                throw new ArgumentNullException("User");

            if (User.GUID_RECORD == Guid.Empty)
            {
                User.GUID_RECORD = Guid.NewGuid();
            }

            if (string.IsNullOrEmpty(User.KEY))
                throw new ArgumentNullException("KEY");

            if (string.IsNullOrEmpty(User.KEY_IDENTITY) && !string.IsNullOrEmpty(User.PROVIDER))
            {
                throw new ArgumentNullException("KEY_IDENTITY");
            }
            if (!string.IsNullOrEmpty(User.KEY_IDENTITY) && string.IsNullOrEmpty(User.PROVIDER))
            {
                User.PROVIDER = "PASSWORD";
            }

            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    /// Check GUID
                    command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER] where [GUID_RECORD] = '{0}'", User.GUID_RECORD);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            throw new Exception("Record already exists");
                    }

                    /// Check key
                    command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER] where [KEY] = '{0}'", User.KEY);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            throw new Exception("Record already exists");
                    }

                    command.CommandText = @"insert into [SYSTEM.SECURITY.USER] (
                        [GUID_RECORD], 
                        [KEY], 
                        [LAST_LOGIN],
                        [LOGIN_ATTEMPT_COUNT],
                        [BATCH_GUID],
                        [HIDDEN],
                        [DELETED]) 
                    values (@GUID_RECORD, @KEY, @LAST_LOGIN, @LOGIN_ATTEMPT_COUNT, @BATCH_GUID, @HIDDEN, @DELETED)";

                    command.Parameters.AddWithValue("@GUID_RECORD", User.GUID_RECORD);
                    command.Parameters.AddWithValue("@KEY", User.KEY);
                    command.Parameters.AddWithValue("@LAST_LOGIN", DateTime.Today);
                    command.Parameters.AddWithValue("@LOGIN_ATTEMPT_COUNT", 0);
                    command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
                    command.Parameters.AddWithValue("@HIDDEN", 0);
                    command.Parameters.AddWithValue("@DELETED", 0);

                    command.ExecuteNonQuery();

                    /// INSERT USER_IDENTITY
                    if (!string.IsNullOrEmpty(User.KEY_IDENTITY) && !string.IsNullOrEmpty(User.PROVIDER))
                    {
                        command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER_IDENTITY] where [USER_GUID] = '{0}' AND PROVIDER = '{1}'", User.GUID_RECORD, User.PROVIDER);
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                                throw new Exception("Record already exists");
                        }

                        command.CommandText = @"insert into [SYSTEM.SECURITY.USER_IDENTITY] (
                        [USER_GUID], 
                        [PROVIDER],
                        [KEY],
                        [BATCH_GUID],
                        [HIDDEN],
                        [DELETED]) 
                        values (@GUID_RECORD, @USER_GUID, @PROVIDER, @KEY, @BATCH_GUID, @HIDDEN, @DELETED)";

                        command.Parameters.AddWithValue("@USER_GUID", User.GUID_RECORD);
                        command.Parameters.AddWithValue("@PROVIDER", User.PROVIDER);
                        command.Parameters.AddWithValue("@KEY", User.KEY_IDENTITY); /// Добавить преобразование в пароль
                        command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
                        command.Parameters.AddWithValue("@HIDDEN", 0);
                        command.Parameters.AddWithValue("@DELETED", 0);

                        command.ExecuteNonQuery();
                    }



                }
            }
        }
예제 #2
0
        public void Update (USER User)
        {
            if (User == null)
                throw new ArgumentNullException("User");

            if (User.GUID_RECORD == Guid.Empty)
                throw new ArgumentNullException("GUID_RECORD");

            if (string.IsNullOrEmpty(User.KEY))
                throw new ArgumentNullException("Key");

            var record = GetById(User.GUID_RECORD);

            var alterColumns = new List<string>();
            var alterValues = new List<SqlParameter>();

            if (!String.Equals(record.KEY, User.KEY))
            {
                alterColumns.Add("[KEY] = @KEY");
                alterValues.Add(new SqlParameter("@KEY", User.KEY));
                /// add check constraint
            }

            if (!String.Equals(record.DELETED, User.DELETED))
            {
                alterColumns.Add("[DELETED] = @DELETED");
                alterValues.Add(new SqlParameter("@DELETED", User.DELETED));
            }

            if (!Nullable<Guid>.Equals(record.HIDDEN, User.HIDDEN))
            {
                alterColumns.Add("[HIDDEN] = @HIDDEN");
                alterValues.Add(new SqlParameter("@HIDDEN", User.HIDDEN));
                if (record.HIDDEN == true && User.HIDDEN == false)
                {
                    alterColumns.Add("[LOGIN_ATTEMPT_COUNT] = @LOGIN_ATTEMPT_COUNT");
                    alterValues.Add(new SqlParameter("@LOGIN_ATTEMPT_COUNT", 0));
                }

            }

            if (alterColumns.Any())
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = String.Format(
                            "update [SYSTEM.SECURITY.USER] set {0} where [GUID_RECORD] = @GUID_RECORD",
                            String.Join(", ", alterColumns));

                        command.Parameters.AddWithValue("@GUID_RECORD", User.GUID_RECORD);
                        command.Parameters.AddRange(alterValues.ToArray());
                        command.ExecuteNonQuery();
                    }
                }
            }
        }