ASP.NET Tutorial/ADO.net Database/SqlCommand — различия между версиями

Версия 15:30, 26 мая 2010

Содержание

1 Attach SqlCommand to DataGrid
2 Avoid SQL injection
3 Avoid SQL Injection attack
4 Browser Snoop
5 Create SqlCommand from sql statement and connection
6 Execuate select command by using the SqlCommand
7 Execute insert command by using SqlCommand
8 Execute update command
9 Executing a Command
10 Executing a Command with Parameters
11 Executing Asynchronous Database Commands
12 Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
13 Read scalar data by using SqlCommand
14 Returning a Single Value

Attach SqlCommand to DataGrid

<%@ 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>
            &nbsp; 
            <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>

Avoid SQL injection

<%@ 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">
    <div>
      <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>
    </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.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();
  }
}

Avoid SQL Injection attack

<%@ 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">
    <div>
        <h1>Add New Shipper</h1>
        <asp:Label ID="lblCompanyName" runat="server" Text="Company Name:"> </asp:Label> 
        <asp:TextBox ID="txtCompanyName" runat="server"></asp:TextBox>
        <br />
        <asp:Label ID="lblPhone" runat="server" Text="Phone:"> </asp:Label> <asp:TextBox ID="txtPhone"
            runat="server"></asp:TextBox><br />
        <br />
        <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" /></div>
    </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();
        }
    }
}

Browser Snoop

<% @ Page Language="VB" %>
<html>
<head>
   <title>Browser Snoop</title>
</head>
<body>
  <center><h1>Browser Capabilities</h1></center><hr/>
  <% 
        Response.Write("ActiveX Controls: " & Request.Browser.ActiveXControls & "<br/>")
        Response.Write("AOL: " & Request.Browser.AOL & "<br/>")
        Response.Write("Background Sounds: " & Request.Browser.BackgroundSounds & "<br/>")
        Response.Write("Beta: " & Request.Browser.Beta & "<br/>")
        Response.Write("CDF: " & Request.Browser.CDF & "<br/>")
        Response.Write("CLR Version: " & Request.Browser.ClrVersion.ToString() & "<br/>")
        Response.Write("Cookies: " & Request.Browser.Cookies & "<br/>")
        Response.Write("Crawler: " & Request.Browser.Crawler & "<br/>")
        Response.Write("ECMA Script Version: " & Request.Browser.EcmaScriptVersion.ToString() & "<br/>")
        Response.Write("Frames: " & Request.Browser.Frames & "<br/>")
        Response.Write("Java Applets: " & Request.Browser.JavaApplets & "<br/>")
        Response.Write("JavaScript: " & Request.Browser.JavaScript & "<br/>")
        Response.Write("Major Version: " & Request.Browser.MajorVersion & "<br/>")
        Response.Write("Minor Version: " & Request.Browser.MinorVersion & "<br/>")
        Response.Write("MS DOM Version: " & Request.Browser.MSDomVersion.ToString() & "<br/>")
        Response.Write("Platform: " & Request.Browser.Platform & "<br/>")
        Response.Write("Tables: " & Request.Browser.Tables & "<br/>")
        Response.Write("Tag Writer: " & Request.Browser.TagWriter.ToString() & "<br/>")
        Response.Write("Type: " & Request.Browser.Type & "<br/>")
        Response.Write("VBScript: " & Request.Browser.VBScript & "<br/>")
        Response.Write("W3C DOM Version: " & Request.Browser.W3CDomVersion.ToString() & "<br/>")
        Response.Write("Win16: " & Request.Browser.Win16 & "<br/>")
        Response.Write("Win32: " & Request.Browser.Win32 & "<br/>")
  %>
</body>
</html>

Create SqlCommand from sql statement and connection

<%@ 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">
    <div>
      <h2>Employees</h2>
    <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.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("<li>");
      htmlStr.Append(reader["TitleOfCourtesy"]);
      htmlStr.Append(" <b>");
      htmlStr.Append(reader.GetString(1));
      htmlStr.Append("</b>, ");
      htmlStr.Append(reader.GetString(2));
      htmlStr.Append(" - employee from ");
      htmlStr.Append(reader.GetDateTime(6).ToString("d"));
      htmlStr.Append("</li>");
    }
    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>

Execuate select command by using the SqlCommand

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

Execute insert command by using SqlCommand

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

Execute update command

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

Executing a Command

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">
    <div>
    <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" />
    </div>
    </form>
</body>
</html>

Executing a Command with Parameters

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

Executing Asynchronous Database Commands

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">
    <div>
    <asp:Label
        id="lblError"
        Runat="server" />
    <asp:GridView
        id="grdProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>

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

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

Read scalar data by using SqlCommand

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

Returning a Single Value

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">
    <div>
    <h1>Box Office Totals</h1>
    <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" />
    <hr />
    <asp:Label
        id="lblResult"
        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>

ASP.NET Tutorial/ADO.net Database/SqlCommand — различия между версиями

Версия 15:30, 26 мая 2010

Содержание

Attach SqlCommand to DataGrid

Avoid SQL injection

Avoid SQL Injection attack

Browser Snoop

Create SqlCommand from sql statement and connection

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

Навигация

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

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

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация

Текущая версия на 11:56, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Версия 15:30, 26 мая 2010 (просмотреть исходный код) (обсуждение) Следующая правка →
(нет различий)