sqlcommondataprovider.cs

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

		public override ArrayList GetExceptions (int exceptionType, int minFrequency) {
			ArrayList exceptions = new ArrayList();

			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Exceptions_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				SqlDataReader reader;

				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@ExceptionType", SqlDbType.Int).Value = exceptionType;
				myCommand.Parameters.Add("@MinFrequency", SqlDbType.Int).Value = minFrequency;
                
				myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read()) 
                    {
                        exceptions.Add( PopulateForumExceptionFromIDataReader(reader) );
                    }
                    reader.Close();
                }
				// Close the connection
				myConnection.Close();

				return exceptions;
			}
        }
        #endregion
        
        #region Disallowed Names
        /// <summary>
        /// Retrieves the collection of disallowed names.
        /// <returns>An ArrayList object.</returns>
        /// </summary>
        public override ArrayList GetDisallowedNames()
        {
          using( SqlConnection myConnection = GetSqlConnection() )  {

            SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_DisallowedNames_Get", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add(this.SettingsIDParameter());
            ArrayList names = null;

            // Execute the command
            myConnection.Open();
              using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
              {
              
                  names = new ArrayList();
                  while ( dr.Read() )
                      names.Add( Convert.ToString(dr["DisallowedName"]) );

                  dr.Close();
                  myConnection.Close();
              }

            return names;
          }
        }

        /// <summary>
        /// Generic method to create, update and delete a disallowed name.
        /// <param name="name">The name that will be added, deleted or update the old name.</param>
        /// <param name="replacement">The name that will be updated.</param>
        /// <param name="action">Datastore operation: create, update or delete.</param>
        /// <returns>Operation status: true on success, otherwise false.</returns>
        /// </summary>
        public override int CreateUpdateDeleteDisallowedName(string name, string replacement, DataProviderAction action)
        {
          using( SqlConnection myConnection = GetSqlConnection() ) {

            SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_DisallowedName_CreateUpdateDelete", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

              myCommand.Parameters.Add(this.SettingsIDParameter());

            // Set the parameters
            //
            if(action == DataProviderAction.Delete)
              myCommand.Parameters.Add("@DeleteName", SqlDbType.Bit).Value = 1;            
            else
              myCommand.Parameters.Add("@DeleteName", SqlDbType.Bit).Value = 0;            

            if(action == DataProviderAction.Update) {
              myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 64).Value = name;
              myCommand.Parameters.Add("@Replacement", SqlDbType.NVarChar, 64).Value = replacement;
            }
            else {
              myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 64).Value = name;
              myCommand.Parameters.Add("@Replacement", SqlDbType.NVarChar, 64).Value = DBNull.Value;
            }

            myConnection.Open();              
            // Handle duplicate values
            try {
            myCommand.ExecuteNonQuery();
            }
            catch { }
              
            myConnection.Close();            
            return 1;
          }
        }
        #endregion

        #region Resources
        public override void CreateUpdateDeleteImage (int userID, Avatar avatar, DataProviderAction action) 
        {
            using(SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Image_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = avatar.Content;
                myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 64).Value = avatar.ContentType;
                myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = avatar.Length;
                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value            = action;
				// 新增文件名
				myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 256).Value = avatar.FileName; 
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
                myConnection.Open();

                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }

        }
        #endregion

        #region Categories


        public override  int CreateCategory(PostCategory category)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = category.SectionID;
                myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = category.Name;
                myCommand.Parameters.Add("@CategoryType", SqlDbType.SmallInt).Value = category.CategoryType;
                myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = category.IsEnabled;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = category.ParentID;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = category.Description;

                // We want to get the CategoryID back
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Direction = ParameterDirection.Output;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                int count = myCommand.ExecuteNonQuery();
                myConnection.Close();

                if(count > 0)
                {
                    try
                    {
                        return (int)myCommand.Parameters["@CategoryID"].Value;
                    }
                    catch
                    {
                        
                    }
                }

                return -1;
            }
        }


        public override  bool UpdateCategory(PostCategory category)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = category.CategoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = category.SectionID;
                myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = category.Name;
                myCommand.Parameters.Add("@CategoryType", SqlDbType.SmallInt).Value = category.CategoryType;
                myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = category.IsEnabled;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = category.ParentID;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = category.Description;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                int count = myCommand.ExecuteNonQuery();
                myConnection.Close();

                return count > 0;
            }
        }


        public override  void DeleteCategory(int categoryID, int forumID)
        {
            using(SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostCategory_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add the parameters
                myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add("@DeleteCategory", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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


        public override Hashtable GetCategories(int forumID)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand(databaseOwner + ".cs_PostCategories_Get", connection);
                command.CommandType = CommandType.StoredProcedure;
                Hashtable categories = new Hashtable();

                // Add Parameters to SPROC
                //
                command.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;

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

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
                        // Add categories
                        PostCategory category = new PostCategory();

                        category.CategoryID =			(int) dr["CategoryID"];
                        category.SectionID =			(int) dr["SectionID"];
                        category.Name =					(string) dr["Name"];
                        category.CategoryType =			(CategoryType)(Int16) dr["CategoryType"];
                        category.IsEnabled =			(bool) dr["IsEnabled"];
                        category.ParentID =				(int) dr["ParentID"];
                        category.Path =					(string) dr["Path"];
						category.DateCreated =				(DateTime) dr["DateCreated"];

                        // fields that are derived at runtime
                        category.TotalThreads =			(int) dr["TotalThreads"];
                        category.TotalSubThreads =			(int) dr["TotalSubThreads"];

                        if(dr["MostRecentPostDate"] != DBNull.Value)
                            category.MostRecentPostDate =	(DateTime) dr["MostRecentPostDate"];
                        else
                            category.MostRecentPostDate = SqlDateTime.MinValue.Value;

                        if(dr["MostRecentSubPostDate"] != DBNull.Value)
                            category.MostRecentSubPostDate =	(DateTime) dr["MostRecentSubPostDate"];
                        else
                            category.MostRecentSubPostDate = SqlDateTime.MinValue.Value;

                        category.Description = dr["Description"] as string;

                        categories.Add(category.CategoryID, category);
                    }

                    dr.Close();
                    connection.Close();
                }

                return categories;
            }
        }


        #endregion

		#region Blog Links

		public override Int32 CreateLink( Link link ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = link.LinkCategoryID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 100).Value = link.Title;
				myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 255).Value = link.Url;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = link.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = link.SortOrder;
                myCommand.Parameters.Add(this.SettingsIDParameter());

				// We want to get the ID back
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Direction = ParameterDirection.Output;

				// Execute the command
				myConnection.Open();
				int count = myCommand.ExecuteNonQuery();
				myConnection.Close();

				if(count > 0) {
					try {
						return (int)myCommand.Parameters["@LinkID"].Value;
					}
					catch {
                        
					}
				}

				return -1;
			}
		}

		public override Boolean UpdateLink( Link link ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkID", SqlDbType.Int).Value = link.LinkID;
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = link.LinkCategoryID;
				myCommand.Parameters.Add("@Title", SqlDbType.NVarChar, 100).Value = link.Title;
				myCommand.Parameters.Add("@Url", SqlDbType.NVarChar, 255).Value = link.Url;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = link.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = link.SortOrder;
				myCommand.Parameters.Add(this.SettingsIDParameter());