Work with SQL Server in hybrid cloud scenarios. Part 2

As a rule, anonymous information is stored in the public Cloud, and the personalizing part is stored in the private. In this connection, the question arises - how to combine both parts in order to produce a single result at the user's request? Suppose you have a customer table divided vertically. Anonymous columns are assigned to a table located in the Windows Azure SQL Database, while columns with sensitive information (e.g., full name) remained in the local SQL Server. You need to link both tables with the key CustomerID. Since they lie in different databases on different servers, using an SQL statement with JOIN does not work. As a possible solution, we considered in the previous articleA scenario where the binding occurred on the local SQL Server. It acted as a kind of entry point for applications, and cloud-based SQL Server was wound up on it as a linked one. In this article, we will consider the case when both the local and cloud servers are equal from the application point of view, and the data are combined directly in it, i.e. at the level of business logic.

Pulling data from SQL Azure in terms of application code is no different from working with local SQL Server. Let's just say it, accurate to the connection string. In the code below, u1qgtaf85k is the name of the Azure SQL Server (it is generated automatically when it is created). Let me remind you that the connection with it is always established via the TCP / IP network library, port 1433. The Trusted_Connection = False parameter is not Integrated Security (it is always standard in SQL Azure), meaning Trust_Server_Certificate = false to avoid a possible man-in attack -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("");
            }
        }
    }
} 

Script 1

Add here the connection with the on-premise resource, i.e. with local SQL Server. With your permission, we will assume that this process does not need to be explained, so I just modify the previous code by adding two methods - ExecuteSQL to connect to the source and execute a query on it, and DumpTable for any visualization of the results. Thus, work with SQL Azure and on-premise SQL Server from the point of view of the application will occur absolutely symmetrically.

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("");
            }
}

Script 2

Now, when we have two vertical DataTables inside the application, we have both vertical [chunks of the previously unified Customers table: one from the local server, the other from SQL Azure, it remains to combine them again using the CustomerID field, which is presently present there and there. For simplicity, we will not touch on the case of a composite key, i.e. we assume that the connection is made by simply equating one column in one table from one column to another. This is a classic ADO.NET task. There are two most common ways to solve it, approximately equivalent in performance. The first way is with DataRelation. It is implemented in the JoinTablesADO method. We create a new DataSet, add both labels to it, create a DataRelation between them, indicating the field in the parent and the field in the child table, on which the JOIN will be built. Which of the two DataTable will be the parent table, and which is the child table, in this situation it does not matter, because in our case, the connection is not 1: to many, but 1: 1. Create an empty blank for the resulting DataTable. Going through the loop over all the records of the "child" table, we get the corresponding record of the "parent" table and combine from the fields of both records DataRow, which we put in the resulting 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;
}

Script 3 The

second way - using Linq. In theory, everything is the same as in the first. The difference in implementation details. First, create the resulting table as a copy of the parent structure. Then add the fields from the child table to it. We get the collection of records as a result of the Linq-query to the collection of records of the parent table by the condition of communication with the collection of records of the child. Which is then added to the resulting table.

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().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(parentColName) equals childRow.Field(childColName)
                                select parentRow.ItemArray.Concat(childRow.ItemArray).ToArray();
            foreach (object[] values in joinedTblRows) joinedTbl.Rows.Add(values);
            return joinedTbl;
        }

Script 4