Udostępnij za pośrednictwem


ObjectDataSource Source Object Example

Illustrates a custom middle-tier business object that can be used with an ObjectDataSource control.

Example

Description

The following code example illustrates a custom middle-tier business object that can be used with an ObjectDataSource control. This topic also illustrates an example ASP.NET page that uses the business object as the source for an ObjectDataSource control. The page includes a GridView control and a DetailsView control that are bound to the ObjectDataSource control.

To use the code, you can create a code file in your Web application's App_Code subdirectory and copy the code into the file. The business object will then be compiled dynamically and included as part of your Web application. Alternatively, you can compile the business object and place it in the Bin directory of an ASP.NET application or in the Global Assembly Cache (GAC). For more information on the App_Code and Bin directories, see Shared Code Folders in ASP.NET Web Sites.

Code

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.UI
Imports System.Web.UI.WebControls

Namespace Samples.AspNet.ObjectDataSource
  '  '  Northwind Employee Data Factory  'PublicClass NorthwindData


    Private _connectionString AsStringPublicSubNew()
      Initialize()
    EndSubPublicSub Initialize()    
      ' Initialize data source. Use "Northwind" connection string from configuration.If ConfigurationManager.ConnectionStrings("Northwind") IsNothingOrElse _
          ConfigurationManager.ConnectionStrings("Northwind").ConnectionString.Trim() = ""ThenThrowNew Exception("A connection string named 'Northwind' with a valid connection string " & _
                            "must exist in the <connectionStrings> configuration section for the application.")
      EndIf

      _connectionString = _
        ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
    EndSub

    ' Select all employees.PublicFunction GetAllEmployees(sortColumns AsString, startRecord AsInteger, maxRecords AsInteger) As DataTable 

      VerifySortColumns(sortColumns)

      Dim sqlCmd AsString = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees "If sortColumns.Trim() = ""Then
        sqlCmd &= "ORDER BY EmployeeID"Else
        sqlCmd &= "ORDER BY " & sortColumns
      EndIfDim conn As SqlConnection  = New SqlConnection(_connectionString)
      Dim da   As SqlDataAdapter = New SqlDataAdapter(sqlCmd, conn)

      Dim ds As DataSet = New DataSet()

      Try      
        conn.Open()

        da.Fill(ds, startRecord, maxRecords, "Employees")        
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn ds.Tables("Employees")
    EndFunctionPublicFunction SelectCount() AsIntegerDim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = New SqlCommand("SELECT COUNT(*) FROM Employees", conn) 

      Dim result AsInteger = 0

      Try      
        conn.Open()

        result = CInt(cmd.ExecuteScalar())
      Catch e As SqlException      
        ' Handle exception.Finally

        conn.Close()
      EndTryReturn result
    EndFunction

    '''''    ' Verify that only valid columns are specified in the sort expression to aSub a SQL Injection attack.PrivateSub VerifySortColumns(sortColumns AsString)    
      If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
        sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)

      Dim columnNames() AsString = sortColumns.Split(",")

      ForEach columnName AsStringIn columnNames      
        SelectCase columnName.Trim().ToLowerInvariant()        
          Case"employeeid"Case"lastname"Case"firstname"Case""CaseElseThrowNew ArgumentException("SortColumns contains an invalid column name.")
        EndSelectNextEndSub


    ' Select an employee.PublicFunction GetEmployee(EmployeeID AsInteger) As DataTable    
      Dim conn As SqlConnection  = New SqlConnection(_connectionString)
      Dim da   As SqlDataAdapter = _
        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

      Dim ds As DataSet = New DataSet()

      Try      
        conn.Open()

        da.Fill(ds, "Employees")
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn ds.Tables("Employees")
    EndFunction


    ' Delete the Employee by ID.PublicFunction DeleteEmployee(EmployeeID AsInteger) AsIntegerDim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = New SqlCommand("DELETE FROM Employees WHERE EmployeeID = @EmployeeID", conn)  
      cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID

      Dim result AsInteger = 0

      Try      
        conn.Open()

        result = cmd.ExecuteNonQuery()
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn result
    EndFunction

    ' Update the Employee by original ID.PublicFunction UpdateEmployee(EmployeeID AsInteger, LastName AsString, FirstName AsString, _
                                   Address AsString, City AsString, Region AsString, _
                                   PostalCode AsString) AsIntegerIfString.IsNullOrEmpty(FirstName) Then _
        ThrowNew ArgumentException("FirstName cannot be null or an empty string.")
      IfString.IsNullOrEmpty(LastName) Then _
        ThrowNew ArgumentException("LastName cannot be null or an empty string.")

      If Address    IsNothingThen Address    = String.Empty 
      If City       IsNothingThen City       = String.Empty 
      If Region     IsNothingThen Region     = String.Empty 
      If PostalCode IsNothingThen PostalCode = String.Empty 

      Dim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = 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

      Dim result AsInteger = 0

      Try      
        conn.Open()

        result = cmd.ExecuteNonQuery()
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn result
    EndFunction

    ' Insert an Employee.PublicFunction InsertEmployee(LastName AsString, FirstName AsString, Address AsString, _
                                   City AsString, Region AsString, PostalCode AsString) AsIntegerIfString.IsNullOrEmpty(FirstName) Then _
        ThrowNew ArgumentException("FirstName cannot be null or an empty string.")
      IfString.IsNullOrEmpty(LastName) Then _
        ThrowNew ArgumentException("LastName cannot be null or an empty string.")

      If Address    IsNothingThen Address    = String.Empty 
      If City       IsNothingThen City       = String.Empty 
      If Region     IsNothingThen Region     = String.Empty 
      If PostalCode IsNothingThen PostalCode = String.Empty 

      Dim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = 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
      Dim p As SqlParameter = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int)
      p.Direction = ParameterDirection.Output

      Dim newEmployeeID AsInteger = 0

      Try      
        conn.Open()

        cmd.ExecuteNonQuery()

        newEmployeeID = CInt(p.Value)
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn newEmployeeID
    EndFunction


    '    ' Methods that support Optimistic Concurrency checks.    '
    ' Delete the Employee by ID.PublicFunction DeleteEmployee(original_EmployeeID AsInteger, original_LastName AsString, _
                                   original_FirstName AsString, original_Address AsString, _
                                   original_City AsString, original_Region AsString, _
                                   original_PostalCode AsString) AsIntegerIfString.IsNullOrEmpty(original_FirstName) Then _
        ThrowNew ArgumentException("FirstName cannot be null or an empty string.")
      IfString.IsNullOrEmpty(original_LastName) Then _
        ThrowNew ArgumentException("LastName cannot be null or an empty string.")

      If original_Address    IsNothingThen original_Address    = String.Empty 
      If original_City       IsNothingThen original_City       = String.Empty 
      If original_Region     IsNothingThen original_Region     = String.Empty 
      If original_PostalCode IsNothingThen original_PostalCode = String.Empty 

      Dim sqlCmd AsString = "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"Dim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = 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

      Dim result AsInteger = 0

      Try      
        conn.Open()

        result = cmd.ExecuteNonQuery()
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn result
    EndFunction

    ' Update the Employee by original ID.PublicFunction UpdateEmployee(EmployeeID AsInteger, LastName AsString, FirstName AsString, _
                                   Address AsString, City AsString, Region AsString, _
                                   PostalCode AsString, _
                                   original_EmployeeID AsInteger, original_LastName AsString, _
                                   original_FirstName AsString, original_Address AsString, _
                                   original_City AsString, original_Region AsString, _
                                   original_PostalCode AsString) AsIntegerIfString.IsNullOrEmpty(FirstName) Then _
        ThrowNew ArgumentException("FirstName cannot be null or an empty string.")
      IfString.IsNullOrEmpty(LastName) Then _
        ThrowNew ArgumentException("LastName cannot be null or an empty string.")

      If Address    IsNothingThen Address    = String.Empty 
      If City       IsNothingThen City       = String.Empty 
      If Region     IsNothingThen Region     = String.Empty 
      If PostalCode IsNothingThen PostalCode = String.Empty 

      If original_Address    IsNothingThen original_Address    = String.Empty 
      If original_City       IsNothingThen original_City       = String.Empty 
      If original_Region     IsNothingThen original_Region     = String.Empty 
      If original_PostalCode IsNothingThen original_PostalCode = String.Empty 

      Dim sqlCmd AsString = "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"Dim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = 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

      Dim result AsInteger = 0

      Try      
        conn.Open()

        result = cmd.ExecuteNonQuery()
      Catch e As SqlException      
        ' Handle exception.Finally      
        conn.Close()
      EndTryReturn result
    EndFunctionEndClassEndNamespace
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//publicclass NorthwindData
  {

    privatestring _connectionString;


    public NorthwindData()
    {
      Initialize();
    }


    publicvoid Initialize()
    {
      // Initialize data source. Use "Northwind" connection stringfrom configuration.

      if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
          ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "")
      {
        thrownew 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"];
    }


    publicint 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.privatevoid 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:
            thrownew 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.publicint 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.publicint UpdateEmployee(int EmployeeID, string LastName, string FirstName,
                              string Address, string City, string Region, string PostalCode)
    {
      if (String.IsNullOrEmpty(FirstName))
        thrownew ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        thrownew 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.publicint InsertEmployee(string LastName, string FirstName,
                               string Address, string City, string Region, string PostalCode)
    {
      if (String.IsNullOrEmpty(FirstName))
        thrownew ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        thrownew 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.publicint 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))
        thrownew ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(original_LastName))
        thrownew 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.publicint 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))
        thrownew ArgumentException("FirstName cannot be null or an empty string.");
      if (String.IsNullOrEmpty(LastName))
        thrownew 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="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  Sub EmployeesDetailsView_ItemInserted(sender AsObject, e As DetailsViewInsertedEventArgs)  
    EmployeesGridView.DataBind() 
  EndSubSub EmployeesDetailsView_ItemUpdated(sender AsObject, e As DetailsViewUpdatedEventArgs)  
    EmployeesGridView.DataBind()
  EndSubSub EmployeesDetailsView_ItemDeleted(sender AsObject, e As DetailsViewDeletedEventArgs)  
    EmployeesGridView.DataBind()
  EndSubSub EmployeesGridView_OnSelectedIndexChanged(sender AsObject, e As EventArgs)  
    EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
      EmployeesGridView.SelectedDataKey.Value.ToString()
    EmployeesDetailsView.DataBind()
  EndSubSub EmployeeDetailsObjectDataSource_OnInserted(sender AsObject, e As ObjectDataSourceStatusEventArgs)  
    EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
      e.ReturnValue.ToString()
    EmployeesDetailsView.DataBind()
  EndSubSub EmployeeDetailsObjectDataSource_OnUpdated(sender AsObject, e As ObjectDataSourceStatusEventArgs)  
    IfCInt(e.ReturnValue) = 0 Then _
      Msg.Text = "Employee was not updated. Please try again."EndSubSub EmployeeDetailsObjectDataSource_OnDeleted(sender AsObject, e As ObjectDataSourceStatusEventArgs)  
    IfCInt(e.ReturnValue) = 0 Then _
      Msg.Text = "Employee was not deleted. Please try again."EndSubSub Page_Load()  
    Msg.Text = ""EndSub

</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>
<%@ 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>

Comments

The example requires a connection string for the sample Northwind database on a SQL Server. The connection string must be defined in the <connectionStrings> element of the application's configuration file. The connectionStrings section might look like the following example:

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

See Also

Reference

ObjectDataSource

ObjectDataSource Web Server Control Overview