PetShop中操作数据的方法

 题目:PetShop中操作数据的方法

1.:SqlHelper.cs
整合了一些操作数据的方法，节省了时间和空间,简单的就这么讲。

2.:为什么要定义常量参数
有些人奇怪为什么操作数据的方法里为什么这样

1private const string SQL_ARTICLE_INSERT = "INSERT INTO NR_Article([columnid],[sort],[time],[title],[content],[author],[hit],[status]) VALUES(@ColumnID,@Sort,@Time,@Title,@Content,@Author,@Hit,@Status)";
2
3private const string PARM_ARTICLE_ID = "@ID";

可能奇怪为什么要用PARM_ARTICLE_ID的形式,
原因是编译后只需要调用就可以了，也就是说这部分的代码编译一次以后直接调用就可以了
而如果采用一般的变量的方式，每执行一次就要动态编译一次(不知道这样说有没有问题)

3.:接口的作用
PetShop里实现了几个数据库的调用方式,利用的就是接口然后再利用反射来确定动态加载的程序集

4.在PetShop里用abstract抽象类实现工厂方法
PetShop里用的是接口，但是我们也可以用abstract的形式,参考了CS的代码Provider + SqlProvider

using System;
using Edot.NewsRelease.Components;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;

namespace Edot.NewsRelease.SqlProvider
{
    /**//// <summary>
    /// SqlProvider 的摘要说明。
    /// 实现数据库操作
    /// </summary>
    /**//*------------------------------------------------------------------
     * 结合了SqlHelper进行数据库操作,用操作串进行操作
     *------------------------------------------------------------------*/
    public class SqlProvider:Provider
    {
        private string dbOwner = "dbo";
        private string connString = null;

        protected string DBOwner
        {
            get{return dbOwner;}
            set{dbOwner = value;}
        }

        protected string ConnString
        {
            get{return connString;}
            set{connString = value;}
        }
        
        public SqlProvider(string strDbOwner,string strConnString)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            DBOwner = strDbOwner;
            ConnString = strConnString;
        }

        public SqlConnection GetSqlConnection()
        {
            try
            {
                return new SqlConnection(ConnString);
            }
            catch
            {    
                throw new Exception("数据库连接字符串出错，请检查!");
            }
        }
        
        Article操作部分#region Article操作部分

        Article操作数据库语句#region Article操作数据库语句
        private const string SQL_ARTICLE_INSERT = "INSERT INTO NR_Article([columnid],[sort],[time],[title],[content],[author],[hit],[status]) VALUES(@ColumnID,@Sort,@Time,@Title,@Content,@Author,@Hit,@Status)";
        private const string SQL_ARTICLE_UPDATE = 
            "UPDATE NR_Article SET [columnid] = @ColumnID,[sort] = @Sort,[time] = @Time,[title] = @Title,[Content] = @content,[author] = @Author,[hit] = @Hit,[status] = @Status WHERE id = @ID";
        private const string SQL_ARTICLE_DELETEBYID = "DELETE FROM NR_Article WHERE [id] = @ID";
        private const string SQL_ARTICLE_DELETEBYCOLUMNID = "DELETE FROM NR_Article WHERE [columnid] = @ColumnID";
        private const string SQL_ARTICLE_GETARTICLESBYCOLUMNID = "SELECT [id],[sort],[time],[title],[content],[author],[hit],[status] FROM NR_Article WHERE [columnid] = @ColumnID order by [time] desc";
        private const string SQL_ARTICLE_GETARTICLEBYID = "SELECT [sort],[time],[title],[columnid],[content],[author],[hit],[status] FROM NR_Article WHERE [id] = @ID order by [time] desc";

        private const string PARM_ARTICLE_ID = "@ID";
        private const string PARM_ATRICLE_COLUMNID = "@ColumnID";
        private const string PARM_ATRICLE_SORT = "@Sort";
        private const string PARM_ARTICLE_TIME = "@Time";
        private const string PARM_ARTICLE_TITLE = "@Title";
        private const string PARM_ARTICLE_CONTENT = "@Content";
        private const string PARM_ARTICLE_AUTHOR = "@Author";
        private const string PARM_ARTICLE_HIT = "@Hit";
        private const string PARM_ATRICLE_STATUS = "@Status";

        #endregion

        Article数据库操作方法#region Article数据库操作方法
        /**//// <summary>
        /// 得到文章集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override ArticleCollection GetArticlesByColumnID(int id)
        {
            SqlParameter parm = new SqlParameter(PARM_ATRICLE_COLUMNID,SqlDbType.Int,4);
            parm.Value = id;

            ArticleCollection articleCollection= new ArticleCollection();
            using(SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.GetConnString(),
                      CommandType.Text,SQL_ARTICLE_GETARTICLESBYCOLUMNID,parm))
            {
                while(dr.Read())
                {
                    Article article = new Article();
                    article.ID = (int)dr["id"];
                    article.Sort =(int)dr["sort"];                
                    article.Time = Convert.ToDateTime(dr["time"]);
                    article.Title = dr["title"].ToString();
                    article.Content = dr["content"].ToString();
                    article.Author = dr["author"].ToString();
                    article.Hit = (int)dr["hit"];
                    article.Status = Convert.ToInt32(dr["status"]);
                    articleCollection.Add(article);
                }
            }
            return articleCollection;
        }

        /**//// <summary>
        /// 根据ID得到单个文章的数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override Article GetArticleByID(int id)
        {
            SqlParameter parm = new SqlParameter(PARM_ARTICLE_ID,SqlDbType.Int,4);
            parm.Value = id;

            Article article = new Article();
            using(SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.GetConnString(),
                      CommandType.Text,SQL_ARTICLE_GETARTICLEBYID,parm))
            {
                if(dr.Read())
                {
                    article.Sort =(int)dr["sort"];                
                    article.Time = Convert.ToDateTime(dr["time"]);
                    article.ColumnID = Convert.ToInt32(dr["columnid"]);
                    article.Title = dr["title"].ToString();
                    article.Content = dr["content"].ToString();
                    article.Author = dr["author"].ToString();
                    article.Hit = (int)dr["hit"];
                    article.Status = Convert.ToInt32(dr["status"]);
                }
            }

            return article;
        }

        /**//// <summary>
        /// 插入文章数据
        /// </summary>
        /// <param name="article">实例类</param>
        public override void InsertArticle(Article article)
        {
            SqlParameter[] parms = GetArticleParameters();

            SetArticleParameters(parms,article);

            using(SqlConnection conn = new SqlConnection(SqlHelper.GetConnString()))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans,CommandType.Text,SQL_ARTICLE_INSERT,parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            
        }

        /**//// <summary>
        /// 更新文章数据
        /// </summary>
        /// <param name="article"></param>
        public override void UpdateArticle(Article article)
        {
            SqlParameter[] parms = GetArticleParameters();

            SetArticleParameters(parms,article);

            using(SqlConnection conn = new SqlConnection(SqlHelper.GetConnString()))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans,CommandType.Text,SQL_ARTICLE_UPDATE,parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

            }
        }

        /**//// <summary>
        /// 删除单个文章
        /// </summary>
        /// <param name="id"></param>
        public override void DeleteArticle(int id)
        {
            SqlParameter parm = new SqlParameter(PARM_ARTICLE_ID,SqlDbType.Int,4);
            parm.Value = id;

            using(SqlConnection conn = new SqlConnection(SqlHelper.GetConnString()))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans,CommandType.Text,SQL_ARTICLE_DELETEBYID,parm);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /**//// <summary>
        /// 删除某个栏目下的所有文章
        /// </summary>
        /// <param name="id"></param>
        public override void DeleteColumnArticles(int id)
        {
            SqlParameter[] parms = GetArticleParameters();
            parms[0].Value = id;

            using(SqlConnection conn = new SqlConnection(SqlHelper.GetConnString()))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans,CommandType.Text,SQL_ARTICLE_DELETEBYCOLUMNID,parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw new Exception("删除栏目下所有文章出错");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /**//// <summary>
        /// 私有成员根据缓存得到参数
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetArticleParameters()
        {
            SqlParameter[] parms = SqlHelper.GetCacheParameters(SQL_ARTICLE_INSERT);

            if(parms == null)
            {
                parms = new SqlParameter[]
                    {
                        
                        new SqlParameter(PARM_ATRICLE_SORT,SqlDbType.Int,4),
                        new SqlParameter(PARM_ATRICLE_COLUMNID,SqlDbType.Int,4), 
                        new SqlParameter(PARM_ARTICLE_TIME,SqlDbType.DateTime,8),
                        new SqlParameter(PARM_ARTICLE_TITLE,SqlDbType.NVarChar,100),
                        new SqlParameter(PARM_ARTICLE_CONTENT,SqlDbType.NText,1000),
                        new SqlParameter(PARM_ARTICLE_AUTHOR,SqlDbType.VarChar,50),
                        new SqlParameter(PARM_ARTICLE_HIT,SqlDbType.Int,4),
                        new SqlParameter(PARM_ATRICLE_STATUS,SqlDbType.TinyInt,1),
                        new SqlParameter(PARM_ARTICLE_ID,SqlDbType.Int,4)
                    };

                SqlHelper.CacheParameters(SQL_ARTICLE_INSERT,parms);
            }

            return parms;
        }

        /**//// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="article"></param>
        private static void SetArticleParameters(SqlParameter[] parms,Article article)
        {
            parms[1].Value = article.ColumnID;
            parms[0].Value = article.Sort;
            parms[2].Value = article.Time;
            parms[3].Value = article.Title;
            parms[4].Value = article.Content;
            parms[5].Value = article.Author;
            parms[6].Value = article.Hit;
            parms[7].Value = article.Status;
            parms[8].Value = article.ID;
        }
        #endregion
}

给出操作Article的部分希望可以帮助一些人