sqlcommondataprovider.cs

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

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

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Web.Mail;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.Discussions.Components;

namespace CommunityServer.Data {

    /// <summary>
    /// Summary description for WebForumsDataProvider
    /// </summary>
    public class SqlCommonDataProvider : CommonDataProvider {

        #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 SqlCommonDataProvider(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 Referrals

        public override void SaveReferralList(ArrayList referrals)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_referrals_Add", connection);
                

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

                // Add parameters
                command.Parameters.Add("@SettingsID", SqlDbType.Int, 4);
                command.Parameters.Add("@SectionID", SqlDbType.Int, 4);
                command.Parameters.Add("@PostID", SqlDbType.Int, 4);
                command.Parameters.Add("@UrlID", SqlDbType.Int, 4);
                command.Parameters.Add("@Url", SqlDbType.NVarChar, 512);

                connection.Open();
                foreach(Referral referral in referrals)
                {

                    command.Parameters["@SettingsID"].Value = referral.SettingsID;
                    command.Parameters["@SectionID"].Value = referral.SectionID;
                    command.Parameters["@PostID"].Value = referral.PostID;
                    command.Parameters["@UrlID"].Value = referral.UrlID;
                    command.Parameters["@Url"].Value = referral.Url;
                    command.ExecuteNonQuery();
                }
                connection.Close();
                command.Dispose();
                connection.Dispose();
            }
        }

        public override ReferralSet GetReferrals(Referral r, int pageSize, int pageIndex)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_referrals_Get", connection);

                command.CommandType = CommandType.StoredProcedure;

                // Add parameters
                command.Parameters.Add(this.SettingsIDParameter());
                command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = r.SectionID;
                command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = r.PostID;
                command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pageSize;
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = pageIndex;

                SqlParameter p = new SqlParameter("@TotalRecords",SqlDbType.Int,4);
                p.Direction = ParameterDirection.Output;
                command.Parameters.Add(p);

                ReferralSet rs = new ReferralSet();
                Referral referral = null;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while(reader.Read())
                {
                    referral = new Referral();
                    referral.PostID = (int)reader["PostID"];
                    referral.SectionID = (int)reader["SectionID"];
                    referral.LastDate = (DateTime)reader["LastDate"];
                    referral.Hits = (int)reader["Hits"];
                    referral.Url = reader["Url"] as string;
                    referral.Title = reader["Subject"] as string;
                    rs.Referrals.Add(referral);
                    
                }

                reader.Close();
                rs.TotalRecords = (int)command.Parameters["@TotalRecords"].Value;

                command.Dispose();
                connection.Close();

                return rs;

            }


        }


        #endregion

        #region Views

        public override void SaveViewList(Hashtable views)
        {
            if(views == null || views.Count == 0)
                return;

            using( SqlConnection connection = GetSqlConnection() ) 
            {
                SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_Posts_Views_Add", connection);

                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@PostID", SqlDbType.Int, 4);
                command.Parameters.Add("@AggCount", SqlDbType.Int, 4);
                command.Parameters.Add("@WebCount", SqlDbType.Int, 4);

                View v = null;
                connection.Open();
                foreach(int postID in views.Keys)
                {
                    v = views[postID] as View;
                    if(v != null)
                    {
                        command.Parameters["@PostID"].Value = v.PostID;
                        command.Parameters["@AggCount"].Value = v.AggCount;
                        command.Parameters["@WebCount"].Value = v.WebCount;

                        command.ExecuteNonQuery();
                    }
                }
                command.Dispose();
                connection.Close();
            }
        }


        #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 #### Site Settings ####
        public override ArrayList LoadAllSiteSettings() {
			using( SqlConnection connection = GetSqlConnection() ) {

				SqlCommand command = new SqlCommand( this.databaseOwner + ".cs_SiteSettings_Get", connection);
				SqlDataReader reader;
				ArrayList list = new ArrayList();

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

				// Add parameters
				command.Parameters.Add("@SiteUrl", SqlDbType.NVarChar, 512).Value ="*";

				connection.Open();

                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // We only expect a single record
                    while(reader.Read()) 
                    {
                        list.Add(PopulateSiteSettingsFromIDataReader(reader));
                    }

                    reader.Close();
                    // All done with the connection
                    //
                    connection.Close();
                }
				return list;
			}
        }

        /****************************************************************
        // LoadSiteSettings
        //
        /// <summary>
        /// Loads the site settings from the database
        /// </summary>
        //
        ****************************************************************/
        public override SiteSettings LoadSiteSettings(string application) {
			using( SqlConnection connection = GetSqlConnection() ) {
				SqlCommand command = new SqlCommand(this.databaseOwner + ".cs_SiteSettings_Get", connection);
				SqlDataReader reader;
				SiteSettings settings = null;

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

				// Add parameters
				command.Parameters.Add("@SiteUrl", SqlDbType.NVarChar, 512).Value = application;

				try {
					connection.Open();
				} catch (SqlException sqlException) {
					throw new CSException(CSExceptionType.DataProvider, "Unable to open connection to data provider.", sqlException);
				}

                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Attempt to populate a site settings object
                    try 
                    {
                        if(reader.Read())
                        {
                            settings = PopulateSiteSettingsFromIDataReader(reader);
                        }
                    } 
                    catch (CSException ex) 
                    {
                        reader.Close();
                        connection.Close();

                        // Do we have bad XML?
                        if (ex.ExceptionType == CSExceptionType.SiteSettingsInvalidXML) 
                        {

                            settings = new SiteSettings();

                            settings.SettingsID = (int) reader["SettingsID"];
                            settings.SiteDomain = reader["SiteUrl"] as string;
                            settings.ForumsDisabled = (bool)reader["Disabled"];
                            settings.SiteKey = (Guid)reader["SettingsKey"];
                            settings.ApplicationName = reader["ApplicationName"] as string;

                            // Save the updated values
                            SaveSiteSettings(settings);

                        }
                    }
                    finally
                    {
                        reader.Close();
                        connection.Close();
                    }
                }

				return settings;
			}
        }

        /****************************************************************
        // SaveSiteSettings
        //
        /// <summary>
        /// Save the site settings from the database
        /// </summary>
        //
        ****************************************************************/
        public override void SaveSiteSettings(SiteSettings siteSettings) {
//            BinaryFormatter binaryFormatter = new BinaryFormatter();
//            MemoryStream ms = new MemoryStream();
//            byte[] b;

			using( SqlConnection connection = GetSqlConnection() ) {