gallerysqldataprovider.cs

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

						comments.Add( comment );
					}

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

				return comments;
			}
		}

		public override void CreateComment(GalleryComment comment) 
		{ 

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

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = comment.SectionID;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = comment.ThreadID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = comment.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = sqlHelper.GetSiteSettings().EnableDuplicatePosts;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = sqlHelper.GetSiteSettings().DuplicatePostIntervalInMinutes;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = comment.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = comment.IsLocked;
				myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = comment.PostType;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = comment.EmoticonID;
				myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 64).Value = comment.Username;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = comment.User.UserID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = comment.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = comment.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = comment.UserHostAddress;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = comment.IsApproved;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = DateTime.Now;
				myCommand.Parameters.Add("@UpdateSectionStatistics", SqlDbType.Bit).Value = false;

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

				SerializerData data = comment.GetSerializerData();

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

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

				myConnection.Open();
				myCommand.ExecuteNonQuery();

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					comment.PostID = (int) myCommand.Parameters["@PostID"].Value;
				} 
				catch {}

				if (comment.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}

		public override ArrayList GetGalleryPermissions(int galleryID) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
				ArrayList permissions = new ArrayList();
				SqlDataReader reader;

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = galleryID;
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Gallery;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();

				reader = myCommand.ExecuteReader();

				while(reader.Read()) 
				{
					GalleryPermission perm = new GalleryPermission();
					CommonDataProvider.PopulatePermissionFromIDataReader(perm, reader);
					permissions.Add(perm);
				}

				myConnection.Close();

				return permissions;
			}
		}

		public override bool ApprovePost(int postID, int userIDApprovedBy) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_ApprovePost", 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("@ApprovedBy", SqlDbType.Int).Value = userIDApprovedBy;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int iResult = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
				myConnection.Close();

				return iResult == 1;        // was the post previously approved?
			}
		}

		public override void UpdatePictureMetadata(int postID, Hashtable metadata)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostMetadata_Update", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@MetadataList", SqlDbType.NText).Value = ConvertMetadataToXML(metadata);
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override Hashtable GetPictureMetadata(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostMetadata_Get", myConnection);
				Hashtable metadata = new Hashtable();
				SqlDataReader reader;

				// 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(this.SettingsIDParameter());

				myConnection.Open();

				reader = myCommand.ExecuteReader();

				while(reader.Read()) 
				{
					PopulateMetadataFromIDataReader(reader, metadata);
				}

				myConnection.Close();

				return metadata;
			}
		}

		public override Hashtable GetAllPictures(int galleryID)
		{
			Hashtable pictures = new Hashtable();

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

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

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

				// Populate the Hashtable
				while(dr.Read()) 
				{
					Picture picture = GalleryDataProvider.PopulatePictureFromIDataReader(dr);
					pictures.Add(picture.PostID, picture);
				}

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

				return pictures;
			}
		}

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

			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_gallery_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()) 
				{
					while(dr.Read())
					{
						Picture picture = PopulatePictureFromIDataReader(dr);

						// Some search specific ones
						picture.PostLevel = (int)dr["PostLevel"];
						picture.Replies = (int)dr["Replies"];

						postSet.Posts.Add(picture);
					}
				}

				// Close the connection
				myConnection.Close();
				return postSet;
			}
		}

		public override SearchResultSet GetSearchResults(SearchQuery query, SearchTerms terms)
		{
			// Modified by [e-mail]
			//string searchSQL = SqlGenerator.SearchText(query, terms, GetSettingsID(), ApplicationType.Gallery);
			// Modified by [e-mail]
			string searchSQL;

			int settingsID = GetSettingsID();
			ApplicationType appType = CSContext.Current.ApplicationType;
			if (appType == ApplicationType.Unknown)
				appType = ApplicationType.Forum; // Trick: Search forums by default?
			SearchMode searchMode = SiteSettingsManager.GetSiteSettings().SearchMode;

			switch (searchMode)
			{
				case SearchMode.ForumsIndexer:
					searchSQL = SqlGenerator.GetSearchCSIndexerSQL(query, terms, settingsID, appType);
					break;
				case SearchMode.SqlServerFullText:
					searchSQL = SqlGenerator.GetSearchFullTextSQL(query, terms, settingsID, appType);
					break;
				case SearchMode.Simple:
				default:
					searchSQL = SqlGenerator.GetSearchSimpleSQL(query, terms, settingsID, appType);
					break;
			}

			SearchResultSet result = new SearchResultSet();
			DateTime searchDuration;

			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand command = new SqlCommand("cs_gallery_Search", connection);
				SqlDataReader reader;

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

				// Add parameters
				command.Parameters.Add("@SearchSQL", SqlDbType.NVarChar, 4000).Value = searchSQL;
				command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
				command.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
				command.Parameters.Add(this.SettingsIDParameter());

				connection.Open();
				reader = command.ExecuteReader();

				// Process first record set 
				//
				while(reader.Read()) 
					result.Posts.Add( PopulatePictureFromIDataReader(reader) );

				// Move to the next result
				//
				if (reader.NextResult()) 
				{
					reader.Read();
					result.TotalRecords = Convert.ToInt32(reader[0]);
				}

				// Get the duration of the search?
				//
				if (reader.NextResult()) 
				{
					reader.Read();

					searchDuration = (DateTime) reader["Duration"];

					// Calculate the number of seconds it took the search to execute
					//
					int ms = Convert.ToInt32(searchDuration.ToString("ff"));
					result.SearchDuration = (double) ms / 1000;
				}

				connection.Close();

				return result;
			}
		}

	}
}