Condividi tramite


Esempio di oggetto di origine ObjectDataSource

Aggiornamento: novembre 2007

Viene illustrato un oggetto business di livello intermedio personalizzato che è possibile utilizzare con un controllo ObjectDataSource.

Esempio

Descrizione

Nell'esempio di codice riportato di seguito viene illustrato un oggetto business di livello intermedio personalizzato che è possibile utilizzare con un controllo ObjectDataSource. In questo argomento viene descritta anche una pagina ASP.NET di esempio che utilizza l'oggetto business come origine di un controllo ObjectDataSource. La pagina include un controllo GridView e un controllo DetailsView che sono associati al controllo ObjectDataSource.

Per utilizzare il codice, è possibile creare un file di codice nella sottodirectory App_Code dell'applicazione Web e copiare il codice nel file. L'oggetto business verrà, quindi, compilato dinamicamente e incluso come parte dell'applicazione Web. In alternativa, è possibile compilare l'oggetto business e inserirlo nella directory Bin di un'applicazione ASP.NET oppure nella Global Assembly Cache. Per ulteriori informazioni sulle directory App_Code e Bin, vedere Cartelle di codice condivise nei siti Web ASP.NET.

Codice

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
  '

  Public Class NorthwindData


    Private _connectionString As String


    Public Sub New()
      Initialize()
    End Sub


    Public Sub Initialize()    
      ' Initialize data source. Use "Northwind" connection string from configuration.

      If ConfigurationManager.ConnectionStrings("Northwind") Is Nothing OrElse _
          ConfigurationManager.ConnectionStrings("Northwind").ConnectionString.Trim() = "" Then      
        Throw New Exception("A connection string named 'Northwind' with a valid connection string " & _
                            "must exist in the <connectionStrings> configuration section for the application.")
      End If

      _connectionString = _
        ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
    End Sub


    ' Select all employees.

    Public Function GetAllEmployees(sortColumns As String, startRecord As Integer, maxRecords As Integer) As DataTable 

      VerifySortColumns(sortColumns)

      Dim sqlCmd As String = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees "

      If sortColumns.Trim() = "" Then
        sqlCmd &= "ORDER BY EmployeeID"
      Else
        sqlCmd &= "ORDER BY " & sortColumns
      End If

      Dim 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()
      End Try

      Return ds.Tables("Employees")
    End Function


    Public Function SelectCount() As Integer

      Dim conn As SqlConnection = New SqlConnection(_connectionString)
      Dim cmd  As SqlCommand    = New SqlCommand("SELECT COUNT(*) FROM Employees", conn) 

      Dim result As Integer = 0

      Try      
        conn.Open()

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

        conn.Close()
      End Try

      Return result
    End Function


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

    Private Sub VerifySortColumns(sortColumns As String)    
      If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
        sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)

      Dim columnNames() As String = sortColumns.Split(",")

      For Each columnName As String In columnNames      
        Select Case columnName.Trim().ToLowerInvariant()        
          Case "employeeid"
          Case "lastname"
          Case "firstname"
          Case ""
          Case Else
            Throw New ArgumentException("SortColumns contains an invalid column name.")
        End Select
      Next
    End Sub



    ' Select an employee.

    Public Function GetEmployee(EmployeeID As Integer) 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()
      End Try

      Return ds.Tables("Employees")
    End Function



    ' Delete the Employee by ID.

    Public Function DeleteEmployee(EmployeeID As Integer) As Integer    
      Dim 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 As Integer = 0

      Try      
        conn.Open()

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

      Return result
    End Function


    ' Update the Employee by original ID.

    Public Function UpdateEmployee(EmployeeID As Integer, LastName As String, FirstName As String, _
                                   Address As String, City As String, Region As String, _
                                   PostalCode As String) As Integer

      If String.IsNullOrEmpty(FirstName) Then _
        Throw New ArgumentException("FirstName cannot be null or an empty string.")
      If String.IsNullOrEmpty(LastName) Then _
        Throw New ArgumentException("LastName cannot be null or an empty string.")

      If Address    Is Nothing Then Address    = String.Empty 
      If City       Is Nothing Then City       = String.Empty 
      If Region     Is Nothing Then Region     = String.Empty 
      If PostalCode Is Nothing Then 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 As Integer = 0

      Try      
        conn.Open()

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

      Return result
    End Function


    ' Insert an Employee.

    Public Function InsertEmployee(LastName As String, FirstName As String, Address As String, _
                                   City As String, Region As String, PostalCode As String) As Integer

      If String.IsNullOrEmpty(FirstName) Then _
        Throw New ArgumentException("FirstName cannot be null or an empty string.")
      If String.IsNullOrEmpty(LastName) Then _
        Throw New ArgumentException("LastName cannot be null or an empty string.")

      If Address    Is Nothing Then Address    = String.Empty 
      If City       Is Nothing Then City       = String.Empty 
      If Region     Is Nothing Then Region     = String.Empty 
      If PostalCode Is Nothing Then 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 As Integer = 0

      Try      
        conn.Open()

        cmd.ExecuteNonQuery()

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

      Return newEmployeeID
    End Function



    '
    ' Methods that support Optimistic Concurrency checks.
    '

    ' Delete the Employee by ID.

    Public Function DeleteEmployee(original_EmployeeID As Integer, original_LastName As String, _
                                   original_FirstName As String, original_Address As String, _
                                   original_City As String, original_Region As String, _
                                   original_PostalCode As String) As Integer

      If String.IsNullOrEmpty(original_FirstName) Then _
        Throw New ArgumentException("FirstName cannot be null or an empty string.")
      If String.IsNullOrEmpty(original_LastName) Then _
        Throw New ArgumentException("LastName cannot be null or an empty string.")

      If original_Address    Is Nothing Then original_Address    = String.Empty 
      If original_City       Is Nothing Then original_City       = String.Empty 
      If original_Region     Is Nothing Then original_Region     = String.Empty 
      If original_PostalCode Is Nothing Then original_PostalCode = String.Empty 

      Dim sqlCmd As String = "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 As Integer = 0

      Try      
        conn.Open()

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

      Return result
    End Function


    ' Update the Employee by original ID.

    Public Function UpdateEmployee(EmployeeID As Integer, LastName As String, FirstName As String, _
                                   Address As String, City As String, Region As String, _
                                   PostalCode As String, _
                                   original_EmployeeID As Integer, original_LastName As String, _
                                   original_FirstName As String, original_Address As String, _
                                   original_City As String, original_Region As String, _
                                   original_PostalCode As String) As Integer

      If String.IsNullOrEmpty(FirstName) Then _
        Throw New ArgumentException("FirstName cannot be null or an empty string.")
      If String.IsNullOrEmpty(LastName) Then _
        Throw New ArgumentException("LastName cannot be null or an empty string.")

      If Address    Is Nothing Then Address    = String.Empty 
      If City       Is Nothing Then City       = String.Empty 
      If Region     Is Nothing Then Region     = String.Empty 
      If PostalCode Is Nothing Then PostalCode = String.Empty 

      If original_Address    Is Nothing Then original_Address    = String.Empty 
      If original_City       Is Nothing Then original_City       = String.Empty 
      If original_Region     Is Nothing Then original_Region     = String.Empty 
      If original_PostalCode Is Nothing Then original_PostalCode = String.Empty 

      Dim sqlCmd As String = "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 As Integer = 0

      Try      
        conn.Open()

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

      Return result
    End Function

  End Class
End Namespace
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="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script >

  Sub EmployeesDetailsView_ItemInserted(sender As Object, e As DetailsViewInsertedEventArgs)  
    EmployeesGridView.DataBind() 
  End Sub


  Sub EmployeesDetailsView_ItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)  
    EmployeesGridView.DataBind()
  End Sub


  Sub EmployeesDetailsView_ItemDeleted(sender As Object, e As DetailsViewDeletedEventArgs)  
    EmployeesGridView.DataBind()
  End Sub

  Sub EmployeesGridView_OnSelectedIndexChanged(sender As Object, e As EventArgs)  
    EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
      EmployeesGridView.SelectedDataKey.Value.ToString()
    EmployeesDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsObjectDataSource_OnInserted(sender As Object, e As ObjectDataSourceStatusEventArgs)  
    EmployeeDetailsObjectDataSource.SelectParameters("EmployeeID").DefaultValue = _
      e.ReturnValue.ToString()
    EmployeesDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsObjectDataSource_OnUpdated(sender As Object, e As ObjectDataSourceStatusEventArgs)  
    If CInt(e.ReturnValue) = 0 Then _
      Msg.Text = "Employee was not updated. Please try again."
  End Sub

  Sub EmployeeDetailsObjectDataSource_OnDeleted(sender As Object, e As ObjectDataSourceStatusEventArgs)  
    If CInt(e.ReturnValue) = 0 Then _
      Msg.Text = "Employee was not deleted. Please try again."
  End Sub

  Sub Page_Load()  
    Msg.Text = ""
  End Sub

</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head >
    <title>ObjectDataSource Example</title>
</head>
<body>
    <form id="form1" >

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

      <asp:ObjectDataSource 
        ID="EmployeesObjectDataSource" 
         
        TypeName="Samples.AspNet.ObjectDataSource.NorthwindData" 
        SortParameterName="SortColumns"
        EnablePaging="true"
        SelectCountMethod="SelectCount"
        StartRowIndexParameterName="StartRecord"
        MaximumRowsParameterName="MaxRecords" 
        SelectMethod="GetAllEmployees" >
      </asp:ObjectDataSource>


      <asp:ObjectDataSource 
        ID="EmployeeDetailsObjectDataSource" 
         
        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 >

  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 >
    <title>ObjectDataSource Example</title>
</head>
<body>
    <form id="form1" >

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

      <asp:ObjectDataSource 
        ID="EmployeesObjectDataSource" 
         
        TypeName="Samples.AspNet.ObjectDataSource.NorthwindData" 
        SortParameterName="SortColumns"
        EnablePaging="true"
        SelectCountMethod="SelectCount"
        StartRowIndexParameterName="StartRecord"
        MaximumRowsParameterName="MaxRecords" 
        SelectMethod="GetAllEmployees" >
      </asp:ObjectDataSource>


      <asp:ObjectDataSource 
        ID="EmployeeDetailsObjectDataSource" 
         
        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>

Commenti

L'esempio richiede una stringa di connessione per il database Northwind di esempio in un SQL Server. La stringa di connessione deve essere definita nell'elemento <connectionStrings> del file di configurazione dell'applicazione. La sezione connectionStrings può assumere la forma illustrata nell'esempio seguente:

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

Vedere anche

Riferimenti

ObjectDataSource

Cenni preliminari sul controllo server Web ObjectDataSource