Как правило, в публичном Облаке хранится обезличенная информация, а персонализируюшая часть - в частном. В связи с чем возникает вопрос - как скомбинировать обе части, чтобы по запросу пользователя выдать единый результат? Предположим, имеется таблица клиентов, поделенная вертикально. Обезличенные колонки отнесены в таблицу, расположенную в 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