gallerysqldataprovider.cs

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

//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CommunityServer.Components;
using CommunityServer.Galleries.Components;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for GallerySqlDataProvider.
	/// </summary>
	public class GallerySqlDataProvider : GalleryDataProvider
	{
		#region Member variables
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#endregion

		#region Constructor
		/****************************************************************
		// SqlDataProvider
		//
		/// <summary>
		/// Class constructor
		/// </summary>
		//
		****************************************************************/
		public GallerySqlDataProvider(string databaseOwner, string connectionString) 
		{

			// Read the connection string for this provider
			//
			this.connectionString = connectionString;

			// Read the database owner name for this provider
			//
			this.databaseOwner = databaseOwner;

            sqlHelper = ProviderHelper.Instance();

		}
		#endregion
	
		#region SettingsID 
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }
		protected SqlParameter SettingsIDParameter()
		{
			SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
			p.Value = GetSettingsID();
			return p;
		}
		#endregion

		#region Helpers
		protected SqlConnection GetSqlConnection () 
		{

			try 
			{
				return new SqlConnection(ConnectionString);
			} 
			catch 
			{
				throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
			}

		}

		public string ConnectionString 
		{
			get 
			{
				return connectionString;
			}
			set 
			{
				connectionString = value;
			}
		}
		#endregion

		public override Hashtable GetGalleries(bool mergePermissions)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable galleries = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Gallery;
				myCommand.Parameters.Add("@AllowInactive", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

				// Get the requested forums
				while (dr.Read()) 
				{
					Gallery gallery = new Gallery();
					PopulateGalleryFromIDataReader(dr, ref gallery);

					// Add the album to the Hashtable
					galleries.Add(gallery.ApplicationKey.ToLower(), gallery);
				}

				// Get the permissions
				if(dr.NextResult())
				{
					while(dr.Read())
					{
						// Get the section id
						GalleryPermission permission = new GalleryPermission();
						CommonDataProvider.PopulatePermissionFromIDataReader(permission, dr);

						// Are we applying this to a particular forum?
						if(dr["ApplicationKey"] != DBNull.Value)
						{
							string applicationKey = (string) dr["ApplicationKey"];
							Gallery gallery = galleries[applicationKey] as Gallery;

							if(gallery == null)
								continue;

							permission.SectionID = gallery.SectionID;
							gallery.PermissionSet.Add( permission.Name, permission );

							if(!permission.View)
								gallery.IsPrivate = true;
						}
						else
						{
							// Apply the permission to all forums
							foreach(Gallery gallery in galleries.Values)
							{
								permission.SectionID = gallery.SectionID;

								// Merge the permission
								if(gallery.PermissionSet[permission.Name] == null)
									gallery.PermissionSet.Add( permission.Name, permission );

								if(!permission.View)
									gallery.IsPrivate = true;
							}
						}
					}
				}
            
				// Done with the reader and the connection
				dr.Close();
				myConnection.Close();

				return galleries;
			}
		}

		public override void UpdatePictureCategories(int galleryID, int postID, string[] categoryList)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_UpdatePostsInCategories", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = galleryID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@CategoryList", SqlDbType.NVarChar, 4000).Value = ConvertCategoriesToXML(categoryList);
				myCommand.Parameters.Add("@CategoryType", SqlDbType.TinyInt).Value = CategoryType.GalleryPicture;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ArrayList GetPictureCategories(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_GetPostInCategories", myConnection);
				ArrayList categories = new ArrayList();

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

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

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

				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"];

					if(dr["Description"] != DBNull.Value)
						category.Description =		(string) dr["Description"];

					categories.Add(category);
				}

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

				// return the collection of IDs
				return categories;
			}
		}

		public override void CreatePicture(Picture picture)
		{
			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 = picture.SectionID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = picture.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 0;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = picture.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = picture.IsLocked;
				myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = picture.PostType;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = picture.EmoticonID;
				myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 64).Value = picture.Username;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = picture.AuthorID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = picture.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = picture.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = picture.UserHostAddress;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = picture.IsSticky;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = picture.StickyDate;

				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;

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

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

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

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

				myConnection.Close();
			}
		}

		public override void CreatePictureData(Picture picture, PostAttachment pictureData)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Add", connection);
				myCommand.CommandType = CommandType.StoredProcedure;


				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = picture.PostID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = picture.AuthorID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = picture.SectionID;
				myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 256).Value = pictureData.FileName;
				myCommand.Parameters.Add("@RealFilename", SqlDbType.NVarChar, 256).Value = pictureData.RealFileName;
				myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = pictureData.Content;
				myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = pictureData.ContentType;
				myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = pictureData.Length;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

		public override void DeletePictureData(int postID)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Delete", connection);
				myCommand.CommandType = CommandType.StoredProcedure;


				// Add parameters
				//