guestbooksqldataprovider.cs

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

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

using System;
using System.Data;
using System.Data.SqlClient;
using CommunityServer.Components;
using CommunityServer.GuestBooks.Components;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for GuestBookSqlDataProvider.
	/// </summary>
	public class GuestBookSqlDataProvider : GuestBookDataProvider
	{
		public GuestBookSqlDataProvider(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();
		}

		#region Member variables
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#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 Helper methods & properties
		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

		#region GetBook
		public override Book GetBook(int bookID, string appKey)
		{
			Book guestBook = null;

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = bookID;
				myCommand.Parameters.Add("@ApplicationType",SqlDbType.SmallInt, 1).Value = ApplicationType.GuestBook;
				myCommand.Parameters.Add("@ApplicationKey",SqlDbType.NVarChar, 256).Value = appKey;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader())
				{
					if(dr.Read())
					{
						guestBook = new Book();
						CommonDataProvider.PopulateSectionFromIDataReader(dr,guestBook);

						if(dr.NextResult())
						{
							while(dr.Read())
							{
								GuestBookPermission gbp = new GuestBookPermission();
								CommonDataProvider.PopulatePermissionFromIDataReader(gbp,dr);
								guestBook.PermissionSet.Add(gbp.Name,gbp);
							}
						}
					}
					dr.Close();
				}

				myCommand.Dispose();
				myConnection.Close();
			}

			return guestBook;
		}
		#endregion

		public override int AddPost(GBPost post, User user)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_guestbook_Post_Create", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
				myCommand.Parameters.Add("@Subject",SqlDbType.NVarChar, 256).Value = post.Subject;

				myCommand.Parameters.Add("@UserID",SqlDbType.Int, 4).Value = user.UserID;
				myCommand.Parameters.Add("@PostAuthor",SqlDbType.NVarChar, 64).Value = user.Username;

				myCommand.Parameters.Add("@Body",SqlDbType.NText).Value = post.Body;
				myCommand.Parameters.Add("@FormattedBody",SqlDbType.NText).Value = post.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress",SqlDbType.NVarChar,32).Value = post.UserHostAddress;

				SerializerData data = post.GetSerializerData();


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

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

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

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

				return (int)myCommand.Parameters["@PostID"].Value;
			}
		}

		public override void DeletePost(int SectionID, int PostID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_guestbook_Post_Delete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = PostID;

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

		public override PostSet GetPosts(int SectionID, int PageIndex, int PageSize, bool sortByNewest)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_guestbook_GetPosts", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID;
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Bit).Value = sortByNewest;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();
				PostSet ps = new PostSet();

				using(SqlDataReader reader = myCommand.ExecuteReader())
				{
					while (reader.Read())
					{
						GBPost post = new GBPost();
						CommonDataProvider.PopulatePostFromIDataReader(reader,post);
						ps.Posts.Add(post);
					}

					if(reader.NextResult() && reader.Read())
						ps.TotalRecords = reader.GetInt32(0);

					reader.Close();

				}
				myConnection.Close();


				return ps;
			}
		}

	}
}