資料來源控制項(1)

透過 ADO.NET 我們可以利用程式碼建立資料庫連線以存取資料，這些程式碼通常會被建立在抽象的 DAL 或 BLL 層。 若須求只有簡單的建立連線與存取資料，我們也可以透過資料來源控制項來實現，而不用使用 ADO.NET 。 它可以存取的對象包括 objects, XML, and databases 。

Understanding the Data Source Controls

資料來源控制項可以用來管理資料的新增、修改、刪除、查詢作業。 它可以和資料繫結控制項配合使用，資料繫結控制項提供使用者介面以便觸發事件進而存取資料。 與 ADO.NET 相比，這將會簡少非常多程式碼的撰寫。

資料來源控制項的階層關係圖

SqlDataSource

簡單範例演練

The SqlDataSource control is used to configure access to relational databases such as SQL Server and Oracle. It can also be configured to work with Open Database Connectivity (ODBC) and Object Linking and Embedding (OLE) Db data connections.

After You configure the control to connect to one of these database types. The code inside the control will then use the appropriate provider based on your configuration settings.

• SelectCommand ：Gets or sets the SQL string to retrieve data
• InsertCommand ：Gets or sets the SQL string to insert data
• UpdateCommand ：Gets or sets the SQL string to update data
• DeleteCommand ：Gets or sets the SQL string to delete data
• SelectCommandType ：指出 SelectCommand 屬性中的文字類型 { Text | StoredProcedure }
• DataSourceMode ：設定 SqlDataSource 控制項擷取資料的模式。 { DataSet | DataReader }
  DataSet：資料以 DataSet 物件取回，取回後連線就會中斷。這個模式才支援排序、篩選和分頁功能。
  DataReader：資料以 IDataReader 物件取回，它只是一個順向唯讀的游標，必須與資料庫保持連線以便讀取每一個列的資料。
  

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand = "SELECT * FROM Customers" 
    DeleteCommand="DELETE FROM Customers WHERE CustomerID = @CustomerID" 
    InsertCommand="INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle)" 
    UpdateCommand="UPDATE Customers SET CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, RevisedTime = @RevisedTime WHERE CustomerID = @CustomerID">
    <DeleteParameters>
        <asp:Parameter Name="CustomerID" Type="String" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="CustomerID" Type="String" />
        <asp:Parameter Name="CompanyName" Type="String" />
        <asp:Parameter Name="ContactName" Type="String" />
        <asp:Parameter Name="ContactTitle" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="CompanyName" Type="String" />
        <asp:Parameter Name="ContactName" Type="String" />
        <asp:Parameter Name="ContactTitle" Type="String" />
        <mcc:MyDayParameter Name="RevisedTime" Type="DateTime" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView3" runat="server" DataSourceID="SqlDataSource1"
    AllowPaging="True" 
    AllowSorting="True" 
    PageSize="5" 
    AutoGenerateColumns="False" 
    AutoGenerateDeleteButton="True" 
    AutoGenerateEditButton="True" 
    DataKeyNames="CustomerID">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID" ReadOnly="True" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
        <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
        <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" />
    </Columns>
</asp:GridView>

You can also work with the data source controls from code.

protected void Page_Init(object sender, EventArgs e)
{
    SqlDataSource sqlDS = new SqlDataSource();
    sqlDS.ConnectionString =
        ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();
    sqlDS.ID = "SqlDataSource2";
    sqlDS.SelectCommandType = SqlDataSourceCommandType.Text;
    sqlDS.SelectCommand = "SELECT * FROM Customers";
    sqlDS.DataSourceMode = SqlDataSourceMode.DataSet;
    this.Controls.Add(sqlDS);
}

啟用資料來源控制項的快取功能

SqlDataSource sqlDS = new SqlDataSource();
sqlDS.DataSourceMode="DataSet"
sqlDS.EnableCaching="True"
sqlDS.CacheDuration="120" //sec

Using Parameters

The SqlDataSource control can also be configured to use parameters for Select, Insert, Update, Filter, and Delete commands. You do so by defining parameters inside your SQL statements using the @param syntax.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand = "SELECT * FROM Customers WHERE CustomerID=@CustomerID"
    SelectCommandType = "Text" 
    DataSourceMode = "DataSet">
    <SelectParameters>
        <asp:QueryStringParameter Name="CustomerID" QueryStringField="CID" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Filtering

Like the ObjectDataSource, you can also filter data inside a SqlDataSource control. Again, the data must be a DataSet because the filter is applied to the ADO.NET DataColumn or DataView.RowFilter property.

The following code shows an example of a SqlDataSource control that first selects all customers from the database. It then applies a FilterExpression to show only those products that have been discontinued.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand = "SELECT * FROM Customers"
    DataSourceMode = "DataSet"
    FilterExpression = "Country = '{0}'"
    >
    <FilterParameters>
        <asp:QueryStringParameter Name="Country" QueryStringField="Country" Type="String" />
    </FilterParameters>
</asp:SqlDataSource>

Caching SqlDataSource Data

Like an ObjectDataSource, you can also configure a SqlDataSource control to be cached by the server. When doing so, however, you must set the DataSourceMode property to DataSet. DataReader sources cannot be cached, as they would hold open a connection to the server.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand = "SELECT * FROM Customers"
    DataSourceMode = "DataSet" 
    EnableCaching="true" 
    CacheDuration="60">
</asp:SqlDataSource>

AccessDataSource

The AccessDataSource control is meant to connect to and work with Microsoft Access filebased databases (.mdb files). This control is very similar to the SqlDataSource control. In fact, it derives from the class SqlDataSource. Therefore, you can expect to work with the AccessDataSource control in a very similar manner.

這個 AccessDataSource 控制項是個給 Jet-based 資料庫使用的，如早期的 Access 資料庫 (.mdf 檔）。 從 Access 2010 開始，Access 資料庫的核心已經由 Jet 變更成 SQL Server。 新版的 Access 資料庫(副檔名是 .accdb)，必須使用 SqlDataSource 控制項建立連線，而不是使用 AccessDataSource 控制項。

<asp:AccessDataSource
    ID="AccessDataSource1" runat="server"
    DataFile="~/App_Data/AccessNorthwind.mdb"
    SelectCommand="SELECT * FROM [Products]">
</asp:AccessDataSource>

The code inside this data source control uses the ADO.NET System.Data.OleDb provider for connecting to an Access data file.