一直在做项目,今天有点空,把累积的东西记录
企业库的操作可以让我们省去写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