sqlcommondataprovider.cs

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

				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_SiteSettings_Save", connection);
				command.CommandType = CommandType.StoredProcedure;

				// Set the parameters
				//
                command.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = siteSettings.ApplicationName;
				command.Parameters.Add("@Disabled", SqlDbType.SmallInt).Value = siteSettings.ForumsDisabled;
                command.Parameters.Add("@SettingsXML", SqlDbType.NText).Value = Serializer.ConvertToString(siteSettings);
                command.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = siteSettings.SettingsID;

				// Open the connection and exectute
				//
				connection.Open();
				command.ExecuteNonQuery();
				connection.Close();

			}
		
//			binaryFormatter = null;
//			ms = null;
		}

		public override Hashtable GetSiteUrls()
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_Site_GetSiteUrls", connection);
				Hashtable siteUrls = new Hashtable();

				// Mark as stored procedure
				command.CommandType = CommandType.StoredProcedure;

				connection.Open();
				using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
				{
					reader.Read();

					string applicationName =	(string)reader["ApplicationName"];
					string siteUrl =			(string)reader["SiteUrl"];

					if(!siteUrls.Contains(applicationName))
						siteUrls.Add(applicationName, new ArrayList());

					((ArrayList)siteUrls[applicationName]).Add(siteUrl);
				}

				return siteUrls;
			}
		}

        #endregion

        #region #### Search ####
        /****************************************************************
        // GetSearchIgnoreWords
        //
        /// <summary>
        /// Loads the lexicon of words used by search.
        /// </summary>
        //
        ****************************************************************/
        public override Hashtable GetSearchIgnoreWords(int settingsID) {
            Hashtable ignoreWords = new Hashtable();

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

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

                myCommand.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = settingsID;

				// Execute the command
				myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // Are we loading a lexicon for ignore word?
                    //
                    while (reader.Read())
                        ignoreWords.Add( Convert.ToInt32(reader["WordHash"]), (string) reader["Word"] );

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

				return ignoreWords;       
			}
        }

        public override void CreateDeleteSearchIgnoreWords (ArrayList words, DataProviderAction action) {

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

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@WordHash", SqlDbType.Int);
				myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 64);
				myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = action;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();

				foreach (string word in words) {
					myCommand.Parameters["@WordHash"].Value = word.GetHashCode();
					myCommand.Parameters["@Word"].Value = word;

					myCommand.ExecuteNonQuery();
				}

				myConnection.Close();
			}
        }

        public override PostSet SearchReindexPosts (int setsize, int settingsID) {
            PostSet postSet = new PostSet();

            using(SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Search_PostReindex", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@RowCount", SqlDbType.Int).Value = setsize;
                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
                
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)) 
                {
                    
                    while(dr.Read())
                    {
                    	ForumPost fp = new ForumPost();
                        CommonDataProvider.PopulatePostFromIDataReader(dr,fp);
                        postSet.Posts.Add(fp);
                    }
                    dr.Close();   


                }

				// Close the connection
				myConnection.Close();
				return postSet;
			}
		}
        
        public override void InsertIntoSearchBarrel (Hashtable words, Post post, int settingsID) {

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

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

				// Execute the command
				//
				myConnection.Open();
            
                myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;

                // Set up the parameters >> Leave out the values, since we will get them on the loop
                myCommand.Parameters.Add("@WordHash", SqlDbType.Int);
                myCommand.Parameters.Add("@Word", SqlDbType.NVarChar, 64);
                myCommand.Parameters.Add("@Weight", SqlDbType.Float);
                myCommand.Parameters.Add("@PostID", SqlDbType.Int);
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int);
            
				foreach (int wordHash in words.Keys) {
					Word word;

					// Get the Word instance to process
					//
					word = (Word) words[wordHash];



						myCommand.Parameters["@WordHash"].Value = wordHash;
						myCommand.Parameters["@Word"].Value = word.Name;
						myCommand.Parameters["@Weight"].Value = word.Weight;
						myCommand.Parameters["@PostID"].Value = post.PostID;
						myCommand.Parameters["@ThreadID"].Value = post.ThreadID;
						myCommand.Parameters["@SectionID"].Value = post.SectionID;

					myCommand.ExecuteNonQuery();
				}

				// Ensure we tear down the connection and command
				myConnection.Close();
                myCommand.Dispose();
				

			}
        }
        #endregion

        #region #### Post ####

		public override bool IsThreadTracked(int threadID, int userID)
		{
			//cs_thread_IsTracked
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_thread_IsTracked", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Direction = ParameterDirection.Output;
                
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();

				return (bool)myCommand.Parameters["@IsTracked"].Value;
			}
		}


        public override void ThreadRate (int threadID, int userID, int rating) {

            using(SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Rate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@Rating", SqlDbType.Int).Value = rating;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        public override ArrayList ThreadRatings (int threadID) {
            ArrayList rating = new ArrayList();

            using(SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Rate_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                SqlDataReader dr;
                
                myConnection.Open();

                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    while (dr.Read()) 
                    {
                        rating.Add( PopulateRatingFromIDataReader(dr) );
                    }
                    dr.Close();
                }
                myConnection.Close();
            }

            return rating;
        }
        #endregion

        #region #### Exceptions and Tracing ####

        public override void LogException (CSException exception, int settingsID) {

            try
            {
                using(SqlConnection myConnection = GetSqlConnection()) 
                {
                    SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Exceptions_Log", myConnection);
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = settingsID;
                    myCommand.Parameters.Add("@ExceptionHash", SqlDbType.VarChar, 128).Value = exception.GetHashCode(settingsID);
                    myCommand.Parameters.Add("@Category", SqlDbType.Int).Value = exception.Category;
                    myCommand.Parameters.Add("@Exception", SqlDbType.NVarChar, 2000).Value = exception.GetBaseException().ToString();
                    myCommand.Parameters.Add("@ExceptionMessage", SqlDbType.NVarChar, 500).Value = exception.Message;
                    myCommand.Parameters.Add("@UserAgent", SqlDbType.NVarChar, 64).Value = exception.UserAgent;
                    myCommand.Parameters.Add("@IPAddress", SqlDbType.VarChar, 15).Value = exception.IPAddress;
                    myCommand.Parameters.Add("@HttpReferrer", SqlDbType.NVarChar, 512).Value = exception.HttpReferrer;
                    myCommand.Parameters.Add("@HttpVerb", SqlDbType.NVarChar, 24).Value = exception.HttpVerb;
                    myCommand.Parameters.Add("@PathAndQuery", SqlDbType.NVarChar, 512).Value = exception.HttpPathAndQuery;
                
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
			
                    // Close the connection
                    myConnection.Close();
                }
            }
            catch{} //Not much to do if exception logging fails

        }

        public override void DeleteExceptions(int settingsID, ArrayList deleteList) {
			using( SqlConnection myConnection = GetSqlConnection() ) {
				StringBuilder sql = new StringBuilder();

				sql.Append("DELETE " + this.databaseOwner + ".cs_Exceptions WHERE SettingsID = " + settingsID);

				if ((deleteList != null) && (deleteList.Count > 0)) {

					sql.Append(" AND (");

					for (int i = 0; i < deleteList.Count; i++) {
						sql.Append("ExceptionID = ");
						sql.Append( deleteList[i].ToString());

						if ((i+1) != deleteList.Count) {
							sql.Append(" OR ");
						} else {
							sql.Append(")");
						}
					}
				}

				SqlCommand myCommand = new SqlCommand(sql.ToString(), myConnection);

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