Работа с 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

Автор: alexejs

Источник