柚子@2010

注意：由于GridView的参数解析可能有问题，如果自定义函数的参数中有除了sortColumns的参数，即page的参数，必须在grid和objectdatasource都定义page为true
ASP.NET

ObjectDataSource 强类型源对象示例

阐释可以和 ObjectDataSource 控件一起使用的自定义中间层业务对象。

示例

下面的代码示例阐释可以和 ObjectDataSource 控件一起使用的自定义中间层业务对象，该控件使用 DataObjectTypeName 属性来指定一个强类型源对象。本主题还阐释了一个将业务对象用作 ObjectDataSource 控件的源的示例 ASP.NET 页。该页包含与 ObjectDataSource 控件绑定的 GridView 控件和 DetailsView 控件。

若要使用该代码，您可以在 Web 应用程序的 App_Code 子目录中创建一个代码文件，然后将代码复制到该文件。然后，业务对象将被动态编译并作为 Web 应用程序的一部分包含在应用程序中。或者，您可以编译业务对象，然后将它放入 ASP.NET 应用程序的 Bin 目录中或全局程序集缓存 (GAC) 中。有关 App_Code 和 Bin 目录的更多信息，请参见 ASP.NET 网站中的共享代码文件夹。

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Samples.AspNet.ObjectDataSource
{
  public class NorthwindEmployee
  {
    private int _employeeID;
    private string _lastName;
    private string _firstName;
    private string _address;
    private string _city;
    private string _region;
    private string _postalCode;

    public NorthwindEmployee()
    {
    }

    public int EmployeeID
    {
      get { return _employeeID; }
      set { _employeeID = value; }
    }

    public string LastName
    {
      get { return _lastName; }
      set { _lastName = value; }
    }

    public string FirstName
    {
      get { return _firstName; }
      set { _firstName = value; }
    }

    public string Address
    {
      get { return _address; }
      set { _address = value; }
    }

    public string City
    {
      get { return _city; }
      set { _city = value; }
    }

    public string Region
    {
      get { return _region; }
      set { _region = value; }
    }

    public string PostalCode
    {
      get { return _postalCode; }
      set { _postalCode = value; }
    }
  }

  //
  //  Northwind Employee Data Factory
  //

  public class NorthwindEmployeeData
  {

    private string _connectionString;

    public NorthwindEmployeeData()
    {
      Initialize();
    }

    public void Initialize()
    {
      // Initialize data source. Use "Northwind" connection string from configuration.

      if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
          ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "")
      {
        throw new Exception("A connection string named 'Northwind' with a valid connection string " +
                            "must exist in the <connectionStrings> configuration section for the application.");
      }

      _connectionString =
        ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
    }

    // Select all employees.

    public List<NorthwindEmployee> GetAllEmployees(string sortColumns, int startRecord, int maxRecords)
    {
      VerifySortColumns(sortColumns);

      string sqlCmd = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees ";

      if (sortColumns.Trim() == "")
        sqlCmd += "ORDER BY EmployeeID";
      else
        sqlCmd += "ORDER BY " + sortColumns;

      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  = new SqlCommand(sqlCmd, conn);

      SqlDataReader reader = null;
      List<NorthwindEmployee> employees = new List<NorthwindEmployee>();
      int count = 0;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
          if (count >= startRecord)
          {
            if (employees.Count < maxRecords)
              employees.Add(GetNorthwindEmployeeFromReader(reader));
            else
              cmd.Cancel();
          }          

          count++;
        }

      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        if (reader != null) { reader.Close(); }
        conn.Close();
      }

      return employees;
    }

    //////////
    // Verify that only valid columns are specified in the sort expression to avoid a SQL Injection attack.

    private void VerifySortColumns(string sortColumns)
    {
      if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
        sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);

      string[] columnNames = sortColumns.Split(',');

      foreach (string columnName in columnNames)
      {
        switch (columnName.Trim().ToLowerInvariant())
        {
          case "employeeid":
            break;
          case "lastname":
            break;
          case "firstname":
            break;
          case "":
            break;
          default:
            throw new ArgumentException("SortColumns contains an invalid column name.");
            break;
        }
      }
    }

    private NorthwindEmployee GetNorthwindEmployeeFromReader(SqlDataReader reader)
    {
      NorthwindEmployee employee = new NorthwindEmployee();

      employee.EmployeeID = reader.GetInt32(0);
      employee.LastName   = reader.GetString(1);
      employee.FirstName  = reader.GetString(2);

      if (reader.GetValue(3) != DBNull.Value)
        employee.Address = reader.GetString(3);

      if (reader.GetValue(4) != DBNull.Value)
        employee.City = reader.GetString(4);

      if (reader.GetValue(5) != DBNull.Value)
        employee.Region = reader.GetString(5);

      if (reader.GetValue(6) != DBNull.Value)
        employee.PostalCode = reader.GetString(6);

      return employee;
    }

    // Select an employee.

    public List<NorthwindEmployee> GetEmployee(int EmployeeID)
    {
      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  =
        new SqlCommand("SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode " +
                       "  FROM Employees WHERE EmployeeID = @EmployeeID", conn);
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;

      SqlDataReader reader = null;
      List<NorthwindEmployee> employees = new List<NorthwindEmployee>();

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        while (reader.Read())
          employees.Add(GetNorthwindEmployeeFromReader(reader));
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        if (reader != null) { reader.Close(); }

        conn.Close();
      }

      return employees;
    }

    //
    // Update the Employee by ID.
    //   This method assumes that ConflictDetection is set to OverwriteValues.

    public int UpdateEmployee(NorthwindEmployee employee)
    {
      if (String.IsNullOrEmpty(employee.FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(employee.LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

      if (employee.Address    == null) { employee.Address    = String.Empty; }
      if (employee.City       == null) { employee.City       = String.Empty; }
      if (employee.Region     == null) { employee.Region     = String.Empty; }
      if (employee.PostalCode == null) { employee.PostalCode = String.Empty; }

      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  = new SqlCommand("UPDATE Employees " +
                                          "  SET FirstName=@FirstName, LastName=@LastName, " +
                                          "  Address=@Address, City=@City, Region=@Region, " +
                                          "  PostalCode=@PostalCode " +
                                          "  WHERE EmployeeID=@EmployeeID", conn);  

      cmd.Parameters.Add("@FirstName",  SqlDbType.VarChar, 10).Value = employee.FirstName;
      cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = employee.LastName;
      cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = employee.Address;
      cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = employee.City;
      cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = employee.Region;
      cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;

      int result = 0;

      try
      {
        conn.Open();

        result = cmd.ExecuteNonQuery();
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        conn.Close();
      }

      return result;
    }

    // Insert an Employee.

    public int InsertEmployee(NorthwindEmployee employee)
    {
      if (String.IsNullOrEmpty(employee.FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(employee.LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

      if (employee.Address    == null) { employee.Address    = String.Empty; }
      if (employee.City       == null) { employee.City       = String.Empty; }
      if (employee.Region     == null) { employee.Region     = String.Empty; }
      if (employee.PostalCode == null) { employee.PostalCode = String.Empty; }

      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  = new SqlCommand("INSERT INTO Employees " +
                                          "  (FirstName, LastName, Address, City, Region, PostalCode) " +
                                          "  Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode); " +
                                          "SELECT @EmployeeID = SCOPE_IDENTITY()", conn);  

      cmd.Parameters.Add("@FirstName",  SqlDbType.VarChar, 10).Value = employee.FirstName;
      cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = employee.LastName;
      cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = employee.Address;
      cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = employee.City;
      cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = employee.Region;
      cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
      SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
      p.Direction = ParameterDirection.Output;

      int newEmployeeID = 0;

      try
      {
        conn.Open();

        cmd.ExecuteNonQuery();

        newEmployeeID = (int)p.Value;
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        conn.Close();
      }

      return newEmployeeID;
    }

    //
    // Delete the Employee by ID.
    //   This method assumes that ConflictDetection is set to OverwriteValues.

    public int DeleteEmployee(NorthwindEmployee employee)
    {
      string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";

      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand cmd = new SqlCommand(sqlCmd, conn);
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;

      int result = 0;

      try
      {
        conn.Open();

        result = cmd.ExecuteNonQuery();
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        conn.Close();
      }

      return result;
    }

  }
}

<%@ Page language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  void EmployeesDetailsView_ItemInserted(Object sender, DetailsViewInsertedEventArgs e)
  {
    EmployeesGridView.DataBind();
  }

  void EmployeesDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
  {
    EmployeesGridView.DataBind();
  }

  void EmployeesDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
  {
    EmployeesGridView.DataBind();
  }

  void EmployeesGridView_OnSelectedIndexChanged(object sender, EventArgs e)
  {
    EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
      EmployeesGridView.SelectedDataKey.Value.ToString();
    EmployeesDetailsView.DataBind();
  }

  void EmployeeDetailsObjectDataSource_OnInserted(object sender, ObjectDataSourceStatusEventArgs e)
  {
    EmployeeDetailsObjectDataSource.SelectParameters["EmployeeID"].DefaultValue =
      e.ReturnValue.ToString();
    EmployeesDetailsView.DataBind();
  }

  void EmployeeDetailsObjectDataSource_OnUpdated(object sender, ObjectDataSourceStatusEventArgs e)
  {
    if ((int)e.ReturnValue == 0)
      Msg.Text = "Employee was not updated. Please try again.";
  }

  void EmployeeDetailsObjectDataSource_OnDeleted(object sender, ObjectDataSourceStatusEventArgs e)
  {
    if ((int)e.ReturnValue == 0)
      Msg.Text = "Employee was not deleted. Please try again.";
  }

  void Page_Load()
  {
    Msg.Text = "";
  }

</script>
<html  >
  <head runat="server">
    <title>ObjectDataSource Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>ObjectDataSource Example</h3>
      <asp:Label id="Msg" runat="server" ForeColor="Red" />

      <asp:ObjectDataSource
        ID="EmployeesObjectDataSource"
        runat="server"
        TypeName="Samples.AspNet.ObjectDataSource.NorthwindEmployeeData"
        DataObjectTypeName="Samples.AspNet.ObjectDataSource.NorthwindEmployee"
        SortParameterName="SortColumns"
        EnablePaging="true"
        StartRowIndexParameterName="StartRecord"
        MaximumRowsParameterName="MaxRecords"
        SelectMethod="GetAllEmployees" >
      </asp:ObjectDataSource>

      <asp:ObjectDataSource
        ID="EmployeeDetailsObjectDataSource"
        runat="server"
        TypeName="Samples.AspNet.ObjectDataSource.NorthwindEmployeeData"
        DataObjectTypeName="Samples.AspNet.ObjectDataSource.NorthwindEmployee"
        SelectMethod="GetEmployee"
        InsertMethod="InsertEmployee"
        UpdateMethod="UpdateEmployee"
        DeleteMethod="DeleteEmployee"
        OnInserted="EmployeeDetailsObjectDataSource_OnInserted"
        OnUpdated="EmployeeDetailsObjectDataSource_OnUpdated"
        OnDeleted="EmployeeDetailsObjectDataSource_OnDeleted">
        <SelectParameters>
          <asp:Parameter Name="EmployeeID" Type="Int32" />
        </SelectParameters>
      </asp:ObjectDataSource>

      <table cellspacing="10">
        <tr>
          <td valign="top">
            <asp:GridView ID="EmployeesGridView"
              DataSourceID="EmployeesObjectDataSource"
              AutoGenerateColumns="false"
              AllowSorting="true"
              AllowPaging="true"
              PageSize="5"
              DataKeyNames="EmployeeID"
              OnSelectedIndexChanged="EmployeesGridView_OnSelectedIndexChanged"
              RunAt="server">

              <HeaderStyle backcolor="lightblue" forecolor="black"/>

              <Columns>
                <asp:ButtonField Text="Details..."
                                 HeaderText="Show Details"
                                 CommandName="Select"/>  

                <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="FirstName"  HeaderText="First Name" SortExpression="FirstName" />
                <asp:BoundField DataField="LastName"   HeaderText="Last Name" SortExpression="LastName, FirstName" />
              </Columns>
            </asp:GridView>
          </td>
          <td valign="top">
            <asp:DetailsView ID="EmployeesDetailsView"
              DataSourceID="EmployeeDetailsObjectDataSource"
              AutoGenerateRows="false"
              EmptyDataText="No records."
              DataKeyNames="EmployeeID"
              Gridlines="Both"
              AutoGenerateInsertButton="true"
              AutoGenerateEditButton="true"
              AutoGenerateDeleteButton="true"
              OnItemInserted="EmployeesDetailsView_ItemInserted"
              OnItemUpdated="EmployeesDetailsView_ItemUpdated"
              OnItemDeleted="EmployeesDetailsView_ItemDeleted"
              RunAt="server">

              <HeaderStyle backcolor="Navy" forecolor="White"/>

              <RowStyle backcolor="White"/>

              <AlternatingRowStyle backcolor="LightGray"/>

              <EditRowStyle backcolor="LightCyan"/>

              <Fields>
                <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>
                <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
                <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>
                <asp:BoundField DataField="Address"    HeaderText="Address"/>
                <asp:BoundField DataField="City"       HeaderText="City"/>
                <asp:BoundField DataField="Region"     HeaderText="Region"/>
                <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
              </Fields>
            </asp:DetailsView>
          </td>
        </tr>
      </table>
    </form>
  </body>
</html>

该示例需要 SQL Server 上的示例 Northwind 数据库的连接字符串。该连接字符串必须在应用程序的配置文件的 <connectionStrings> 元素中定义。connectionStrings 节可能看起来类似于下面的示例：

复制代码

<configuration>
  <system.web>
    <connectionStrings>
      <add
        name="Northwind"
        connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;" />
    </connectionStrings>

  </system.web>
</configuration>