[ASP.NET 2.0] - Quelques utilisations du cache – chargement asynchrone avec SQL Server 2005

[ASP.NET 2.0] - Quelques utilisations du cache – chargement asynchrone avec SQL Server 2005

I - Mise en cache de la page :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Outputcache Duration="60" VaryByParam="none" %>

La page est mise en cache pour une minute ici

II - Mise en cache de données :

protected void Page_Load(object sender, EventArgs e)

    {

System.Data.OleDb.OleDbDataAdapter oOleDbDataAdapter = new                                      System.Data.OleDb.OleDbDataAdapter();

System.Data.DataTable oDataTable = new DataTable();

        try

        {

            if (Cache["ContactsKey"] == null)

            {

                oOleDbDataAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand();

                oOleDbDataAdapter.SelectCommand.Connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsromagnyMes documentsAccessContactsDb.mdb;");

                oOleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;

                oOleDbDataAdapter.SelectCommand.CommandText = "SELECT * FROM Contact";

                oOleDbDataAdapter.SelectCommand.Connection.Open();

                oOleDbDataAdapter.Fill(oDataTable);

                Cache["ContactsKey"] = oDataTable;

            }

            GridView1.DataSource = Cache["ContactsKey"];

            GridView1.DataBind();

        }

        catch (System.SystemException ex)

        {

            //

            lblMessage.Text = ex.Message;

        }

        finally

        {

            if (oOleDbDataAdapter != null)

                if (oOleDbDataAdapter.SelectCommand != null)

                    if (oOleDbDataAdapter.SelectCommand.Connection.State == ConnectionState.Open)

                        oOleDbDataAdapter.SelectCommand.Connection.Close();

        }

    }

III - Avec cachedependency

1 er exemple : avec un fichier ici je « branche sur la base de données Acces, si des changemeents interviennent alors le cache concerné sera vidé

protected void Page_Load(object sender, EventArgs e)

    {

        System.Data.OleDb.OleDbDataAdapter oOleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter();

        System.Data.DataTable oDataTable = new DataTable();

        try

        {

            if (Cache["ContactsKey"] == null)

            {

                oOleDbDataAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand();

                oOleDbDataAdapter.SelectCommand.Connection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsromagnyMes documentsAccessContactsDb.mdb;");

                oOleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;

                oOleDbDataAdapter.SelectCommand.CommandText = "SELECT * FROM Contact";

                oOleDbDataAdapter.SelectCommand.Connection.Open();

                oOleDbDataAdapter.Fill(oDataTable);

                System.Web.Caching.CacheDependency oCacheDependency = new System.Web.Caching.CacheDependency(@"C:Documents and SettingsromagnyMes documentsAccessContactsDb.mdb");

                Cache.Insert("ContactsKey", oDataTable, oCacheDependency);

            }

            GridView1.DataSource = Cache["ContactsKey"];

            GridView1.DataBind();

        }

        catch (System.SystemException ex)

        {

            //

            lblMessage.Text = ex.Message;

        }

        finally

        {

            if (oOleDbDataAdapter != null)

                if (oOleDbDataAdapter.SelectCommand != null)

                    if (oOleDbDataAdapter.SelectCommand.Connection.State == ConnectionState.Open)

                        oOleDbDataAdapter.SelectCommand.Connection.Close();

        }

    }

2 eme exemple : avec SQL Server

A - Dans Web.config

<configuration>

      <appSettings/>

 <connectionStrings>

    <add name="ContactsDbConnectionString"

         connectionString="Data Source=.;Initial Catalog=ContactsDB;Integrated Security=SSPI;"

         providerName="System.Data.SqlClient"

         />

 </connectionStrings>

      <system.web>

    <caching>

      <sqlCacheDependency enabled="true">

        <databases>

          <add name="ContactsDbSqlConnection"

               connectionStringName="ContactsDbConnectionString"

               pollTime="500"

               />

        </databases>

      </sqlCacheDependency>

    </caching>

B – Attention il faut avoir activer la notification des tables grace à l’utilitaire aspnet_regsql.exe (C:/WINDOWS/Microsoft.NET/Frameworkv2.0.50727)

Ce qui donne quelque chose du style :

aspnet_regsql.exe –S DONJR –U sa –d ContactsDB -ed

Puis pour chaque table

aspnet_regsql.exe –S DONJR –U sa –d ContactsDB  -t Contact -et

-          DONJR > c’est le nom du serveur

-          sa > nom de l’utilisateur (ajouter le mot de passe avec –p)

-          ContactsDB > c’est la base de données

-          Contact > c’est le nom d’une des tables à activer

C - Code

protected void Page_Load(object sender, EventArgs e)

    {

System.Data.SqlClient.SqlDataAdapter oSqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter();

        System.Data.DataTable oDataTable = new DataTable();

        try

        {

            if (Cache["ContactsKeySql"] == null)

            {

                oSqlDataAdapter.SelectCommand = new System.Data.SqlClient.SqlCommand();

                oSqlDataAdapter.SelectCommand.Connection = new System.Data.SqlClient.SqlConnection(@"Data Source=.;Initial Catalog=ContactsDB;Integrated Security=SSPI;");

                oSqlDataAdapter.SelectCommand.CommandType = CommandType.Text;

                oSqlDataAdapter.SelectCommand.CommandText = "SELECT * FROM Contact";

                oSqlDataAdapter.SelectCommand.Connection.Open();

                oSqlDataAdapter.Fill(oDataTable);

                Cache.Insert("ContactsKeySql", oDataTable, new System.Web.Caching.SqlCacheDependency("ContactsDbSqlConnection", "Contact"));

            }

            GridView1.DataSource = Cache["ContactsKeySql"];

            GridView1.DataBind();

        }

        catch (System.SystemException ex)

        {

            //

            lblMessage.Text = ex.Message;

        }

        finally

        {

            if (oSqlDataAdapter != null)

                if (oSqlDataAdapter.SelectCommand != null)

                    if (oSqlDataAdapter.SelectCommand.Connection.State == ConnectionState.Open)

                        oSqlDataAdapter.SelectCommand.Connection.Close();

        }

}

IV - Chargement Asynchrone avec SQL Server

protected void Page_Load(object sender, EventArgs e)

    {

PageAsyncTask oPageAsyncTask = new PageAsyncTask(BeginInvokeMethod, EndInvokeMethod, EndTimeOutInvoke, null);

Page.RegisterAsyncTask(oPageAsyncTask);

    }

System.Data.SqlClient.SqlConnection oSqlConnection;

    System.Data.SqlClient.SqlCommand oSqlCommand;

    public IAsyncResult BeginInvokeMethod(object sender, EventArgs e, AsyncCallback callBack, object stateObject)

    {

        oSqlConnection = new System.Data.SqlClient.SqlConnection(@"async=true;Data Source=.;Initial Catalog=ContactsDB;Integrated Security=SSPI;");

        oSqlCommand = new System.Data.SqlClient.SqlCommand();

        oSqlCommand.Connection = oSqlConnection;

        oSqlCommand.CommandType = CommandType.Text;

        oSqlCommand.CommandText = "SELECT * FROM Contact";

        oSqlConnection.Open();

       return oSqlCommand.BeginExecuteReader(callBack, stateObject, CommandBehavior.CloseConnection);

    }

    public void EndInvokeMethod(IAsyncResult result)

    {

        System.Data.SqlClient.SqlDataReader oSqlDataReader = oSqlCommand.EndExecuteReader(result);

        if (oSqlDataReader != null)

        {

            GridView1.DataSource = oSqlDataReader;

            GridView1.DataBind();

        }

    }

    public void EndTimeOutInvoke(IAsyncResult result)

    {

        if (oSqlConnection != null)

            if (oSqlConnection.State == ConnectionState.Open)

                oSqlConnection.Close();

    }

Méthodes disponibles en asynchrone :

-          BeginExecuteNonQuery()

-          BeginExecuteReader()

-          BeginExecuteXmlReader()

+

-          EndExecuteNonQuery()

-          EndExecuteReader()

-          EndExecuteXmlReader()