ASP.NET Tutorial/ADO.net Database/DataRows
Содержание
DataRow States and DataRow Versions
<source lang="csharp">
File: App_Code\Product.cs using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.Configuration; public class Product {
private SqlDataAdapter dad = new SqlDataAdapter(); public DataTable GetAll() { return (DataTable)HttpContext.Current.Session["ProductsToEdit"]; } public void Update(int id, string title, string director) { DataTable products = (DataTable)HttpContext.Current.Session["ProductstoEdit"]; DataRow rowToEdit = products.Rows.Find(id); rowToEdit["title"] = title; rowToEdit["director"] = director; } public void RejectChanges() { DataTable products = (DataTable)HttpContext.Current.Session["ProductstoEdit"]; products.RejectChanges(); } public void AcceptChanges() { DataTable products = (DataTable)HttpContext.Current.Session["ProductstoEdit"]; dad.Update(products); products.AcceptChanges(); } public Product() { string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; dad = new SqlDataAdapter("SELECT Id,Title,Director FROM Products", connectionString); SqlCommandBuilder builder = new SqlCommandBuilder(dad); dad.UpdateBatchSize = 0; HttpContext context = HttpContext.Current; if (context.Session["ProductsToEdit"] == null) { DataTable dtblProducts = new DataTable(); dad.Fill(dtblProducts); dtblProducts.PrimaryKey = new DataColumn[] { dtblProducts.Columns["Id"] }; context.Session["ProductsToEdit"] = dtblProducts; } }
}
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration> File: ShowProduct.aspx <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <!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 btnReject_Click(object sender, EventArgs e) { Product product = new Product(); product.RejectChanges(); grdProducts.DataBind(); } protected void btnAccept_Click(object sender, EventArgs e) { Product product = new Product(); product.AcceptChanges(); grdProducts.DataBind(); }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Product</title>
</head> <body>
<form id="form1" runat="server">
Edit Products
<asp:GridView id="grdProducts" DataSourceID="srcProducts" DataKeyNames="Id" AutoGenerateEditButton="true" Runat="server"> <Columns> <asp:TemplateField> <ItemTemplate> <%# ((DataRowView)Container.DataItem).Row.RowState %> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
<asp:Button id="btnReject" Text="Reject Changes" OnClick="btnReject_Click" Runat="server" /> <asp:Button id="btnAccept" Text="Accept Changes" OnClick="btnAccept_Click" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="Product" SelectMethod="GetAll" UpdateMethod="Update" Runat="server" />
</form>
</body> </html></source>
Selecting DataRows
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Web.Configuration" %> <!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 btnSearch_Click(object sender, EventArgs e) { DataTable dtblProducts = (DataTable)Cache["ProductsToFilter"]; if (dtblProducts == null) { dtblProducts = GetProductsFromDB(); Cache["ProductsToFilter"] = dtblProducts; } string filter = String.Format("Title LIKE "{0}*"", txtTitle.Text); DataRow[] rows = dtblProducts.Select(filter, "Title"); grdProducts.DataSource = rows; grdProducts.DataBind(); } private DataTable GetProductsFromDB() { string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; SqlDataAdapter dad = new SqlDataAdapter("SELECT Title, Director FROM Products", connectionString); DataTable dtblProducts = new DataTable(); dad.Fill(dtblProducts); return dtblProducts; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DataTable Select</title>
</head> <body>
<form id="form1" runat="server">
<asp:TextBox id="txtTitle" Tooltip="Search" Runat="server" /> <asp:Button id="btnSearch" Text="Search" Runat="server" OnClick="btnSearch_Click" />
<asp:GridView id="grdProducts" AutoGenerateColumns="false" Runat="server"> <Columns> <asp:TemplateField HeaderText="Title"> <ItemTemplate> <%# ((DataRow)Container.DataItem)["Title"] %> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Director"> <ItemTemplate> <%# ((DataRow)Container.DataItem)["Director"] %> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>
The DataView object represents an in-memory database view.
<source lang="csharp">
You can use a DataView object to create a sortable, filterable view of a DataTable. <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Web.Configuration" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
void Page_Load() { if (Session["ProductsToSort"] == null) { string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; SqlDataAdapter dad = new SqlDataAdapter("SELECT Id,Title,Director FROM Products", connectionString); DataTable dtblProducts = new DataTable(); dad.Fill(dtblProducts); Session["ProductsToSort"] = dtblProducts.DefaultView; } if (!Page.IsPostBack) BindProducts(); } void BindProducts() { grdProducts.DataSource = Session["ProductsToSort"]; grdProducts.DataBind(); } protected void grdProducts_Sorting(object sender, GridViewSortEventArgs e) { DataView dvwProducts = (DataView)Session["ProductsToSort"]; dvwProducts.Sort = e.SortExpression; BindProducts(); }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DataView</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" AllowSorting="true" OnSorting="grdProducts_Sorting" Runat="server" />
</form>
</body> </html>
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration></source>