ASP.Net/ADO.net Database/DataSet
Содержание
- 1 Build a DataSet
- 2 Build a DataSet with relationship
- 3 Converting XML to DataSet and Vice versa
- 4 Converting XML to DataSet and Vice versa (VB)
- 5 Create DataSet pragmatically
- 6 DataSet Serialization and Deserialization using Binary Format
- 7 Finding a Particular Row in a DataSet
- 8 Get query result from DataSet
- 9 Load Table from DataSet
- 10 Loop through data in Sql Server by DataSet
- 11 Loop through DataSet
- 12 Output the content of a DataSet as XML
- 13 Programmatically creating a DataSet object
Build a DataSet
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="TestDataSet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Using a DataSet</title>
</head>
<body>
<form id="form1" runat="server">
<div id="container">
<asp:GridView id="grdProducts" runat="server" />
<hr />
<asp:GridView id="grdCustomer" runat="server" />
</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 TestDataSet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.Tables.Add( MakeCustomerData() );
ds.Tables.Add( MakeProductData() );
ds.Tables[1].TableName = "Products";
grdCustomer.DataSource = ds.Tables[0].DefaultView;
grdCustomer.DataBind();
grdProducts.DataSource = ds.Tables["Products"].DefaultView;
grdProducts.DataBind();
}
private DataTable MakeCustomerData()
{
DataTable table = new DataTable();
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn firstNameCol = new DataColumn("FirstName", typeof(string));
DataColumn lastNameCol = new DataColumn("LastName", typeof(string));
DataColumn phoneCol = new DataColumn("Phone", typeof(string));
table.Columns.Add(idCol);
table.Columns.Add(firstNameCol);
table.Columns.Add(lastNameCol);
table.Columns.Add(phoneCol);
DataRow r1 = table.NewRow();
r1[1] = "A";
r1[2] = "a";
r1[3] = "123-4567";
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2["FirstName"] = "B";
r2["LastName"] = "b";
r2["Phone"] = "111-1111";
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3["FirstName"] = "C";
r3["LastName"] = "c";
r3["Phone"] = "222-2222";
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4["FirstName"] = "D";
r4["LastName"] = "d";
r4["Phone"] = "333-3333";
table.Rows.Add(r4);
return table;
}
private DataTable MakeProductData()
{
DataTable table = new DataTable();
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn nameCol = new DataColumn("Name", typeof(string));
DataColumn priceCol = new DataColumn("Price", typeof(double));
table.Columns.Add(idCol);
table.Columns.Add(nameCol);
table.Columns.Add(priceCol);
DataRow r1 = table.NewRow();
r1[1] = "Book";
r1[2] = 49.99;
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[1] = "Apple";
r2[2] = 0.99;
table.Rows.Add(r2);
return table;
}
}
Build a DataSet with relationship
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SettingRelations" Debug="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Setting Relationships in a DataSet</title>
</head>
<body>
<form id="form1" runat="server">
<div id="container">
<h1>Setting Relationships in a DataSet</h1>
<p>This example demonstrates how to relate two DataTables in a DataSet</p>
<asp:GridView id="grdAuthors" runat="server" />
<hr />
<asp:GridView id="grdBooks" runat="server" />
<hr />
<dl>
<asp:Label id="labReport" runat="server" />
</dl>
</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 SettingRelations : System.Web.UI.Page
{
DataSet ds = null;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
ds.Tables.Add(MakeAuthorData());
ds.Tables.Add(MakeBookData());
DataRelation relation = new DataRelation("Book2Author", ds.Tables["Authors"].Columns["AuthorId"], ds.Tables["Books"].Columns["AuthorId"]);
ds.Relations.Add(relation);
grdAuthors.DataSource = ds.Tables["Authors"].DefaultView;
grdAuthors.DataBind();
grdBooks.DataSource = ds.Tables["Books"].DefaultView;
grdBooks.DataBind();
foreach (DataRow artistRow in ds.Tables["Authors"].Rows)
{
string fname = (string)artistRow["FirstName"];
string lname = (string)artistRow["LastName"];
labReport.Text += "<dt>" + fname + " " + lname;
foreach (DataRow bookRow in artistRow.GetChildRows(relation))
{
string title = (string)bookRow["Title"];
labReport.Text += "<dd>" + title;
}
}
}
private DataTable MakeAuthorData()
{
DataTable table = new DataTable();
table.TableName = "Authors";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "AuthorId";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
DataColumn firstNameCol = new DataColumn("FirstName", typeof(string));
DataColumn lastNameCol = new DataColumn("LastName", typeof(string));
table.Columns.Add(idCol);
table.Columns.Add(firstNameCol);
table.Columns.Add(lastNameCol);
DataRow r1 = table.NewRow();
r1[0] = 10;
r1[1] = "A";
r1[2] = "a";
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[0] = 20;
r2["FirstName"] = "B";
r2["LastName"] = "b";
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[0] = 30;
r3["FirstName"] = "C";
r3["LastName"] = "c";
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[0] = 40;
r4["FirstName"] = "D";
r4["LastName"] = "d";
table.Rows.Add(r4);
return table;
}
private DataTable MakeBookData()
{
DataTable table = new DataTable();
table.TableName = "Books";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn authorCol = new DataColumn("AuthorId", typeof(Int32));
DataColumn nameCol = new DataColumn("Title", typeof(string));
DataColumn priceCol = new DataColumn("Price", typeof(double));
table.Columns.Add(idCol);
table.Columns.Add(authorCol);
table.Columns.Add(nameCol);
table.Columns.Add(priceCol);
DataRow r1 = table.NewRow();
r1[1] = 30;
r1[2] = "Java";
r1[3] = 49.99;
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[1] = 10;
r2[2] = "C#";
r2[3] = 19.99;
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[1] = 40;
r3[2] = "Javascript";
r3[3] = 24.99;
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[1] = 40;
r4[2] = "Oracle";
r4[3] = 24.99;
table.Rows.Add(r4);
return table;
}
}
Converting XML to DataSet and Vice versa
<%@ Page Language="c#" Debug="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<html>
<title>Converting XML to DataSet and Vice versa. </title>
<head>
<script language="c#" runat="server">
void Page_Load(Object sender,EventArgs e) {
try
{
DataSet Ds = new DataSet();
Ds.ReadXml(Server.MapPath("Data.XML"));
Ds.AcceptChanges();
Ds.Tables[0].Rows[0]["LastModified"] = DateTime.Now.ToString();
DataSet dsChanges = new DataSet();
dsChanges = Ds.GetChanges();
dsChanges.WriteXml(Server.MapPath("") + "\\DataWrite.xml");
}
catch (Exception Ex)
{
LblValue.Text = Ex.Message;
}
}
</script>
</head>
<body>
<form id="frm" runat="server">
<asp:Label id="LblValue" runat="server" />
<hr>
<a href="Data.xml">Data.xml</a> | <a href="DataWrite.xml">DataWrite.xml</a>
</form>
</body>
Converting XML to DataSet and Vice versa (VB)
<%@ Page Language="vb" Debug="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<html>
<head>
<title>Converting XML to DataSet and Vice versa.</title>
<script language="vb" runat="server">
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
Dim Ds As New DataSet
Ds.ReadXml(Server.MapPath("Data.XML"))
Ds.AcceptChanges()
Ds.Tables(0).Rows(0).Item("LastModified") = DateTime.Now.ToString()
Dim dsChanges As DataSet
dsChanges = Ds.GetChanges()
dsChanges.WriteXml(Server.MapPath("") & "\DataWrite.xml")
Catch ex As Exception
lblvalue.text = ex.Message
End Try
End Sub
</script>
</head>
<body>
<form id="frm" runat="server">
<asp:Label id="LblValue" runat="server" />
<hr>
<a href="DSRead.xml">DSRead.xml</a> | <a href="DSWrite.xml">DSWrite.xml</a>
</form>
</body>
Create DataSet pragmatically
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default_aspx" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="Bugs"
runat="server"
CellPadding="4"
ForeColor="#333333"
GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF"
ForeColor="White"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1"
Font-Bold="True"
ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:GridView ID="BugConstraints"
runat="server"
BackColor="#DEBA84"
BorderColor="#DEBA84"
BorderStyle="None"
BorderWidth="1px"
CellPadding="3"
CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
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 Default_aspx : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = CreateDataSet();
Bugs.DataSource = ds.Tables["Bugs"];
Bugs.DataBind();
BugConstraints.DataSource = ds.Tables["Bugs"].Constraints;
BugConstraints.DataBind();
}
}
private DataSet CreateDataSet( )
{
DataSet dataSet = new DataSet();
DataTable tblBugs = new DataTable("Bugs");
DataColumn newColumn;
newColumn = tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true;
newColumn.AutoIncrementSeed = 1;
newColumn.AutoIncrementStep = 1;
newColumn.AllowDBNull = false;
//newColumn.Unique = true;
UniqueConstraint constraint = new UniqueConstraint("Unique_BugID", newColumn);
tblBugs.Constraints.Add(constraint);
DataColumn[] columnArray = new DataColumn[1];
columnArray[0] = newColumn;
tblBugs.PrimaryKey = columnArray;
newColumn = tblBugs.Columns.Add("Product", Type.GetType("System.Int32"));
newColumn.AllowDBNull = false;
newColumn.DefaultValue = 1;
DataColumn bugProductColumn = newColumn;
newColumn = tblBugs.Columns.Add("Version", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 50;
newColumn.DefaultValue = "0.1";
newColumn = tblBugs.Columns.Add( "Description", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 8000;
newColumn.DefaultValue = "";
newColumn = tblBugs.Columns.Add("Reporter", Type.GetType("System.Int32"));
newColumn.AllowDBNull = false;
DataColumn bugReporterColumn = newColumn;
DataRow newRow;
newRow = tblBugs.NewRow();
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "D1";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow();
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "D2";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow();
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "D3";
newRow["Reporter"] = 6;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow();
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "D4";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow();
newRow["Product"] = 2;
newRow["Version"] = "0.1";
newRow["Description"] = "D5";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow();
newRow["Product"] = 2;
newRow["Version"] = "0.1";
newRow["Description"] = "D6";
newRow["Reporter"] = 6;
tblBugs.Rows.Add(newRow);
dataSet.Tables.Add(tblBugs);
DataTable tblProduct = new DataTable("lkProduct");
newColumn = tblProduct.Columns.Add("ProductID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true;
newColumn.AutoIncrementSeed = 1;
newColumn.AutoIncrementStep = 1;
newColumn.AllowDBNull = false;
newColumn.Unique = true;
newColumn = tblProduct.Columns.Add("ProductDescription", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 8000;
newColumn.DefaultValue = "";
newRow = tblProduct.NewRow();
newRow["ProductDescription"] = "Bug Tracking";
tblProduct.Rows.Add(newRow);
newRow = tblProduct.NewRow();
newRow["ProductDescription"] = "Information Manager";
tblProduct.Rows.Add(newRow);
dataSet.Tables.Add(tblProduct);
DataTable tblPeople = new DataTable("People");
newColumn = tblPeople.Columns.Add("PersonID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true;
newColumn.AutoIncrementSeed = 1;
newColumn.AutoIncrementStep = 1;
newColumn.AllowDBNull = false;
UniqueConstraint uniqueConstraint = new UniqueConstraint("Unique_PersonID", newColumn);
tblPeople.Constraints.Add(uniqueConstraint);
DataColumn PersonIDColumn = newColumn;
columnArray = new DataColumn[1];
columnArray[0] = newColumn;
tblPeople.PrimaryKey = columnArray;
newColumn = tblPeople.Columns.Add("FullName", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 80;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add("eMail", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 100;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add("Phone", Type.GetType("System.String"));
newColumn.AllowDBNull = false;
newColumn.MaxLength = 20;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add("Role", Type.GetType("System.Int32"));
newColumn.DefaultValue = 0;
newColumn.AllowDBNull = false;
newRow = tblPeople.NewRow();
newRow["FullName"] = "Jason";
newRow["email"] = "j@server.ru";
newRow["Phone"] = "123-111-1111";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow();
newRow["FullName"] = "Dole";
newRow["email"] = "d@server.ru";
newRow["Phone"] = "234-111-2222";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow();
newRow["FullName"] = "John";
newRow["email"] = "j@server.ru";
newRow["Phone"] = "345-222-3333";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow();
newRow["FullName"] = "Jess";
newRow["email"] = "j@server.ru";
newRow["Phone"] = "456-333-4444";
newRow["Role"] = 3;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow();
newRow["FullName"] = "Ron";
newRow["email"] = "ron@server.ru";
newRow["Phone"] = "567-555-5555";
newRow["Role"] = 2;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow();
newRow["FullName"] = "Tank";
newRow["email"] = "t@server.ru";
newRow["Phone"] = "617-555-1234";
newRow["Role"] = 2;
tblPeople.Rows.Add(newRow);
dataSet.Tables.Add(tblPeople);
ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_BugToPeople", PersonIDColumn, bugReporterColumn);
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
tblBugs.Constraints.Add(fk);
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;
dataColumn1 = dataSet.Tables["People"].Columns["PersonID"];
dataColumn2 = dataSet.Tables["Bugs"].Columns["Reporter"];
dataRelation = new System.Data.DataRelation("BugsToReporter",dataColumn1,dataColumn2);
dataSet.Relations.Add(dataRelation);
return dataSet;
}
}
DataSet Serialization and Deserialization using Binary Format
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Runtime.Serialization.Formatters.Binary" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void Page_Load(object sender, System.EventArgs e)
{
DataSet categories = GetCategories();
string fileName = Server.MapPath("App_Data/Data.dat");
using (FileStream stream = new FileStream(fileName, FileMode.Create))
{
categories.RemotingFormat = SerializationFormat.Binary;
BinaryFormatter format = new BinaryFormatter();
format.Serialize(stream, categories);
stream.Flush();
}
Response.Write("File written successfully");
}
void btnReadFromFile_Click(object sender, EventArgs e)
{
string fileName = Server.MapPath("App_Data/Data.dat");
using (FileStream stream = new FileStream(fileName, FileMode.Open))
{
BinaryFormatter format = new BinaryFormatter();
DataSet categoriesFromFile = (DataSet) format.Deserialize(stream);
gridCategories.DataSource = categoriesFromFile.Tables[0].DefaultView;
gridCategories.DataBind();
}
}
DataSet GetCategories()
{
string connString = WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
string sql = "Select * from Production.ProductSubcategory";
DataSet categories = new DataSet("Categories");
using (SqlConnection connection = new SqlConnection(connString))
{
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(categories);
}
return categories;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>DataSet Serialization and Deserialization using Binary Format</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button runat="Server"
ID="btnReadFromFile"
OnClick="btnReadFromFile_Click"
Text="Read the Data from file" />
<br />
<asp:GridView id="gridCategories"
runat="server"
AutoGenerateColumns="False"
CellPadding="4"
HeaderStyle-BackColor="blue"
HeaderStyle-ForeColor="White"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True">
<Columns>
<asp:BoundField HeaderText="Category ID" DataField="ProductSubcategoryID" />
<asp:BoundField HeaderText="Name" DataField="Name" ItemStyle-HorizontalAlign="Right" />
<asp:BoundField HtmlEncode="false" DataFormatString="{0:d}" HeaderText="Last Modified Date" DataField="ModifiedDate" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Finding a Particular Row in a DataSet
<%@ Page Language="C#" ClassName="Default" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
void Page_Load(Object sender, EventArgs e)
{
SqlConnection sqlConnection;
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand;
DataSet dataSet;
DataTable dataTable;
if (IsPostBack) {
try{
sqlConnection = new SqlConnection( "Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)" );
sqlCommand = new SqlCommand( "Select * From Customers", sqlConnection );
sqlDataAdapter = new SqlDataAdapter( sqlCommand );
dataSet = new DataSet();
sqlDataAdapter.Fill( dataSet, "Customers" );
DataColumn [] pkColumn = new DataColumn[1];
pkColumn[0] = dataSet.Tables[0].Columns["CustomerID"];
dataSet.Tables[0].PrimaryKey = pkColumn;
DataRow rowFound = dataSet.Tables[0].Rows.Find(customerIdTextBox.Text);
if (rowFound == null)
{
msgLabel.Text = "The Customer ID entered was not found.";
}
else
{
StringBuilder stringBuilder = new StringBuilder("Contact ");
stringBuilder.Append(rowFound["ContactName"].ToString());
stringBuilder.Append(", ");
stringBuilder.Append(rowFound["ContactTitle"].ToString());
stringBuilder.Append(" at ");
stringBuilder.Append(rowFound["CompanyName"].ToString());
msgLabel.Text = stringBuilder.ToString();
}
}
catch( Exception exception )
{
msgLabel.Text = exception.ToString();
}
}
}
</script>
<html>
<head>
<title>Finding a Particular Row in a DataSet</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:label id="customerIdLabel" runat="server">Customer ID:</asp:label>
<asp:textbox id="customerIdTextBox" runat="server"></asp:textbox>
<asp:button id="findButton" runat="server" Text="Find"></asp:button><br>
<asp:label id="msgLabel" runat="server" ></asp:label></form>
</body>
</html>
Get query result from DataSet
<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<script runat=server>
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
DBConn = New OleDbConnection( _
"PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("EmployeeDatabase.mdb;"))
DBCommand = New OleDbDataAdapter _
("Select Count(ID) as TheCount " _
& "from Employee", DBConn)
DBCommand.Fill(DSPageData, _
"EmpCount")
lblMessage.Text = "Total Employees: " _
& DSPageData.Tables("EmpCount"). _
Rows(0).Item("TheCount")
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Values in an Access Table</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:label
id="lblMessage"
runat="Server"
/>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Load Table from DataSet
<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<script runat=server>
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
If Not IsPostBack Then
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
DBConn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" & Server.MapPath("EmployeeDatabase.mdb;"))
DBCommand = New OleDbDataAdapter _
("Select * From Employee Order By LastName, FirstName", DBConn)
DBCommand.Fill(DSPageData, "Employee")
dgEmps.DataSource = DSPageData.Tables("Employee").DefaultView
dgEmps.DataBind()
End If
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Creating a Basic DataGrid Control</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:Label
id="lblMessage"
Font-Size="12pt"
Font-Bold="True"
Font-Name="Lucida Console"
text="Employee List"
runat="server"
/>
<BR><BR>
<asp:datagrid
id="dgEmps"
runat="server"
autogeneratecolumns="True"
>
</asp:datagrid>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Loop through data in Sql Server by DataSet
<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat=server>
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
Dim DBConn as SQLConnection
Dim DBCommand As SQLDataAdapter
Dim DSPageData as New DataSet
Dim I as Long
" DBConn = New SQLConnection("server=localhost;" _
" & "Initial Catalog=TT;" _
" & "User Id=sa;" _
" & "Password=yourpassword;")
DBConn = New SQLConnection("Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";")
DBCommand = New SQLDataAdapter _
("Select * from Employee", DBConn)
DBCommand.Fill(DSPageData, _
"Emps")
For I = 0 To _
DSPageData.Tables("Emps").Rows.Count - 1
"Code to process record
lblMessage.Text = lblMessage.Text _
& "<BR>Processed Record: " _
& DSPageData.Tables("Emps"). _
Rows(I).Item("ID")
Next
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Iterating through Records in an SQL Server Table</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:label
id="lblMessage"
runat="Server"
/>
</form>
</BODY>
</HTML>
Loop through DataSet
<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<script runat=server>
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
Dim I as Long
DBConn = New OleDbConnection( _
"PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" _
& Server.MapPath("EmployeeDatabase.mdb;"))
DBCommand = New OleDbDataAdapter _
("Select * from Employee", DBConn)
DBCommand.Fill(DSPageData, _
"Employee")
For I = 0 To _
DSPageData.Tables("Employee").Rows.Count - 1
"Code to process record
lblMessage.Text = lblMessage.Text _
& "<BR>Processed Record: " _
& DSPageData.Tables("Employee"). _
Rows(I).Item("ID")
Next
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Iterating through Records in an Access Table</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:label
id="lblMessage"
runat="Server"
/>
</form>
</BODY>
</HTML>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Output the content of a DataSet as XML
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="OutputDataSet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Outputting a DataSet</title>
</head>
<body>
<form id="form1" runat="server">
<div id="container">
<asp:GridView id="grdProducts" runat="server" />
<hr />
<asp:GridView id="grdCustomer" runat="server" />
<hr />
Retrieving via indexing: <asp:Label ID="labSingle" runat="server" />
<hr />
<asp:Button ID="btnXml" runat="server" Text="Output as XML" OnClick="btnXml_Click" />
<br />
<asp:TextBox ID="txtXml" runat="server" Rows="20" Columns="60" TextMode="MultiLine"/>
</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 OutputDataSet : System.Web.UI.Page
{
DataSet ds = null;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
ds.Tables.Add(MakeCustomerData());
ds.Tables.Add(MakeProductData());
ds.Tables[1].TableName = "Products";
grdCustomer.DataSource = ds.Tables[0].DefaultView;
grdCustomer.DataBind();
grdProducts.DataSource = ds.Tables["Products"].DefaultView;
grdProducts.DataBind();
double sum = 0.0;
int count = 0;
foreach (DataRow dr in ds.Tables["Products"].Rows)
{
double price = (double)dr["Price"];
sum += price;
count++;
}
double average = sum / count;
labSingle.Text = "Average: " + average;
}
private DataTable MakeCustomerData()
{
DataTable table = new DataTable();
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn firstNameCol = new DataColumn("FirstName", typeof(string));
DataColumn lastNameCol = new DataColumn("LastName", typeof(string));
DataColumn phoneCol = new DataColumn("Phone", typeof(string));
table.Columns.Add(idCol);
table.Columns.Add(firstNameCol);
table.Columns.Add(lastNameCol);
table.Columns.Add(phoneCol);
DataRow r1 = table.NewRow();
r1[1] = "A";
r1[2] = "a";
r1[3] = "123-4567";
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2["FirstName"] = "B";
r2["LastName"] = "b";
r2["Phone"] = "234-1111";
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3["FirstName"] = "C";
r3["LastName"] = "c";
r3["Phone"] = "345-444";
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4["FirstName"] = "D";
r4["LastName"] = "d";
r4["Phone"] = "456-2222";
table.Rows.Add(r4);
return table;
}
private DataTable MakeProductData()
{
DataTable table = new DataTable();
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn nameCol = new DataColumn("Name", typeof(string));
DataColumn priceCol = new DataColumn("Price", typeof(double));
table.Columns.Add(idCol);
table.Columns.Add(nameCol);
table.Columns.Add(priceCol);
DataRow r1 = table.NewRow();
r1[1] = "Book";
r1[2] = 49.99;
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[1] = "Apple";
r2[2] = 0.99;
table.Rows.Add(r2);
return table;
}
protected void btnXml_Click(object sender, EventArgs e)
{
string s = ds.GetXml();
txtXml.Text = s;
}
}
Programmatically creating a DataSet object
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
DataSet customerOrders = new DataSet("CustomerOrders");
DataTable customers = customerOrders.Tables.Add("Customers");
DataTable orders = customerOrders.Tables.Add("Orders");
customers.Columns.Add("CustomerID", Type.GetType("System.Int32"));
customers.Columns.Add("FirstName", Type.GetType("System.String"));
customers.Columns.Add("LastName", Type.GetType("System.String"));
customers.Columns.Add("Phone", Type.GetType("System.String"));
customers.Columns.Add("Email", Type.GetType("System.String"));
orders.Columns.Add("CustomerID", Type.GetType("System.Int32"));
orders.Columns.Add("OrderID", Type.GetType("System.Int32"));
orders.Columns.Add("OrderAmount", Type.GetType("System.Double"));
orders.Columns.Add("OrderDate", Type.GetType("System.DateTime"));
customerOrders.Relations.Add("Cust_Order_Rel", customerOrders.Tables["Customers"].Columns["CustomerID"], customerOrders.Tables["Orders"].Columns["CustomerID"]);
DataRow row = customers.NewRow();
row["CustomerID"] = 1;
row["FirstName"] = "first";
row["LastName"] = "last";
row["Phone"] = "111-1111";
row["Email"] = "test@test.ru";
customers.Rows.Add(row);
row = orders.NewRow();
row["CustomerID"] = 1;
row["OrderID"] = 22;
row["OrderAmount"] = 0;
row["OrderDate"] = "11/10/2007";
orders.Rows.Add(row);
Response.Write(Server.HtmlEncode(customerOrders.GetXml()));
gridResults.DataSource = customerOrders.Tables["Customers"];
gridResults.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Programmatically creating a DataSet object</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView runat="Server" ID="gridResults" />
</div>
</form>
</body>
</html>