Query SQL Azure and On-Premise Data in One Codeset

I was asked recently how to query SQL Azure along with an on-premise data source. There are actually lots of ways to do this, but a quick and simple method is to use LINQ - or Language Integrated Query. There are always pro's and con's for any data access methods, but I'll show this one as an example, one I've used in other places. It's important to understand what you're doing, and why.

Note that the key here is in three parts - the connections, the DataRelation object space within the .NET environment, and the LINQ query that presents the data. I'll show this example in C#, and keep in mind that you don't even have to call the Azure SDK - this is all straight Tabular Data Stream (TDS) layer that SQL Server uses. Although I'm querying from SQL Server as well as SQL Azure, you could also query Oracle, XML, text files, even a Excel Spreadsheet. To keep it simple I'm querying two data sources, but you can include more in your tests.

For this example I'll tie together two Purchase Order databases, assuming that a PO number is shared between them - a key relationship that may not exist for you. Be careful on that front.

I'm using a special connection string for SQL Azure, since it requires a different format for the server name, login name and of course it needs to be encrypted. You can see that below. As always, test systems only, your mileage may vary, don't run wth siccors.

//Set up two connections, more are possibleSqlConnection companyConnection = new SqlConnection("Data Source=FirstServer;Server=tcp:[serverName].database.windows.net;Database=PurchaseOrders;User ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False;Encrypt=True;");SqlConnection lineItemConnection = new SqlConnection("Data Source=SecondServer;Integrated Security=SSPI;Initial Catalog=PurchaseOrders");

SqlDataAdapter headerAdapter = new SqlDataAdapter("SELECT * FROM Company", companyConnection);//Note: if this is to be a different source such as Oracle or text files, set up a different//connection information above and possibly a different connection belowSqlDataAdapter lineitemAdapter = new SqlDataAdapter("SELECT * FROM LineItem", lineItemConnection);

// This is the combined datasetDataSet companyLineItems = new DataSet();

// Go get data from each dataset from aboveheaderAdapter.Fill(companyLineItems, "companies");lineitemAdapter.Fill(companyLineItems, "lineItems");

Another method is to use Language Integrated Query (LINQ). LINQ is a single data query language used in .NET programming languages. It's completely integrated into the .NET framework, so developers use this construct quite often. I have an entire article on this technology here, so I won't repeat that information in this article.