ASP.Net/ADO.net Database/GridView
Содержание
- 1 Adding a delete link to the GridView
- 2 Adding Indicators to display Sorting Direction
- 3 Change row style for higher value
- 4 Checking for Update errors using the RowUpdated event (C#)
- 5 Checking for Update errors using the RowUpdated event (VB)
- 6 Displaying data in database table with the GridView control.
- 7 GridView custom paging with ObjectDataSource
- 8 GridView format event
- 9 GridView selection style
- 10 GridView Summaries
- 11 Highlight Rows
- 12 Numeric Format Strings
- 13 Paging Through Data
- 14 Selecting Data
- 15 Sortable GridView
- 16 The GridView control supports programmatic databinding
- 17 Time and Date Format Strings
- 18 Turning off AutoGenerateColumns in the GridView control
- 19 Turn off the GridLine
- 20 Using Data Keys
- 21 Using the RowCreated Event to programmatically change the style
- 22 Using the RowDeleted event to catch SQL errors (C#)
- 23 Using the RowDeleted event to catch SQL errors (VB)
Adding a delete link to the GridView
<!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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page"
Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"
Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Adding Indicators to display Sorting Direction
<%@ Page Language="C#" %>
<script runat="server">
void deptView_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
string imageUrl = (deptView.SortDirection==SortDirection.Ascending ?"Asc.gif" :"Desc.gif");
for(int i=0; i<deptView.Columns.Count; i++)
{
string columnExpression = deptView.Columns[i].SortExpression;
if (columnExpression != "" && columnExpression == deptView.SortExpression)
{
Image img = new Image();
img.ImageUrl =imageUrl;
e.Row.Cells[i].Controls.Add(img);
}
}
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Adding Indicators to display Sorting Direction</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="deptView"
AllowSorting="true"
runat="server"
AutoGenerateColumns="false"
DataSourceID="deptSource"
OnRowCreated="deptView_RowCreated">
<Columns>
<asp:BoundField HeaderText="Department ID"
DataField="DepartmentID"
SortExpression="DepartmentID" />
<asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name" />
<asp:BoundField HeaderText="Group Name" DataField="GroupName" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="deptSource"
Runat="server"
SelectCommandType="Text"
SelectCommand="Select DepartmentID, Name, GroupName, ModifiedDate from HumanResources.Department"
ConnectionString="<%$ConnectionStrings:AdventureWorks%>">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
Change row style for higher value
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="FormatHighPrices" %>
<!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:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice FROM Products">
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
AutoGenerateColumns="False"
DataSourceID="SqlDataSource1"
OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="UnitPrice"
HtmlEncode="false"
HeaderText="Price"
DataFormatString="{0:C}" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
File: Default.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 FormatHighPrices : System.Web.UI.Page
{
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
decimal price = (decimal)DataBinder.Eval(e.Row.DataItem, "UnitPrice");
if (price > 50)
{
e.Row.BackColor = System.Drawing.Color.Maroon;
e.Row.ForeColor = System.Drawing.Color.White;
e.Row.Font.Bold = true;
}
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
Checking for Update errors using the RowUpdated event (C#)
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
if (e.Exception != null)
{
this.lblErrorMessage.Text = e.Exception.Message;
}
}
</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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true"
OnRowUpdated="GridView1_RowUpdated">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID" DataField="CustomerID"
SortExpression="CustomerID" Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.foo.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="lblErrorMessage" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Checking for Update errors using the RowUpdated event (VB)
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowUpdated(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs)
If (Not IsDBNull(e.Exception)) Then
Me.lblErrorMessage.Text = e.Exception.Message
End If
End Sub
</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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true"
OnRowUpdated="GridView1_RowUpdated">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID" DataField="CustomerID"
SortExpression="CustomerID" Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="lblErrorMessage" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Displaying data in database table with the GridView control.
<%@ 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"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id,Title,Director,InStocks,DateReleased
FROM Products"
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>
GridView custom paging with 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">
<asp:ObjectDataSource ID="sourceEmployees"
runat="server"
SelectMethod="GetEmployees"
TypeName="EmployeeDB"
EnablePaging="True"
SelectCountMethod="CountEmployees">
</asp:ObjectDataSource>
<asp:GridView ID="GridView1"
runat="server"
CellPadding="4"
DataSourceID="sourceEmployees"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Black"
GridLines="None"
AutoGenerateColumns="False"
AllowPaging="True"
PageSize="5">
<FooterStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="Pink" ForeColor="Black" />
<PagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="White" 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" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: EmployeeDB
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 EmployeeDetails[] GetEmployees(string sortExpression)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.rumandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
con.Open();
adapter.Fill(ds, "Employees");
}
catch (SqlException err)
{
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
DataView view = ds.Tables[0].DefaultView;
view.Sort = sortExpression;
ArrayList employees = new ArrayList();
foreach (DataRowView row in view)
{
EmployeeDetails emp = new EmployeeDetails(
(int)row["EmployeeID"], (string)row["FirstName"],
(string)row["LastName"], (string)row["TitleOfCourtesy"]);
employees.Add(emp);
}
return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
}
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();
}
}
public EmployeeDetails[] GetEmployees(int startRowIndex, int maximumRows)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetEmployeePage", con);
cmd.rumandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Int, 4));
cmd.Parameters["@Start"].Value = startRowIndex + 1;
cmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int, 4));
cmd.Parameters["@Count"].Value = maximumRows;
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();
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
GridView format event
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewFormattingEvents.aspx.cs" Inherits="GridViewFormattingEvents" %>
<!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:SqlDataSource ID="sourceEmployees"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy, City FROM Employees" >
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="sourceEmployees"
Font-Names="Verdana"
Font-Size="Small"
GridLines="Horizontal"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID"
EnableSortingAndPagingCallbacks="True"
PageSize="5"
HorizontalAlign="Justify"
OnRowCreated="GridView1_RowCreated">
<HeaderStyle BorderColor="Black"
BorderStyle="Solid"
BorderWidth="2px"
BackColor="Bisque"
HorizontalAlign="Left" />
<Columns>
<asp:BoundField DataField="EmployeeID"
HeaderText="ID"
InsertVisible="False"
ReadOnly="True"
SortExpression="EmployeeID" >
<ItemStyle HorizontalAlign="Left" Width="75px" />
</asp:BoundField>
<asp:BoundField DataField="FirstName"
HeaderText="First Name"
SortExpression="FirstName" >
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="LastName"
HeaderText="Last Name"
SortExpression="LastName" >
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="TitleOfCourtesy"
HeaderText="Title Of Courtesy"
SortExpression="TitleOfCourtesy" >
<ItemStyle Width="75px" />
</asp:BoundField>
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" >
<ItemStyle Width="100px" />
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: GridViewFormattingEvents.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 GridViewFormattingEvents : System.Web.UI.Page
{
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string title = (string)DataBinder.Eval(e.Row.DataItem, "TitleOfCourtesy");
if (title == "Ms." || title == "Mrs.")
{
e.Row.BackColor = System.Drawing.Color.LightPink;
e.Row.ForeColor = System.Drawing.Color.Maroon;
}
else if (title == "Mr.")
{
e.Row.BackColor = System.Drawing.Color.LightCyan;
e.Row.ForeColor = System.Drawing.Color.DarkBlue;
}
}
}
}
File: Web.config
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
GridView selection style
<%@ 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:GridView ID="GridView1"
runat="server"
CellPadding="4"
DataSourceID="sourceEmployees"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Black"
GridLines="None"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID">
<FooterStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="Pink" ForeColor="Black" />
<PagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowSelectButton="True"
ButtonType="Image"/>
<asp:CommandField ButtonType="Button" ShowSelectButton="True" />
<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" />
</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;
con.Open();
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["@EmployeeID"].Value;
}
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;
con.Open();
cmd.ExecuteNonQuery();
}
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;
con.Open();
cmd.ExecuteNonQuery();
}
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;
con.Open();
cmd.ExecuteNonQuery();
}
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;
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;
}
public EmployeeDetails[] GetEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.rumandType = CommandType.StoredProcedure;
ArrayList employees = new ArrayList();
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));
}
public int CountEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("CountEmployees", con);
cmd.rumandType = CommandType.StoredProcedure;
con.Open();
return (int)cmd.ExecuteScalar();
}
}
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>
GridView Summaries
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="GridViewSummaries" %>
<!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:SqlDataSource ID="sourceProducts"
runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products">
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
AutoGenerateColumns="False"
CellPadding="4"
DataKeyNames="ProductID"
DataSourceID="sourceProducts"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Black"
GridLines="None"
AllowPaging="True"
OnDataBound="GridView1_DataBound"
ShowFooter="True">
<FooterStyle BackColor="White" Font-Bold="True" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="Pink" ForeColor="Black" />
<PagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="Product" SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" DataFormatString="{0:C}" HeaderText="Price"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="Units In Stock" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: Default.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 GridViewSummaries : System.Web.UI.Page
{
protected void GridView1_DataBound(object sender, EventArgs e)
{
decimal valueInStock = 0;
foreach (GridViewRow row in GridView1.Rows)
{
decimal price = Decimal.Parse(row.Cells[2].Text.Substring(1));
int unitsInStock = Int32.Parse(row.Cells[3].Text);
valueInStock += price * unitsInStock;
}
GridViewRow footer = GridView1.FooterRow;
footer.Cells[0].ColumnSpan = 3;
footer.Cells[0].HorizontalAlign = HorizontalAlign.Center;
footer.Cells.RemoveAt(2);
footer.Cells.RemoveAt(1);
footer.Cells[0].Text = "Total value in stock (on this page): " + valueInStock.ToString("C");
}
}
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>
Highlight Rows
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
protected void grdProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
decimal totals = (decimal)DataBinder.Eval(e.Row.DataItem, "BoxOfficeTotals");
if (totals > 30)
e.Row.BackColor = System.Drawing.Color.Yellow;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Highlight Rows</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
OnRowDataBound="grdProducts_RowDataBound"
AutoGenerateColumns="false"
Runat="server">
<Columns>
<asp:BoundField
DataField="Title"
HeaderText="Title" />
<asp:BoundField
DataField="BoxOfficeTotals"
DataFormatString="{0:c}"
HtmlEncode="false"
HeaderText="Box Office Totals" />
</Columns>
</asp:GridView>
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT * FROM Products"
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>
Numeric Format Strings
Type Format String Example
Currency {0:C} $1,234.50
Scientific {0:E} 1.234.50E+004
Percentage {0:P} 45.6%
Fixed Decimal {0:F?} {0:F3} would be 123.400.
{0:F0} would be 123.
Paging Through Data
<%@ 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>Page Grid</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
AllowPaging="true"
PageSize="3"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id,Title,Director FROM Products"
Runat="server" />
</form>
</body>
</html>
Selecting Data
<%@ 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" >
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdProductCategories"
DataKeyNames="Id"
DataSourceID="srcProductCategories"
AutoGenerateSelectButton="true"
SelectedRowStyle-CssClass="selectedRow"
Runat="server" />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
Runat="server" />
<asp:SqlDataSource
id="srcProductCategories"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id, Name FROM ProductCategories"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Title,Director FROM Products
WHERE CategoryId=@CategoryId"
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="CategoryId"
ControlID="grdProductCategories"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</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>
Sortable GridView
<%@ 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:SqlDataSource ID="sourceProducts"
runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice FROM Products">
</asp:SqlDataSource>
<asp:GridView ID="gridProducts"
runat="server"
AllowSorting="True"
DataSourceID="sourceProducts"
AllowPaging="True">
<RowStyle BackColor="White" ForeColor="Red" />
<SelectedRowStyle BackColor="White" Font-Bold="True" ForeColor="Black" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
The GridView control supports programmatic databinding
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void Page_Load()
{
List<string> shoppingList = new List<string>();
shoppingList.Add("A");
shoppingList.Add("B");
shoppingList.Add("C");
shoppingList.Add("D");
grdShoppingList.DataSource = shoppingList;
grdShoppingList.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Shopping List</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdShoppingList"
Runat="server" />
</div>
</form>
</body>
</html>
Time and Date Format Strings
Type Format String Syntax Example
Short Date {0:d} M/d/yyyy 10/30/2008
Long Date {0:D} dddd, MMMM dd, Monday, January 30,
yyyy 2008
Long Date {0:f } dddd, MMMM dd, Monday, January 30,
and Short Time yyyy HH:mm aa 2008 10:00 AM
Long Date {0:F} dddd, MMMM dd, Monday, January 30
and Long Time yyyy HH:mm:ss aa 2008 10:00:23 AM
ISO Sortable {0:s} yyyy-MM-ddTHH:mm:ss 2008-01-30T10:00:23
Month and Day {0:M} MMMM dd January 30
General {0:G} M/d/yyyy HH:mm:ss aa 10/30/2008
Turning off AutoGenerateColumns in the GridView control
<!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:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.foo.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Turn off the GridLine
<%@ 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:SqlDataSource ID="sourceProducts"
runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products">
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
AutoGenerateColumns="False"
CellPadding="4"
DataKeyNames="ProductID"
DataSourceID="sourceProducts"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Black"
GridLines="None"
AllowPaging="True">
<FooterStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="Pink" ForeColor="Black" />
<PagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="White" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="ProductID"
HeaderText="ID"
InsertVisible="False"
ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName"
HeaderText="Product"
SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice"
DataFormatString="{0:C}"
HeaderText="Price"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock"
HeaderText="UnitsInStock"
SortExpression="Units In Stock" />
</Columns>
</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>
Using Data Keys
<%@ 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" >
<body>
<form id="form1" runat="server">
<asp:GridView
id="grdEmployees"
DataSourceID="srcEmployees"
DataKeyNames="LastName,FirstName"
AutoGenerateSelectButton="true"
SelectedRowStyle-CssClass="selectedRow"
Runat="server" />
<asp:DetailsView
id="dtlEmployees"
DataSourceID="srcEmployeeDetails"
Runat="server" />
<asp:SqlDataSource
id="srcEmployees"
ConnectionString="<%$ ConnectionStrings:Employees %>"
SelectCommand="SELECT LastName,FirstName
FROM Employees"
Runat="server" />
<asp:SqlDataSource
id="srcEmployeeDetails"
ConnectionString="<%$ ConnectionStrings:Employees %>"
SelectCommand="SELECT * FROM Employees
WHERE FirstName=@FirstName AND LastName=@LastName"
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="FirstName"
ControlID="grdEmployees"
PropertyName="SelectedDataKey("FirstName")" />
<asp:ControlParameter
Name="LastName"
ControlID="grdEmployees"
PropertyName="SelectedDataKey("LastName")" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
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 the RowCreated Event to programmatically change the style
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Using the RowCreated Event to programmatically change the style</title>
<script runat="server">
protected void gridProducts_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
int daysToManufacture = (int)DataBinder.Eval(e.Row.DataItem, "DaysToManufacture");
if (daysToManufacture == 0)
{
e.Row.BackColor = System.Drawing.Color.LightPink;
e.Row.ForeColor = System.Drawing.Color.Maroon;
}
else if (daysToManufacture == 1)
{
e.Row.BackColor = System.Drawing.Color.LightCyan;
e.Row.ForeColor = System.Drawing.Color.DarkBlue;
}
else
{
e.Row.BackColor = System.Drawing.Color.LightGray;
e.Row.ForeColor = System.Drawing.Color.Red;
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="productsSource" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select ProductID, Name, ProductNumber, DaysToManufacture from Production.Product">
</asp:SqlDataSource>
<asp:GridView runat="server" ID="gridProducts" DataSourceID="productsSource"
AutoGenerateColumns="false" OnRowCreated="gridProducts_RowCreated">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="ProductNumber" HeaderText="Number" />
<asp:BoundField DataField="DaysToManufacture" HeaderText="Days To Manufacture" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Using the RowDeleted event to catch SQL errors (C#)
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
if (e.Exception != null)
{
this.lblErrorMessage.Text = e.Exception.Message;
e.ExceptionHandled = true;
}
}
protected void SqlDataSource1_Deleted(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.Exception != null)
{
this.lblErrorMessage.Text = e.Exception.Message;
e.ExceptionHandled = true;
}
}
</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:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
AllowSorting="True" AllowPaging="True"
AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" OnRowDeleted="GridView1_RowDeleted">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page" Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="CustomerID" DataField="CustomerID"
SortExpression="CustomerID" Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString="http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
DeleteCommand="DELETE From Customers WHERE (CustomerID = @CustomerID)"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID" OnDeleted="SqlDataSource1_Deleted">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="lblErrorMessage" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>
Using the RowDeleted event to catch SQL errors (VB)
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As GridViewDeletedEventArgs)
If (Not IsDBNull(e.Exception)) Then
Me.lblErrorMessage.Text = e.Exception.Message
e.ExceptionHandled = True
End If
End Sub
Protected Sub SqlDataSource1_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
If (e.Exception IsNot Nothing) Then
Me.lblErrorMessage.Text = e.Exception.Message
e.ExceptionHandled = True
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1"
Runat="server"
DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AllowSorting="True"
AllowPaging="True"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
OnRowDeleted="GridView1_RowDeleted">
<PagerStyle HorizontalAlign="Center"></PagerStyle>
<PagerSettings Position="TopAndBottom"
FirstPageText="Go to the first page"
LastPageText="Go to the last page"
Mode="NextPreviousFirstLast">
</PagerSettings>
<Columns>
<asp:BoundField ReadOnly="True"
HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"
Visible="False"></asp:BoundField>
<asp:HyperLinkField HeaderText="CompanyName"
DataNavigateUrlFields="CustomerID,Country" SortExpression="CompanyName"
DataNavigateUrlFormatString=
"http://www.yourServer.ru/Customer.aspx?id={0}&country={1}"
DataTextField="CompanyName">
</asp:HyperLinkField>
<asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" NullDisplayText="N/A"
DataField="Region" SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
DeleteCommand="DELETE From Customers WHERE (CustomerID = @CustomerID)"
UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
[ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region,
[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
[Fax] = @Fax WHERE [CustomerID] = @original_CustomerID" OnDeleted="SqlDataSource1_Deleted">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="Address"></asp:Parameter>
<asp:Parameter Type="String" Name="City"></asp:Parameter>
<asp:Parameter Type="String" Name="Region"></asp:Parameter>
<asp:Parameter Type="String" Name="PostalCode"></asp:Parameter>
<asp:Parameter Type="String" Name="Country"></asp:Parameter>
<asp:Parameter Type="String" Name="Phone"></asp:Parameter>
<asp:Parameter Type="String" Name="Fax"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="lblErrorMessage" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
File: Web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="AppConnectionString1"
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories>
<add directoryName="VB"></add>
<add directoryName="CS"></add>
</codeSubDirectories>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<authentication mode="Windows"></authentication>
<identity impersonate="true"/>
</system.web>
</configuration>