Overview: DataSet을 이용하여 관계형으로 Table 3개를 캐싱해본다.
1.Net Framework 4.6 이상에서 설치가능
2.설치
NuGet 패캐지 관리에서 설치 및 참조 추가 확인
3.참조 확인
4. Simple Source
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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(); } |
5. Process diagram
6.DB
7.WebService Call (DataSet)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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; } } } |
9.디버그 ds
10.화면
11.Result
