ASP.NET Tutorial/ADO.net Database/SqlDataReader

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

Building Data Access Components with ADO.NET

   <source lang="csharp">

File: App_Code\Product.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class Product {

   private static readonly string _connectionString;
   private string _title;
   private string _director;
   public string Title
   {
       get { return _title; }
       set { _title = value; }
   }
   public string Director
   {
       get { return _director; }
       set { _director = value; }
   }
   public List<Product> GetAll()
   {
       List<Product> results = new List<Product>();
       SqlConnection con = new SqlConnection(_connectionString);
       SqlCommand cmd = new SqlCommand("SELECT Title,Director FROM Products", con);
       using (con)
       {
           con.Open();
           SqlDataReader reader = cmd.ExecuteReader();
           while (reader.Read())
           {
               Product newProduct = new Product();
               newProduct.Title = (string)reader["Title"];
               newProduct.Director = (string)reader["Director"];
               results.Add(newProduct);
           }
       }
       return results;
   }
   static Product()
   {
       _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: ShowProduct.aspx <%@ Page Language="C#" %> <!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>Show Product</title>

</head> <body>

   <form id="form1" runat="server">
   <asp:GridView
       id="grdProducts"
       DataSourceID="srcProducts"
       Runat="server" />
   <asp:ObjectDataSource
       id="srcProducts"
       TypeName="Product"
       SelectMethod="GetAll"
       Runat="server" />
   </form>

</body> </html></source>


DataReader Multiple result set

   <source lang="csharp">

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DataReaderMultiple" %> <!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:Literal runat="server" ID="HtmlContent" />
   </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; using System.Text; using System.Data.SqlClient; public partial class DataReaderMultiple : System.Web.UI.Page {

   protected void Page_Load(object sender, EventArgs e)
   {
   string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
   SqlConnection con = new SqlConnection(connectionString);
   string sql = @"SELECT TOP 5 EmployeeID, FirstName, LastName FROM Employees;" +
     "SELECT TOP 5 ContactName, ContactTitle FROM Customers;" +
     "SELECT TOP 5 SupplierID, CompanyName, ContactName FROM Suppliers";
   SqlCommand cmd = new SqlCommand(sql, con);
   con.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   StringBuilder htmlStr = new StringBuilder("");
   int i = 0;
   do
   {
htmlStr.Append("

Rowset: "); htmlStr.Append(i.ToString()); htmlStr.Append("

");
     while (reader.Read())
     {
htmlStr.Append("
  • "); for (int field = 0; field < reader.FieldCount; field++) { htmlStr.Append(reader.GetName(field).ToString()); htmlStr.Append(": "); htmlStr.Append(reader.GetValue(field).ToString()); htmlStr.Append("   "); } htmlStr.Append("
  • ");
         }
         htmlStr.Append("

    "); i++; } while (reader.NextResult());
       reader.Close();
       con.Close();
       HtmlContent.Text = htmlStr.ToString();
       }
    

    } File: Web.config <?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.ru/.NetConfiguration/v2.0">

     <connectionStrings>
       <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
     </connectionStrings>
     <appSettings>
       <add key="factory" value="System.Data.SqlClient" />
       <add key="employeeQuery" value="SELECT * FROM Employees" />
     </appSettings>
     <system.web>
       <compilation debug="true"/>
       <authentication mode="Windows"/>
     </system.web>
    

    </configuration></source>


    Returning a Resultset

       <source lang="csharp">
    

    File: App_Code\Product.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class Product {

       private static readonly string _connectionString;
       private string _title;
       private string _director;
       public string Title
       {
           get { return _title; }
           set { _title = value; }
       }
       public string Director
       {
           get { return _director; }
           set { _director = value; }
       }
       public List<Product> GetAll()
       {
           List<Product> results = new List<Product>();
           SqlConnection con = new SqlConnection(_connectionString);
           SqlCommand cmd = new SqlCommand("SELECT Title,Director FROM Products", con);
           using (con)
           {
               con.Open();
               SqlDataReader reader = cmd.ExecuteReader();
               while (reader.Read())
               {
                   Product newProduct = new Product();
                   newProduct.Title = (string)reader["Title"];
                   newProduct.Director = (string)reader["Director"];
                   results.Add(newProduct);
               }
           }
           return results;
       }    
       static Product()
       {
           _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
       }
    

    }

    File: ShowProduct.aspx <%@ Page Language="C#" %> <!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>Show Product</title>
    

    </head> <body>

       <form id="form1" runat="server">
    
       <asp:GridView
           id="grdProducts"
           DataSourceID="srcProducts"
           Runat="server" />
       <asp:ObjectDataSource
           id="srcProducts"
           TypeName="Product"
           SelectMethod="GetAll"
           Runat="server" />
    
       </form>
    

    </body> </html> File: Web.config <configuration>

     <connectionStrings>
       <add name="Products" 
            connectionString="Data Source=.\SQLEXPRESS;
            AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
     </connectionStrings>
    

    </configuration></source>


    The SqlConnection, SqlCommand, and SqlDataReader objects (C#)

       <source lang="csharp">
    

    <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">

       protected void Page_Load(object sender, EventArgs e)
       {
           if (!Page.IsPostBack)
           {
               SqlConnection MyConnection;
               SqlCommand MyCommand;
               SqlDataReader MyReader;
               MyConnection = new SqlConnection();
               MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
               MyCommand = new SqlCommand();
               MyCommand.rumandText = " SELECT TOP 3 * FROM CUSTOMERS ";
               MyCommand.rumandType = CommandType.Text;
               MyCommand.Connection = MyConnection;
               MyCommand.Connection.Open();
               MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
               gvCustomers.DataSource = MyReader;
               gvCustomers.DataBind();
               MyCommand.Dispose();
               MyConnection.Dispose();
           }
       }
    

    </script> <html> <body>

       <form id="form1" runat="server">
    
           <asp:GridView ID="gvCustomers" runat="server">
           </asp:GridView>    
    
       </form>
    

    </body> </html> File: Web.config <configuration>

     <connectionStrings>
           <add name="DSN_Northwind" 
                connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
                providerName="System.Data.SqlClient" />
       </connectionStrings>
    

    </configuration></source>


    The SqlConnection, SqlCommand, and SqlDataReader objects (VB)

       <source lang="csharp">
    

    <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">

       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 
           If Not Page.IsPostBack Then
               Dim MyConnection As SqlConnection
               Dim MyCommand As SqlCommand
               Dim MyReader As SqlDataReader
               MyConnection = New SqlConnection()
               MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
               MyCommand = New SqlCommand()
               MyCommand.rumandText = "SELECT TOP 3 * FROM CUSTOMERS"
               MyCommand.rumandType = CommandType.Text
               MyCommand.Connection = MyConnection
               MyCommand.Connection.Open()
               MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
               gvCustomers.DataSource = MyReader
               gvCustomers.DataBind()
               MyCommand.Dispose()
               MyConnection.Dispose()
           End If
       End Sub
    

    </script> <html> <body>

       <form id="form1" runat="server">
    
           <asp:GridView ID="gvCustomers" runat="server">
           </asp:GridView>    
    
       </form>
    

    </body> </html> File: Web.config <configuration>

     <connectionStrings>
           <add name="DSN_Northwind" 
                connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
                providerName="System.Data.SqlClient" />
       </connectionStrings>
    

    </configuration></source>