gallerysqldataprovider.cs

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

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
		}

		public override void UpdatePicture(Picture picture, int editedByUserID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = picture.PostID;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = picture.Subject;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = picture.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = picture.FormattedBody;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = picture.EmoticonID;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = picture.IsLocked;
				myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = editedByUserID;
				myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = picture.EditNotes;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = picture.IsSticky;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = picture.StickyDate;
				myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = picture.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

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

		public override void DeletePicture(int sectionID, int threadID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Delete", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int, 4).Value = threadID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

		public override void DeletePost(int postID, int userID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_DeletePost", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
				myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int, 4).Value = userID;
				myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = "Blogger Delete";
				myCommand.Parameters.Add("@DeleteChildPosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

		public override ThreadSet GetPictures(GalleryThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_gallery_Threads_GetThreadSet", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;
					command.Parameters.Add("@sqlPopulate", SqlDbType.NVarChar, 4000).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);
					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					//If we want to display the list of categories for the post, we will need to 
					//use a dataset so we can join the child records
					if(query.IncludeCategories)
					{
						DataSet ds = new DataSet();
						SqlDataAdapter da = new SqlDataAdapter(command);

						//wait as long as possible to open the conn
						connection.Open();
						da.Fill(ds);
						connection.Close();

						//keep a referece to the relation since it is used in the GetChildRows look up anyway
						DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
						ds.Relations.Add(relation);
                        
						DataRowCollection posts = ds.Tables[0].Rows;
						foreach(DataRow dr in posts)
							ts.Threads.Add(PopulatePictureFromIDataReader(dr, relation));
						ds.Clear();
						ds.Dispose();

						ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
					}
					else
					{
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{
						
							while(dr.Read())
								ts.Threads.Add(PopulatePictureFromIDataReader(dr));

							dr.NextResult();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
					}

					return ts;

				}
			}
		}

		public override int GetPictureSortOrder(int postID, GalleryThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_gallery_Post_GetSortOrder", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
					command.Parameters.Add("@sqlPopulate", SqlDbType.NVarChar, 4000).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					// Execute the command
					connection.Open();
					int sortOrder = Convert.ToInt32(command.ExecuteScalar().ToString());
					connection.Close();

					return sortOrder;
				}
			}
		}

		public override Picture GetPicture(int postID, int userID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Post", 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("@TrackViews", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if (!dr.Read()) 
				{
					dr.Close();
					myConnection.Close();
					// we did not get back a post
					throw new CSException(CSExceptionType.PostNotFound, postID.ToString());
				}

				Picture p = PopulatePictureFromIDataReader(dr);

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


				// we have a post to work with  
				return p;
			}
		}

		public override int GetRandomPostID(int sectionID, int categoryID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_GetRandomPostID", myConnection);
				int postID;

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if(!dr.Read())
				{
					dr.Close();
					myConnection.Close();
					return -1;
				}

				postID = (int) dr["PostID"];

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


				// we have a post to work with  
				return postID;
			}
		}

		public override PostAttachment GetPictureData(int postID)
		{
			PostAttachment data = new PostAttachment();

			// Create Instance of Connection and Command Object
			//
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_PostAttachment", connection);
				command.CommandType = CommandType.StoredProcedure;

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

				// Execute the command
				connection.Open();
				SqlDataReader dr = command.ExecuteReader();

				// Populate the Hashtable
				while(dr.Read()) 
				{
					data.PostID = (int)dr["PostID"];
					data.ForumID = (int)dr["SectionID"];
					data.UserID = (int)dr["UserID"];
					data.DateCreated = (DateTime)dr["Created"];
					data.FileName = (string)dr["FileName"];
					data.ContentType = (string)dr["ContentType"];
					data.Length = (int)dr["ContentSize"];
					data.Content = dr["Content"] as byte[];
				}

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

				return data;
			}
		}

		public override ArrayList GetComments(int postID, bool allowUnapproved)
		{
			using( SqlConnection myConnection = GetSqlConnection() )
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Posts_PostSet", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList comments = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value            = postID;
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value         = 0;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value          = 1000;
				myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value            = 0;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value         = SortOrder.Ascending;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value            = 0;
				myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@AllowUnapproved", SqlDbType.Bit).Value	= allowUnapproved;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				SqlDataReader reader = myCommand.ExecuteReader();

				// Get the results
				//
				while(reader.Read())
					if((int)reader["PostID"] != postID)
					{
						GalleryComment comment = new GalleryComment();
						CommonDataProvider.PopulatePostFromIDataReader(reader,comment);