ASP.NET Tutorial/ADO.net Database/SqlCommand
Содержание
- 1 Attach SqlCommand to DataGrid
- 2 Avoid SQL injection
- 3 Avoid SQL Injection attack
- 4 Add New Shipper
- 5 Browser Capabilities
- 5.1 Create SqlCommand from sql statement and connection
- 5.2 Employees
- 5.3 Execuate select command by using the SqlCommand
- 5.4 Execute insert command by using SqlCommand
- 5.5 Execute update command
- 5.6 Executing a Command
- 5.7 Executing a Command with Parameters
- 5.8 Executing Asynchronous Database Commands
- 5.9 Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
- 5.10 Read scalar data by using SqlCommand
- 5.11 Returning a Single Value
- 6 Box Office Totals
Attach SqlCommand to DataGrid
<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) { if (!Page.IsPostBack) { string ConnectionString = ConfigurationSettings.AppSettings["MSDEConnectString"]; SqlConnection myConnection = new SqlConnection(ConnectionString); try{ string CommandTextPublisher = "SELECT PublisherID, PublisherName FROM Publisher"; SqlCommand myCommandPublishers = new SqlCommand(CommandTextPublisher, myConnection); myConnection.Open(); DropDownList1.DataSource = myCommandPublishers.ExecuteReader(); DropDownList1.DataTextField = "PublisherName"; DropDownList1.DataValueField = "PublisherID"; DropDownList1.DataBind(); DropDownList1.Items.Insert(0, new ListItem("-- All Publishers --", "0")); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } } } void ApplyFilter_Click(Object sender, EventArgs e) { string ConnectionString = ConfigurationSettings.AppSettings["MSDEConnectString"]; SqlConnection myConnection = new SqlConnection(ConnectionString); try{ string CommandTextBooks = "SELECT Book.BookTitle, Publisher.PublisherName FROM Book INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID"; string filterValue = DropDownList1.SelectedValue; if (filterValue != "0") CommandTextBooks += " WHERE Book.BookPublisherID = " + filterValue; CommandTextBooks+=" ORDER BY Book.BookTitle"; SqlCommand myCommandBooks = new SqlCommand(CommandTextBooks, myConnection); myConnection.Open(); DataGrid1.DataSource = myCommandBooks.ExecuteReader(); DataGrid1.DataBind(); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } }
</script> <html> <head> </head> <body>
<form runat="server"> Select a Publisher: <asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList> <asp:Button id="Button1" onclick="ApplyFilter_Click" runat="server" Text="Show Titles"></asp:Button> <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </form>
</body> </html>
File: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
Avoid SQL injection
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SqlInjectionCorrected" %> <!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:TextBox id="txtID" runat="server">ALFKI" OR "1"="1</asp:TextBox> <asp:Label id="Label1" runat="server">Enter Customer ID:</asp:Label> <asp:Button id="cmdGetRecords" runat="server" Text="Get Records" OnClick="cmdGetRecords_Click"></asp:Button> <asp:GridView id="GridView1" runat="server" Width="392px" Height="123px"></asp:GridView>
</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.Data.SqlClient; public partial class SqlInjectionCorrected : System.Web.UI.Page {
protected void cmdGetRecords_Click(object sender, System.EventArgs e) { string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" + "Integrated Security=SSPI"; SqlConnection con = new SqlConnection(connectionString); string sql = "SELECT Orders.CustomerID, Orders.OrderID, COUNT(UnitPrice) AS Items, " + "SUM(UnitPrice * Quantity) AS Total FROM Orders " + "INNER JOIN [Order Details] " + "ON Orders.OrderID = [Order Details].OrderID " + "WHERE Orders.CustomerID = @CustID " + "GROUP BY Orders.OrderID, Orders.CustomerID"; SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.Add("@CustID", txtID.Text); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind(); reader.Close(); con.Close(); }
}</source>
Avoid SQL Injection attack
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="AddShipper" %> <!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>Add New Shipper</title>
</head> <body>
<form id="form1" runat="server">
Add New Shipper
<asp:Label ID="lblCompanyName" runat="server" Text="Company Name:"> </asp:Label> <asp:TextBox ID="txtCompanyName" runat="server"></asp:TextBox>Text="Good Add Shipper" />
<asp:Label ID="lblPhone" runat="server" Text="Phone:"> </asp:Label> <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
<asp:Button ID="btnBadAddShipper" runat="server" Text="Bad Add Shipper" OnClick="btnBadAddShipper_Click" /> <asp:Button ID="btnGoodAddShipper" runat="server" OnClick="btnGoodAddShipper_Click"
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Configuration; 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; public partial class AddShipper : System.Web.UI.Page {
protected void btnBadAddShipper_Click(object sender, EventArgs e) { string connStr = "Server=(local);Database=Northwind;Integrated Security=SSPI"; string cmdStr = "insert into Shippers (CompanyName, Phone) values ("" + txtCompanyName.Text + "", "" + txtPhone.Text + "")"; using (SqlConnection conn = new SqlConnection(connStr)) using (SqlCommand cmd = new SqlCommand(cmdStr, conn)) { conn.Open(); cmd.ExecuteNonQuery(); } } protected void btnGoodAddShipper_Click(object sender, EventArgs e) { string connStr = "Server=(local);Database=Northwind;Integrated Security=SSPI"; string cmdStr = "insert into Shippers (CompanyName, Phone) values (" + "@CompanyName, @Phone)"; using (SqlConnection conn = new SqlConnection(connStr)) using (SqlCommand cmd = new SqlCommand(cmdStr, conn)) { cmd.Parameters.AddWithValue("@CompanyName", txtCompanyName.Text); cmd.Parameters.AddWithValue("@Phone", txtPhone.Text); conn.Open(); cmd.ExecuteNonQuery(); } }
}</source>
Browser Snoop
<source lang="csharp">
<% @ Page Language="VB" %> <html> <head>
<title>Browser Snoop</title>
</head> <body>
Browser Capabilities
<% Response.Write("ActiveX Controls: " & Request.Browser.ActiveXControls & "
") Response.Write("AOL: " & Request.Browser.AOL & "
") Response.Write("Background Sounds: " & Request.Browser.BackgroundSounds & "
") Response.Write("Beta: " & Request.Browser.Beta & "
") Response.Write("CDF: " & Request.Browser.CDF & "
") Response.Write("CLR Version: " & Request.Browser.ClrVersion.ToString() & "
") Response.Write("Cookies: " & Request.Browser.Cookies & "
") Response.Write("Crawler: " & Request.Browser.Crawler & "
") Response.Write("ECMA Script Version: " & Request.Browser.EcmaScriptVersion.ToString() & "
") Response.Write("Frames: " & Request.Browser.Frames & "
") Response.Write("Java Applets: " & Request.Browser.JavaApplets & "
") Response.Write("JavaScript: " & Request.Browser.JavaScript & "
") Response.Write("Major Version: " & Request.Browser.MajorVersion & "
") Response.Write("Minor Version: " & Request.Browser.MinorVersion & "
") Response.Write("MS DOM Version: " & Request.Browser.MSDomVersion.ToString() & "
") Response.Write("Platform: " & Request.Browser.Platform & "
") Response.Write("Tables: " & Request.Browser.Tables & "
") Response.Write("Tag Writer: " & Request.Browser.TagWriter.ToString() & "
") Response.Write("Type: " & Request.Browser.Type & "
") Response.Write("VBScript: " & Request.Browser.VBScript & "
") Response.Write("W3C DOM Version: " & Request.Browser.W3CDomVersion.ToString() & "
") Response.Write("Win16: " & Request.Browser.Win16 & "
") Response.Write("Win32: " & Request.Browser.Win32 & "
") %>
</body> </html></source>
Create SqlCommand from sql statement and connection
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DataReader" %> <!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">
Employees
<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.Data.SqlClient; using System.Text; using System.Web.Configuration; public partial class DataReader : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) { string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); string sql = "SELECT * FROM Employees"; SqlCommand cmd = new SqlCommand(sql, con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); StringBuilder htmlStr = new StringBuilder(""); while (reader.Read()) {htmlStr.Append("
} 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>
Execuate select command by using the 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 = ConfigurationSettings.AppSettings["MSDEConnectString"]; string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID"; SqlConnection myConnection = new SqlConnection(ConnectionString); SqlCommand myCommand = new SqlCommand(CommandText, myConnection); myConnection.Open(); DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection); DataGrid1.DataBind(); }
</script> <html> <head> </head> <body>
<form runat="server"> <asp:datagrid id="DataGrid1" runat="server" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </form>
</body> </html> File: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
Execute insert command by using 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 = ConfigurationSettings.AppSettings["MSDEConnectString"]; SqlConnection myConnection = new SqlConnection(ConnectionString); try{ string CommandText = "INSERT Publisher (PublisherName, PublisherCity, PublisherContact_Email, PublisherWebsite) VALUES ("New Publisher", "Newcastle", "bigcheese@newpublish.ru", "http://www.newpublish.ru")"; SqlCommand myCommand = new SqlCommand(CommandText, myConnection); myConnection.Open(); lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery()); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } }
</script> <html> <head> </head> <body>
<form runat="server"> Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label> </form>
</body> </html>
File: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
Execute update command
<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 = ConfigurationSettings.AppSettings["MSDEConnectString"]; SqlConnection myConnection = new SqlConnection(ConnectionString); try{ string CommandText = "UPDATE Publisher Set PublisherName="Old Publisher", PublisherCity = "Manchester" WHERE PublisherID = 6"; SqlCommand myCommand = new SqlCommand(CommandText, myConnection); myConnection.Open(); lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery()); } catch (Exception ex){ throw(ex); } finally{ myConnection.Close(); } }
</script> <html> <head> </head> <body>
<form runat="server"> Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label> </form>
</body> </html> File: Web.config <configuration>
<appSettings> <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
Executing a Command
<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 int _id; private string _title; private string _director; public int Id { get { return _id; } set { _id = value; } } public string Title { get { return _title; } set { _title = value; } } public string Director { get { return _director; } set { _director = value; } } public void Update(int id, string title, string director) { SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("UPDATE ProductS SET Title=@Title,Director=@Director WHERE Id=@Id", con); cmd.Parameters.AddWithValue("@Title", title); cmd.Parameters.AddWithValue("@Director", director); cmd.Parameters.AddWithValue("@Id", id); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public void Delete(int id) { SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("DELETE ProductS WHERE Id=@Id", con); cmd.Parameters.AddWithValue("@Id", id); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public List<Product> GetAll() { List<Product> results = new List<Product>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT Id,Title,Director FROM Products", con); using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Product newProduct = new Product(); newProduct.Id = (int)reader["Id"]; 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: index.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" DataKeyNames="Id" AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="Product" SelectMethod="GetAll" UpdateMethod="Update" DeleteMethod="Delete" Runat="server" />
</form>
</body> </html></source>
Executing a Command with Parameters
<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 int _id; private string _title; private string _director; public int Id { get { return _id; } set { _id = value; } } public string Title { get { return _title; } set { _title = value; } } public string Director { get { return _director; } set { _director = value; } } public void Update(int id, string title, string director) { SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("ProductUpdate", con); cmd.rumandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Id", id); cmd.Parameters.AddWithValue("@Title", title); cmd.Parameters.AddWithValue("@Director", director); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public List<Product> GetAll() { List<Product> results = new List<Product>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("ProductSelect", con); cmd.rumandType = CommandType.StoredProcedure; using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Product newProduct = new Product(); newProduct.Id = (int)reader["Id"]; 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: ProductStoredProcedures.sql CREATE PROCEDURE dbo.ProductSelect AS SELECT Id, Title, Director FROM Products CREATE PROCEDURE dbo.ProductUpdate (
@Id int, @Title NVarchar(100), @Director NVarchar(100)
) AS UPDATE Products SET
Title = @Title, Director = @Director
WHERE Id = @Id
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" DataKeyNames="Id" AutoGenerateEditButton="true" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="Product" SelectMethod="GetAll" UpdateMethod="Update" Runat="server" />
</form>
</body> </html></source>
Executing Asynchronous Database Commands
<source lang="csharp">
File: App_Code\AsyncDataLayer.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Collections.Generic; public class AsyncDataLayer {
private static readonly string _connectionString; private SqlCommand _cmdProducts; public IAsyncResult BeginGetProducts(AsyncCallback callback, Object state) { SqlConnection con = new SqlConnection(_connectionString); _cmdProducts = new SqlCommand("WAITFOR DELAY "0:0:01";SELECT Title,Director FROM Products", con); con.Open(); return _cmdProducts.BeginExecuteReader(callback, state, CommandBehavior.CloseConnection); } public List<AsyncDataLayer.Product> EndGetProducts(IAsyncResult result) { List<AsyncDataLayer.Product> results = new List<AsyncDataLayer.Product>(); SqlDataReader reader = _cmdProducts.EndExecuteReader(result); while (reader.Read()) { AsyncDataLayer.Product newProduct = new AsyncDataLayer.Product(); newProduct.Title = (string)reader["Title"]; newProduct.Director = (string)reader["Director"]; results.Add(newProduct); } return results; } static AsyncDataLayer() { _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString + ";Asynchronous Processing=true"; } public class Product { private string _title; private string _director; public string Title { get { return _title; } set { _title = value; } } public string Director { get { return _director; } set { _director = value; } } }
} 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: Default.aspx <%@ Page Language="C#" Async="true" AsyncTimeout="1" Trace="true" %> <%@ Import Namespace="System.Threading" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
private AsyncDataLayer dataLayer = new AsyncDataLayer(); void Page_Load() { PageAsyncTask task = new PageAsyncTask(BeginGetData, EndGetData, TimeoutData, null, true); Page.RegisterAsyncTask(task); Page.ExecuteRegisteredAsyncTasks(); } IAsyncResult BeginGetData(object sender, EventArgs e, AsyncCallback callback, object state) { Trace.Warn("BeginGetData: " + Thread.CurrentThread.GetHashCode()); return dataLayer.BeginGetProducts(callback, state); } void EndGetData(IAsyncResult ar) { Trace.Warn("EndGetDate: " + Thread.CurrentThread.GetHashCode()); grdProducts.DataSource = dataLayer.EndGetProducts(ar); grdProducts.DataBind(); } void TimeoutData(IAsyncResult ar) { lblError.Text = "Could not retrieve data!"; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Page AsyncTask</title>
</head> <body>
<form id="form1" runat="server">
<asp:Label id="lblError" Runat="server" /> <asp:GridView id="grdProducts" Runat="server" />
</form>
</body> </html></source>
Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
<source lang="csharp">
This parameter causes the database connection associated with the SqlDataReader to close automatically after all the records have been fetched from the SqlDataReader. 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; public SqlDataReader GetAll() { SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT Title,Director FROM Products", con); con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } 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>
Read scalar data by using 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 = ConfigurationSettings.AppSettings["MSDEConnectString"]; string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID"; SqlConnection myConnection = new SqlConnection(ConnectionString); SqlCommand myCommand = new SqlCommand(CommandText, myConnection); myConnection.Open(); string CommandTextCount = "SELECT COUNT(*) FROM Publisher"; SqlCommand myCommandCount = new SqlCommand(CommandTextCount, myConnection); lblTotal.Text = Convert.ToString(myCommandCount.ExecuteScalar()); DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection); DataGrid1.DataBind(); }
</script> <html> <head> </head> <body>
<asp:Label id="lblTotal" runat="server"></asp:Label> <form runat="server"> <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </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 a Single Value
<source lang="csharp">
File: Default.aspx <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Web.Configuration" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("SELECT Totals FROM Products WHERE Title=@Title", con); cmd.Parameters.AddWithValue("@Title", txtTitle.Text); using (con) { con.Open(); Object result = cmd.ExecuteScalar(); if (result != null) lblResult.Text = String.Format("{0:c}", result); else lblResult.Text = "No match!"; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Execute Scalar</title>
</head> <body>
<form id="form1" runat="server">
Box Office Totals
<asp:Label id="lblTitle" Text="Product Title:" AssociatedControlID="txtTitle" Runat="server" /> <asp:TextBox id="txtTitle" Runat="server" /> <asp:Button id="btnSearch" Text="Search" OnClick="btnSearch_Click" Runat="server" />
<asp:Label id="lblResult" 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>