Робота з SQL Server в сценаріях гібридного Хмари. Частина 2

  Як правило, в публічному Хмарі зберігається знеособлена інформація, а персоналізіруюшая частина — в приватному. У зв'язку з чим виникає питання — як скомбінувати обидві частини, щоб gj pfghjce gjkmpjdfntkz видати єдиний результат? Припустимо, є таблиця клієнтів, поділена вертикально. Знеособлені колонки віднесені в таблицю, розташовану в Windows Azure SQL Database, а колонки з чутливою інформацією (напр., ПІБ) залишилися в локальному SQL Server. Потрібно зв'язати обидві таблиці по ключу CustomerID. Оскільки вони лежать у різних базах на різних серверах, використання SQL-оператора з JOIN не проходить. В якості можливого рішення ми розглянули в попередньому матеріалі сценарій, при якому зв'язування відбувалося на локальному SQL Server. Він виступав у якості своєрідної точки входу для додатків, і хмарний SQL Server був заведений на нього як прілінкованний. У цьому матеріалі ми розглянемо випадок, коли і локальний, і хмарний сервери з точки зору програми рівноправні, а об'єднання даних відбувається безпосередньо в ньому, тобто на рівні бізнес-логіки.
 
Витягування даних з SQL Azure з точки зору прикладного коду нічим не відрізняється від роботи з локальним SQL Server. Скажімо так, з точністю до рядка з'єднання. У наведеному нижче коді u1qgtaf85k — ім'я сервера SQL Azure (воно генерується автоматично при його створенні). Нагадаю, що з'єднання з ним завжди встановлюється з мережевої бібліотеці TCP / IP, порт 1433. Параметр Trusted_Connection = False — це не Integrated Security (в SQL Azure вона завжди стандартна), мається на увазі Trust_Server_Certificate = false , щоб уникнути можливої ​​атаки man-in-the-middle.
 
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Resources;

namespace DevCon2013
{
    class Program
    {
        static void Main(string[] args)
        {
            ResourceManager resMan = new ResourceManager("DevCon2013.Properties.Resources", System.Reflection.Assembly.GetExecutingAssembly());
            
            string sqlAzureConnString = String.Format(@"Server=tcp:u1qgtaf85k.database.windows.net,1433;Database=AdventureWorks2012;User ID=alexejs;Password={0};Trusted_Connection=False;Encrypt=True", resMan.GetString("Password"));
            SqlConnection cnn = new SqlConnection(sqlAzureConnString); cnn.Open();

            SqlCommand cmd = cnn.CreateCommand(); cmd.CommandText = "select top 100 CustomerID, AccountNumber from Sales.Customer order by CustomerID";
            DataTable tbl = new DataTable(); tbl.Load(cmd.ExecuteReader());

            cnn.Close();

            foreach (DataRow r in tbl.Rows)
            {
                for (int i = 0; i < tbl.Columns.Count; i++) Debug.Write(String.Format("{0}\t", r[i]));
                Debug.WriteLine("");
            }
        }
    }
} 

Скрипт 1
 
Додамо сюди ж з'єднання з on-premise ресурсом, тобто з локальним SQL Server. C Вашого дозволу будемо вважати, що цей процес в пояснень не потребує, тому я просто модифікується попередній код, додавши два методи — ExecuteSQL для з'єднання з джерелом і виконання по ньому запиту і DumpTable для який-ніякий візуалізації результатів. Таким чином, робота з SQL Azure і on-premise SQL Server з точки зору програми відбуватиметься абсолютно симетрично.
 
 
string sqlOnPremiseConnString = @"Server=(local);Integrated Security=true;Database=AdventureWorks2012";

DataTable resultsOnPremise = ExecuteSQL(sqlOnPremiseConnString, "select BusinessEntityID, FirstName, LastName from Person.Person where BusinessEntityID between 1 and 100");

string sqlAzureConnString = String.Format(@"Server=tcp:u1qgtaf85k.database.windows.net,1433;Database=AdventureWorks2012;User ID=alexejs;Password={0};Trusted_Connection=False;Encrypt=True", resMan.GetString("Password"));

DataTable resultsFromAzure = ExecuteSQL(sqlAzureConnString, "select CustomerID, AccountNumber from Sales.Customer where CustomerID between 1 and 100");

...

static DataTable ExecuteSQL(string cnnStr, string query)
{
            SqlConnection cnn = new SqlConnection(cnnStr); cnn.Open();
            SqlCommand cmd = cnn.CreateCommand(); cmd.CommandText = query;
            DataTable tbl = new DataTable(); tbl.Load(cmd.ExecuteReader());
            cnn.Close(); return tbl;
}

static void DumpTable(DataTable tbl)
{
            foreach (DataRow r in tbl.Rows)
            {
                for (int i = 0; i < tbl.Columns.Count; i++) Debug.Write(String.Format("{0}\t", r[i]));
                Debug.WriteLine("");
            }
}

Скрипт 2
 
Тепер, коли у нас всередині програми у двох DataTable є обидва вертикальних [шматка перш єдиної таблиці Customers: один з локального сервера, другий — з SQL Azure — залишається їх знову об'єднати по полю CustomerID, яке завбачливо присутній і там, і там. Для простоти Не будемо зачіпати випадок композитного ключа, тобто припускаємо, що зв'язок здійснюється простим прирівнянням однієї колонки в одній таблиці одній колонці в іншій. Це класична задача ADO.NET. Найбільш уживаних способів її рішення два, по продуктивності приблизно еквівалентні. Перший спосіб — за допомогою DataRelation. Він реалізований у методі JoinTablesADO. Створюємо новий DataSet, додаємо в нього обидві таблички, створюємо зв'язок (DataRelation) між ними, вказуючи поле в батьківській і поле в дочірній таблиці, за яким буде будуватися JOIN. Хто з двох DataTable буде батьківської таблицею, а хто дочірньої, в даній ситуації неважливо, тому що в нашому випадку зв'язок не 1: до багатьох, а 1:1. Створюємо порожню заготовку для результуючої DataTable. Пробіг в циклі за всіма записами «дочірньої» таблиці, отримуємо відповідну їй запис «батьківської» таблиці і комбінуємо з полів обох записів DataRow, яку кладемо в результуючу DataTable.
 
 
DumpTable(JoinTablesADO(resultsFromAzure, resultsOnPremise, "CustomerID", "BusinessEntityID"));

...

static DataTable JoinTablesADO(DataTable parentTbl, DataTable childTbl, string parentColName, string childColName)
{
       DataSet ds = new DataSet(); ds.Tables.Add(parentTbl); ds.Tables.Add(childTbl);
       DataRelation dr = new DataRelation("ля-ля", parentTbl.Columns[parentColName], childTbl.Columns[childColName]);
       ds.Relations.Add(dr);

       DataTable joinedTbl = new DataTable(); 
       foreach (DataColumn c in parentTbl.Columns) joinedTbl.Columns.Add(c.Caption, c.DataType);
       foreach (DataColumn c in childTbl.Columns) joinedTbl.Columns.Add(c.Caption, c.DataType); 
       //К сож., Clone() над DataColumn не поддерживается :(

       foreach (DataRow childRow in childTbl.Rows)
       {
          DataRow parentRow = childRow.GetParentRow("ля-ля");
          DataRow currentRowForResult = joinedTbl.NewRow();
          for (int i = 0; i < parentTbl.Columns.Count; i++) currentRowForResult[i] = parentRow[i];
          for (int i = 0; i < childTbl.Columns.Count; i++) currentRowForResult[parentTbl.Columns.Count + i] = childRow[i];
          joinedTbl.Rows.Add(currentRowForResult);
        }
        return joinedTbl;
}

Скрипт 3
 
Другий спосіб — за допомогою Linq. Ідейно тут все те ж саме, що в першому. Різниця в деталях реалізації. Спочатку створюємо результуючу таблицю як копію структури батьківської. Потім додаємо в неї поля з дочірньою таблиці. Отримуємо колекцію записів як результат Linq-запиту до колекції записів батьківської таблиці за умовою зв'язку з колекцією записів дочірньої. Яка потім складається в результуючу таблицю.
 
 
DumpTable(JoinTablesLinq(resultsFromAzure, resultsOnPremise, "CustomerID", "BusinessEntityID"));

...

static DataTable JoinTablesLinq(DataTable parentTbl, DataTable childTbl, string parentColName, string childColName)
        {
            DataTable joinedTbl = parentTbl.Clone();
            var childColumns = childTbl.Columns.OfType<DataColumn>().Select(c => new DataColumn(c.ColumnName, c.DataType, c.Expression, c.ColumnMapping));

            joinedTbl.Columns.AddRange(childColumns.ToArray()); 
            var joinedTblRows = from parentRow in parentTbl.AsEnumerable()
                                join childRow in childTbl.AsEnumerable()
                                on parentRow.Field<int>(parentColName) equals childRow.Field<int>(childColName)
                                select parentRow.ItemArray.Concat(childRow.ItemArray).ToArray();

            foreach (object[] values in joinedTblRows) joinedTbl.Rows.Add(values);
            return joinedTbl;
        }

Скрипт 4
  
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.