Esempio n. 1
0
        /// <summary>
        /// The get user.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="providerUserKey">
        /// The provider user key.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        /// <param name="userIsOnline">
        /// The user is online.
        /// </param>
        /// <returns>
        /// </returns>
        public DataRow GetUser([NotNull] string appName, [NotNull] object providerUserKey, [NotNull] string userName, bool userIsOnline)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@UserKey", providerUserKey);
                cmd.Parameters.AddWithValue("@UserIsOnline", userIsOnline);
                cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
                using (DataTable dt = this._msSqlDbAccess.GetData(cmd))
                {
                    if (dt.Rows.Count > 0)
                    {
                        return(dt.Rows[0]);
                    }
                    else
                    {
                        return(null);
                    }
                }
            }
        }
Esempio n. 2
0
        /// <summary>
        /// The create user.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="password">
        /// The password.
        /// </param>
        /// <param name="passwordSalt">
        /// The password salt.
        /// </param>
        /// <param name="passwordFormat">
        /// The password format.
        /// </param>
        /// <param name="email">
        /// The email.
        /// </param>
        /// <param name="passwordQuestion">
        /// The password question.
        /// </param>
        /// <param name="passwordAnswer">
        /// The password answer.
        /// </param>
        /// <param name="isApproved">
        /// The is approved.
        /// </param>
        /// <param name="providerUserKey">
        /// The provider user key.
        /// </param>
        public void CreateUser([NotNull] string appName, [NotNull] string username, [NotNull] string password, [NotNull] string passwordSalt,
                               int passwordFormat, [NotNull] string email, [NotNull] string passwordQuestion, [NotNull] string passwordAnswer,
                               bool isApproved, [NotNull] object providerUserKey)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_createuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Input Parameters
                cmd.Parameters.AddWithValue("Username", username);
                cmd.Parameters.AddWithValue("Password", password);
                cmd.Parameters.AddWithValue("PasswordSalt", passwordSalt);
                cmd.Parameters.AddWithValue("PasswordFormat", passwordFormat);
                cmd.Parameters.AddWithValue("Email", email);
                cmd.Parameters.AddWithValue("PasswordQuestion", passwordQuestion);
                cmd.Parameters.AddWithValue("PasswordAnswer", passwordAnswer);
                cmd.Parameters.AddWithValue("IsApproved", isApproved);
                cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);

                // Input Output Parameters
                var paramUserKey = new SqlParameter("UserKey", SqlDbType.UniqueIdentifier);
                paramUserKey.Direction = ParameterDirection.InputOutput;
                paramUserKey.Value     = providerUserKey;
                cmd.Parameters.Add(paramUserKey);

                // Execute
                this._msSqlDbAccess.ExecuteNonQuery(cmd);

                // Retrieve Output Parameters
                providerUserKey = paramUserKey.Value;
            }
        }
Esempio n. 3
0
        /// <summary>
        /// The update user.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="user">
        /// The user.
        /// </param>
        /// <param name="requiresUniqueEmail">
        /// The requires unique email.
        /// </param>
        /// <returns>
        /// The update user.
        /// </returns>
        public int UpdateUser([NotNull] object appName, [NotNull] MembershipUser user, bool requiresUniqueEmail)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_updateuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("UserKey", user.ProviderUserKey);
                cmd.Parameters.AddWithValue("UserName", user.UserName);
                cmd.Parameters.AddWithValue("Email", user.Email);
                cmd.Parameters.AddWithValue("Comment", user.Comment);
                cmd.Parameters.AddWithValue("IsApproved", user.IsApproved);
                cmd.Parameters.AddWithValue("LastLogin", user.LastLoginDate);
                cmd.Parameters.AddWithValue("LastActivity", user.LastActivityDate.ToUniversalTime());
                cmd.Parameters.AddWithValue("UniqueEmail", requiresUniqueEmail);

                // Add Return Value
                var p = new SqlParameter("ReturnValue", SqlDbType.Int);
                p.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(p);

                this._msSqlDbAccess.ExecuteNonQuery(cmd); // Execute Non SQL Query
                return(Convert.ToInt32(p.Value));         // Return
            }
        }
Esempio n. 4
0
        /// <summary>
        /// The get profile structure.
        /// </summary>
        /// <returns>
        /// </returns>
        public DataTable GetProfileStructure()
        {
            string sql = @"SELECT TOP 1 * FROM {0}".FormatWith(MsSqlDbAccess.GetObjectName("prov_Profile"));

            using (var cmd = new SqlCommand(sql))
            {
                cmd.CommandType = CommandType.Text;
                return(this._msSqlDbAccess.GetData(cmd));
            }
        }
Esempio n. 5
0
 /// <summary>
 /// Database Action - Create Role
 /// </summary>
 /// <param name="appName">
 /// Application Name
 /// </param>
 /// <param name="roleName">
 /// Role Name
 /// </param>
 public void CreateRole(object appName, object roleName)
 {
     using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_createrole")))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("ApplicationName", appName);
         cmd.Parameters.AddWithValue("RoleName", roleName);
         this._msSqlDbAccess.ExecuteNonQuery(cmd);
     }
 }
Esempio n. 6
0
 /// <summary>
 /// Database Action - Find Users in Role
 /// </summary>
 /// <param name="appName">
 /// Application Name
 /// </param>
 /// <param name="roleName">
 /// Role Name
 /// </param>
 /// <returns>
 /// Datatable containing User Information
 /// </returns>
 public DataTable FindUsersInRole(object appName, object roleName)
 {
     using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_findusersinrole")))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("ApplicationName", appName);
         cmd.Parameters.AddWithValue("RoleName", roleName);
         return(this._msSqlDbAccess.GetData(cmd));
     }
 }
Esempio n. 7
0
 /// <summary>
 /// Database Action - Get Role Exists
 /// </summary>
 /// <param name="appName">
 /// Application Name
 /// </param>
 /// <param name="roleName">
 /// Role Name
 /// </param>
 /// <returns>
 /// Database containing Role Information
 /// </returns>
 public object GetRoleExists(object appName, object roleName)
 {
     using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_exists")))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("ApplicationName", appName);
         cmd.Parameters.AddWithValue("RoleName", roleName);
         return(this._msSqlDbAccess.ExecuteScalar(cmd));
     }
 }
Esempio n. 8
0
 /// <summary>
 /// Database Action - Get Roles
 /// </summary>
 /// <param name="appName">
 /// Application Name
 /// </param>
 /// <param name="username">
 /// The username.
 /// </param>
 /// <returns>
 /// Database containing Role Information
 /// </returns>
 public DataTable GetRoles(object appName, object username)
 {
     using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_getroles")))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("ApplicationName", appName);
         cmd.Parameters.AddWithValue("Username", username);
         return(this._msSqlDbAccess.GetData(cmd));
     }
 }
Esempio n. 9
0
 /// <summary>
 /// Database Action - Remove User From Role
 /// </summary>
 /// <param name="appName">
 /// Application Name
 /// </param>
 /// <param name="userName">
 /// User Name
 /// </param>
 /// <param name="roleName">
 /// Role Name
 /// </param>
 public void RemoveUserFromRole(object appName, string userName, string roleName)
 {
     using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_removeuserfromrole")))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("ApplicationName", appName);
         cmd.Parameters.AddWithValue("Username", userName);
         cmd.Parameters.AddWithValue("RoleName", roleName);
         this._msSqlDbAccess.ExecuteNonQuery(cmd);
     }
 }
Esempio n. 10
0
        /// <summary>
        /// The get user name by email.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="email">
        /// The email.
        /// </param>
        /// <returns>
        /// </returns>
        public DataTable GetUserNameByEmail([NotNull] string appName, [NotNull] string email)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getusernamebyemail")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Email", email);
                return(this._msSqlDbAccess.GetData(cmd));
            }
        }
Esempio n. 11
0
        /// <summary>
        /// The unlock user.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        public void UnlockUser([NotNull] string appName, [NotNull] string userName)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_unlockuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@UserName", userName);
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 12
0
        /// <summary>
        /// Binds the data.
        /// </summary>
        private void BindData()
        {
            using (DataSet ds = LegacyDb.ds_forumadmin(this.PageContext.PageBoardID))
            {
                this.CategoryList.DataSource = ds.Tables[MsSqlDbAccess.GetObjectName("Category")];
            }

            // Hide the New Forum Button if there are no Categories.
            this.NewForum.Visible = this.CategoryList.Items.Count < 1;

            this.DataBind();
        }
Esempio n. 13
0
        /// <summary>
        /// The delete user.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="deleteAllRelatedData">
        /// The delete all related data.
        /// </param>
        public void DeleteUser([NotNull] string appName, [NotNull] string username, bool deleteAllRelatedData)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_deleteuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@DeleteAllRelated", deleteAllRelatedData);
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 14
0
        /// <summary>
        /// The get all users.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <returns>
        /// </returns>
        public DataTable GetAllUsers([NotNull] string appName, int pageIndex, int pageSize)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getallusers")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                return(this._msSqlDbAccess.GetData(cmd));
            }
        }
Esempio n. 15
0
        /// <summary>
        /// The upgrade membership.
        /// </summary>
        /// <param name="previousVersion">
        /// The previous version.
        /// </param>
        /// <param name="newVersion">
        /// The new version.
        /// </param>
        public void UpgradeMembership(int previousVersion, int newVersion)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_upgrade")))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // Nonstandard args
                cmd.Parameters.AddWithValue("@PreviousVersion", previousVersion);
                cmd.Parameters.AddWithValue("@NewVersion", newVersion);
                cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 16
0
        /// <summary>
        /// The find users by name.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="usernameToMatch">
        /// The username to match.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <returns>
        /// </returns>
        public DataTable FindUsersByName([NotNull] string appName, [NotNull] string usernameToMatch, int pageIndex, int pageSize)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_findusersbyname")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Username", usernameToMatch);
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                return(this._msSqlDbAccess.GetData(cmd));
            }
        }
Esempio n. 17
0
        /// <summary>
        /// The get user password info.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="updateUser">
        /// The update user.
        /// </param>
        /// <returns>
        /// </returns>
        public DataTable GetUserPasswordInfo([NotNull] string appName, [NotNull] string username, bool updateUser)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getuser")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@UserIsOnline", updateUser);
                cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
                return(this._msSqlDbAccess.GetData(cmd));
            }
        }
Esempio n. 18
0
        /// <summary>
        /// The change password question and answer.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="passwordQuestion">
        /// The password question.
        /// </param>
        /// <param name="passwordAnswer">
        /// The password answer.
        /// </param>
        public void ChangePasswordQuestionAndAnswer([NotNull] string appName, [NotNull] string username, [NotNull] string passwordQuestion, [NotNull] string passwordAnswer)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_changepasswordquestionandanswer")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@PasswordQuestion", passwordQuestion);
                cmd.Parameters.AddWithValue("@PasswordAnswer", passwordAnswer);
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 19
0
        /// <summary>
        /// Bind data for this control.
        /// </summary>
        private void BindData()
        {
            // get list of forums and their moderating data
            using (DataSet ds = LegacyDb.forum_moderatelist(this.PageContext.PageUserID, this.PageContext.PageBoardID))
            {
                this.CategoryList.DataSource = ds.Tables[MsSqlDbAccess.GetObjectName("Category")];
            }

            // bind data to controls
            this.DataBind();

            if (this.CategoryList.Items.Count.Equals(0))
            {
                this.InfoPlaceHolder.Visible = true;
            }
        }
Esempio n. 20
0
        /// <summary>
        /// The change password.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="newPassword">
        /// The new password.
        /// </param>
        /// <param name="newSalt">
        /// The new salt.
        /// </param>
        /// <param name="passwordFormat">
        /// The password format.
        /// </param>
        /// <param name="newPasswordAnswer">
        /// The new password answer.
        /// </param>
        public void ChangePassword([NotNull] string appName, [NotNull] string username, [NotNull] string newPassword, [NotNull] string newSalt, int passwordFormat, [NotNull] string newPasswordAnswer)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_changepassword")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@Password", newPassword);
                cmd.Parameters.AddWithValue("@PasswordSalt", newSalt);
                cmd.Parameters.AddWithValue("@PasswordFormat", passwordFormat);
                cmd.Parameters.AddWithValue("@PasswordAnswer", newPasswordAnswer);

                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 21
0
        /// <summary>
        /// The get number of users online.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="timeWindow">
        /// The time window.
        /// </param>
        /// <returns>
        /// The get number of users online.
        /// </returns>
        public int GetNumberOfUsersOnline([NotNull] string appName, int timeWindow)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getnumberofusersonline")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@TimeWindow", timeWindow);
                cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
                var p = new SqlParameter("ReturnValue", SqlDbType.Int);
                p.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(p);
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
                return(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value));
            }
        }
Esempio n. 22
0
        /// <summary>
        /// Database Action - Delete Role
        /// </summary>
        /// <param name="appName">
        /// Application Name
        /// </param>
        /// <param name="roleName">
        /// Role Name
        /// </param>
        /// <param name="deleteOnlyIfRoleIsEmpty">
        /// The delete Only If Role Is Empty.
        /// </param>
        /// <returns>
        /// Status as integer
        /// </returns>
        public int DeleteRole(object appName, object roleName, object deleteOnlyIfRoleIsEmpty)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_deleterole")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("ApplicationName", appName);
                cmd.Parameters.AddWithValue("RoleName", roleName);
                cmd.Parameters.AddWithValue("DeleteOnlyIfRoleIsEmpty", deleteOnlyIfRoleIsEmpty);

                var p = new SqlParameter("ReturnValue", SqlDbType.Int);
                p.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(p);

                this._msSqlDbAccess.ExecuteNonQuery(cmd);

                return(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value));
            }
        }
Esempio n. 23
0
        /// <summary>
        /// The add profile column.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        /// <param name="columnType">
        /// The column type.
        /// </param>
        /// <param name="size">
        /// The size.
        /// </param>
        public void AddProfileColumn([NotNull] string name, SqlDbType columnType, int size)
        {
            // get column type...
            string type = columnType.ToString();

            if (size > 0)
            {
                type += "(" + size + ")";
            }

            string sql = "ALTER TABLE {0} ADD [{1}] {2} NULL".FormatWith(
                MsSqlDbAccess.GetObjectName("prov_Profile"), name, type);

            using (var cmd = new SqlCommand(sql))
            {
                cmd.CommandType = CommandType.Text;
                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 24
0
        /// <summary>
        /// The reset password.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        /// <param name="password">
        /// The password.
        /// </param>
        /// <param name="passwordSalt">
        /// The password salt.
        /// </param>
        /// <param name="passwordFormat">
        /// The password format.
        /// </param>
        /// <param name="maxInvalidPasswordAttempts">
        /// The max invalid password attempts.
        /// </param>
        /// <param name="passwordAttemptWindow">
        /// The password attempt window.
        /// </param>
        public void ResetPassword([NotNull] string appName, [NotNull] string userName, [NotNull] string password, [NotNull] string passwordSalt,
                                  int passwordFormat,
                                  int maxInvalidPasswordAttempts,
                                  int passwordAttemptWindow)
        {
            using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_resetpassword")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ApplicationName", appName);

                // Nonstandard args
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@Password", password);
                cmd.Parameters.AddWithValue("@PasswordSalt", passwordSalt);
                cmd.Parameters.AddWithValue("@PasswordFormat", passwordFormat);
                cmd.Parameters.AddWithValue("@MaxInvalidAttempts", maxInvalidPasswordAttempts);
                cmd.Parameters.AddWithValue("@PasswordAttemptWindow", passwordAttemptWindow);
                cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);

                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }
Esempio n. 25
0
        /// <summary>
        /// The set profile properties.
        /// </summary>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="userID">
        /// The user id.
        /// </param>
        /// <param name="values">
        /// The values.
        /// </param>
        /// <param name="settingsColumnsList">
        /// The settings columns list.
        /// </param>
        public void SetProfileProperties([NotNull] object appName, [NotNull] object userID, [NotNull] SettingsPropertyValueCollection values, [NotNull] List <SettingsPropertyColumn> settingsColumnsList)
        {
            using (var cmd = new SqlCommand())
            {
                string table = MsSqlDbAccess.GetObjectName("prov_Profile");

                StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(table);
                sqlCommand.Append(" WHERE UserId = @UserID) ");
                cmd.Parameters.AddWithValue("@UserID", userID);

                // Build up strings used in the query
                var columnStr = new StringBuilder();
                var valueStr  = new StringBuilder();
                var setStr    = new StringBuilder();
                int count     = 0;

                foreach (SettingsPropertyColumn column in settingsColumnsList)
                {
                    // only write if it's dirty
                    if (values[column.Settings.Name].IsDirty)
                    {
                        columnStr.Append(", ");
                        valueStr.Append(", ");
                        columnStr.Append(column.Settings.Name);
                        string valueParam = "@Value" + count;
                        valueStr.Append(valueParam);
                        cmd.Parameters.AddWithValue(valueParam, values[column.Settings.Name].PropertyValue);

                        if (column.DataType != SqlDbType.Timestamp)
                        {
                            if (count > 0)
                            {
                                setStr.Append(",");
                            }

                            setStr.Append(column.Settings.Name);
                            setStr.Append("=");
                            setStr.Append(valueParam);
                        }

                        count++;
                    }
                }

                columnStr.Append(",LastUpdatedDate ");
                valueStr.Append(",@LastUpdatedDate");
                setStr.Append(",LastUpdatedDate=@LastUpdatedDate");
                cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);

                sqlCommand.Append("BEGIN UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
                sqlCommand.Append(" WHERE UserId = '").Append(userID.ToString()).Append("'");

                sqlCommand.Append(" END ELSE BEGIN INSERT ").Append(table).Append(" (UserId").Append(columnStr.ToString());
                sqlCommand.Append(") VALUES ('").Append(userID.ToString()).Append("'").Append(valueStr.ToString()).Append(
                    ") END");

                cmd.CommandText = sqlCommand.ToString();
                cmd.CommandType = CommandType.Text;

                this._msSqlDbAccess.ExecuteNonQuery(cmd);
            }
        }