ObjectDataSource 소스 개체 예제

Visual Studio 2010

업데이트: 2007년 11월

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

설명

다음 코드 예제에서는 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
{
  //
  //  Northwind Employee Data Factory
  //

  public class NorthwindData
  {

    private string _connectionString;


    public NorthwindData()
    {
      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 DataTable 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);
      SqlDataAdapter da   = new SqlDataAdapter(sqlCmd, conn);

      DataSet ds = new DataSet();

      try
      {
        conn.Open();

        da.Fill(ds, startRecord, maxRecords, "Employees");        
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        conn.Close();
      }

      return ds.Tables["Employees"];
    }


    public int SelectCount()
    {
      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  = new SqlCommand("SELECT COUNT(*) FROM Employees", conn); 

      int result = 0;

      try
      {
        conn.Open();

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

      return result;
    }


    //////////
    // 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;
        }
      }
    }



    // Select an employee.

    public DataTable GetEmployee(int EmployeeID)
    {
      SqlConnection  conn = new SqlConnection(_connectionString);
      SqlDataAdapter da   = 
        new SqlDataAdapter("SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode " +
                           "  FROM Employees WHERE EmployeeID = @EmployeeID", conn); 
      da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;

      DataSet ds = new DataSet();

      try
      {
        conn.Open();

        da.Fill(ds, "Employees");
      }
      catch (SqlException e)
      {
        // Handle exception.
      }
      finally
      {
        conn.Close();
      }

      return ds.Tables["Employees"];
    }



    // Delete the Employee by ID.

    public int DeleteEmployee(int EmployeeID)
    {
      SqlConnection conn = new SqlConnection(_connectionString);
      SqlCommand    cmd  = new SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", conn);  
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID;

      int result = 0;

      try
      {
        conn.Open();

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

      return result;
    }


    // Update the Employee by original ID.

    public int UpdateEmployee(int EmployeeID, string LastName, string FirstName,
                              string Address, string City, string Region, string PostalCode)
    {
      if (String.IsNullOrEmpty(FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

      if (Address    == null) { Address    = String.Empty; }
      if (City       == null) { City       = String.Empty; }
      if (Region     == null) { Region     = String.Empty; }
      if (PostalCode == null) { 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 = FirstName;
      cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = LastName;
      cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = Address;
      cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = City;
      cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = Region;
      cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 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(string LastName, string FirstName,
                               string Address, string City, string Region, string PostalCode)
    {
      if (String.IsNullOrEmpty(FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

      if (Address    == null) { Address    = String.Empty; }
      if (City       == null) { City       = String.Empty; }
      if (Region     == null) { Region     = String.Empty; }
      if (PostalCode == null) { 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 = FirstName;
      cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = LastName;
      cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = Address;
      cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = City;
      cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = Region;
      cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = 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;
    }



    //
    // Methods that support Optimistic Concurrency checks.
    //

    // Delete the Employee by ID.

    public int DeleteEmployee(int original_EmployeeID, string original_LastName, 
                              string original_FirstName, string original_Address,
                              string original_City, string original_Region,
                              string original_PostalCode)
    {
      if (String.IsNullOrEmpty(original_FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(original_LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

      if (original_Address    == null) { original_Address    = String.Empty; }
      if (original_City       == null) { original_City       = String.Empty; }
      if (original_Region     == null) { original_Region     = String.Empty; }
      if (original_PostalCode == null) { original_PostalCode = String.Empty; }

      string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = @original_EmployeeID " + 
                      " AND LastName = @original_LastName AND FirstName = @original_FirstName " +
                      " AND Address = @original_Address AND City = @original_City " +
                      " AND Region = @original_Region AND PostalCode = @original_PostalCode";

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

      cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID;
      cmd.Parameters.Add("@original_FirstName",  SqlDbType.VarChar, 10).Value = original_FirstName;
      cmd.Parameters.Add("@original_LastName",   SqlDbType.VarChar, 20).Value = original_LastName;
      cmd.Parameters.Add("@original_Address",    SqlDbType.VarChar, 60).Value = original_Address;
      cmd.Parameters.Add("@original_City",       SqlDbType.VarChar, 15).Value = original_City;
      cmd.Parameters.Add("@original_Region",     SqlDbType.VarChar, 15).Value = original_Region;
      cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode;

      int result = 0;

      try
      {
        conn.Open();

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

      return result;
    }


    // Update the Employee by original ID.

    public int UpdateEmployee(int EmployeeID, string LastName, string FirstName,
                              string Address, string City, string Region, string PostalCode,
                              int original_EmployeeID, string original_LastName,
                              string original_FirstName, string original_Address, 
                              string original_City, string original_Region,
                              string original_PostalCode)
    {
      if (String.IsNullOrEmpty(FirstName))
        throw new ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        throw new ArgumentException("LastName cannot be null or an empty string.");

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

      if (original_Address    == null) { original_Address    = String.Empty; }
      if (original_City       == null) { original_City       = String.Empty; }
      if (original_Region     == null) { original_Region     = String.Empty; }
      if (original_PostalCode == null) { original_PostalCode = String.Empty; }

      string sqlCmd = "UPDATE Employees " + 
                      "  SET FirstName = @FirstName, LastName = @LastName, " + 
                      "  Address = @Address, City = @City, Region = @Region, " +
                      "  PostalCode = @PostalCode " +
                      "  WHERE EmployeeID = @original_EmployeeID " +
                      " AND LastName = @original_LastName AND FirstName = @original_FirstName " +
                      " AND Address = @original_Address AND City = @original_City " +
                      " AND Region = @original_Region AND PostalCode = @original_PostalCode";

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

      cmd.Parameters.Add("@FirstName",  SqlDbType.VarChar, 10).Value = FirstName;
      cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = LastName;
      cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = Address;
      cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = City;
      cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = Region;
      cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode;
      cmd.Parameters.Add("@original_EmployeeID", SqlDbType.Int).Value = original_EmployeeID;
      cmd.Parameters.Add("@original_FirstName",  SqlDbType.VarChar, 10).Value = original_FirstName;
      cmd.Parameters.Add("@original_LastName",   SqlDbType.VarChar, 20).Value = original_LastName;
      cmd.Parameters.Add("@original_Address",    SqlDbType.VarChar, 60).Value = original_Address;
      cmd.Parameters.Add("@original_City",       SqlDbType.VarChar, 15).Value = original_City;
      cmd.Parameters.Add("@original_Region",     SqlDbType.VarChar, 15).Value = original_Region;
      cmd.Parameters.Add("@original_PostalCode", SqlDbType.VarChar, 10).Value = original_PostalCode;

      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.NorthwindData" 
        SortParameterName="SortColumns"
        EnablePaging="true"
        SelectCountMethod="SelectCount"
        StartRowIndexParameterName="StartRecord"
        MaximumRowsParameterName="MaxRecords" 
        SelectMethod="GetAllEmployees" >
      </asp:ObjectDataSource>


      <asp:ObjectDataSource 
        ID="EmployeeDetailsObjectDataSource" 
        runat="server" 
        TypeName="Samples.AspNet.ObjectDataSource.NorthwindData" 
        ConflictDetection="CompareAllValues"
        OldValuesParameterFormatString="original_{0}"
        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>
표시: