Web Cache (DataTable)

OverView : C# WebService 3.5 이상 에서 Db DataTable Cache 화 하기

Controller.HttpContext.Cache 속성을 통해 이 클래스의 인스턴스를 가져올 수 있으며
싱글톤 HttpContext.Current.Cache를 통해 가져올 수도 있습니다.
이 클래스는 내부적으로 할당된 다른 캐싱 엔진을 사용하기 때문에 명시적으로 생성될 것으로 예상되지 않습니다.
코드를 작동시키는 가장 간단한 방법은 다음을 수행하는 것입니다.

1.Simple Source


public class AccountController : System.Web.Mvc.Controller{ 
  public System.Web.Mvc.ActionResult Index(){
    List<object> list = new List<Object>();

    HttpContext.Cache["ObjectList"] = list;                 // add
    list = (List<object>)HttpContext.Cache["ObjectList"]; // retrieve
    HttpContext.Cache.Remove("ObjectList");                 // remove
    return new System.Web.Mvc.EmptyResult();
  }
}

public class AccountController : System.Web.Mvc.Controller{

public System.Web.Mvc.ActionResult Index(){

List<object> list = new List<Object>();

HttpContext.Cache["ObjectList"] = list; // add

list = (List<object>)HttpContext.Cache["ObjectList"]; // retrieve

HttpContext.Cache.Remove("ObjectList"); // remove

return new System.Web.Mvc.EmptyResult();

}

2.HttpContext.Cache

3.DB

4.Insert Query

링크 : https://github.com/iammukeshm/RepositoryPatternWithCachingAndHangfire/blob/master/SampleCustomers.sql


insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Caressa', 'Walsh', '868-449-3013', 'cwalsh0@google.es', '3/23/1996');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Rhea', 'Andrichuk', '733-892-8926', 'randrichuk1@elpais.com', '12/11/1997');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Amelia', 'Epp', '247-422-8619', 'aepp2@spiegel.de', '7/16/1999');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Cesaro', 'Goodwin', '606-804-3382', 'cgoodwin3@salon.com', '8/16/1990');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Lira', 'Crookall', '320-145-2453', 'lcrookall4@g.co', '9/14/1996');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Tye', 'Collin', '548-245-2593', 'tcollin5@dailymail.co.uk', '4/9/1993');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Clare', 'Kelson', '324-249-1362', 'ckelson6@amazon.co.jp', '11/26/1992');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Wilmer', 'Swaite', '657-532-9884', 'wswaite7@addthis.com', '9/22/1990');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Delphine', 'Gasson', '392-230-1556', 'dgasson8@51.la', '8/7/1994');
insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Manon', 'Yeowell', '789-838-9532', 'myeowell9@jimdo.com', '10/11/1999');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Caressa', 'Walsh', '868-449-3013', 'cwalsh0@google.es', '3/23/1996');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Rhea', 'Andrichuk', '733-892-8926', 'randrichuk1@elpais.com', '12/11/1997');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Amelia', 'Epp', '247-422-8619', 'aepp2@spiegel.de', '7/16/1999');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Cesaro', 'Goodwin', '606-804-3382', 'cgoodwin3@salon.com', '8/16/1990');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Lira', 'Crookall', '320-145-2453', 'lcrookall4@g.co', '9/14/1996');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Tye', 'Collin', '548-245-2593', 'tcollin5@dailymail.co.uk', '4/9/1993');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Clare', 'Kelson', '324-249-1362', 'ckelson6@amazon.co.jp', '11/26/1992');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Wilmer', 'Swaite', '657-532-9884', 'wswaite7@addthis.com', '9/22/1990');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Delphine', 'Gasson', '392-230-1556', 'dgasson8@51.la', '8/7/1994');

insert into Customers (FirstName, LastName, Contact, Email, DateOfBirth) values ('Manon', 'Yeowell', '789-838-9532', 'myeowell9@jimdo.com', '10/11/1999');

5.WebService Call


using System.Web.Services;
using System.Data;

namespace WebService1
{
    public class Service1 : System.Web.Services.WebService
    {

        [WebMethod()]
        public DataTable GetProductCatalog()
        {
            // Return the complete DataSet (from the cache, if possible).         
            //return Function.GetCache();
            return CacheClass.GetCustomerDataSet();
        }

        [WebMethod()]
        public string[] GetProductList()
        {
            // Get the customer table (from the cache if possible).         
            DataTable dt = CacheClass.GetCustomerDataSet();
            // Create an array that will hold the name of each customer.         
            string[] names = new string[dt.Rows.Count];
            // Fill the array.         
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                names[i] = row["FirstName"].ToString();
                i += 1;
            }
            return names;
        }
    }
}

using System.Web.Services;

using System.Data;

namespace WebService1

{

public class Service1 : System.Web.Services.WebService

{

[WebMethod()]

public DataTable GetProductCatalog()

{

// Return the complete DataSet (from the cache, if possible).

//return Function.GetCache();

return CacheClass.GetCustomerDataSet();

}

[WebMethod()]

public string[] GetProductList()

{

// Get the customer table (from the cache if possible).

DataTable dt = CacheClass.GetCustomerDataSet();

// Create an array that will hold the name of each customer.

string[] names = new string[dt.Rows.Count];

// Fill the array.

int i = 0;

foreach (DataRow row in dt.Rows)

{

names[i] = row["FirstName"].ToString();

i += 1;

}

return names;

}

6.Cache Insert


using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace WebService1
{
    public class CacheClass
    {
        private static string _key = "Customers";
        private static string connectionString = "Server=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=MYID;Initial Catalog=MYDB;Data Source=MYPC";

        public static DataTable GetCustomerDataSet()
        {
            // Check for cached item.         
            //DataSet ds = HttpContext.Current.Cache["Products"] as DataSet;
            //_cache.Remove(_key);
            //DataTable ds = HttpContext.Current.Cache[_key] as DataTable;
            if (HttpContext.Current.Cache[_key] as DataTable == null)
            {
                // Recreate the item.             
                string SQL = "SELECT * FROM Customers";
                // Create ADO.NET objects.             
                SqlConnection con = new SqlConnection(connectionString);
                SqlCommand com = new SqlCommand(SQL, con);
                SqlDataAdapter adapter = new SqlDataAdapter(com);

                DataTable ds = new DataTable();
                ds.TableName = _key;

                // Execute the command.             
                try
                {
                    con.Open();
                    adapter.Fill(ds);
                    // Store the item in the cache (for 60 Minutes).                 
                    //Do what you need to do here. Database Interaction, Serialization,etc.
                    HttpContext.Current.Cache.Insert("Customers", ds, null, DateTime.Now.AddMinutes(60), TimeSpan.Zero);

                }
                catch (Exception err)
                {
                    System.Diagnostics.Debug.WriteLine(err.ToString());
                }
                finally
                {
                    con.Close();
                }
            }
            return HttpContext.Current.Cache[_key] as DataTable;
        }

    }
}

using System;

using System.Web;

using System.Data;

using System.Data.SqlClient;

namespace WebService1

{

public class CacheClass

{

private static string _key = "Customers";

private static string connectionString = "Server=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=MYID;Initial Catalog=MYDB;Data Source=MYPC";

public static DataTable GetCustomerDataSet()

{

// Check for cached item.

//DataSet ds = HttpContext.Current.Cache["Products"] as DataSet;

//_cache.Remove(_key);

//DataTable ds = HttpContext.Current.Cache[_key] as DataTable;

if (HttpContext.Current.Cache[_key] as DataTable == null)

{

// Recreate the item.

string SQL = "SELECT * FROM Customers";

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand com = new SqlCommand(SQL, con);

SqlDataAdapter adapter = new SqlDataAdapter(com);

DataTable ds = new DataTable();

ds.TableName = _key;

// Execute the command.

try

{

con.Open();

adapter.Fill(ds);

// Store the item in the cache (for 60 Minutes).

//Do what you need to do here. Database Interaction, Serialization,etc.

HttpContext.Current.Cache.Insert("Customers", ds, null, DateTime.Now.AddMinutes(60), TimeSpan.Zero);

}

catch (Exception err)

{

System.Diagnostics.Debug.WriteLine(err.ToString());

}

finally

{

con.Close();

}

return HttpContext.Current.Cache[_key] as DataTable;

}

7.화면

8.Result


<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
<string>Caressa</string>
<string>Rhea</string>
<string>Amelia</string>
<string>Cesaro</string>
<string>Lira</string>
<string>Tye</string>
<string>Clare</string>
<string>Wilmer</string>
<string>Delphine</string>
<string>Manon</string>
<string>Bev</string>
<string>Lodovico</string>

<string>Caressa</string>

<string>Amelia</string>

<string>Cesaro</string>

<string>Clare</string>

<string>Wilmer</string>

<string>Delphine</string>

<string>Manon</string>

<string>Lodovico</string>

9.접속 화면 수가 많아지면 메모리 사용도 올라 간다. (10화면) => 접속 종류 되면 원래대로 된다.

source : https://stackoverflow.com/questions/41684213/looking-for-a-very-simple-cache-example?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa