ASP.NET Tutorial/ADO.net Database/ObjectDataSource — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 18:30, 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
<source lang="csharp">
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">
<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>
</form>
</body> </html></source>
Binding to a DataSet
<source lang="csharp">
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">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductDataReader" SelectMethod="GetProducts" Runat="server" />
</form>
</body> </html></source>
Bind SqlDataReader with asp:ObjectDataSource
<source lang="csharp">
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">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductDataReader" SelectMethod="GetProducts" Runat="server" />
</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></source>
Creating a Customer class to demonstrate the ObjectDataSource control (C#)
<source lang="csharp">
<!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">
<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>
</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 }
}</source>
Creating a Customer class to demonstrate the ObjectDataSource control (VB)
<source lang="csharp">
<!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">
<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>
</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</source>
ObjectDataSource binds DataBound controls such as the GridView, DetailsView, and FormView controls to a component
<source lang="csharp">
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">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductCollection" SelectMethod="GetProducts" Runat="server" />
</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></source>
ObjectDataSource Insert
<source lang="csharp">
<%@ 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">
<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>
</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(); } }
}</source>
ObjectDataSource Update
<source lang="csharp">
<%@ 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">
<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>
</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></source>
Paging, Sorting, and Filtering Data with the ObjectDataSource Control
<source lang="csharp">
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">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" AllowPaging="true" PageSize="3" CssClass="products" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductUIPaging" SelectMethod="GetProductsDataSet" Runat="server" />
</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></source>
Using ObjectDataSource
<source lang="csharp">
<%@ 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">
<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>
</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(); } }
}</source>
Using two ObjectDataSource controls in one page
<source lang="csharp">
<%@ 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">
<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>
</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(); } }
}</source>