THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

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 possible
SqlConnection 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 below
SqlDataAdapter lineitemAdapter = new SqlDataAdapter("SELECT * FROM LineItem", lineItemConnection);

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

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

// Join them
DataRelation relation = companyLineItems.Relations.Add("CustLineItems",
  companyLineItems.Tables["companies"].Columns["companyID"],
  companyLineItems.Tables["lineItems"].Columns["companyID"]);

  // Display the combined data
foreach (DataRow pRow in companyLineItems.Tables["companies"].Rows)
{
  Console.WriteLine(pRow["companyID"]);
 foreach (DataRow cRow in pRow.GetChildRows(relation))
 Console.WriteLine("\t" + cRow["LineItem"]);
}
 Using Language Integrated Query (LINQ)
 
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.

Here's a snippet of an example of two text files in C# (the SQL Server examples are longer, to be more clear I'm just using text files here):
 class PurchaseOrder
{
    public string CompanyName { get; set; }
    public string CompanyAddress { get; set; }
    public int ID { get; set; }
    public List<int> LineItems { get; set; }
}

class PopulateCollection
{
    static void Main()
    {
   string[] POHeaders = System.IO.File.ReadAllLines(@"../../../POHeaders.csv");
         // File example, Bucksoft,1234 Mockingbird Lane,1001

        string[] LineItems = System.IO.File.ReadAllLines(@"../../../LineItems.csv");
        // File example, 1001, Product 234, Product 237, Product 341, Product 795

        // Merge the data sources - This is the main part of the work
        IEnumerable<PurchaseOrder> queryNamesLineItems =
            from nameLine in POHeaders
            let splitName = nameLine.Split(',')
            from itemLine in LineItems
            let splitLineItemLine = itemLine.Split(',')
            where splitName[2] == splitLineItemLine[0]
            select new PurchaseOrder()
            {
                CompanyName = splitName[0],
                CompanyAddress = splitName[1],
                ID = Convert.ToInt32(splitName[2]),
                LineItems = (from itemAsText in splitLineItemLine.Skip(1) // Do not show the key again
                              select Convert.ToInt32(itemAsText)).
                              ToList()
            };

        List<PurchaseOrder> companys = queryNamesLineItems.ToList();

        // Display each company's name and items
        foreach (var company in companys)
        {
            Console.WriteLine("The items are {0} {1} {2} {3} {4}", company.CompanyName, company.CompanyAddress, company.LineItems);
        }

        //Keep console window open in debug mode
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }
}

Published Tuesday, September 20, 2011 9:26 AM by BuckWoody
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement