ASP.NET Tutorial/ADO.net Database/DataRows

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

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>