Executing Asynchronous Database Commands : SqlCommand « ADO.net Database « ASP.NET Tutorial





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>










18.3.SqlCommand
18.3.1.Create SqlCommand from sql statement and connection
18.3.2.Executing a Command
18.3.3.Executing a Command with Parameters
18.3.4.Returning a Single Value
18.3.5.Read scalar data by using SqlCommand
18.3.6.Execute insert command by using SqlCommand
18.3.7.Execuate select command by using the SqlCommand
18.3.8.Execute update command
18.3.9.Attach SqlCommand to DataGrid
18.3.10.Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
18.3.11.Executing Asynchronous Database Commands
18.3.12.Avoid SQL injection
18.3.13.Avoid SQL Injection attack
18.3.14.Browser Snoop