sqlcommondataprovider.cs

本系统是在asp版《在线文件管理器》的基础上设计制作

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = summary.PostID;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Read the values
                    //
                    while (dr.Read()) 
                    {

                        if (summary.Answers[ (string) dr["Vote"] ] != null)
                            ((PollItem) summary.Answers[ (string) dr["Vote"] ]).Total = (int) dr["VoteCount"];
                    }
            
                    if (dr.NextResult()) 
                    {

                        while (dr.Read()) 
                        {
                            summary.Voters.Add( (int) dr["UserID"], (string) dr["Vote"] );
                        }
                    }

                    dr.Close();
                }
            
				// Close the conneciton
				myConnection.Close();

				return summary;
			}
        }

        #endregion

        /****************************************************************
        // ToggleOptions
        //
        /// <summary>
        /// Allows use to change various settings without updating the profile directly
        /// </summary>
        //
        ****************************************************************/
        public override void ToggleOptions(string username, bool hideReadThreads, ViewOptions viewOptions) {
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_ToggleOptions", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
				myCommand.Parameters.Add("@HideReadThreads", SqlDbType.Bit).Value = hideReadThreads;

				if (ViewOptions.NotSet == viewOptions)
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = DBNull.Value;
				else if (ViewOptions.Threaded == viewOptions)
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = false;
				else
					myCommand.Parameters.Add("@FlatView", SqlDbType.Bit).Value = true;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();

				myConnection.Close();
			}
        }

        /****************************************************************
        // ChangeForumGroupSortOrder
        //
        /// <summary>
        /// Used to move forums group sort order up or down
        /// </summary>
        //
        ****************************************************************/
        public override void ChangeGroupSortOrder(int forumGroupID, bool moveUp) {
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UpdateForumGroupSortOrder", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = forumGroupID;
				myCommand.Parameters.Add("@MoveUp", SqlDbType.Bit).Value = moveUp;
                myCommand.Parameters.Add(this.SettingsIDParameter());


				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();

				myConnection.Close();
			}
        }

        /****************************************************************
        // UpdateMessageTemplate
        //
        /// <summary>
        /// update the message in the database
        /// </summary>
        //
        ****************************************************************/
        public override void CreateUpdateDeleteMessage (Message message, DataProviderAction action) {

            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Message_CreateUpdateDelete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@MessageID", SqlDbType.Int).Value         = message.MessageID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 1024).Value  = message.Title;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value            = message.Body;
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value            = action;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }


        /****************************************************************
        // GetMessages
        //
        /// <summary>
        /// Returns a collection of ForumMessages
        /// </summary>
        //
        ****************************************************************/
        public override ArrayList GetMessages(int messageID) {

            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_GetForumMessages", myConnection);
				SqlDataReader reader;
				ArrayList messages = new ArrayList();
				//            ForumMessage message;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@MessageID", SqlDbType.Int).Value = messageID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read()) 
                    {
                        // TODO Terry Denham, ForumMessages were moved to xml file instead of db. This will need to be updated at some point.
                        /*                message = new ForumMessage();

                                        message.MessageID = Convert.ToInt32(reader["MessageID"]);
                                        message.Title = (string) reader["Title"];
                                        message.Body = (string) reader["Body"];

                                        messages.Add(message);
                        */
                    }
                }

				myConnection.Close();

				return messages;
			}
        }

        /// <summary>
        /// Enables user credentials from previous versions to be safely upgraded. Usually this is a result of storing
        /// hashed values differently
        /// </summary>
        /// <param name="username">Name of User</param>
        /// <param name="password">Raw (unhashed) password</param>
        /// <returns></returns>
        public override bool UpgradePassword(string username, string password)
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_users_BackPort", myConnection);
                SqlDataReader reader;
				

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                string hashedPassword = null;
                string salt = null;
                int format = 0;


                // Execute the command
                myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.SingleRow))
                {
                    //Check to see if we can find the previous credentials. 
                    //NOTE: They might not be wrong. User could have simply entered the wrong password
                    if(reader.Read())
                    {
                        hashedPassword = reader["Password"] as string;
                        salt = reader["PasswordSalt"] as string;
                        format = (int)reader["PasswordFormat"];
                    }
                    reader.Close();
                }
                if(hashedPassword != null)
                {
                    //Was the password correct in a previous version?
                    if(UserProviderManager.ValidatePreviousPassword(hashedPassword, password, salt,format))
                    {
                        string newPassword;
                        UserProviderManager.EncodePassword(password, out newPassword, out salt, out format);

                        //Under Partial trust, EncodePassword will return null. This seems exceptable since
                        //Forums 2.0 never supported partial trust (ie, those affected by the change in passwords, should never 
                        //see the exception. Just incase, we will check for it and exception out.
                        if(newPassword == null)
                            throw new Exception("Your password could not be upgraded. Please contact your administrator");

                        //Need to lookup Forums 2.0 passwords and see if this could be an issue.
                        if(newPassword.Length > 128)
                            throw new Exception("Invalid Password Length to upgrade. Please contact your administrator");

                        //We can not directly call the MembershipAPI here, so we need to write to the aspnet_membership
                        //table directly. 
                        myCommand.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = newPassword;
                        myCommand.Parameters.Add("@PasswordSalt", SqlDbType.NVarChar, 128).Value = salt;
                        myCommand.Parameters.Add("@PasswordFormat", SqlDbType.Int, 3).Value = format;
                        return myCommand.ExecuteNonQuery() > 0;
                    }
                }

                myConnection.Close();

				
            }

            return false;
            

        }

        /****************************************************************
        // GetUserIDByEmail
        //
        /// <summary>
        /// Returns the username given an email address
        /// </summary>
        //
        ****************************************************************/
        public override int GetUserIDByEmail(string emailAddress) {
            // return all of the forums and their total and daily posts
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_GetByEmail", myConnection);
				SqlDataReader reader;
				int userID = 0;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 64).Value = emailAddress;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (reader.Read())
                        userID = (int) reader["cs_UserID"];

                    reader.Close();
                }
				myConnection.Close();

				return userID;
			}
        }


		/****************************************************************
		// GetUserIDByEmail
		//
		/// <summary>
		/// Returns the userid given an application-specific user token (e.g., Passport user ID)
		/// </summary>
		//
		****************************************************************/
		public override int GetUserIDByAppUserToken(string appUserToken) 
		{
			int userID = 0;

			// Create Instance of Connection and Command Object
			using (SqlConnection myConnection = GetSqlConnection())
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetUserIDByAppToken", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Pass sproc parameters
				myCommand.Parameters.Add("@AppUserToken", SqlDbType.VarChar, 128).Value = appUserToken;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				object res = myCommand.ExecuteScalar();
                myConnection.Close();

				// this method is not expected to throw any exception