в 15:30, 26 мая 2010

2010-05-26T15:30:57Z

Admin: 1 версия

2010-05-26T11:56:59Z

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">
<div>
<asp:ListBox ID="lstUser" runat="server" Height="152px" Width="192px"></asp:ListBox></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;
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">
<TABLE id="Table1" width="100%">
<TR>
<TD>
<asp:Repeater id="Repeater1" runat="server">
<HeaderTemplate>
<h2>Repeater</h2>
</HeaderTemplate>
<ItemTemplate>
<li>
<%# DataBinder.Eval(Container.DataItem, "TitleOfCourtesy") %>
<b>
<%# DataBinder.Eval(Container.DataItem, "LastName") %>
</b>,
<%# DataBinder.Eval(Container.DataItem, "FirstName") %>
</li>
</ItemTemplate>
</asp:Repeater></TD>
<TD>
<H2>foreach approach</H2>
<asp:Literal id="HtmlContent" runat="server"></asp:Literal></TD>
</TR>
</TABLE>
</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("<li>");
htmlStr.Append(dr["TitleOfCourtesy"].ToString());
htmlStr.Append(" <b>");
htmlStr.Append(dr["LastName"].ToString());
htmlStr.Append("</b>, ");
htmlStr.Append(dr["FirstName"].ToString());
htmlStr.Append("</li>");
}
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 + "<br />";
description += "City : " + this.City + "<br />";
description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a><br/>";
description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a><br/><br/>";
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 + "<br />";
description += "City : " + this.City + "<br />";
description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a><br/>";
description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a><br/><br/>";
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">
<h2>A CheckBoxList</h2>

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

<h2>A RadioButtonList</h2>

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

<h2>A DropDownList</h2>

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

<h2>A ListBox </h2>

<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>

<h2>A DropDownList
</h2>

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

<h2>A ListBox
</h2>

<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>
<div id="pageContent">
<form id="form1" runat="server">
<table>
<tr>
<td><asp:Button Text="Serialize as XML" runat="server" ID="XmlButton" OnClick="XmlButton_Click" Width="200px" /></td>
<td><asp:Label ID="XmlSize" runat="server" /></td>
</tr>
<tr>
<td><asp:Button Text="Serialize as Binary" runat="server" ID="BinButton" OnClick="BinButton_Click" Width="200px" /></td>
<td><asp:Label ID="BinSize" runat="server" /></td>
</tr>
</table>
</form>
</div>
</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">
<div>
<asp:TreeView
id="TreeView1"
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></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>

← Предыдущая	Версия 15:30, 26 мая 2010
(нет различий)

ASP.NET Tutorial/ADO.net Database/DataSet - История изменений

в 15:30, 26 мая 2010

Admin: 1 версия