sqlcommondataprovider.cs

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

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

				return count > 0;
			}
		}

		public override void DeleteLink( Int32 linkID ) {
			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 = linkID;
				myCommand.Parameters.Add("@DeleteLink", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

		public override Hashtable GetLinks( Int32 linkCategoryID ) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Links_Get", connection);
				command.CommandType = CommandType.StoredProcedure;
				Hashtable links = new Hashtable();

				// Add Parameters to SPROC
				//
				command.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategoryID;
                command.Parameters.Add(this.SettingsIDParameter());

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

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
                        // Add links
                        Link link = new Link();

                        link.LinkID = (Int32)dr[ "LinkID" ];
                        link.LinkCategoryID = (Int32)dr[ "LinkCategoryID" ];
                        link.Title = (String)dr[ "Title" ];
                        link.Url = (String)dr[ "Url" ];
                        link.IsEnabled = (Boolean)dr[ "IsEnabled" ];
                        link.SortOrder = (Int32)dr[ "SortOrder" ];

                        links.Add( link.LinkID, link );
                    }

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

				return links;
			}
		}

		public override void ChangeLinkSortOrder( Int32 linkID, Boolean moveUp ) {
			using( SqlConnection myConnection = GetSqlConnection() ) { 
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Link_UpdateSortOrder", myConnection); 

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

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

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

				myConnection.Close(); 
			}
		}


		public override Int32 CreateLinkCategory( LinkCategory linkCategory ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = linkCategory.ForumID;
				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = linkCategory.Name;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = linkCategory.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = linkCategory.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = linkCategory.SortOrder;
                myCommand.Parameters.Add(this.SettingsIDParameter());

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

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

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

				return -1;
			}
		}

		public override Boolean UpdateLinkCategory( LinkCategory linkCategory ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategory.LinkCategoryID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = linkCategory.ForumID;
				myCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 256).Value = linkCategory.Name;
				myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 2000).Value = linkCategory.Description;
				myCommand.Parameters.Add("@IsEnabled", SqlDbType.Bit).Value = linkCategory.IsEnabled;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = linkCategory.SortOrder;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

				return count > 0;
			}
		}

		public override void DeleteLinkCategory( Int32 linkCategoryID, Int32 forumID ) {
			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add the parameters
				myCommand.Parameters.Add("@LinkCategoryID", SqlDbType.Int).Value = linkCategoryID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
				myCommand.Parameters.Add("@DeleteLinkCategory", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

		public override Hashtable GetLinkCategories( Int32 forumID ) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_LinkCategories_Get", connection);
				command.CommandType = CommandType.StoredProcedure;
				Hashtable linkCategories = new Hashtable();

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

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

                    // Populate the Hashtable
                    while(dr.Read()) 
                    {
                        // Add links
                        LinkCategory linkCategory = new LinkCategory();

					
                        linkCategory.LinkCategoryID = (Int32)dr[ "LinkCategoryID" ];
                        linkCategory.ForumID = (Int32)dr[ "SectionID" ];
                        linkCategory.Name = (String)dr[ "Name" ];
                        if ( !dr.IsDBNull( dr.GetOrdinal( "Description" ) ) ) 
                        {
                            linkCategory.Description = (String)dr[ "Description" ];
                        }
                        linkCategory.IsEnabled = (Boolean)dr[ "IsEnabled" ];
                        linkCategory.SortOrder = (Int32)dr[ "SortOrder" ];

                        linkCategories.Add( linkCategory.LinkCategoryID, linkCategory );
                    }

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

				return linkCategories;
			}
		}

		public override void ChangeLinkCategorySortOrder( Int32 linkCategoryID, Boolean moveUp ) {
			using( SqlConnection myConnection = GetSqlConnection() ) { 
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_LinkCategory_UpdateSortOrder", myConnection); 

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

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

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

				myConnection.Close(); 
			}
		}

		#endregion

		#region Blog Feedback

		
		public override ArrayList GetFeedback( Int32 forumID ) {
			Int32 totalRecords;
			return GetFeedback( forumID, 0, 0, out totalRecords );
		}

		public override ArrayList GetFeedback( Int32 forumID, Int32 pageIndex, Int32 pageSize, out Int32 totalRecords ) {
			ArrayList feedback = new ArrayList();

			using ( SqlConnection cn = GetSqlConnection() )
			using ( SqlCommand cmd = new SqlCommand( databaseOwner + ".cs_weblog_Feedback_Get", cn ) ) {
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                cmd.Parameters.Add(this.SettingsIDParameter());
				cmd.Parameters.Add("@TotalRecords", SqlDbType.Int ).Direction = ParameterDirection.Output;
				if ( pageSize > 0 ) {
					cmd.Parameters.Add( "@UsePaging", SqlDbType.Bit ).Value = true;
					cmd.Parameters.Add( "@PageSize", SqlDbType.Int ).Value = pageSize;
					cmd.Parameters.Add( "@PageIndex", SqlDbType.Int ).Value = pageIndex;
				}
				
				cn.Open();
                using(SqlDataReader dr = cmd.ExecuteReader())
                {

                    // PostID, PostAuthor, Subject, PostDate, IsApproved, PostName, TitleUrl
                    while ( dr.Read() ) 
                    {
                    	BlogFeedbackItem item = new BlogFeedbackItem();
					
                        item.PostID = (Int32)dr[ "PostID" ];
                        item.PostAuthor = (String)dr[ "PostAuthor" ];
                        item.Subject = (String)dr[ "Subject" ];
                        item.IsApproved = (Boolean)dr[ "IsApproved" ];
                        item.TitleUrl = dr[ "TitleUrl" ] as String;
                        item.PostDate = (DateTime)dr[ "PostDate" ];
                        item.Body = dr[ "Body" ] as String;

                        feedback.Add( item );
                    }

                    dr.Close();
                }

				totalRecords = (Int32)cmd.Parameters[ "@TotalRecords" ].Value;

				cn.Close();

			}

			return feedback;
		}

		#endregion

        #region #### Vote ####
        /****************************************************************
        // Vote
        //
        /// <summary>
        /// Votes for a poll
        /// </summary>
        //
        ****************************************************************/
        public override void Vote(int postID, int userID, string selection) {
            // Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Vote_Create", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@Vote", SqlDbType.NVarChar, 2).Value = selection;
                myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
        }
        
        /****************************************************************
        // GetVoteResults
        //
        /// <summary>
        /// Returns a collection of threads that the user has recently partipated in.
        /// </summary>
        //
        ****************************************************************/
        public override PollSummary GetPoll(PollSummary summary) {

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

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