Содержание

1 Attach SqlCommand to DataGrid
2 Avoid SQL injection
3 Avoid SQL Injection attack
4 Add New Shipper
- 4.1 Browser Snoop
5 Browser Capabilities
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>
       

       <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"

Text="Good Add Shipper" />

   </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("

"); htmlStr.Append(reader["TitleOfCourtesy"]); htmlStr.Append(" "); htmlStr.Append(reader.GetString(1)); htmlStr.Append(", "); htmlStr.Append(reader.GetString(2)); htmlStr.Append(" - employee from "); htmlStr.Append(reader.GetDateTime(6).ToString("d")); 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">

   <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>

ASP.NET Tutorial/ADO.net Database/SqlCommand

Содержание

Attach SqlCommand to DataGrid

Avoid SQL injection

Avoid SQL Injection attack

Add New Shipper

Browser Snoop

Browser Capabilities

Create SqlCommand from sql statement and connection

Employees

Execuate select command by using the SqlCommand

Execute insert command by using SqlCommand

Execute update command

Executing a Command

Executing a Command with Parameters

Executing Asynchronous Database Commands

Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.

Read scalar data by using SqlCommand

Returning a Single Value

Box Office Totals

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация