ASP.NET Tutorial/Data Binding/GridView
Содержание
- 1 Assign data source to GridView
- 2 DataBinding to GridView (C#)
- 3 Displaying a message when no records match.
- 4 Displaying a template when no records match.
- 5 Editing Data
- 6 Formatting the GridView Control
- 7 GridView DataBind
- 8 GridView with template
- 9 Pageable asp:GridView
- 10 Prevent concurrency conflict
- 11 Set RowStyle and HeaderStyle for GridView
- 12 Sortable GridView
- 13 Use asp:SqlDataSource and asp:GridView to edit database table
- 14 Using Fields with the GridView Control
- 15 Using Programmatic DataBinding
- 16 Using the Null value
- 17 Using the PagerStyle and PagerSettings objects in the GridView control
Assign data source to GridView
<%@ 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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice FROM Products">
</asp:SqlDataSource>
</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>
DataBinding to GridView (C#)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="BasicGridView" %>
<!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:GridView ID="GridView1" runat="server">
</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;
using System.Web.Configuration;
using System.Data.SqlClient;
public partial class BasicGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
string selectSQL = "SELECT ProductID, ProductName, UnitPrice FROM Products";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
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>
Displaying a message when no records match.
<%@ 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 btnSubmit_Click(object sender, EventArgs e)
{
grdProducts.Visible = true;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Empty Data Text</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox
id="txtTitle"
Runat="server" />
<asp:Button
id="btnSubmit"
Text="Search"
OnClick="btnSubmit_Click"
Runat="server" />
<hr />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
EmptyDataText="No Matching Products!"
Visible="false"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Title,Director FROM Products WHERE Title LIKE @Title+"%""
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="Title"
ControlID="txtTitle"
PropertyName="Text" />
</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>
Displaying a template when no records match.
<%@ 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 btnSubmit_Click(object sender, EventArgs e)
{
grdProducts.Visible = true;
}
protected void dtlProduct_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
txtTitle.Text = (string)e.Values["Title"];
grdProducts.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Empty Data Template</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox
id="txtTitle"
Runat="server" />
<asp:Button
id="btnSubmit"
Text="Search"
OnClick="btnSubmit_Click"
Runat="server" />
<hr />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
Visible="false"
Runat="server">
<EmptyDataTemplate>
No matching products were found.
<asp:DetailsView
id="dtlProduct"
DataSourceID="srcProducts"
DefaultMode="Insert"
AutoGenerateInsertButton="true"
AutoGenerateRows="false"
Runat="server"
OnItemInserted="dtlProduct_ItemInserted">
<Fields>
<asp:BoundField
HeaderText="Title:"
DataField="Title" />
<asp:BoundField
HeaderText="Director:"
DataField="Director" />
</Fields>
</asp:DetailsView>
</EmptyDataTemplate>
</asp:GridView>
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Title,Director FROM Products WHERE Title LIKE @Title+"%""
InsertCommand="INSERT Products (Title, Director)
VALUES (@Title, @Director)"
Runat="server">
<SelectParameters>
<asp:ControlParameter
Name="Title"
ControlID="txtTitle"
PropertyName="Text" />
</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>
Editing Data
<%@ Page Language="C#" MaintainScrollPositionOnPostback="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 id="Head1" runat="server">
<title>Edit GridView</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
DataKeyNames="Id"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id,Title,Director FROM Products"
UpdateCommand="UPDATE Products SET Title=@Title, Director=@Director
WHERE Id=@Id"
DeleteCommand="DELETE Products WHERE Id=@Id"
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>
Formatting the GridView Control
AlternatingRowStyle: format every other row.
FooterStyle: format the footer row.
HeaderStyle: format the header row.
PagerStyle: format the pager row.
RowStyle: format each row.
SelectedRowStyle: format the selected row.
GridLines: Possible values are Both, Vertical, Horizontal, and None.
ShowFooter: When True, renders a footer row at the bottom of the GridView.
ShowHeader: When True, renders a header row at the top of the GridView.
<%@ 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">
<style type="text/css">
.grid
{
font:16px Arial, Sans-Serif;
}
.grid td, .grid th
{
padding:10px;
}
.header
{
text-align:left;
color:white;
background-color:blue;
}
.row td
{
border-bottom:solid 1px blue;
}
.alternating
{
background-color:#eeeeee;
}
.alternating td
{
border-bottom:solid 1px blue;
}
</style>
<title>Format Grid</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
GridLines="None"
CssClass="grid"
HeaderStyle-CssClass="header"
RowStyle-CssClass="row"
AlternatingRowStyle-CssClass="alternating"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id,Title,Director 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 DataBind
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="GridViewDataBind" %>
<!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:GridView ID="grid" runat="server">
</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;
using System.Data.SqlClient;
public partial class GridViewDataBind : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
string sql = "SELECT EmployeeID, FirstName, LastName, Title, City FROM Employees";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
reader.Close();
}
finally
{
con.Close();
}
}
}
}
GridView with template
<%@ 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:GridView ID="GridView2"
runat="server"
AutoGenerateColumns="False"
DataKeyNames="ProductID"
DataSourceID="sourceProducts">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" ReadOnly="True" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="UnitPrice" HeaderText="Price" />
<asp:TemplateField HeaderText="Status">
<ItemStyle Width="100px" />
<ItemTemplate>
<small>
<b>In Stock:</b> <%# Eval("UnitsInStock") %><br />
<b>On Order:</b> <%# Eval("UnitsOnOrder") %><br />
<b>Reorder:</b> <%# Eval("ReorderLevel") %>
<br /><br />
<asp:LinkButton runat="server"
Text="Edit"
CommandName="Edit"
ID="Linkbutton1" />
</small>
</ItemTemplate>
<EditItemTemplate>
<small>
<b>In Stock:</b> <%# Eval("UnitsInStock") %><br />
<b>On Order:</b> <%# Eval("UnitsOnOrder") %><br /><br />
<b>Reorder:</b> <asp:TextBox Text="<%# Bind("ReorderLevel") %>" Width="25px" runat="server" id="txtReorder" />
<asp:RangeValidator id="rngValidator" MinimumValue="0" MaximumValue="100" ControlToValidate="txtReorder" runat="server" ErrorMessage="Value out of range." Type="Integer"></asp:RangeValidator>
<br /><br />
<asp:LinkButton runat="server"
Text="Update"
CommandName="Update"
ID="Linkbutton1" />
<asp:LinkButton runat="server"
Text="Cancel"
CommandName="Cancel"
ID="Linkbutton2"
CausesValidation="False" />
</small>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
</asp:GridView>
<asp:SqlDataSource ID="sourceProducts" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder,ReorderLevel FROM Products" UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=CONVERT(money,@UnitPrice), ReorderLevel=@ReorderLevel WHERE ProductID=@ProductID">
</asp:SqlDataSource>
</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>
Pageable asp:GridView
<%@ Page Language="VB" AutoEventWireup="false"%>
<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="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1"
runat="server"
AllowPaging="True"
BackColor="White"
BorderColor="#DEDFDE"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4"
DataSourceID="SqlDataSource1"
ForeColor="Black"
GridLines="Vertical"
PageSize="2">
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Prevent concurrency conflict
The ConflictDetection property can have one of the following two values:
CompareAllValues
OverwriteChanges
<%@ 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 srcProducts_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows == 0)
lblMessage.Text = "Could not update record";
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Concurrency</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblMessage" EnableViewState="false" runat="server" />
<asp:GridView
id="grdProducts"
DataSourceID="srcProducts"
DataKeyNames="Id"
AutoGenerateEditButton="true"
Runat="server" />
<asp:SqlDataSource
id="srcProducts"
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}"
ConnectionString="<%$ ConnectionStrings:Products %>"
SelectCommand="SELECT Id,Title,Director FROM Products"
UpdateCommand="UPDATE Products SET Title=@Title, Director=@Director
WHERE Id=@original_Id AND Title=@original_Title AND Director=@original_Director"
Runat="server"
OnUpdated="srcProducts_Updated" />
</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>
Set RowStyle and HeaderStyle for GridView
Property Description
DataField Identifies the field
DataFormatString Formats the field.
ApplyFormatInEditMode format the value even when editing.
The default is false,
FooterText, HeaderText text in header and footer
and HeaderImageUrl
ReadOnly it prevents the value from being changed in edit mode.
InsertVisible If true, it prevents the value being set in insert mode.
Visible
SortExpression Sorts your results based on one or more columns.
HtmlEncode If true (the default), all text will be HTML encoded to prevent special characters from mangling the page.
NullDisplayText Displays the text that will be shown for a null value.
The default is an empty string
ConvertEmptyStringToNull If true, converts all empty strings to null values.
ControlStyle Configures the appearance
HeaderStyle
FooterStyle
and ItemStyle
<%@ 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>
<table>
<tr>
<td valign="top" style="width: 328px">
Global style settings:<br />
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="false">
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="UnitPrice" HeaderText="Price" />
</Columns>
</asp:GridView>
</td>
<td valign="top" style="width: 358px">
Column-specific styles:<br />
<asp:GridView ID="GridView2"
runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="false" >
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name">
<ItemStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
</asp:BoundField>
<asp:BoundField DataField="UnitPrice" HeaderText="Price" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT ProductID, ProductName, UnitPrice FROM Products"></asp:SqlDataSource>
</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>
Sortable GridView
<%@ Page Language="VB" AutoEventWireup="false" %>
<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="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Books]"></asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1"
runat="server"
AllowSorting="True"
AutoGenerateColumns="False"
BackColor="White"
BorderColor="#DEDFDE"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4"
DataKeyNames="BookID"
DataSourceID="SqlDataSource1"
ForeColor="Black"
GridLines="Vertical">
<FooterStyle BackColor="#CCCC99" />
<Columns>
<asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" ReadOnly="True"
SortExpression="BookID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" />
<asp:BoundField DataField="YearPublished" HeaderText="YearPublished" />
<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
<asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="LastReadOn" />
<asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="PageCount" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Use asp:SqlDataSource and asp:GridView to edit database table
Each column can be any of several column types
The order of your column tags determines the left-to-right order of columns in the GridView.
Class Description
BoundField text
ButtonField button
CheckBoxField check box
It¡�s used automatically for true/false fields (in SQL Server, these are fields that use the bit data type).
CommandField provides selection or editing buttons.
HyperLinkField a hyperlink.
ImageField image data from a binary field.
TemplateField specify multiple fields, custom controls, and arbitrary HTML
<%@ Page Language="VB" AutoEventWireup="false"%>
<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="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Books] WHERE [BookID] = @BookID"
InsertCommand="INSERT INTO [Books] ([Title], [Author], [YearPublished], [Price], [LastReadOn], [PageCount]) VALUES (@Title, @Author, @YearPublished, @Price, @LastReadOn, @PageCount)"
SelectCommand="SELECT * FROM [Books]"
UpdateCommand="UPDATE [Books] SET [Title] = @Title, [Author] = @Author, [YearPublished] = @YearPublished, [Price] = @Price, [LastReadOn] = @LastReadOn, [PageCount] = @PageCount WHERE [BookID] = @BookID">
<DeleteParameters>
<asp:Parameter Name="BookID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="Author" Type="String" />
<asp:Parameter Name="YearPublished" Type="Int32" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="LastReadOn" Type="DateTime" />
<asp:Parameter Name="PageCount" Type="Int32" />
<asp:Parameter Name="BookID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Title" Type="String" />
<asp:Parameter Name="Author" Type="String" />
<asp:Parameter Name="YearPublished" Type="Int32" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="LastReadOn" Type="DateTime" />
<asp:Parameter Name="PageCount" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="BookID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BookID" HeaderText="Book ID" InsertVisible="False" ReadOnly="True"
SortExpression="BookID" />
<asp:TemplateField HeaderText="Title" SortExpression="Title">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text="<%# Bind("Title") %>"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text="<%# Bind("Title") %>"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" />
<asp:BoundField DataField="YearPublished" HeaderText="Published" SortExpression="YearPublished" />
<asp:TemplateField HeaderText="Price" SortExpression="Price">
<EditItemTemplate>
$<asp:TextBox ID="TextBox2" runat="server" Text="<%# Bind("Price") %>" Columns="10"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox2"
Display="Dynamic" ErrorMessage="You must enter a price."></asp:RequiredFieldValidator>
<asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="TextBox2"
Display="Dynamic" ErrorMessage="You must enter a valid numeric value greater than or equal to zero."
Operator="GreaterThanEqual" Type="Double" ValueToCompare="0"></asp:CompareValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text="<%# Bind("Price", "{0:c}") %>"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="LastReadOn" HeaderText="LastReadOn" SortExpression="Last Read" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="PageCount" HeaderText="PageCount" SortExpression="Pages" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle Font-Bold="True"/>
<PagerStyle HorizontalAlign="Right" />
<HeaderStyle Font-Bold="True"/>
<AlternatingRowStyle BackColor="White" />
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle Font-Bold="True" ForeColor="White" />
<PagerStyle HorizontalAlign="Right" />
<HeaderStyle Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Using Fields with the GridView Control
BoundField: display the value of a data item as text.
CheckBoxField: display the value of a data item as a check box.
CommandField: display links for editing, deleting, and selecting rows.
ButtonField: display the value of a data item as a button (image button, link button, or push button).
HyperLinkField: display the value of a data item as a link.
ImageField: display the value of a data item as an image.
TemplateField: customize the appearance of a data item.
Using Programmatic DataBinding
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Drawing.Text" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void Page_Load()
{
if (!Page.IsPostBack)
{
InstalledFontCollection fonts = new InstalledFontCollection();
GridView1.DataSource = fonts.Families;
GridView1.DataBind();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Fonts</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView
id="GridView1"
Runat="server" />
</div>
</form>
</body>
</html>
Using the Null value
<!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"
PageSize="10">
<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 HeaderText="CompanyName"
DataField="CompanyName"
SortExpression="CompanyName"/>
<asp:BoundField HeaderText="ContactName"
DataField="ContactName"
SortExpression="ContactName"/>
<asp:BoundField HeaderText="ContactTitle"
DataField="ContactTitle"
SortExpression="ContactTitle"/>
<asp:BoundField HeaderText="Address"
DataField="Address"
SortExpression="Address"/>
<asp:BoundField HeaderText="City"
DataField="City"
SortExpression="City"/>
<asp:BoundField HeaderText="Region"
NullDisplayText="N/A"
DataField="Region"
SortExpression="Region"/>
<asp:BoundField HeaderText="PostalCode"
DataField="PostalCode"
SortExpression="PostalCode"/>
<asp:BoundField HeaderText="Country"
DataField="Country"
SortExpression="Country"/>
<asp:BoundField HeaderText="Phone"
DataField="Phone"
SortExpression="Phone"/>
<asp:BoundField HeaderText="Fax"
DataField="Fax"
SortExpression="Fax"/>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1"
Runat="server"
SelectCommand="SELECT * FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues" EnableCaching="True"
CacheKeyDependency="MyKey"
CacheDuration="Infinite">
</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>
Using the PagerStyle and PagerSettings objects 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" PageSize="10">
<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:BoundField HeaderText="CompanyName" DataField="CompanyName"
SortExpression="CompanyName"></asp:BoundField>
<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" 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"
ConflictDetection="CompareAllValues" EnableCaching="True"
CacheKeyDependency="MyKey" CacheDuration="Infinite">
</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>