ASP.NET Tutorial/ADO.net Database/ObjectDataSource — различия между версиями

Текущая версия на 11:56, 26 мая 2010

Содержание

1 asp:ObjectDataSource with UpdateParameters
2 Binding to a DataSet
3 Bind SqlDataReader with asp:ObjectDataSource
4 Creating a Customer class to demonstrate the ObjectDataSource control (C#)
5 Creating a Customer class to demonstrate the ObjectDataSource control (VB)
6 ObjectDataSource binds DataBound controls such as the GridView, DetailsView, and FormView controls to a component
7 ObjectDataSource Insert
8 ObjectDataSource Update
9 Paging, Sorting, and Filtering Data with the ObjectDataSource Control
10 Using ObjectDataSource
11 Using two ObjectDataSource controls in one page

asp:ObjectDataSource with UpdateParameters

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class Products
{    private readonly string _conString;
    public void UpdateProduct(int id, string title, string director, DateTime dateReleased)
    {
        SqlConnection con = new SqlConnection(_conString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.rumandText = "UPDATE Products SET Title=@Title,Director=@Director,DateReleased=
        cmd.Parameters.AddWithValue("@Title", title);
        cmd.Parameters.AddWithValue("@Director", director);
        cmd.Parameters.AddWithValue("@DateReleased", dateReleased);
        cmd.Parameters.AddWithValue("@Id", id);
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
    public SqlDataReader GetProducts()
    {
        SqlConnection con = new SqlConnection(_conString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.rumandText = "SELECT Id,Title,Director,DateReleased FROM Products";
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    public Products()
    {            
        _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString;
    }
}

File: Web.config
<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Products</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        DataKeyNames="Id"
        AutoGenerateEditButton="true"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="Products"
        SelectMethod="GetProducts"
        UpdateMethod="UpdateProduct"
        Runat="server">
        <UpdateParameters>
        <asp:Parameter Name="title" />
        <asp:Parameter Name="director" />
        <asp:Parameter Name="dateReleased" Type="DateTime" />
        <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
    </asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>

Binding to a DataSet

File: ProductDataSet.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class ProductDataSet
{
    private readonly string _conString;
    public DataSet GetProducts()
    {
        string commandText = "SELECT Id,Title,Director FROM Products";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, _conString);
        DataSet dstProducts = new DataSet();
        using (dad)
        {
            dad.Fill(dstProducts);
        }
        return dstProducts;
    }

    public ProductDataSet()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString;
    }
}
File: Web.config
<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>
File: ShowProductDataSet.aspx
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Product DataSet</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="ProductDataReader"
        SelectMethod="GetProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>

Bind SqlDataReader with asp:ObjectDataSource

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class ProductDataReader
{
    private readonly string _conString;
    public SqlDataReader GetProducts()
    {
        SqlConnection con = new SqlConnection(_conString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.rumandText = "SELECT Id,Title,Director FROM Products";
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    public ProductDataReader()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString;
    }
}
            
File: index.aspx
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Product DataReader</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="ProductDataReader"
        SelectMethod="GetProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>
File: Web.config
<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>

Creating a Customer class to demonstrate the ObjectDataSource control (C#)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
            InsertMethod="Insert" SelectMethod="Select" TypeName="Customer"
            UpdateMethod="Update">
            <SelectParameters>
                <asp:QueryStringParameter Name="customerID" QueryStringField="ID"
                    Type="Int32" />
            </SelectParameters>
        </asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>
File: App_Code\Customer.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public class Customer
{
    private int _customerID;
    private string _companyName;
    private string _contactName;
    private string _contactTitle;
    public int CustomerID
    {
        get
        {
            return _customerID;
        }
        set
        {
            _customerID = value;
        }
    }
    public string CompanyName
    {
        get
        {
            return _companyName;
        }
        set
        {
            _companyName = value;
        }
    }
    public string ContactName
    {
        get
        {
            return _contactName;
        }
        set
        {
            _contactName = value;
        }
    }
    public string ContactTitle
    {
        get
        {
            return _contactTitle;
        }
        set
        {
            _contactTitle = value;
        }
    }
    public Customer()
    {
    }
    public System.Data.DataSet Select(Int32 customerId)
    {
        // Implement logic here to retrieve the Customer 
        // data based on the methods customerId parameter
        System.Data.DataSet ds = new System.Data.DataSet();
        ds.Tables.Add(new System.Data.DataTable());
        return ds;
    }
    public void Insert(Customer c)
    {
        // Implement Insert logic
    }
    public void Update(Customer c)
    {
        // Implement Update logic
    }
    public void Delete(Customer c)
    {
        // Implement Delete logic
    }
}

Creating a Customer class to demonstrate the ObjectDataSource control (VB)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
            InsertMethod="Insert" SelectMethod="Select" TypeName="Customer"
            UpdateMethod="Update">
            <SelectParameters>
                <asp:QueryStringParameter Name="customerID" QueryStringField="ID"
                    Type="Int32" />
            </SelectParameters>
        </asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>
File: App_Code\Customer.vb
Imports Microsoft.VisualBasic
Public Class Customer
    Private _customerID As Integer
    Private _companyName As String
    Private _contactName As String
    Private _contactTitle As String
    Public Property CustomerID() As Integer
        Get
            Return _customerID
        End Get
        Set(ByVal value As Integer)
            _customerID = value
        End Set
    End Property
    Public Property CompanyName() As Integer
        Get
            Return _companyName
        End Get
        Set(ByVal value As Integer)
            _companyName = value
        End Set
    End Property
    Public Property ContactName() As Integer
        Get
            Return _contactName
        End Get
        Set(ByVal value As Integer)
            _contactName = value
        End Set
    End Property
    Public Property ContactTitle() As Integer
        Get
            Return _contactTitle
        End Get
        Set(ByVal value As Integer)
            _contactTitle = value
        End Set
    End Property
    Public Function [Select](ByVal customerID As Integer) As System.Data.DataSet
        " You would implement logic here to reterive
        " Customer data based on the customerID parameter
        Dim ds As New System.Data.DataSet()
        ds.Tables.Add(New System.Data.DataTable())
        Return ds
    End Function
    Public Sub Insert(ByVal c As Customer)
        " Implement Insert logic
    End Sub
    Public Sub Update(ByVal c As Customer)
        " Implement Update logic
    End Sub
    Public Sub Delete(ByVal c As Customer)
        " Implement Delete logic
    End Sub
End Class

ObjectDataSource binds DataBound controls such as the GridView, DetailsView, and FormView controls to a component

using System;
using System.Web.Configuration;
using System.Collections.Generic;
public class ProductCollection
{
    public List<string> GetProducts()
    {
        List<string> products = new List<string>();
        products.Add("A");
        products.Add("B");
        products.Add("C");
        return products;
    }
}
File: index.aspx
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Product Collection</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="ProductCollection"
        SelectMethod="GetProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>
File: Web.config
<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>

ObjectDataSource Insert

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ObjectDataSourceInsert.aspx.cs" Inherits="ObjectDataSourceInsert" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource ID="sourceEmployees" 
                              runat="server" 
                              InsertMethod="InsertEmployee"
                              SelectMethod="GetEmployees" 
                              TypeName="EmployeeDB" 
                              OnUpdating="sourceEmployees_Updating" 
                              UpdateMethod="UpdateEmployee" 
                              DataObjectTypeName="EmployeeDetails" 
                              OnInserted="sourceEmployees_Inserted">
            <InsertParameters>
             <asp:Parameter Direction="ReturnValue" Name="EmployeeID" Type="Int32" Size="4" />
            </InsertParameters>
            
            </asp:ObjectDataSource>
        <asp:GridView ID="GridView1" 
                      runat="server" 
                      DataSourceID="sourceEmployees" 
                      CellPadding="4" 
                      Font-Names="Verdana" 
                      Font-Size="Small" 
                      ForeColor="#333333" 
                      GridLines="None" 
                      AutoGenerateColumns="False" 
                      EnableSortingAndPagingCallbacks="True" 
                      PageSize="5">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
            
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" InsertVisible="False" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy" SortExpression="TitleOfCourtesy" />
            </Columns>
        </asp:GridView>
        <asp:DetailsView ID="DetailsView1" 
                         runat="server" 
                         DataSourceID="sourceEmployees"
                         Height="50px" 
                         Width="125px" 
                         AutoGenerateRows="False">
            <Fields>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy" SortExpression="TitleOfCourtesy" />
                <asp:CommandField ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
        <asp:Label ID="lblConfirmation" runat="server"></asp:Label>&nbsp; &nbsp; &nbsp; &nbsp;
        &nbsp;&nbsp;<br />
       
    
    </div>
    </form>
</body>
</html>
File: ObjectDataSourceInsert.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ObjectDataSourceInsert : System.Web.UI.Page
{
  protected void sourceEmployees_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
  {
    if (e.Exception == null)
    {
      lblConfirmation.Text = "Inserted record " + e.ReturnValue.ToString();
    }
  }
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  
</configuration>
File: EmployeeDB.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;

public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }

  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
      
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
}

ObjectDataSource Update

<%@ Page Language="C#" AutoEventWireup="true"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource ID="sourceEmployees" 
                              runat="server" 
                              InsertMethod="InsertEmployee"
                              SelectMethod="GetEmployees" 
                              TypeName="EmployeeDB" 
                              OnUpdating="sourceEmployees_Updating" 
                              UpdateMethod="UpdateEmployee" 
                              DataObjectTypeName="EmployeeDetails"/>
        <asp:GridView ID="GridView1" 
                      runat="server" 
                      DataSourceID="sourceEmployees" 
                      CellPadding="4" 
                      Font-Names="Verdana" 
                      Font-Size="Small" 
                      ForeColor="#333333" 
                      GridLines="None" 
                      AutoGenerateColumns="False" 
                      EnableSortingAndPagingCallbacks="True" 
                      PageSize="5">
           
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
            
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy" SortExpression="TitleOfCourtesy" />
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
            </Columns>
        </asp:GridView>
        &nbsp; &nbsp;
        &nbsp;&nbsp;<br />
       
    
    </div>
    </form>
</body>
</html>
File: EmployeeDB.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;

public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }

  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
      
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  
</configuration>

Paging, Sorting, and Filtering Data with the ObjectDataSource Control

File: ShowUIPaging.aspx
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show User Interface Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        AllowPaging="true"
        PageSize="3"
        CssClass="products"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="ProductUIPaging"
        SelectMethod="GetProductsDataSet"
        Runat="server" />
    </div>
    </form>
</body>
</html>
File: ProductUIPaging.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class ProductUIPaging
{
    private readonly string _conString;
    public DataSet GetProductsDataSet()
    {
        string commandText = "SELECT Id,Title,Director FROM Products";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, _conString);
        DataSet dstProducts = new DataSet();
        using (dad)
        {
            dad.Fill(dstProducts);
        }
        return dstProducts;
    }
    public ProductUIPaging()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString;
    }
}

File: Web.config
<configuration>
  <connectionStrings>
    <add name="Employees" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>

Using ObjectDataSource

<%@ Page Language="C#" AutoEventWireup="true"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource ID="sourceEmployees" 
                              runat="server" 
                              SelectMethod="GetEmployees"
                              TypeName="EmployeeDB"/>
        <asp:ListBox ID="ListBox1" 
                     runat="server" 
                     DataSourceID="sourceEmployees" 
                     DataTextField="EmployeeID"
                     Width="131px"/>
        <asp:GridView ID="GridView1" 
                      runat="server" 
                      CellPadding="4" 
                      DataSourceID="sourceEmployees"
                      Font-Names="Verdana" 
                      Font-Size="Small" 
                      ForeColor="#333333" 
                      GridLines="None">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
         </div>
    </form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  
</configuration>
File: EmployeeDB.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;

public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }

  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
      
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
}

Using two ObjectDataSource controls in one page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ObjectDataSourceParameters.aspx.cs" Inherits="ObjectDataSourceParameters" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource ID="sourceEmployeesList" 
                              runat="server" 
                              SelectMethod="GetEmployees"
                              TypeName="EmployeeDB"/>
        <asp:ObjectDataSource ID="sourceEmployee" 
                              runat="server" 
                              SelectMethod="GetEmployee"
                              TypeName="EmployeeDB" 
                              OnSelecting="sourceEmployee_Selecting">
                <SelectParameters>
                    <asp:ControlParameter ControlID="lstEmployees" 
                                          Name="employeeID" 
                                          PropertyName="SelectedValue" />
                </SelectParameters>
            </asp:ObjectDataSource>
        <asp:ListBox ID="lstEmployees" 
                     runat="server" 
                     DataSourceID="sourceEmployeesList" 
                     DataTextField="EmployeeID"
                     Width="131px" 
                     AutoPostBack="True" 
                     Height="171px"/>
        <asp:DetailsView ID="DetailsView1" 
                         runat="server" 
                         AutoGenerateRows="False" 
                         BorderStyle="Groove"
                         BorderWidth="2px" 
                         CellPadding="4" 
                         DataSourceID="sourceEmployee" 
                         Font-Names="Verdana"
                         Font-Size="Small" 
                         ForeColor="#333333" 
                         GridLines="None" 
                         Height="50px" 
                         Width="125px">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <CommandRowStyle BackColor="#FFFFC0" Font-Bold="True" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <Fields>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy" SortExpression="TitleOfCourtesy" />
            </Fields>
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:DetailsView>
         </div>
    </form>
</body>
</html>
File: ObjectDataSourceParameters.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ObjectDataSourceParameters : System.Web.UI.Page
{

  protected void sourceEmployee_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
  {
    if (e.InputParameters["employeeID"] == null) e.Cancel = true;
  }
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  
</configuration>
File: EmployeeDB.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;

public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;
  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }
  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }
  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;
  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }
  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
  
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
      return (int)cmd.Parameters["@EmployeeID"].Value;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }

  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;
    try
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
      throw new ApplicationException("Data error.");
    }
    finally
    {
      con.Close();
    }
  }
  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
      
    try 
    {
      con.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.rumandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
        
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
      
      reader.Read();
      EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      reader.Close();
      return emp;
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    ArrayList employees = new ArrayList();
    try 
    {
      con.Open();
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
        employees.Add(emp);
      }
      reader.Close();
      
      return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
      
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.rumandType = CommandType.StoredProcedure;
        
    try 
    {
      con.Open();
      return (int)cmd.ExecuteScalar();
    }
    catch (SqlException err) 
    {
      throw new ApplicationException("Data error.");
    }
    finally 
    {
      con.Close();      
    }
  }
}

ASP.NET Tutorial/ADO.net Database/ObjectDataSource — различия между версиями

Текущая версия на 11:56, 26 мая 2010

Содержание

asp:ObjectDataSource with UpdateParameters

Binding to a DataSet

Bind SqlDataReader with asp:ObjectDataSource

Creating a Customer class to demonstrate the ObjectDataSource control (C#)

Creating a Customer class to demonstrate the ObjectDataSource control (VB)

ObjectDataSource binds DataBound controls such as the GridView, DetailsView, and FormView controls to a component

ObjectDataSource Insert

ObjectDataSource Update

Paging, Sorting, and Filtering Data with the ObjectDataSource Control

Using ObjectDataSource

Using two ObjectDataSource controls in one page

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация

Версия 15:30, 26 мая 2010 (просмотреть исходный код) (обсуждение) ← Предыдущая правка	Текущая версия на 11:56, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)