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