Misrosoft Enterprise Lib操作

一直在做项目，今天有点空，把累积的东西记录

企业库的操作可以让我们省去写SQLHelper类，或者oracleHelper类，具体操作如下，以新闻的增删改查为例，全部用存储过程实现，存储过程就不写出来了。

新闻实体类

public class News
    {
        /// <summary>
        /// 新闻ID
        /// </summary>
        public int NewsID { get; set; }
        /// <summary>
        /// 新闻标题
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 新闻内容
        /// </summary>
        public string Content { get; set; }
        /// <summary>
        /// 排序号
        /// </summary>
        public int OrderBy { get; set; }
        /// <summary>
        /// 是否显示
        /// </summary>
        public int IsShow { get; set; }
        /// <summary>
        /// 是否推荐
        /// </summary>
        public int IsRecommend { get; set; }
        /// <summary>
        /// 是否审核
        /// </summary>
        public int IsCheck { get; set; }
        /// <summary>
        /// 点击率
        /// </summary>
        public int ClickNumber { get; set; }
        /// <summary>
        /// 文章来源
        /// </summary>
        public string PageFrom { get; set; }
        /// <summary>
        /// 关键字
        /// </summary>
        public string Keywords { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        public string Author { get; set; }
        /// <summary>
        /// 日期
        /// </summary>
        public DateTime CreateDate { get; set; }
        /// <summary>
        /// 主题图片
        /// </summary>
        public string Image { get; set; }
        /// <summary>
        /// 是否置顶
        /// </summary>
        public int IsTop { get; set; }
        /// <summary>
        /// 所属镇区
        /// </summary>
        public string Address { get; set; }
        /// <summary>
        /// 新闻类别
        /// </summary>
        public int CategoryID { get; set; }
    }

 

DataRecordTable类

public class DataRecordTable
    {
        DataTable table;
        int pageIndex;
        int pageSize;
        int pageCount;
        int recordCount;
        public DataRecordTable() { }
        /// <summary>
        /// 获取当前页
        /// </summary>
        public int PageIndex
        {
            get { return pageIndex; }
            set { pageIndex = value; }
        }
        public DataTable Table
        {
            get { return table; }
            set { table = value; }
        }

        /// <summary>
        /// 获取每页显示的记录数量
        /// </summary>
        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }

        /// <summary>
        /// 获取总页数
        /// </summary>
        public int PageCount
        {
            get { return pageCount; }
            set { PageCount = value; }
        }

        /// <summary>
        /// 获取总记录数
        /// </summary>
        public int RecordCount
        {
            get { return recordCount; }
            set { recordCount = value; }
        }

        public DataRecordTable(DataTable table, int pageSize, int pageIndex, int pageCount, int recordCount)
        {
            this.table = table;
            this.pageIndex = pageIndex;
            this.pageSize = pageSize;
            this.pageCount = pageCount;
            this.recordCount = recordCount;
        }
    }

 

访问层：

//增加，因为要用到oracle的clob字段，所以要转成oracleDatabase数据库

//如果没有clob字段，则没有必要

public class NewsDAO
    {
        public int Add(News news,out int newsId)
        {
            OracleDatabase database = (OracleDatabase)DatabaseFactory.CreateDatabase();
            OracleCommand cmd = (OracleCommand)database.GetStoredProcCommand("NewsPackage.NewsAdd");
            database.AddInParameter(cmd, "inTitle", DbType.String, news.Title);
            database.AddParameter(cmd, "inContent", OracleType.Clob, news.Content.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, news.Content);
            database.AddInParameter(cmd, "inOrderBy", DbType.Int32, news.OrderBy);
            database.AddInParameter(cmd, "inIsShow", DbType.Int32, news.IsShow);
            database.AddInParameter(cmd, "inIsRecommend", DbType.Int32, news.IsRecommend);
            database.AddInParameter(cmd, "inIsCheck", DbType.Int32, news.IsCheck);
            database.AddInParameter(cmd, "inClickNumber", DbType.Int32, news.ClickNumber);
            database.AddInParameter(cmd, "inPageFrom", DbType.String, news.PageFrom);
            database.AddInParameter(cmd, "inKeywords", DbType.String, news.Keywords);
            database.AddInParameter(cmd, "inAuthor", DbType.String, news.Author);
            database.AddInParameter(cmd, "inImage", DbType.String, news.Image);
            database.AddInParameter(cmd, "inIsTop", DbType.Int32, news.IsTop);
            database.AddInParameter(cmd, "inAddress", DbType.String, news.Address);
            database.AddInParameter(cmd, "inCategoryID", DbType.Int32, news.CategoryID);
            database.AddOutParameter(cmd, "outNewsID",DbType.Int32,4);
            database.AddOutParameter(cmd, "outState", DbType.Int32,4);
            database.ExecuteNonQuery(cmd);
            int state = Convert.ToInt32(database.GetParameterValue(cmd, "outState"));
            newsId = Convert.ToInt32(database.GetParameterValue(cmd, "outNewsID"));
            return state;
        }

//得到列表，采用了前一段时间写的oracle通用分页存储过程
        public DataRecordTable GetList(string orderField, string orderBy, int pageIndex, int pageSize, string where)
        {
            DataRecordTable table = new DataRecordTable();
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand cmd = database.GetStoredProcCommand("NewsPackage.NewsGetList");
            database.AddInParameter(cmd, "inPageSize", DbType.Int32, pageSize);
            database.AddInParameter(cmd, "inPageIndex", DbType.Int32, pageIndex);
            database.AddInParameter(cmd, "inOrderField", DbType.String, orderField);
            database.AddInParameter(cmd, "inIsOrderBy", DbType.String, orderBy);
            database.AddInParameter(cmd, "inWhere", DbType.String, where);
            database.AddOutParameter(cmd, "outRecordCount", DbType.Int32, 4);
            database.AddOutParameter(cmd, "outPageCount", DbType.Int32, 4);
           
            DataSet ds = database.ExecuteDataSet(cmd);
            int recordCount = Convert.ToInt32(database.GetParameterValue(cmd, "outRecordCount"));
            int pageCount = Convert.ToInt32(database.GetParameterValue(cmd, "outPageCount"));
            table = new DataRecordTable(ds.Tables[0], pageSize, pageIndex, pageCount, recordCount);
            return table;
        }

//更新
        public int Update(News news)
        {
            OracleDatabase database = (OracleDatabase)DatabaseFactory.CreateDatabase();
            OracleCommand cmd = (OracleCommand)database.GetStoredProcCommand("NewsPackage.NewsEdit");
            database.AddInParameter(cmd, "inNewsID", DbType.Int32, news.NewsID);
            database.AddInParameter(cmd, "inTitle", DbType.String, news.Title);
            database.AddParameter(cmd,"inContent",OracleType.Clob, news.Content.Length,ParameterDirection.Input,false,0,0,"Content",DataRowVersion.Default,news.Content);
            database.AddInParameter(cmd, "inOrderBy", DbType.Int32, news.OrderBy);
            database.AddInParameter(cmd, "inIsShow", DbType.Int32, news.IsShow);
            database.AddInParameter(cmd, "inIsRecommend", DbType.Int32, news.IsRecommend);
            database.AddInParameter(cmd, "inIsCheck", DbType.Int32, news.IsCheck);
            database.AddInParameter(cmd, "inPageFrom", DbType.String, news.PageFrom);
            database.AddInParameter(cmd, "inKeywords", DbType.String, news.Keywords);
            database.AddInParameter(cmd, "inAuthor", DbType.String, news.Author);
            database.AddInParameter(cmd, "inImage", DbType.String, news.Image);
            database.AddInParameter(cmd, "inIsTop", DbType.Int32, news.IsTop);
            database.AddInParameter(cmd, "inAddress", DbType.String, news.Address);
            database.AddInParameter(cmd, "inCategoryID", DbType.Int32, news.CategoryID);
            database.AddOutParameter(cmd, "outState", DbType.Int32, 4);
            database.ExecuteNonQuery(cmd);
           
            int state = Convert.ToInt32(database.GetParameterValue(cmd, "outState"));
            return state;
        }

//删除
        public bool Delete(int newsId)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand cmd = database.GetStoredProcCommand("NewsPackage.NewsDelete");
            database.AddInParameter(cmd, "inNewsID", DbType.Int32, newsId);
            int row = database.ExecuteNonQuery(cmd);
            return row > 0;
        }

//得到一个实体
        public News GetEntity(int newsId)
        {
            News news = new News();
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand cmd = database.GetStoredProcCommand("NewsPackage.NewsGetEntity");
            database.AddInParameter(cmd, "inNewsID", DbType.Int32, newsId);
            using (IDataReader reader = database.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    news.NewsID = newsId;
                    news.Title = reader["title"].ToString();
                    news.Content = reader["content"].ToString();
                    news.OrderBy = Convert.ToInt32(reader["orderBy"]);
                    news.IsShow = Convert.ToInt32(reader["isShow"]);
                    news.IsRecommend = Convert.ToInt32(reader["isRecommend"]);
                    news.IsCheck = Convert.ToInt32(reader["isCheck"]);
                    news.ClickNumber = Convert.ToInt32(reader["clickNumber"]);
                    news.PageFrom = reader["pageFrom"].ToString();
                    news.Keywords = reader["keywords"].ToString();
                    news.Author = reader["author"].ToString();
                    news.CreateDate = DateTime.Parse(reader["createDate"].ToString());
                    news.Image = reader["image"].ToString();
                    news.IsTop = Convert.ToInt32(reader["isTop"]);
                    news.Address = reader["address"].ToString();
                    news.CategoryID = Convert.ToInt32(reader["categoryId"]);
                }
            }
            return news;
        }

    }

 

web.config的配置如下：

在<configsections>节点下增加节点

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>

 

然后在<connectionstring>上面添加如下：

<dataConfiguration defaultDatabase="ConnectionString"/>
 <connectionStrings>
  <add name="ConnectionString" connectionString="Data Source=orcl;User ID=newdongguang2;Password=*****;Pooling=true;MAX Pool Size=512;Min Pool Size=5;Connection Lifetime=30;" providerName="System.Data.OracleClient"/>
 </connectionStrings>

当然你要去下载一个企业库，我的企业库为2.0,vs2008

 

 

最近对这段程序做了测试，发现clob数据在大于32767时出错，在网上找了许久，原来是oracle缓冲只有32767字节,无奈之下，只有改成传统的方法。如下：

            OracleDatabase database = (OracleDatabase)DatabaseFactory.CreateDatabase();
            OracleConnection connect = (OracleConnection)database.CreateConnection();
           
            connect.Open();
            OracleTransaction tran=null;
            try
            {
                //事务是必须的
                tran = connect.BeginTransaction();
                OracleCommand command = connect.CreateCommand();
                command.Transaction = tran;
                command.Parameters.Add(new OracleParameter("inTitle", news.Title));
                command.Parameters.Add(new OracleParameter("inOrderBy", news.OrderBy));
                command.Parameters.Add(new OracleParameter("inIsShow", news.IsShow));
                command.Parameters.Add(new OracleParameter("inIsRecommend", news.IsRecommend));
                command.Parameters.Add(new OracleParameter("inIsCheck", news.IsCheck));
                command.Parameters.Add(new OracleParameter("inClickNumber", news.ClickNumber));
                command.Parameters.Add(new OracleParameter("inPageFrom", news.PageFrom));
                command.Parameters.Add(new OracleParameter("inKeywords", news.Keywords));
                command.Parameters.Add(new OracleParameter("inAuthor", news.Author));
                command.Parameters.Add(new OracleParameter("inImage", news.Image));
                command.Parameters.Add(new OracleParameter("inIsTop", news.IsTop));
                command.Parameters.Add(new OracleParameter("inAddress", news.Address));
                command.Parameters.Add(new OracleParameter("inCategoryID", news.CategoryID));
                command.Parameters.Add(new OracleParameter("outNewsID", OracleType.Int32)).Direction = ParameterDirection.Output;
                command.Parameters.Add(new OracleParameter("outState", news.Title)).Direction = ParameterDirection.Output;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "NewsPackage.NewsAdd";
                command.ExecuteNonQuery();

                //返回参数

                state = Convert.ToInt32(command.Parameters["outState"].Value.ToString());
                newsId = Convert.ToInt32(command.Parameters["outNewsId"].Value.ToString());

                //清除原来的参数
                command.Parameters.Clear();
                command.CommandText = "select Content from News where NewsId=" + newsId + " for update";
                command.CommandType = CommandType.Text;
               
                using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        OracleLob clob = reader.GetOracleLob(0);//读入二进制对性
                        clob.Erase();//清空其中的数据
                        clob.Position = 0;
                        clob.BeginBatch(OracleLobOpenMode.ReadWrite);//开始写入
                        int buffersize = 100;
                        int retval = 0;
                        byte[] bts = new byte[buffersize];

                        //将字符串序列化为二进制流
                        MemoryStream stream = new MemoryStream();
                        BinaryFormatter formatter = new BinaryFormatter();
                        formatter.Serialize(stream, news.Content);

                        //将二进制流写入Clob字符中
                        stream.Seek(0, SeekOrigin.Begin);
                        retval = stream.Read(bts, 0, buffersize);
                        while (retval == buffersize)
                        {
                            clob.Write(bts, 0, buffersize);
                            retval = stream.Read(bts, 0, buffersize);
                        }
                        clob.Write(bts, 0, 100);
                        clob.EndBatch();//结束写入
                        clob.Flush();//刷新
                        clob.Close();//关闭
                       
                    }
                }
                tran.Commit();
               
            }
            catch
            {

                if (tran != null)
                    tran.Rollback();

            }
            finally
            {
                connect.Close();
            }

得到clob有以下方法：

            News news = new News();
            OracleDatabase database = (OracleDatabase)DatabaseFactory.CreateDatabase();

            using (OracleConnection conn = (OracleConnection)database.CreateConnection())
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "NewsPackage.NewsGetEntity";
                cmd.Parameters.Add(new OracleParameter("inNewsID",newsId));
                cmd.Parameters.Add(new OracleParameter("cur_out",OracleType.Cursor)).Direction = ParameterDirection.Output;
                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        news.NewsID = Convert.ToInt32(reader["newsId"]);
                        news.Title = reader["title"].ToString();

                        //得到clob
                        OracleLob clob = reader.GetOracleLob(reader.GetOrdinal("content"));
                        clob.Position = 0;
                        byte[] tt = new byte[clob.Length];
                        clob.Read(tt, 0, (int)clob.Length);
                        MemoryStream ms = new MemoryStream(tt);
                        BinaryFormatter bb = new BinaryFormatter();
                        object oo = bb.Deserialize(ms);//反序列化取出字符数据
                        news.Content = oo.ToString();

 

                        //news.Content = reader["content"].ToString();
                        news.OrderBy = Convert.ToInt32(reader["orderBy"]);
                        news.IsShow = Convert.ToInt32(reader["isShow"]);
                        news.IsRecommend = Convert.ToInt32(reader["isRecommend"]);
                        news.IsCheck = Convert.ToInt32(reader["isCheck"]);
                        news.ClickNumber = Convert.ToInt32(reader["clickNumber"]);
                        news.PageFrom = reader["pageFrom"].ToString();
                        news.Keywords = reader["keywords"].ToString();
                        news.Author = reader["author"].ToString();
                        news.CreateDate = DateTime.Parse(reader["createDate"].ToString());
                        news.Image = reader["image"].ToString();
                        news.IsTop = Convert.ToInt32(reader["isTop"]);
                        news.Address = reader["address"].ToString();
                        news.CategoryID = Convert.ToInt32(reader["categoryId"]);
                    }
                }
            }
            return news;

 

 

操作clob方法参照了http://blog.csdn.net/sky_dj/archive/2009/06/02/4235041.aspx