Enterprise Library DataAccess(数据层)笔记

1、前行引入命名空间：

using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.ServiceLocation;
using System.Data.Common;
using System.Data.SqlClient;

2、创建database实例，这里有两种方式

     第一种：Database db = DatabaseFactory.CreateDatabase("QuickStarts Instance");

     这里的QuickStarts Instance是配置文件中的 connectionStrings  -> add name。配置文件如下：

     <configSections>

           <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" requirePermission="true" />

      </configSections>

<dataConfiguration defaultDatabase="Connection String" />
  <connectionStrings>
    <add name="QuickStarts Instance" connectionString="Database=EntLibQuickStarts;Server=.;uid=sa;pwd=11"
        providerName="System.Data.SqlClient" />
  </connectionStrings>

      第二种方式： Database DB = EnterpriseLibraryContainer.Current.GetInstance<Database>("QuickStarts Instance");

      但这种方式一定要加一个命名空间的引入：using Microsoft.Practices.ServiceLocation;

3、数据操作之IDataReader

    

     using (IDataReader reader = DB.ExecuteReader(CommandType.Text, "select * from Categories;"))
                {
                    while (reader.Read())
                    {
                        ListItem item = new ListItem();
                        item.Value = reader["CategoryID"].ToString();
                        item.Text = reader["CategoryName"].ToString();
                        DropDownList1.Items.Add(item);
                    }
                }

      以上是SQL语句的形式，如果是存储过程的话，代码如下：

      DbCommand  procedureCmd=DB.GetStoredProcCommand("GetProductsByCategory");
                DB.AddInParameter(procedureCmd,"CategoryID",DbType.Int32,2);
                using (IDataReader readerProcedure = DB.ExecuteReader(procedureCmd))
                {
                    while (readerProcedure.Read())
                    {
                        ListItem item = new ListItem();
                        item.Value = readerProcedure["ProductID"].ToString();
                        item.Text = readerProcedure["ProductName"].ToString();
                        DropDownList2.Items.Add(item);
                    }
                }

4、数据操作之DataSet

    sql语句形式：

     DataSet ds = db.ExecuteDataSet(CommandType.Text,"select * from Customers");

    存储过程形式：

     DbCommand proCmd = DB.GetStoredProcCommand("GetProductsByCategory");
                DB.AddInParameter(proCmd,"CategoryID",DbType.Int32,2);
                DataTable proDs = DB.ExecuteDataSet(proCmd).Tables[0];

     给DataSet中DataTable命名：

     string sqlString = "select * from Products;select * from Categories;";
     DbCommand cmd = DB.GetSqlStringCommand(sqlString);

     DataSet loadDs = new DataSet("ProductsTable");
            DB.LoadDataSet(CommandType.Text,sqlString,loadDs,new string[]{"Products","Categorys"});//this is a very important point
            DataTable dt = loadDs.Tables["Products"];

     修改DataTable中的行：

       1、删除行：dt.Rows[dt.Rows.Count-1].Delete();

       2、添加行：

          object[] rowData = new object[] { 83, "xiaochunProduct", 2, 18.00, "2012/11/26 10:13:40" };
            dt.Rows.Add(rowData);

       3、编辑行：

           object[] rowData2 = dt.Rows[80].ItemArray;
            rowData2[1] = "qqqqq";
            rowData2[4] = System.DateTime.Now.ToShortDateString();
            dt.Rows[80].ItemArray = rowData2;

5、获取返回结果中第一行第一列的值

           int count = (int)db.ExecuteScalar(CommandType.Text,"select count(*) from Customers");

6、执行SQL语句，返回受影响的行数

          int count2 = db.ExecuteNonQuery(CommandType.Text, "insert into ……");