ObjectDataSource 강력한 형식의 소스 개체 예제

Visual Studio 2010

업데이트: 2007년 11월

ObjectDataSource 컨트롤과 함께 사용할 수 있는 사용자 지정 중간 계층 비즈니스 개체를 보여 줍니다.

설명

다음 코드 예제에서는 사용자 지정 중간 계층 비즈니스 개체에 대해 설명합니다. 이 개체는 DataObjectTypeName 속성을 사용하여 강력한 형식의 소스 개체를 지정하는 ObjectDataSource 컨트롤과 함께 사용할 수 있습니다. 또한 이 항목에서는 비즈니스 개체를 ObjectDataSource 컨트롤의 소스로 사용하는 ASP.NET 페이지 예제도 보여 줍니다. 이 페이지에는 ObjectDataSource 컨트롤에 바인딩된 GridView 컨트롤과 DetailsView 컨트롤이 포함되어 있습니다.

코드를 사용하려면 웹 응용 프로그램의 App_Code 하위 디렉터리에 코드 파일을 만든 다음 이 파일에 코드를 복사합니다. 그런 다음 비즈니스 개체가 동적으로 컴파일되어 웹 응용 프로그램의 일부로 포함됩니다. 또는 비즈니스 개체를 컴파일한 다음 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 xmlns="http://www.w3.org/1999/xhtml" >
  <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>
표시: