[c#] How to use Data Access Application Block?

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 + "}";
            }
        }
    }
}