企业库数据库访问模块通过抽象工厂模式,允许用户通过简单的配置选择不同的数据库作为程序的数据源,大大解决了切换数据库时带来的麻烦.因为我本机只安装了SQL Server 2005,所以在此只做SQL的演示,需要深究的朋友可以访问以下网站:
http://msdn.microsoft.com/en-us/library/ff664408%28v=PandP.50%29.aspx
企业库数据库访问模块的几大功能:
1. 最简单的功能,通过ExecuteNonQuery.方法执行SQL语句.
2. 执行ExecuteDataSet,返回DataSet类型的数据集.
3. 执行ExecuteScalar,获取返回的第一行第一列的信息.
4. 执行存储过程.
5. 通过代码实现事务.
6. 通过DataSet更新数据库.
7. 返回值XML化.
8. 将返回的数据对象化.
9. 异步访问数据库.
以上的功能我会在下面一一介绍,测试程序我已打包,大家可以点击这里下载.
下面介绍如何使用Microsoft Enterprise Library 5.0中的数据库访问模块.
1. 首先创建一个测试数据库,创建数据库的SQL文件我打包在压缩包里了,大家可以点击上面的下载链接下载.执行完SQL文件后,可以看到我们创建好的TestDB数据库:
2. 下载安装好MicrosoftEnterprise Library 5.0,然后在运行EntLibConfig.exe,选择Blocks菜单 ,单击 AddDatabase Settings .
3. 配置好文件之后保存为App.config文件,并添加到创建好的应用程序中.并添加相应的引用,在此我不再多讲,大家下载我打包好的程序运行即可看到
4. 下面来介绍我在应用程序中实现的各个功能:
(1) 通过ExecuteNonQuery.方法执行SQL语句:
/// <summary>
/// 执行ExecuteNonQuery
/// </summary>
private void ExecuteNonQuery_Click(object sender, EventArgs e)
{
db.ExecuteNonQuery(CommandType.Text, "INSERT INTO [College] ([CollegeID],[Name]) values (6,'体育学院')");
}
(2) 执行ExecuteDataSet,返回DataSet类型的数据集.
/// <summary>
/// 执行ExecuteDataSet,返回College列表
/// </summary>
/// <returns></returns>
private void ExecuteDataSet_Click(object sender, EventArgs e)
{
string sql = "select * from College";
DbCommand dw = db.GetSqlStringCommand(sql);
dataGridView1.DataSource = db.ExecuteDataSet(dw).Tables[0];
}
(3) 执行ExecuteScalar,返回第一行第一列的值.
/// <summary>
/// 执行ExecuteScalar,返回第一行第一列的值
/// </summary>
/// <returns></returns>
private void ExecuteScalar_Click(object sender, EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase("ConnectionString");
string sql = "select [Name] from College where [CollegeID] = 1";
DbCommand dc = db.GetSqlStringCommand(sql);
string str = "获取的学院名称为:" + (string)db.ExecuteScalar(dc);
MessageBox.Show(str);
sql = "select [CollegeID] from College where [CollegeID] = 1";
dc = db.GetSqlStringCommand(sql);
str = "获取的学院ID为:" + (int)db.ExecuteScalar(dc);
MessageBox.Show(str);
}
(4) 执行存储过程.
/// <summary>
/// 执行存储过程
/// </summary>
private void StoredProcCommand_Click(object sender, EventArgs e)
{
DbCommand dc = db.GetStoredProcCommand("usp_College_LoadByID");
db.AddInParameter(dc, "@CollegeID", System.Data.DbType.Int32, 5);
dataGridView1.DataSource = db.ExecuteDataSet(dc).Tables[0];
}
(5) 通过代码实现事务.
/// <summary>
/// 事务
/// </summary>
private void Transaction_Click(object sender, EventArgs e)
{
DbCommand dc1 = db.GetStoredProcCommand("usp_College_Insert");
db.AddInParameter(dc1, "@CollegeID", DbType.Int32, 7);
db.AddInParameter(dc1, "@Name", DbType.String, "文旅学院");
DbCommand dc2 = db.GetStoredProcCommand("usp_College_Insert");
db.AddInParameter(dc2, "@CollegeID", DbType.Int32, 7);
db.AddInParameter(dc2, "@Name", DbType.String, "化工学院");
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
//添加一个ID为7的学院
db.ExecuteNonQuery(dc1, trans);
//添加一个ID为7的学院,主键重复,事务将回滚
db.ExecuteNonQuery(dc2, trans);
//提交事务.
trans.Commit();
}
catch
{
//回滚
trans.Rollback();
}
conn.Close();
}
//查看数据库,数据未被添加,说明事务已回滚
ExecuteDataSet_Click(null, null);
}
(6) 通过DataSet更新数据库.
/// <summary>
/// 通过DataSet更新数据库
/// </summary>
private void DataSetUpdate_Click(object sender, EventArgs e)
{
DataSet productsDataSet = new DataSet();
string sql = "Select * From College";
DbCommand cmd = db.GetSqlStringCommand(sql);
string CollegeTableName = "College";
//恢复原始数据
db.LoadDataSet(cmd, productsDataSet, CollegeTableName);
//获取数据表格
DataTable dTable = productsDataSet.Tables[CollegeTableName];
//添加一个新信息入DataSet中
DataRow addedRow = dTable.Rows.Add(new object[] { 8, "外国语学院" });
//修改一个原有数据
dTable.Rows[0]["Name"] = "国教院";
//提供插入,更新,删除存储过程
DbCommand insertCommand = db.GetStoredProcCommand("usp_College_Insert");
db.AddInParameter(insertCommand, "@CollegeID", DbType.Int32, "CollegeID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
DbCommand deleteCommand = db.GetStoredProcCommand("usp_College_Delete");
db.AddInParameter(deleteCommand, "@CollegeID", DbType.Int32, "CollegeID", DataRowVersion.Current);
DbCommand updateCommand = db.GetStoredProcCommand("usp_College_Update");
db.AddInParameter(updateCommand, "@CollegeID", DbType.Int32, "CollegeID", DataRowVersion.Current);
db.AddInParameter(updateCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
//通过DataSet更新数据库
int rowsAffected = db.UpdateDataSet(productsDataSet, CollegeTableName, insertCommand, updateCommand, deleteCommand,
Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);
MessageBox.Show("影响的行数:" + rowsAffected);
}
(7) 返回值XML化.
/// <summary>
/// 返回值XML化
/// </summary>
private void ReturnXML_Click(object sender, EventArgs e)
{
//使用"FOR XML AUTO"参数使得SQL返回XML格式的信息
SqlDatabase sqldb = (SqlDatabase)DatabaseFactory.CreateDatabase("ConnectionString");
DbCommand cmd = sqldb.GetSqlStringCommand("SELECT * FROM College FOR XML AUTO");
IEnumerable<string> productList;
using (var reader = sqldb.ExecuteXmlReader(cmd))
{
if (reader.IsStartElement())
{
var root = (XElement)XNode.ReadFrom(reader);
productList = root.Elements("CollegeID")
.Attributes("Name")
.Select(a => a.Value).ToArray();
MessageBox.Show(((XElement)root).ToString());
}
}
}
(8) 将返回的数据对象化.
/// <summary>
/// DataAsObject
/// </summary>
private void DataAsObject_Click(object sender, EventArgs e)
{
//将返回的数据对象化
var results = db.ExecuteSprocAccessor<College>("usp_College_LoadAll");
MessageBox.Show(results.ElementAt(0).ToString());
}
(9) 异步访问数据库.
/// <summary>
/// 异步访问数据库
/// </summary>
private void Async_Click(object sender, EventArgs e)
{
//创建新的数据库连接,属性必须添加:Asynchronous Processing=true
String connectionString = @"server=(local); database=TestDB; Integrated Security=true; Asynchronous Processing=true";
Database Sqldb = new SqlDatabase(connectionString);
DbCommand cmd = Sqldb.GetStoredProcCommand("usp_College_LoadbyID");
Sqldb.AddInParameter(cmd, "@CollegeID", DbType.Int32, 1);
try
{
IAsyncResult result = Sqldb.BeginExecuteReader(cmd, MyEndExecuteCallback, Sqldb);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//当获取完毕执行该函数
private void MyEndExecuteCallback(IAsyncResult result)
{
try
{
Database Sqldb = (Database)result.AsyncState;
IDataReader reader = db.EndExecuteReader(result);
College c = new College((int)reader[0], (string)reader[1]);
MessageBox.Show(c.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
出处:http://www.cnblogs.com/huangcong/archive/2010/05/31/1748672.html 请输
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
分享到:
相关推荐
Microsoft Enterprise Library 5.0.pdf
Enterprise Library --Data Access Application Block整理(入门,示例)
This release includes: Caching Block, Cryptography Block, Data Access Block, Exception Handling Block, Logging Block, Policy Injection Block, Security Block, Validation Block, and Unity; ☆ 文件清单...
闲来无事研究一下MVC4.0 和微软企业库5.0用他们做了个小程序 开发环境是vs2010 今天就把这个源码放出来 方便想学习的人做个入门学习。
☆ 资料如下:☆ ☆ 源代码 ☆ Enterprise Library 5.0.msi Enterprise Library 5.0 - Source Code....[Microsoft Press] Microsoft Enterprise Library 5.0 [Packt Publishing] Microsoft Enterprise Library 5.0
Developer's Guide to Microsoft Enterprise Library 5.0 c# Edition 懂的来拿吧
Microsoft Enterprise Library 5.0 Migration Guide
Enterprise Library 5.0 源码 已编译dll
Enterprise Library5.0 开发很快,我认为还是很强大的
Enterprise Library – Data Access Application Block 学习手册(最新版) - Part 1 Enterprise Library for .Net Framework 3.5 – EntLib v4.1 是 patterns & practices 小组为.NET Framework 3.5 开发一套企业库...
This release includes: Data Access Block, Exception Handling Block, Logging Block, Policy Injection Block, Semantic Logging Block, Transient Fault Handling Block, Validation Block, and Unity;...
1.enterprise library 5.0 加密模块中如何自定义加密类 2.由于enterprise library 5.0 加密模块中使用的对称加密密钥全部要求是受保护的密钥,有时我们也许需要使用这些加密算法,但对密钥的保护要求没那么高,没...
EnterpriseLibrary5.0使用手册(英文版)。
Microsoft.Practices.EnterpriseLibrary5.0几个重要的Dll,微软.NET企业级开发库
Enterprise Library 5.0 Developer's Guide.pdf 微软企业库5.0开发手册
解压后直接点击安装,需要用到Dll,直接引用对应的DLL到项目中
The Data Access Application Block The Exception Handling Application Block The Transient Fault Handling Application Block The Logging Application Block The Semantic Logging Application Block The ...
Microsoft Enterprise Library is a collection of reusable application blocks designed to assist software developers with common enterprise development challenges. This release includes: Caching Block,...
微软企业库 5.0 + 源代码 安装程序 你懂的^_^
微软的企业库,包含如下模块:Logging,Cache,Exception Handle等。详细信息请关注博客:http://blog.csdn.net/ghostbear