ASP.NET Tutorial/ADO.net Database/SqlDataReader

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

Building Data Access Components with ADO.NET

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">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="Product"
        SelectMethod="GetAll"
        Runat="server" />
    </div>
    </form>
</body>
</html>


DataReader Multiple result set

<%@ 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">
    <div>
  
    <asp:Literal runat="server" ID="HtmlContent" />
    </div>
    </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("<h2>Rowset: ");
      htmlStr.Append(i.ToString());
      htmlStr.Append("</h2>");
      while (reader.Read())
      {
        htmlStr.Append("<li>");
        for (int field = 0; field < reader.FieldCount; field++)
        {
          htmlStr.Append(reader.GetName(field).ToString());
          htmlStr.Append(": ");
          htmlStr.Append(reader.GetValue(field).ToString());
          htmlStr.Append("&nbsp;&nbsp;&nbsp;");
        }
        htmlStr.Append("</li>");
      }
      htmlStr.Append("<br><br>");
      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>


Returning a Resultset

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">
    <div>
    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="Product"
        SelectMethod="GetAll"
        Runat="server" />
    </div>
    </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>


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

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>


The SqlConnection, SqlCommand, and SqlDataReader objects (VB)

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/SqlDataReader&oldid=2649»