[c#] How to use Data Access Application Block?
来源：互联网 发布：python零基础书籍推荐 编辑：程序博客网 时间：2024/05/31 19:54
How to use Data Access Application Block用于进行db操作。
ref links:
最好的guide
http://msdn.microsoft.com/en-us/library/ff953181(v=pandp.50).aspx
其中关于database connection manager的部分要留意：
Managing Connections
For many years, developers have fretted about the ideal way to manage connections in data access code. Connections are scarce, expensive in terms of resource usage, and can cause a big performance hit if not managed correctly. You must obviously open a connection before you can access data, and you should make sure it is closed after you have finished with it. However, if the operating system does actually create a new connection, and then closes and destroys it every time, execution in your applications would flow like molasses.
Instead, ADO.NET holds a pool of open connections that it hands out to applications that require them. Data access code must still go through the motions of calling the methods to create, open, and close connections, but ADO.NET automatically retrieves connections from the connection pool when possible, and decides when and whether to actually close the underlying connection and dispose it. The main issues arise when you have to decide when and how your code should call the Close method. The Data Access block helps to resolve these issues by automatically managing connections as far as is reasonably possible.
When you use the Data Access block to retrieve a DataSet, the ExecuteDataSet method automatically opens and closes the connection to the database. If an error occurs, it will ensure that the connection is closed. If you want to keep a connection open, perhaps to perform multiple operations over that connection, you can access the ActiveConnection property of your DbCommand object and open it before calling the ExecuteDataSet method. TheExecuteDataSet method will leave the connection open when it completes, so you must ensure that your code closes it afterwards.
In contrast, when you retrieve a DataReader or an XmlReader, the ExecuteReader method (or, in the case of the XmlReader, the ExecuteXmlReadermethod) must leave the connection open so that you can read the data. The ExecuteReader method sets the CommandBehavior property of the reader toCloseConnection so that the connection is closed when you dispose the reader.Commonly, you will use a using construct to ensure that the reader is disposed, as shown here:
using (IDataReader reader = db.ExecuteReader(cmd)){ // use the reader here}其他：
http://huan-lin.blogspot.hk/2012/09/data-access-application-block.html
http://msdn.microsoft.com/en-us/library/ff664719(v=pandp.50).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2
1. download "Microsoft Enterprise Library 5.0" msi file (not source code msi) from http://www.microsoft.com/en-us/download/details.aspx?id=15104
2. run the msi file to install "Microsoft Enterprise Library 5.0"
3. in your web app project, add following references:
* Microsoft.Practices.EnterpriseLibrary.Common (enterprise library shared library)
* Microsoft.Practices.EnterpriseLibrary.Data (enterprise library Data Access Application Block)
* Microsoft.Practices.ServiceLocation (Microsoft.Practices.ServiceLocation)
* System.Transactions
4. refer to http://msdn.microsoft.com/en-us/library/ff664387(v=pandp.50).aspx
right click web.config file, select "Edit Enterprise Library V5 Configuration" to add database connection setting.
sql server connection settting example:
name: "VISITOR_LOG_DB"
connectionString: "Data Source=.\SQLEXPRESS;Initial Catalog=VISITOR_LOG_DB;User Id=sa;PassWord=xxxx;"
providerName: "System.Data.SqlClient"
(optional) set default db
and finally close the configuration tool, and select "Yes" to save it to web.config file
in fact, it will generate following codes in web.config
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
</configSections>
<dataConfiguration defaultDatabase="VISITOR_LOG_DB" />
<connectionStrings>
<add name="VISITOR_LOG_DB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=VISITOR_LOG_DB;User Id=sa;PassWord=!234Qwer;"
providerName="System.Data.SqlClient" />
</connectionStrings>
5. Example Codes
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.Common;using System.Transactions;using Microsoft.Practices.EnterpriseLibrary.Data;using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;namespace DAAB_Demo{ public partial class _Default : System.Web.UI.Page { protected string data; protected void Page_Load(object sender, EventArgs e) { //create database connection Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("VISITOR_LOG_DB"); //query example //string value="2' or '1'='1"; //test sql injection string value = "1"; string strSql = "select * from VISITOR_LOG where VISITOR_NAME=@VISITOR_NAME"; DbCommand cmd = db.GetSqlStringCommand(strSql); db.AddInParameter(cmd, "VISITOR_NAME", DbType.String, value); using (IDataReader rdr = db.ExecuteReader(cmd)) { while (rdr.Read()) { data = rdr["VISITOR_COMPANY"].ToString() + "," + rdr["SIGNIN_TIME"].ToString(); } } //Example: get first record and first column value cmd = db.GetSqlStringCommand("select count(*) from VISITOR_LOG where ID=@ID"); db.AddInParameter(cmd, "ID", DbType.Int32, 1); int count=(int)db.ExecuteScalar(cmd); data = data + ", count=" + count; //update|insert|delete example cmd = db.GetSqlStringCommand("update VISITOR_LOG set SIGNIN_TIME=@SIGNIN_TIME where ID=@ID"); db.AddInParameter(cmd, "SIGNIN_TIME", DbType.DateTime, DateTime.Now); db.AddInParameter(cmd, "ID", DbType.Int32, 1); db.ExecuteNonQuery(cmd); //transaction example try { using (TransactionScope scope = new TransactionScope()) { DbCommand cmd1 = db.GetSqlStringCommand("update VISITOR_LOG set VISITOR_COMPANY='AAA' where ID=1"); DbCommand cmd2 = db.GetSqlStringCommand("update VISITOR_LOG set VISITOR_COMPANY='ccc' where ID=1"); int affectedRows = db.ExecuteNonQuery(cmd1); affectedRows += db.ExecuteNonQuery(cmd2); if (affectedRows > 0) { throw new TransactionAbortedException("give up transaction"); } scope.Complete(); } } catch (TransactionAbortedException ex) { data = "transaction fail: {" + ex.Message + "}"; } catch (ApplicationException ex) { data = "app error: {" + ex.Message + "}"; } } }}
- [c#] How to use Data Access Application Block?
- Data Access Application Block
- Data Access Application Block
- Data Access Application Block
- Data Access Application Block
- Data Access Application Block
- Data Access Application Block
- Data Access Application Block 概述
- Data Access Application Block 概述
- Data Access Application Block 概述
- Data Access Application Block 概述
- Data Access Application Block 概述
- iOS: How to use "Block"
- How To use Data Referenece ...
- Data Access Application Block for .NET
- Data Access Application Block for .NET
- Data Access Application Block for .NET
- Data Access Application Block for .NET
- django 配置404，500页面
- 使用Java混淆工具yguard
- 查找进程并kill
- Zend Studio 运行错误解决方法
- Dtree+jQuery生成动态目录树
- [c#] How to use Data Access Application Block?
- webapp上的蒙版，包含使用touchstart,touchmove
- Android 仿Windows Metro 界面UI
- x264编码格式
- jquery tabs杂记
- 面试题
- ARM汇编指令
- 设置&获取ActionBar的高度
- 你可能不知道的Shell
