Working with SQL Server in Hybrid Cloud Scripts. Part 2

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

Retrieving data from SQL Azure in terms of application code is no different from working with a local SQL Server. Let's just say, up to the connection string. In the code below, u1qgtaf85k is the name of the SQL Azure server (it is generated automatically when it is created). I recall that the connection with it is always established via the TCP / IP network library, port 1433. The parameter Trusted_Connection = False 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

Let's add here the connection with the on-premise resource, i.e. with local SQL Server. From your permission, we will assume that this process does not need explanations, so I simply modify the previous code by adding two methods — ExecuteSQL to connect to the source and run the query on it, and DumpTable for any visualization of the results. Thus, working with SQL Azure and on-premise SQL Server from the point of view of the application will be completely symmetrical.
')

 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 that we have inside the application, in two DataTables, we have both vertical [pieces of the previously single Customers table: one from the local server, the other - from SQL Azure - it remains to rejoin them across the CustomerID field, which is prudently present both there and there. For simplicity, we will not touch upon the case of a composite key, i.e. we assume that communication is carried out by simply equating one column in one table with one column in another. This is a classic ADO.NET task. The most common ways to solve it are two, approximately equivalent in performance. The first method is using DataRelation. It is implemented in the JoinTablesADO method. Create a new DataSet, add both labels to it, create a DataRelation between them, specifying the field in the parent and the field in the child table, which will be used to build the JOIN. Which of the two DataTables will be the parent table, and who is a child, in this situation it does not matter, since in our case, the relationship is not 1: to many, but 1: 1. Create an empty stub for the resulting DataTable. Running in a loop over all the records of the "child" table, we get the corresponding record of the "parent" table and combine them from the fields of both DataRow records that 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 is with Linq. It’s all the same here as the first one. The difference is in the implementation details. First, create the resulting table as a copy of the parent structure. Then we add fields from the child table to it. We get a collection of records as a result of a Linq query to the collection of records of the parent table according to the condition of connection 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<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; } 

Script 4