ASP.NET Tutorial/ADO.net Database/DataSet

Материал из .Net Framework эксперт
Версия от 14:56, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Create DataSet by your own

   <source lang="csharp">

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DataSetBinding" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> 

   <title>Untitled Page</title>

</head> <body> 

   <form id="form1" runat="server">
<asp:ListBox ID="lstUser" runat="server" Height="152px" Width="192px"></asp:ListBox>
   </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 DataSetBinding : System.Web.UI.Page { 

   protected void Page_Load(object sender, EventArgs e)
   {
       DataSet dsInternal = new DataSet();
       dsInternal.Tables.Add("Users");
       dsInternal.Tables["Users"].Columns.Add("Name");
       dsInternal.Tables["Users"].Columns.Add("Country");
       DataRow rowNew = dsInternal.Tables["Users"].NewRow();
       rowNew["Name"] = "A";
       rowNew["Country"] = "USA";
       dsInternal.Tables["Users"].Rows.Add(rowNew);
       rowNew = dsInternal.Tables["Users"].NewRow();
       rowNew["Name"] = "B";
       rowNew["Country"] = "Canada";
       dsInternal.Tables["Users"].Rows.Add(rowNew);
       rowNew = dsInternal.Tables["Users"].NewRow();
       rowNew["Name"] = "C";
       rowNew["Country"] = "Japan";
       dsInternal.Tables["Users"].Rows.Add(rowNew);
       lstUser.DataSource = dsInternal.Tables["Users"];
       lstUser.DataTextField = "Name";
       lstUser.DataSource = dsInternal;
       lstUser.DataMember = "Users";
       lstUser.DataTextField = "Name";
       this.DataBind();
   }

}</source>


Fill a DataSet

   <source lang="csharp">

<%@ Page language="c#" Inherits="FillDataSet" CodeFile="Default.aspx.cs" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> 

 <HEAD>
   <title>FillDataSet</title>
   </HEAD>
 <body>
   <form id="Form1" method="post" runat="server">

           <asp:Repeater id="Repeater1" runat="server">
             <HeaderTemplate>

Repeater

             </HeaderTemplate>
             <ItemTemplate>
  • <%# DataBinder.Eval(Container.DataItem, "TitleOfCourtesy") %> <%# DataBinder.Eval(Container.DataItem, "LastName") %> , <%# DataBinder.Eval(Container.DataItem, "FirstName") %> 
    •              </ItemTemplate>
    </asp:Repeater>

    foreach approach

    <asp:Literal id="HtmlContent" runat="server"></asp:Literal>    		 
       </form>
 </body>

    </HTML> File: Default.aspx.cs using System; using System.Collections; using System.ruponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Text; using System.Data.SqlClient; public partial class FillDataSet : System.Web.UI.Page { 

     protected void Page_Load(object sender, System.EventArgs e)
 {
   string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
   SqlConnection con = new SqlConnection(connectionString);
   string sql = "SELECT * FROM Employees";
   SqlDataAdapter da = new SqlDataAdapter(sql, con);
   DataSet ds = new DataSet();
   da.Fill(ds, "Employees");
   StringBuilder htmlStr = new StringBuilder("");
   foreach (DataRow dr in ds.Tables["Employees"].Rows)
   {
    htmlStr.Append("
  • "); htmlStr.Append(dr["TitleOfCourtesy"].ToString()); htmlStr.Append(" "); htmlStr.Append(dr["LastName"].ToString()); htmlStr.Append(", "); htmlStr.Append(dr["FirstName"].ToString()); htmlStr.Append("
    • "); 
       }
   HtmlContent.Text = htmlStr.ToString();
   Repeater1.DataSource = ds;
   Repeater1.DataMember = "Employees";
   Repeater1.DataBind();
 }

    }</source>


    Fill DataSet with SqlDataAdapter

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> 

       void Page_Load(object sender, EventArgs e)
   {
      string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
      string CommandText = "SELECT * FROM Publisher";
      SqlConnection myConnection = new SqlConnection(ConnectionString);
      SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
      SqlDataAdapter myAdapter = new SqlDataAdapter();
      myAdapter.SelectCommand = myCommand;
      DataSet myDataSet = new DataSet();
      try {
         myConnection.Open();
         myAdapter.Fill(myDataSet);
      } catch (Exception ex) {
         throw (ex);
      } finally {
         myConnection.Close();
      }
      myGrid.DataSource = myDataSet;
      myGrid.DataBind();
   }

    </script> <html> <body> 

       <form runat="server">
       <asp:DataGrid id="myGrid" runat="server"></asp:DataGrid>
   </form>

    </body> </html> File: Web.config <configuration> 

       <appSettings>
     <add key="MSDEConnectString"
          value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
   
   </appSettings>

    </configuration></source>


    Iterating Through A DataSet

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> 

       void Page_Load(object sender, EventArgs e)
   {
       string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
       string CommandText = "SELECT AuthorName, AuthorCity, AuthorContact_Email, AuthorWebsite FROM Author";
       SqlConnection myConnection = new SqlConnection(ConnectionString);
       SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
      SqlDataAdapter myAdapter = new SqlDataAdapter();
      myAdapter.SelectCommand = myCommand;
      DataSet myDataSet = new DataSet();
      try
      {
         myConnection.Open();
         myAdapter.Fill(myDataSet, "Author");
      } catch (Exception ex) {
         throw (ex);
      } finally
      {
         myConnection.Close();
      }
      for (int i=0; i<=myDataSet.Tables["Author"].Rows.Count-1; i++)
      {
         Author p = new Author();
         p.Name = myDataSet.Tables["Author"].Rows[i]["AuthorName"].ToString();
         p.City = myDataSet.Tables["Author"].Rows[i]["AuthorCity"].ToString();
         p.Email = myDataSet.Tables["Author"].Rows[i]["AuthorContact_Email"].ToString();
         p.Website = myDataSet.Tables["Author"].Rows[i]["AuthorWebsite"].ToString();
         Label1.Text += p.ToString();
      }
   }

       public class Author
   {
       public string Name;
       public string City;
       public string Email;
       public string Website;
       public Author()
       {}
       public string ToString()
       {
           string description = "";
           description = "Name : " + this.Name + "
    ";
           description += "City : " + this.City + "
    ";
           description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a>
    ";
           description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a>

    ";
           return description;
       }
   }

    </script> <html> <body> 

       <asp:Label id="Label1" runat="server"></asp:Label>

    </body> </html>

    File: DataSet <configuration> 

       <appSettings>
     <add key="MSDEConnectString"
          value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
   
   </appSettings>

    </configuration></source>


    Iterating Through A DataSet from MySQL database

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.Odbc" %> <script runat="server"> 

       void Page_Load(object sender, EventArgs e)
   {
       string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MySQLConnectString"]);
       string CommandText = "SELECT AuthorName, AuthorCity, AuthorContact_Email, AuthorWebsite FROM Author";
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
       OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
       OdbcDataAdapter myAdapter = new OdbcDataAdapter();
       myAdapter.SelectCommand = myCommand;
       DataSet myDataSet = new DataSet();
       try {
         myConnection.Open();
         myAdapter.Fill(myDataSet, "Author");
      } catch (Exception ex) {
         throw (ex);
      } finally {
         myConnection.Close();
      }
      for (int i=0; i<=myDataSet.Tables["Author"].Rows.Count-1; i++)
      {
         Author p = new Author();
         p.Name = myDataSet.Tables["Author"].Rows[i]["AuthorName"].ToString();
         p.City = myDataSet.Tables["Author"].Rows[i]["AuthorCity"].ToString();
         p.Email = myDataSet.Tables["Author"].Rows[i]["AuthorContact_Email"].ToString();
         p.Website = myDataSet.Tables["Author"].Rows[i]["AuthorWebsite"].ToString();
         Label1.Text += p.ToString();
      }
   }
   public class Author
   {
       public string Name;
       public string City;
       public string Email;
       public string Website;
       public Author()
       {}
       public string ToString()
       {
           string description = "";
           description = "Name : " + this.Name + "
    ";
           description += "City : " + this.City + "
    ";
           description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a>
    ";
           description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a>

    ";
           return description;
       }
   }

    </script> <html> <head> </head> <body> 

       <asp:Label id="Label1" runat="server"></asp:Label>

    </body> </html> File: Web.config <configuration> 

       <appSettings>
     <add key="MySQLConnectString"
          value="driver={MySQL ODBC 3.51 Driver};server=localhost;database=Books;uid=YourID;pwd=letmein;" />
   </appSettings>

    </configuration></source>


    List Binding To A DataSet

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> 

       DataSet myDataSet = new DataSet();
   void Page_Load(object sender, EventArgs e)
   {
      string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
      string CommandText = "SELECT * FROM Publisher";
      SqlConnection myConnection = new SqlConnection(ConnectionString);
      SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
      SqlDataAdapter myAdapter = new SqlDataAdapter();
      myAdapter.SelectCommand = myCommand;
      try {
         myConnection.Open();
         myAdapter.Fill(myDataSet, "Publisher");
      } catch (Exception ex) {
         throw (ex);
      } finally {
         myConnection.Close();
      }
      Page.DataBind();
   }

    </script> <html> <body> 

       <form runat="server">

    A CheckBoxList

               <asp:CheckBoxList id="CheckBoxList1" runat="server" BorderWidth="2px" BorderStyle="Dotted" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myDataSet.Tables["Publisher"] %>"></asp:CheckBoxList>

    A RadioButtonList

               <asp:RadioButtonList id="RadioButtonList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myDataSet.Tables["Publisher"] %>"></asp:RadioButtonList>

    A DropDownList

               <asp:DropDownList id="DropDownList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myDataSet.Tables["Publisher"] %>"></asp:DropDownList>

    A ListBox

               <asp:ListBox id="ListBox1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myDataSet.Tables["Publisher"] %>" Rows="5"></asp:ListBox>
       
   </form>

    </body> </html>

    File: Web.config <configuration> 

       <appSettings>
       <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
   </appSettings>

    </configuration></source>


    Pulling Single Values From Dataset Bounded Lists

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> 

       DataSet myDataSet = new DataSet();
   void BuildDataSetTable(string commandText, string tableName)
   {
      string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
      SqlConnection myConnection = new SqlConnection(ConnectionString);
      SqlCommand myCommand = new SqlCommand(commandText, myConnection);
      SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
      try {
         myConnection.Open();
         myAdapter.Fill(myDataSet, tableName);
      } catch (Exception ex) {
         throw(ex);
      } finally {
         myConnection.Close();
      }
   }
   void Page_Load(object sender, EventArgs e) {
      if (!(Page.IsPostBack)) 
      {
         string SelectPublisher = "SELECT PublisherID, PublisherName From Publisher";
         BuildDataSetTable(SelectPublisher, "Publisher");
         Label1.Text = "Select a Publisher";
         DropDownList1.DataBind();
         RadioButtonList1.DataBind();
         ListBox1.DataBind();
      }
   }
   void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e) {
         string SelectBook = "SELECT * FROM Book WHERE BookPublisherID=" + RadioButtonList1.SelectedItem.Value;
         BuildDataSetTable(SelectBook, "Book");
         DataGrid1.DataBind();
   }
   void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {
         string SelectBook = "SELECT * FROM Book WHERE BookPublisherID=" + DropDownList1.SelectedItem.Value;
         BuildDataSetTable(SelectBook, "Book");
         DataGrid1.DataBind();
   }
   void ListBox1_SelectedIndexChanged(object sender, EventArgs e) {
         string SelectBook = "SELECT * FROM Book WHERE BookPublisherID=" + ListBox1.SelectedItem.Value;
         BuildDataSetTable(SelectBook, "Book");
         DataGrid1.DataBind();
   }

    </script> <html> <head> </head> <body> 

       <form runat="server">
   
       <asp:RadioButtonList id="RadioButtonList1" runat="server" DataSource="<%# myDataSet.Tables["Publisher"] %>" DataTextField="PublisherName" DataValueField="PublisherID" AutoPostBack="True" OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged"></asp:RadioButtonList>

    A DropDownList

           <asp:DropDownList id="DropDownList1" runat="server" DataSource="<%# myDataSet.Tables["Publisher"] %>" DataTextField="PublisherName" DataValueField="PublisherID" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>

    A ListBox

           <asp:ListBox id="ListBox1" runat="server" DataSource="<%# myDataSet.Tables["Publisher"] %>" DataTextField="PublisherName" DataValueField="PublisherID" AutoPostBack="True" OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"></asp:ListBox>
   
   
       <asp:Label id="Label1" runat="server"></asp:Label>
   
   
       <asp:DataGrid id="DataGrid1" runat="server" DataSource="<%# myDataSet.Tables["Book"] %>" BorderStyle="None" BorderWidth="1px" BorderColor="#CC9966" BackColor="White" CellPadding="4">
           <FooterStyle forecolor="#330099" backcolor="#FFFFCC"></FooterStyle>
           <HeaderStyle font-bold="True" forecolor="#FFFFCC" backcolor="#990000"></HeaderStyle>
           <PagerStyle horizontalalign="Center" forecolor="#330099" backcolor="#FFFFCC"></PagerStyle>
           <SelectedItemStyle font-bold="True" forecolor="#663399" backcolor="#FFCC66"></SelectedItemStyle>
           <ItemStyle forecolor="#330099" backcolor="White"></ItemStyle>
       </asp:DataGrid>
   
   </form>

    </body> </html> File: Web.config <configuration> 

       <appSettings>
       <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
   </appSettings>

    </configuration></source>


    Serialization capabilities of DataSet

       <source lang="csharp">

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 

       Inherits="Default"%>

    <!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>Test DataSet Serialization</title>

    </head> <body> 

           <form id="form1" runat="server">
    <asp:Button Text="Serialize as XML" runat="server" ID="XmlButton" OnClick="XmlButton_Click" Width="200px" /> <asp:Label ID="XmlSize" runat="server" />
    <asp:Button Text="Serialize as Binary" runat="server" ID="BinButton" OnClick="BinButton_Click" Width="200px" /> <asp:Label ID="BinSize" runat="server" /> 
           </form>

    </body> </html> File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Data.SqlClient; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Runtime.Serialization.Formatters.Binary;

    public partial class Default : System.Web.UI.Page { 

     private string XmlFile = @"c:\serial.xml";
 private string BinFile = @"c:\serial.bin";
 private DataSet GetData()
 {
   DataSet ds = new DataSet();
   SqlDataAdapter adapter = new SqlDataAdapter(
     "SELECT * FROM [order details]",
     ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
   adapter.Fill(ds);
   return ds;
 }
 protected void XmlButton_Click(object sender, EventArgs e)
 {
   DataSet ds = GetData();
   ds.RemotingFormat = SerializationFormat.Xml;
   StreamWriter writer = new StreamWriter(XmlFile);
   BinaryFormatter bin = new BinaryFormatter();
   bin.Serialize(writer.BaseStream, ds);
   writer.Close();
   FileInfo fi = new FileInfo(XmlFile);
   XmlSize.Text = (fi.Length/1024).ToString() + " KB";
 }
 protected void BinButton_Click(object sender, EventArgs e)
 {
   DataSet ds = GetData();
   ds.RemotingFormat = SerializationFormat.Binary;
   StreamWriter writer = new StreamWriter(BinFile);
   BinaryFormatter bin = new BinaryFormatter();
   bin.Serialize(writer.BaseStream, ds);
   writer.Close();
   FileInfo fi = new FileInfo(BinFile);
   BinSize.Text = (fi.Length/1024).ToString() + " KB";
 }

    }</source>


    The DataSet object represents an in-memory database.

       <source lang="csharp">

    A single DataSet can contain one or many DataTable objects. You can define parent/child relationships between the DataTable objects contained in a DataSet. <%@ 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 (!Page.IsPostBack)
           BuildTree();
   }
   void BuildTree()
   {
       string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
       SqlConnection con = new SqlConnection(connectionString);
       SqlDataAdapter dadCategories = new SqlDataAdapter("SELECT Id,Name FROM ProductCategories", con);
       SqlDataAdapter dadProducts = new SqlDataAdapter("SELECT Title,CategoryId FROM Products", con);
       DataSet dstProducts = new DataSet();
       using (con)
       {
           con.Open();
           dadCategories.Fill(dstProducts, "Categories");
           dadProducts.Fill(dstProducts, "Products");
       }
       dstProducts.Relations.Add("Children", dstProducts.Tables["Categories"].Columns["Id"], dstProducts.Tables["Products"].Columns["CategoryId"]);
       foreach (DataRow categoryRow in dstProducts.Tables["Categories"].Rows)
       {
           string name = (string)categoryRow["Name"];
           TreeNode catNode = new TreeNode(name);
           TreeView1.Nodes.Add(catNode);
           DataRow[] productRows = categoryRow.GetChildRows("Children");
           foreach (DataRow productRow in productRows)
           {
               string title = (string)productRow["Title"];
               TreeNode productNode = new TreeNode(title);
               catNode.ChildNodes.Add(productNode);
           }
       }
   }

    </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> 

       <title>Show DataSet</title>

    </head> <body> 

       <form id="form1" runat="server">

       <asp:TreeView
       id="TreeView1"
       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>


    Use OleDbDataAdapter to fill DataSet

       <source lang="csharp">

    <%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <script runat="server"> 

       void Page_Load(object sender, EventArgs e)
   {
      string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["AccessConnectString"]);
      string CommandText = "SELECT * FROM Publisher";
   
      OleDbConnection myConnection = new OleDbConnection(ConnectionString);
      OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
   
      OleDbDataAdapter myAdapter = new OleDbDataAdapter();
   
      myAdapter.SelectCommand = myCommand;
   
      DataSet myDataSet = new DataSet();
   
      try {
         myConnection.Open();
   
         myAdapter.Fill(myDataSet);
   
      } catch (Exception ex) {
         throw (ex);
      } finally {
         myConnection.Close();
      }
      myGrid.DataSource = myDataSet;
      myGrid.DataBind();
   
   }

    </script> <html> <head> </head> <body> 

       <form runat="server">
       <asp:DataGrid id="myGrid" runat="server"></asp:DataGrid>
   </form>

    </body> </html> File: <configuration> 

       <appSettings>
     <add key="AccessConnectString"
          value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Books.mdb;" />
   </appSettings>

    </configuration></source>

    Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/DataSet&oldid=2742»