ASP.NET Tutorial/ADO.net Database/DataSet
Содержание
- 1 Create DataSet by your own
- 2 Fill a DataSet
- 3 Repeater
- 4 foreach approach
- 5 Fill DataSet with SqlDataAdapter
- 6 Iterating Through A DataSet
- 7 Iterating Through A DataSet from MySQL database
- 8 List Binding To A DataSet
- 9 A CheckBoxList
- 10 A RadioButtonList
- 11 A DropDownList
- 12 A ListBox
- 13 Pulling Single Values From Dataset Bounded Lists
- 14 A DropDownList
- 15 A ListBox
- 16 Serialization capabilities of DataSet
- 17 The DataSet object represents an in-memory database.
- 18 Use OleDbDataAdapter to fill DataSet
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">
</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> </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("
} 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>