ASP.NET Tutorial/ADO.net Database/DataReader — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 18:30, 26 мая 2010

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>