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 ……");
转自：http://www.cnblogs.com/msql/archive/2012/12/05/2803521.html
