Memory Cache (3 tables TableMappings)

Overview: DataSet TableMappings을 이용하여 관계형으로 Table 3개를 캐싱해본다.

1.Net Framework 4.6 이상에서 설치가능

2.설치

NuGet 패캐지 관리에서 설치 및 참조 추가 확인

3.참조 확인

4. Simple Source


using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        SqlCommand com = new SqlCommand();
                        SqlDataAdapter da = new SqlDataAdapter(
                            "SELECT [Account_ID]" +
                                  ",[Account_Number]" +
                                  ",[Balance]" +
                                  "  FROM [Bank].[dbo].[Account];" +
                            "SELECT [IDX]" +
                                  ",[Account_ID]" +
                                  ",[Name]" +
                                  ",[Mobile]" +
                                  ",[Regi_Date]" +
                                  "  FROM [Bank].[dbo].[CustomerDetails];" +
                             "SELECT [IDX]" +
                                  ",[Account_ID]" +
                                  ",[Amount]" +
                                  ",[New_Balance]" +
                                  ",[Transaction_Date]" +
                                  "  FROM [Bank].[dbo].[Transaction];"
                                  , conn);
                        // Add table mappings.
                        //da.SelectCommand.Parameters.AddWithValue("@year", 2002);
                        da.TableMappings.Add("Table", "Account");
                        da.TableMappings.Add("Table1", "CustomerDetails");
                        da.TableMappings.Add("Table2", "Transaction");
                        da.Fill(ds);
                        DataTable account = ds.Tables["Account"];
                        DataTable customerdetails = ds.Tables["CustomerDetails"];
                        DataTable transaction = ds.Tables["Transaction"];
                        DataRelation relation1 = new DataRelation("AccountCustomerDetails",
                                                    account.Columns["Account_ID"],
                                                    customerdetails.Columns["Account_ID"], true);
                        DataRelation relation2 = new DataRelation("CustomerDetailsTransaction",
                                                    customerdetails.Columns["Account_ID"],
                                                    transaction.Columns["Account_ID"], true);
                        ds.Relations.Add(relation1);
                        ds.Relations.Add(relation2);
                    }

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand com = new SqlCommand();

SqlDataAdapter da = new SqlDataAdapter(

"SELECT [Account_ID]" +

",[Account_Number]" +

",[Balance]" +

" FROM [Bank].[dbo].[Account];" +

"SELECT [IDX]" +

",[Account_ID]" +

",[Name]" +

",[Mobile]" +

",[Regi_Date]" +

" FROM [Bank].[dbo].[CustomerDetails];" +

"SELECT [IDX]" +

",[Account_ID]" +

",[Amount]" +

",[New_Balance]" +

",[Transaction_Date]" +

" FROM [Bank].[dbo].[Transaction];"

, conn);

// Add table mappings.

//da.SelectCommand.Parameters.AddWithValue("@year", 2002);

da.TableMappings.Add("Table", "Account");

da.TableMappings.Add("Table1", "CustomerDetails");

da.TableMappings.Add("Table2", "Transaction");

da.Fill(ds);

DataTable account = ds.Tables["Account"];

DataTable customerdetails = ds.Tables["CustomerDetails"];

DataTable transaction = ds.Tables["Transaction"];

DataRelation relation1 = new DataRelation("AccountCustomerDetails",

account.Columns["Account_ID"],

customerdetails.Columns["Account_ID"], true);

DataRelation relation2 = new DataRelation("CustomerDetailsTransaction",

customerdetails.Columns["Account_ID"],

transaction.Columns["Account_ID"], true);

ds.Relations.Add(relation1);

ds.Relations.Add(relation2);

}

5. Process diagram

6.DB

7.WebService Call (DataSet)


using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
 
namespace WebService1
{   
    public class Service1 : System.Web.Services.WebService
    {
 
        [WebMethod()]
        public DataSet 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).         
            DataSet ds = CacheClass.GetCustomerDataSet();
            IEnumerable<DataRow> result = (from Account in ds.Tables["Account"].AsEnumerable()
                                           join Transaction in ds.Tables["Transaction"].AsEnumerable()
                                           on new { Account_ID = Account["Account_ID"] } equals new { Account_ID = Transaction["Account_ID"] }
                                           where Account.Field<int>("Account_ID") == 1
                                           select Account);
            // Create an array that will hold the name of each customer.         
            string[] names = new string[result.Count()];
            // Fill the array.         
            int i = 0;
            foreach (DataRow row in result)
            {
                names[i] = row["Account_Number"].ToString();
                i += 1;
            }
            return names;
        }
    }
}

using System.Web.Services;

using System.Data;

using System.Data.SqlClient;

using System.Collections.Generic;

using System.Linq;

namespace WebService1

{

public class Service1 : System.Web.Services.WebService

{

[WebMethod()]

public DataSet 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).

DataSet ds = CacheClass.GetCustomerDataSet();

IEnumerable<DataRow> result = (from Account in ds.Tables["Account"].AsEnumerable()

join Transaction in ds.Tables["Transaction"].AsEnumerable()

on new { Account_ID = Account["Account_ID"] } equals new { Account_ID = Transaction["Account_ID"] }

where Account.Field<int>("Account_ID") == 1

select Account);

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

string[] names = new string[result.Count()];

// Fill the array.

int i = 0;

foreach (DataRow row in result)

{

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

i += 1;

}

return names;

}

8.Cache Insert (DataSet)


using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Caching;
namespace WebService1
{
    public class CacheClass
    {
        private static string _key = "Customers";
        private static readonly MemoryCache _cache = MemoryCache.Default;
        //private static string connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;user ID=sa";
        private static string connectionString = "Server=SQLOLEDB.1;Password=*******;Persist Security Info=True;User ID=MYID;Initial Catalog=MYDB;Data Source=MYCOM";
        public static DataSet GetCustomerDataSet()
        {
             if (!(_cache.Get(_key) is DataSet ds))
            {
                ds = new DataSet();
                // Execute the command.             
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        string SQL = "SELECT Account_ID, Account_Number, Balance FROM [Bank].[dbo].[Account]";
                        SqlCommand com = new SqlCommand(SQL, conn);
                        SqlDataAdapter adapter = new SqlDataAdapter(com);
                        conn.Open();
                        adapter.Fill(ds, "Account");
                        ////
                        SQL = "SELECT IDX, Account_ID, Amount, New_Balance, Transaction_Date FROM [Bank].[dbo].[Transaction]";
                        com = new SqlCommand(SQL, conn);
                        adapter = new SqlDataAdapter(com);
                        adapter.Fill(ds, "Transaction");
                        ///
                        SQL = "SELECT IDX,Account_ID,Name,Mobile,Regi_Date FROM [Bank].[dbo].[CustomerDetails]";
                        com = new SqlCommand(SQL, conn);
                        adapter = new SqlDataAdapter(com);
                        adapter.Fill(ds, "CustomerDetails");
                        DataRelation relation = ds.Relations.Add("Relation",
                          ds.Tables["Account"].Columns["Account_ID"],
                          ds.Tables["Transaction"].Columns["Account_ID"]);
                        DataRelation relation2 = ds.Relations.Add("Relation2",
                          ds.Tables["Account"].Columns["Account_ID"],
                          ds.Tables["CustomerDetails"].Columns["Account_ID"]);
                        //conn.Close();
                        //conn.Dispose();
                    }
                    var cacheItemPolicy = new CacheItemPolicy()
                    {
                        //Set your Cache expiration.
                        AbsoluteExpiration = DateTime.Now.AddMinutes(60)
                    };
                    _cache.Add(_key, ds, cacheItemPolicy);
                }
                catch (Exception err)
                {
                    System.Diagnostics.Debug.WriteLine(err.ToString());
                }
            }
            return _cache.Get(_key) as DataSet;
        }
    }
}

using System;

using System.Data;

using System.Data.SqlClient;

using System.Runtime.Caching;

namespace WebService1

{

public class CacheClass

{

private static string _key = "Customers";

private static readonly MemoryCache _cache = MemoryCache.Default;

//private static string connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;user ID=sa";

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

public static DataSet GetCustomerDataSet()

{

if (!(_cache.Get(_key) is DataSet ds))

{

ds = new DataSet();

// Execute the command.

try

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

string SQL = "SELECT Account_ID, Account_Number, Balance FROM [Bank].[dbo].[Account]";

SqlCommand com = new SqlCommand(SQL, conn);

SqlDataAdapter adapter = new SqlDataAdapter(com);

conn.Open();

adapter.Fill(ds, "Account");

////

SQL = "SELECT IDX, Account_ID, Amount, New_Balance, Transaction_Date FROM [Bank].[dbo].[Transaction]";

com = new SqlCommand(SQL, conn);

adapter = new SqlDataAdapter(com);

adapter.Fill(ds, "Transaction");

///

SQL = "SELECT IDX,Account_ID,Name,Mobile,Regi_Date FROM [Bank].[dbo].[CustomerDetails]";

com = new SqlCommand(SQL, conn);

adapter = new SqlDataAdapter(com);

adapter.Fill(ds, "CustomerDetails");

DataRelation relation = ds.Relations.Add("Relation",

ds.Tables["Account"].Columns["Account_ID"],

ds.Tables["Transaction"].Columns["Account_ID"]);

DataRelation relation2 = ds.Relations.Add("Relation2",

ds.Tables["Account"].Columns["Account_ID"],

ds.Tables["CustomerDetails"].Columns["Account_ID"]);

//conn.Close();

//conn.Dispose();

}

var cacheItemPolicy = new CacheItemPolicy()

{

//Set your Cache expiration.

AbsoluteExpiration = DateTime.Now.AddMinutes(60)

};

_cache.Add(_key, ds, cacheItemPolicy);

}

catch (Exception err)

{

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

}

return _cache.Get(_key) as DataSet;

}