大概看了一下别人写的架构分析。 还是园子里写的不错。可惜看的不完全明白。还是要自己消化消化。既然分三层,看得时候总要一步一步,俺先从最原始一层DAL看起。
有两套Factory(DALFactory&ProfileDALFactory)
我的理解:DALFactory实现了IDAL,创建SQLSERVERDAL、ORACLEDAL,访问数据库还是离不开SELECT。。。那就看看它是怎么包装SELECT的
实现这个第一层包装 PepShop在DBUtilities里用类--- 数据访问组件基础类 DBUtility(SQLHelper/OracalHelper) 封装了数据库的基本操作。方法都是静态的。
思想来源于:Microsoft Data Access Application Block for .NET http://msdn.microsoft.com/en-us/library/cc309504.aspx
MS-DAAB
是Enterprise Library里面的一个Block,对数据库的一些操作进行了包装.
使数据库编程更加容易,一句话就可以实现打开数据库、查询、返回DataSet....... 实现分层
Issues:
我机子上装了SQL Server 2005 + VSTS2008,所以安装、连接数据库的时候有了点小麻烦:
SetUpQuickStartsDB.bat里面有这样一行:
OSQL -S (local)\SQLEXPRESS -E -i DataAccessQuickStarts.sql //就是在SQLEXPRESS实例下创建数据库,OSQL是命令行模式的SSMSEE
DataAccessQuickStarts.sql里面有这样一行:
CREATE DATABASE EntLibQuickStarts COLLATE SQL_Latin1_General_CP1_CI_AS //创建数据库 EntLibQuickStarts , collate是制定排序规则
运行这个BAT文件(已改为UNICODE)出了错误 Syntax error, 后来我在QUERY里执行,发现后面的Insert命令有一些不成功 删掉一些行 OK
发现数据库被创建在:C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data 下面,
数据库没有在:C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
可能是因为我装了SQL SERVER 2005的缘故。
后来的连接出了问题。
App.Config里ConnectionStrings是这样的:
<add name="QuickStarts Instance" connectionString=
"Database=EntLibQuickStarts;Server=(local)\SQLEXPRESS;Integrated Security=SSPI"
providerName="System.Data.SqlClient" />
如果是SQL Server connectionString是这样的:
Data Source=DYLANBOX;Initial Catalog=EntLibQuickStarts;Integrated Security=True
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<dataConfiguration defaultDatabase="QuickStarts Instance" />
<connectionStrings>
<add name="QuickStarts Instance" connectionString="Data Source=DYLANBOX;Initial Catalog=EntLibQuickStarts;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
运行:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
Database db = DatabaseFactory.CreateDatabase("QuickStarts Instance");
int count = (int)db.ExecuteScalar(
CommandType.Text,
"SELECT COUNT(*) FROM Customers");
count为什么是0? a a a
最后发现,我执行了BAT文件确实在.\SQLEXPRESS下创建了数据库EntLibQuickStarts; 因为Syntax error 所以各个表中没有数据。
然后又用Query在SQL Server 2005下(数据库是DYLANBOX)创建了EntLibQuickStarts,表中有数据。
所以用 connectionString: Data Source=DYLANBOX;Initial Catalog=EntLibQuickStarts;Integrated Security=True 才正确
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
Database db = DatabaseFactory.CreateDatabase();
DataSet ds = db.ExecuteDataSet(
CommandType.Text,
"SELECT * From Customers");
dataGrid1.DataSource = ds.Tables[0];
注意:DatabaseFactory.CreateDatabase()只是定义一个Dabase reference
db.ExecuteScalar / db.ExecuteDataSet 等 responsible for opening and closing the connection to the real database defined in the configuration file
db.ExecuteReader("") for procedure
using (IDataReader dataReader = _db.ExecuteReader("GetCategories"))
{
// Processing code
while (dataReader.Read())
{
Category item = new Category(
dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2));
this.cmbCategory.Items.Add(item);
}
}
// Procedure in Categories.sql
// OSQL -S (local)\SQLEXPRESS -E -i Categories.sql
CREATE PROCEDURE dbo.GetCategories
AS
/* SET NOCOUNT ON */
SELECT CategoryID, CategoryName, Description
FROM Categories
RETURN
GO
不用存储过程怎么写?
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
DataSet ds = _db.ExecuteDataSet(CommandType.Text, "Select * from categories");
foreach (DataRow dr in ds.Tables[0].Rows)
{
Category item = new Category(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString());
this.cmbCategory.Items.Add(item);
}
用存储过程 Retrieve products
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
_db.LoadDataSet(
"GetProductsByCategory",
this.dsProducts,
new string[] { "Products" },
selectedCategory.CategoryId);
--/
CREATE Procedure GetProductsByCategory
(
@CategoryID int
)
AS
SELECT
ProductID, ProductName, CategoryID, UnitPrice, LastUpdate
FROM
Products
WHERE
CategoryID = @CategoryID
RETURN 0
GO
//不用存储过程
String sel = string.Format("SELECT Products.* FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Categories.CategoryID = {0}", selectedCategory.CategoryId);
dsProducts.Load(
(_db.ExecuteDataSet(CommandType.Text,sel).Tables[0].CreateDataReader()),
LoadOption.PreserveChanges,
dsProducts.Tables[0]);
题外话: cast untype dataset to type dataset
_db.ExecuteDataSet返回的是一个UnTyped dataSet,转为Typed
// load the generic data from the data layer into a strongly typed dataset
dsTyped.Load(
dsUntyped.Tables[0].CreateDataReader(),
LoadOption.OverwriteChanges,
dsTyped.mytablename);