【ASP.NET step by step】之一 Create a DAL

[原文] http://msdn.microsoft.com/en-us/library/aa581776.aspx#aspnet_tutorial01_dataaccesslayer_cs_topic3

Let's fire up Visual Studio and get started!

前言

我们为什么需要DAL？
因为没有DAL，我们常常将data-specific logic混入PL，造成紧耦合！
This may take the form of writing ADO.NET code in the ASP.NET page's code portion or using the SqlDataSource control from the markup portion.

        private void Form1_Load(object sender, EventArgs e)
        {

            dataGridView1.DataSource = bindingSource1;
            GetData("Select * from Products");
            
        }

        private void GetData(string selectCommand)
        {

            try
            {
                string CONNECTSTRING = @"Data Source=DylanBox;Initial Catalog=Northwind;Integrated Security=SSPI";
                DataTable ds = new DataTable();
                SqlConnection connection = new SqlConnection(CONNECTSTRING);
                SqlCommand command = new SqlCommand(selectCommand, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command); ;

               
                adapter.Fill(ds);
                bindingSource1.DataSource = ds;

                dataGridView1.AutoResizeColumns(
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
                

                connection.Close();

            }
            catch (SqlException)
            {

                throw;
            }
            finally
            {

            }          

        }

 

Step 1. Create a File System-based Web site
Northwind放在App_data目录下，Server Explore会看到这个数据库。

Step 2: Creating the Data Access Layer

 

我搞了个简单的山寨版DAL, 希望对于你理解能有所帮助：

namespace DALClass
{
    public class DataTableAdapter
    {
        private static DataTable dt = new DataTable();

        public DataTable GetProducts()
        {
            GetData("Select * from Products");
            return dt;            
        }


        private void GetData(string selectCommand)
        {

            try
            {
                string CONNECTSTRING = @"Data Source=DylanTest;Initial Catalog=Northwind;Integrated Security=SSPI";
               
                SqlConnection connection = new SqlConnection(CONNECTSTRING);
                SqlCommand command = new SqlCommand(selectCommand, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command); ;


                adapter.Fill(dt);     
                connection.Close();

            }
            catch (SqlException)
            {

                throw;
            }
        }
    }
}

 

客户端引入DALClass，代码：

//
using DALClass;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTableAdapter adapter = new DataTableAdapter();
        GridView1.DataSource = adapter.GetProducts();
        GridView1.DataBind();
    }
}

预先了解一下使用.NET创建DataSet、以此为基础创建BLL，这段代码是什么样的：

.NET创建DataSet
ProductsTableAdapter productsAdapter = new ProductsTableAdapter();
GridView1.DataSource = productsAdapter.GetProducts();

BLL
ProductsBLL productLogic = new ProductsBLL();
GridView1.DataSource = productLogic.GetProducts();
GridView1.DataBind();

好像区别不是很大？呵呵，看完后面几章其义自现...

可以看出DAL就是一个包含了“Datatable缓存”和“TableAdapter”的集合体，对PL层和底层Database解耦。

 

和数据库有关的code，such as creating a connection to the database, issuing SELECT, INSERT, UPDATE, and DELETE commands, and so on 应该在DAL层.
DAL层包含访问DB的方法，这个例子我们创建这些方法：

• GetCategories(), which will return information about all of the categories
• GetProducts(), which will return information about all of the products
• GetProductsByCategoryID(categoryID), which will return all products that belong to a specified category
• GetProductByProductID(productID), which will return information about a particular product

PL层调用DAL来request data

什么是Strongly-typed?
A strongly-typed object is one whose schema is rigidly defined at compile time, whereas the opposite, a loosely-typed object, is one whose schema is not known until runtime.
反映在数据库操作，是这样的：
   SQL query返回的DataSet和DataReader默认都是Loosely-typed object，他们的列名是在执行query才知道的，
        所以访问方法是 DataTable.Rows[index]["columnName"]
     而对于Strongly-typed DataTable，每个列实现为Property，访问方法是 DataTable.Rows[index].columnName

如何返回Strongly-typed object？
  方法一、custom businese object类，将数据库表的每一列实现为custom business object类的property
  方法二、Typed DataSets (其实最后实现的还是类，每一列实现为property,和方法一是一样的)
         包含Strongly-typed DataTable和TableAdapter，
         TableAdapter包含了一些方法用于populate the dataset‘s datable，负责Datatable和DataBase的交互

For more information on the advantages and disadvantages of using Typed DataSets versus custom business objects, refer to
Designing Data Tier Components and Passing Data Through Tiers  （这篇文章 谁看懂了教教俺 谢谢！）

注意：
用设计器创建的Typed dataset和Adapter的名空间是： 你添加的DataSet ---Northwind.xsd 名称 + TableAdapters, 就是 NorthwindTableAdapters

Step 3: Adding Parameterized Methods to the Data Access Layer

Step 4: Inserting, Updating, and Deleting Data

两种模式：
   Database direct pattern
   batch update pattern

Each Insert, Update, and Delete Request Is Sent to the Database Immediately  
 
All Changes are Synchronized with the Database When the Update Method is Invoked
batch pattern原理：
a developer deletes, inserts, and modifies the DataRows in a DataTable and then passes those DataRows or DataTable into an update method. This method then enumerates the DataRows passed in, determines whether or not they've been modified, added, or deleted (via the DataRow's RowState property value), and issues the appropriate database request for each record.

TableAdapter uses the batch update pattern by default, but also supports the DB direct pattern.

Both data modification patterns use the TableAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties to issue their INSERT, UPDATE, and DELETE commands to the database.
例子：用Batch模式，对符合条件的product的UnitPrice加倍
代码：
NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new
NorthwindTableAdapters.ProductsTableAdapter();

// For each product, double its price if it is not discontinued and
// there are 25 items in stock or less
Northwind.ProductsDataTable products = productsAdapter.GetProducts();
foreach (Northwind.ProductsRow product in products)
   if (!product.Discontinued && product.UnitsInStock <= 25)
      product.UnitPrice *= 2;

// Update the products
productsAdapter.Update(products);
Creating Custom Insert, Update, and Delete Methods
因为前面的方法创建的Insert等命令，有时可能过于笨重，如何创建自定义的命令。
例如对Insert命令，在SQL最后添加 SELECT SCOPE_IDENTITY() 可以让命令返回新创建记录的ProductID，注意要将Insert命令的执行方式改为Scalar，因为默认NoQuery返回的是受影响的行数。
// Add a new product
int new_productID = Convert.ToInt32(productsAdapter.InsertProduct("New Product", 1, 1, "12 tins per carton", 14.95m, 10, 0, 10, false));
// On second thought, delete the product
productsAdapter.Delete(new_productID);

Step 5: Completing the Data Access Layer
有个问题需要注意，如果要执行多个表的联合查询，要用subqueries 而不要用JOIN
是因为用了JOIN以后，设计器不会自动创建Insert、Update和Delete了，因为它们依赖GetProducts()方法，添加了JOIN, 就不会自动创建了。
Fortunately, the auto-generated methods for inserting, updating, and deleting are not affected by subqueries in the SELECT clause.
因此我们用subqueries，当然如果你不用Update任何数据，只是View数据，用JOIN当然是可以的。

If you update the GetProducts() SELECT using JOIN syntax the DataSet Designer won't be able to auto-generate the methods for inserting, updating, and deleting database data using the DB direct pattern. Instead, you'll have to manually create them much like we did with the InsertProduct method earlier in this tutorial. Furthermore, you'll manually have to provide the InsertCommand, UpdateCommand, and DeleteCommand property values if you want to use the batch updating pattern.

Adding the Remaining TableAdapters和上面的方法一样 添加其他表的Adapter, 注意：添加完需要compile才产生代码(This schema information is translated into C# or Visual Basic code at design time when compiled or at runtime (if needed)

Adding Custom Code to the DAL利用Partial class机制来extend auto-generated code
目的：add a GetProducts() method to the SuppliersRow class. The SuppliersRow class represents a single record in the Suppliers table; each supplier can provider zero to many products, so GetProducts() will return those products of the specified supplier.
实现：create a new class file in the App_Code folder named SuppliersRow.cs and add the following code:

using System;
using System.Data;
using NorthwindTableAdapters;
public partial class Northwind
{
public partial class SuppliersRow
{
public Northwind.ProductsDataTable GetProducts()
{
ProductsTableAdapter productsAdapter = new ProductsTableAdapter();
return productsAdapter.GetProductsBySupplierID(this.SupplierID);
}
}
}


在SupplierAndProducts页面，通过desinger编辑Columns得到，注意是如何调用这个GetProducts方法的：

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="DataWebControlStyle">
                <HeaderStyle CssClass="HeaderStyle" />
                <AlternatingRowStyle CssClass="AlternatingRowStyle" />
                <Columns>
                    <asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
                    <asp:TemplateField HeaderText="Products">
                        <ItemTemplate>

                            <asp:BulletedList ID="BulletedList1" runat="server" DataSource='<%# ((Northwind.SuppliersRow)((System.Data.DataRowView) Container.DataItem).Row).GetProducts() %>'

                                    DataTextField="ProductName">
                            </asp:BulletedList>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

注意绑定的语法：

GridView1绑定了Suppliers表，而Gridview1限制了只有两列，

第一列是一个BoundField绑定到CompanyName
第二列是一个TemplatField内含BulletedList控件，控件的DataSource：

((Northwind.SuppliersRow)((System.Data.DataRowView) Container.DataItem).Row).GetProducts()

其实是调用SuppliersRow的GetProducts()方法，根据Suppliers表的当前行的SupplierID返回Products







SuppliersAndProducts.aspx.cs

public partial class SuppliersAndProducts : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SuppliersTableAdapter suppliersAdapter = new SuppliersTableAdapter();
        GridView1.DataSource = suppliersAdapter.GetSuppliers();
        GridView1.DataBind();
    }
}

最后看起来是这样的：