ASP.Net/ADO.net Database/DataSet
Содержание
- 1 Build a DataSet
- 2 Build a DataSet with relationship
- 3 Setting Relationships in a DataSet
- 3.1 Converting XML to DataSet and Vice versa
- 3.2 Converting XML to DataSet and Vice versa (VB)
- 3.3 Create DataSet pragmatically
- 3.4 DataSet Serialization and Deserialization using Binary Format
- 3.5 Finding a Particular Row in a DataSet
- 3.6 Get query result from DataSet
- 3.7 Load Table from DataSet
- 3.8 Loop through data in Sql Server by DataSet
- 3.9 Loop through DataSet
- 3.10 Output the content of a DataSet as XML
- 3.11 Programmatically creating a DataSet object
Build a DataSet
<source lang="csharp">
<%@ 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">
<asp:GridView id="grdProducts" runat="server" />
<asp:GridView id="grdCustomer" 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 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; }
}
</source>
Build a DataSet with relationship
<source lang="csharp">
<%@ 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">
Setting Relationships in a DataSet
This example demonstrates how to relate two DataTables in a DataSet
<asp:GridView id="grdAuthors" runat="server" />
<asp:GridView id="grdBooks" runat="server" />
-
<asp:Label id="labReport" 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 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 += "
}
</source>
Converting XML to DataSet and Vice versa
<source lang="csharp">
<%@ 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" />
<a href="Data.xml">Data.xml</a> | <a href="DataWrite.xml">DataWrite.xml</a> </form> </body> </source>
Converting XML to DataSet and Vice versa (VB)
<source lang="csharp">
<%@ 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" />
<a href="DSRead.xml">DSRead.xml</a> | <a href="DSWrite.xml">DSWrite.xml</a> </form> </body> </source>
Create DataSet pragmatically
<source lang="csharp">
<%@ 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">
<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>
</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; }
}
</source>
DataSet Serialization and Deserialization using Binary Format
<source lang="csharp">
<%@ 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">
<asp:Button runat="Server" ID="btnReadFromFile" OnClick="btnReadFromFile_Click" Text="Read the Data from file" />
<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>
</form>
</body> </html>
</source>
Finding a Particular Row in a DataSet
<source lang="csharp">
<%@ 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>
<asp:label id="msgLabel" runat="server" ></asp:label></form> </body>
</html>
</source>
Get query result from DataSet
<source lang="csharp">
<%@ 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">
<asp:label
id="lblMessage" runat="Server"
/> </form> </BODY> </HTML>
</source>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Load Table from DataSet
<source lang="csharp">
<%@ 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">
<asp:Label
id="lblMessage" Font-Size="12pt" Font-Bold="True" Font-Name="Lucida Console" text="Employee List" runat="server"
/>
<asp:datagrid
id="dgEmps" runat="server" autogeneratecolumns="True"
> </asp:datagrid> </form> </BODY> </HTML>
</source>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Loop through data in Sql Server by DataSet
<source lang="csharp">
<%@ 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 _ & "
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">
<asp:label
id="lblMessage" runat="Server"
/> </form> </BODY> </HTML>
</source>
Loop through DataSet
<source lang="csharp">
<%@ 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 _ & "
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">
<asp:label
id="lblMessage" runat="Server"
/> </form> </BODY> </HTML>
</source>
<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>
Output the content of a DataSet as XML
<source lang="csharp">
<%@ 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">
<asp:GridView id="grdProducts" runat="server" />
<asp:GridView id="grdCustomer" runat="server" />
Retrieving via indexing: <asp:Label ID="labSingle" runat="server" />
<asp:Button ID="btnXml" runat="server" Text="Output as XML" OnClick="btnXml_Click" />
<asp:TextBox ID="txtXml" runat="server" Rows="20" Columns="60" TextMode="MultiLine"/>
</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; }
}
</source>
Programmatically creating a DataSet object
<source lang="csharp">
<%@ 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">
<asp:GridView runat="Server" ID="gridResults" />
</form>
</body> </html>
</source>