ASP.NET Tutorial/ADO.net Database/DataReader — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 11:56, 26 мая 2010
Содержание
Create DataReader object from SqlCommand
<%@ 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);
try
{
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read()){
Author p = new Author();
p.Name = myReader.GetString(0);
p.City = myReader.GetString(1);
p.Email = myReader.GetString(2);
p.Website = myReader.GetString(3);
Label1.Text += p.ToString();
}
myReader.Close();
} catch (Exception ex)
{
throw (ex);
}
finally
{
myConnection.Close();
}
}
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: Web.config
<configuration>
<appSettings>
<add key="MSDEConnectString"
value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
</appSettings>
</configuration>
Iterating Through A DataReader
<%@ 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);
try
{
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
for (int i=0; i<=myReader.FieldCount-1; i++)
{
if (myReader.IsDBNull(i))
{
Label1.Text += "Warning: Field " + i + " is NULL.";
}
}
Author p = new Author();
p.Name = myReader.GetString(0);
p.City = myReader.GetString(1);
p.Email = myReader.GetString(2);
p.Website = myReader.GetString(3);
Label1.Text += p.ToString();
}
}
else
{
Label1.Text = "No rows returned";
}
if(!(myReader.IsClosed))
{
myReader.Close();
}
} catch (Exception ex) {
throw (ex);
} finally {
myConnection.Close();
}
}
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: Web.config
<configuration>
<appSettings>
<add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
</appSettings>
</configuration>
List Binding DataReader
<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
SqlDataReader myReader;
void Page_Load(object sender, EventArgs e)
{
string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
string CommandText = "SELECT PublisherID, PublisherName FROM Publisher";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
try
{
myConnection.Open();
myReader = myCommand.ExecuteReader();
ListBox1.DataBind();
myReader.Close();
}catch (Exception ex) {
throw (ex);
} finally {
myConnection.Close();
}
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h1>Pulling Values From Data Bound Lists
</h1>
<h2>A CheckBoxList
</h2>
<asp:CheckBoxList id="CheckBoxList1" runat="server" BorderWidth="2px" BorderStyle="Dotted" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:CheckBoxList>
<h2>A RadioButtonList
</h2>
<asp:RadioButtonList id="RadioButtonList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:RadioButtonList>
<h2>A DropDownList
</h2>
<asp:DropDownList id="DropDownList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:DropDownList>
<h2>A ListBox
</h2>
<asp:ListBox id="ListBox1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>" 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>
Returning Multiple Resultsets
File: App_Code\DataLayer1.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
public class DataLayer1
{
private static readonly string _connectionString;
public class ProductCategory
{
private int _id;
private string _name; public int Id
{
get { return _id; }
set { _id = value; }
}
public string Name
{
get { return _name; }
set { _name = value; }
}
}
public class Product
{
private string _title;
private int _categoryId;
public string Title
{
get { return _title; }
set { _title = value; }
}
public int CategoryId
{
get { return _categoryId; }
set { _categoryId = value; }
}
}
public static void GetProductData(List<DataLayer1.ProductCategory> productCategories, List<DataLayer1.Product> products)
{
string commandText = "SELECT Id,Name FROM ProductCategories;SELECT Title, CategoryId FROM Products";
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand(commandText, con);
using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataLayer1.ProductCategory newCategory = new DataLayer1.ProductCategory();
newCategory.Id = (int)reader["Id"];
newCategory.Name = (string)reader["Name"];
productCategories.Add(newCategory);
}
reader.NextResult();
while (reader.Read())
{
DataLayer1.Product newProduct = new DataLayer1.Product();
newProduct.Title = (string)reader["Title"];
newProduct.CategoryId = (int)reader["CategoryID"];
products.Add(newProduct);
}
}
}
static DataLayer1()
{
_connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
}
}
File: Web.config
<configuration>
<connectionStrings>
<add name="Products"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
</connectionStrings>
</configuration>
File: ShowDataLayer1.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!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()
{
List<DataLayer1.ProductCategory> categories = new List<DataLayer1.ProductCategory>();
List<DataLayer1.Product> products = new List<DataLayer1.Product>();
DataLayer1.GetProductData(categories, products);
grdCategories.DataSource = categories;
grdCategories.DataBind();
grdProducts.DataSource = products;
grdProducts.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show DataLayer1</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Product Categories</h1>
<asp:GridView
id="grdCategories"
Runat="server" />
<h1>Products</h1>
<asp:GridView
id="grdProducts"
Runat="server" />
</div>
</form>
</body>
</html>
Using the DataReader Object
DataReader represents the results of a database query.
You get a DataReader by calling a Command object"s ExecuteReader() method.
Verify whether a DataReader has any rows by checking the HasRows property or calling the Read() method.
The DataReader represents a single row of data at a time.
To get the next row of data, you need to call the Read() method.
When you get to the last row, the Read() method returns False.
Working with Multiple Active Resultsets
File: ShowMARS.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!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 = @"MultipleActiveResultSets=True;"
+ @"Data Source=.\SQLExpress;Integrated Security=True;"
+ @"AttachDBFileName=|DataDirectory|MyDatabase.mdf;User Instance=True";
SqlConnection con = new SqlConnection(connectionString);
string cmdCategoriesText = "SELECT Id,Name FROM ProductCategories";
SqlCommand cmdCategories = new SqlCommand(cmdCategoriesText, con);
string cmdProductsText = "SELECT Title FROM Products "
+ "WHERE CategoryId=@CategoryID";
SqlCommand cmdProducts = new SqlCommand(cmdProductsText, con);
cmdProducts.Parameters.Add("@CategoryId", SqlDbType.Int);
using (con)
{
con.Open();
SqlDataReader categories = cmdCategories.ExecuteReader();
while (categories.Read())
{
int id = categories.GetInt32(0);
string name = categories.GetString(1);
TreeNode catNode = new TreeNode(name);
TreeView1.Nodes.Add(catNode);
cmdProducts.Parameters["@CategoryId"].Value = id;
SqlDataReader products = cmdProducts.ExecuteReader();
while (products.Read())
{
string title = products.GetString(0);
TreeNode productNode = new TreeNode(title);
catNode.ChildNodes.Add(productNode);
}
products.Close();
}
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show MARS</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TreeView
id="TreeView1"
Runat="server" />
</div>
</form>
</body>
</html>