ASP.NET Tutorial/ADO.net Database/DataReader
Содержание
Create DataReader object from SqlCommand
<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); 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 + "
"; 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: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
Iterating Through A DataReader
<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); 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 + "
"; 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: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
List Binding DataReader
<source lang="csharp">
<%@ 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">
Pulling Values From Data Bound Lists
A CheckBoxList
<asp:CheckBoxList id="CheckBoxList1" runat="server" BorderWidth="2px" BorderStyle="Dotted" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:CheckBoxList>
A RadioButtonList
<asp:RadioButtonList id="RadioButtonList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:RadioButtonList>
A DropDownList
<asp:DropDownList id="DropDownList1" runat="server" DataValueField="PublisherID" DataTextField="PublisherName" DataSource="<%# myReader %>"></asp:DropDownList>
A ListBox
<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></source>
Returning Multiple Resultsets
<source lang="csharp">
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">
Product Categories
<asp:GridView id="grdCategories" Runat="server" />
Products
<asp:GridView id="grdProducts" Runat="server" />
</form>
</body> </html></source>
Using the DataReader Object
<source lang="csharp">
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.</source>
Working with Multiple Active Resultsets
<source lang="csharp">
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">
<asp:TreeView id="TreeView1" Runat="server" />
</form>
</body> </html></source>