1 out of 1 rated this helpful - Rate this topic

How to: Connect to a Federation in SQL Azure

This topic describes how you can connect to a federation in SQL Azure. For general considerations about connecting to databases in SQL Azure, see Guidelines for Connecting to SQL Azure Database.

Establishing a connection to the federation’s data is accomplished by using the USE FEDERATION (SQL Azure Database) statement. All connections are established to the database containing the federation (Federation Root,) which then routes the connection to the appropriate federation member. The federation member that the connection is routed to is determined by the federation key value, which is specified as part of the USE FEDERATION statement.

Console Application Example

The following example is a console application that connects to a SQL Azure database with federation. The console application performs the following steps:

First, the console application uses a SQLConnectionStringBuilder object to construct the connection string..
Then the console application connects to federation root to retrieve and display the federation member data.
Finally, the console application connects to a federation member and counts the rows in the federation member.

Use this Example

To use this example in Visual Studio with your SQL Azure server, do the following steps:

Open Visual Studio and create a new console application.
Replace the code in the program file with the code from this example.
Replace the value of the eight static data members.
Run the code.

Copy


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ConnectToFederationConsoleApp
{
    class Program
    {
        private static string federationName = "<ProvideFederationName>";
        private static string distributionName = "<ProvideDistributionName>";
        private static long federationKey = <ProvideAValue>;
        private static string tableName = "<ProvideTableName>";

        static void Main(string[] args)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            csb.DataSource = dataSource;
            csb.InitialCatalog = databaseName;
            csb.Encrypt = true;
            csb.TrustServerCertificate = true;
            csb.UserID = userName;
            csb.Password = password;

            try
            {
                using (SqlConnection connection = new SqlConnection(csb.ToString()))
                {
                    connection.Open();

                    using (SqlCommand command = connection.CreateCommand())
                    {
                        // Route the connection to Federation Root
                        command.CommandText = "USE FEDERATION ROOT WITH RESET";
                        command.ExecuteNonQuery();

                        //Retrieve Federation Root metadata
                        command.CommandText = @"SELECT f.name, fmc.federation_id, fmc.member_id, fmc.range_low, fmc.range_high " +
                                                            "FROM sys.federations f " +
                                                            "JOIN sys.federation_member_distributions fmc " +
                                                            "ON f.federation_id=fmc.federation_id " +
                                                            "ORDER BY fmc.federation_id, fmc.range_low, fmc.range_high";;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            Console.WriteLine("name\t\tfederation_id\tmember_id\trange_low\trange_high");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader["name"] + "\t" + 
                                    reader["federation_id"] + "\t" + 
                                    reader["member_id"] + "\t" + 
                                    reader["range_low"] + "\t" + 
                                    reader["range_high"] + "\t");
                            }
                            Console.WriteLine();
                        }

                        // Route the connection to a federation member
                        command.CommandText = "USE FEDERATION " + federationName + "(" + distributionName + "=" + federationKey + ") WITH RESET, FILTERING = OFF";
                        command.ExecuteNonQuery();
                        command.CommandText = @"SELECT * FROM " + tableName;

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            int iRowCount = 0;
                            while (reader.Read())
                            {
                                iRowCount++;
                            }
                            Console.WriteLine("There are {0} rows in this federation member with the federation key value of {1}.", iRowCount, federationKey.ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine( String.Format("Error:\r\n{0}\r\n{1}\r\n{2}", ex.Source, ex.Message, ex.StackTrace));
            }

            Console.WriteLine("\nPress ENTER to continue ... ");
            Console.ReadLine();

        }
    }
}

How to: Connect to a Federation in SQL Azure

Console Application Example

Use this Example

See Also

Concepts