ASP.NET Tutorial/ADO.net Database/SqlDataReader
Содержание
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("
} 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>