//====================================================================================================//
//=== btnCustomerQueryMember_Click ===//
//=== ===//
//=== This function retrieves the data for the federation member with the federation key specified ===//
//=== on the UI. If you check Filtering On, it will return the row with the federation key value. ===//
//=== This function retrieves the data for both Customer and CustomerAddress tables. ===//
//=== ===//
//=== To query one federation member data: ===//
//=== 1. Connection to the SQL Azure server, the initial catalog is AdventureWorks3. ===//
//=== 2. Use the USE statement to route the connection to the federation member. ===//
//=== 3. Retrieve the Customer table data. ===//
//=== 4. Retrieve the CustomerAddress table data. ===//
//====================================================================================================//
private void btnCustomerQueryMember_Click(object sender, EventArgs e)
{
long lFederationKey = Convert.ToInt64(numericUpDownFederatedKey.Value);
StringBuilder sbResults = new StringBuilder();
List <Customer> dataCustomer = null;
List <CustomerAddress> dataCustomerAddress = null;
string strCmd = String.Format("USE FEDERATION {0}({1}={2}) WITH RESET, FILTERING={3}",
CONST_FEDERATION_NAME,
CONST_DISTRIBUTION_NAME,
lFederationKey,
(bFilteringOn ? "ON" : "OFF"));
try
{
using (AWEFEntities dc = new AWEFEntities())
{
dc.Connection.Open();
sbResults.AppendFormat("-- EF Open {0}\r\n\r\n", dc.Connection.ConnectionString.ToString());
dc.ExecuteStoreCommand(strCmd, null);
sbResults.AppendFormat("-- EF ExecuteStoreCommand {0}\r\n", strCmd);
dataCustomer = dc.Customers.ToList();
sbResults.AppendFormat("-- EF Query {0}\r\n", dc.Customers.CommandText);
dataCustomerAddress = dc.CustomerAddresses.ToList();
sbResults.AppendFormat("-- EF Query {0}\r\n", dc.CustomerAddresses.CommandText);
dc.Connection.Close();
}
txtResults.Text = String.Format("----- {0} : ----- Query federation member data -----\r\n{1}\r\n",
DateTime.Now.ToString(), sbResults.ToString()) + txtResults.Text;
this.dataGridViewCustomer.DataSource = dataCustomer;
this.dataGridViewCustomerAddress.DataSource = dataCustomerAddress;
}
catch (Exception ex)
{
txtResults.Text = String.Format("----- {0} : ----- Query federation member data -----\r\n{1}\r\n",
DateTime.Now.ToString(), sbResults.ToString()) + txtResults.Text;
txtResults.Text = String.Format("----- {0} : ----- Query federation member data -----\r\n{1}\r\n",
DateTime.Now.ToString(),
String.Format("-- Error:\r\n{0}\r\n{1}\r\n{2}", ex.Source, ex.Message, ex.StackTrace)) + txtResults.Text;
}
}
//====================================================================================================//
//=== btnCustomerFanOut_Click ===//
//=== ===//
//=== This function performs a fan-out query to retrieve data from the customer and the ===//
//=== customeraddress table. ===//
//=== ===//
//=== To do a fan-out query to retrieve data from multiple federation members. ===//
//=== 1. Connect to the SQL Azure server, the initial catalog is AdventureWorks3. ===//
//=== 2. For each of the federation members starting with the one with the federation key ===//
//=== specified ===//
//=== a. Use the USE statement to route the connection to the federation member ===//
//=== b. Retrieve the Customer table data ===//
//=== c. Retrieve the CustomerAddress table data ===//
//====================================================================================================//
private void btnCustomerFanOut_Click(object sender, EventArgs e)
{
long lFederationKey = Convert.ToInt64(numericUpDownFederatedKey.Value);
int iFederatedMemberCount = 0;
StringBuilder sbResults = new StringBuilder();
List <Customer> dataCustomer = new List <Customer>(), dataCustomerTemp = null;
List <CustomerAddress> dataCustomerAddress = new List <CustomerAddress>(), dataCustomerAddressTemp = null;
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(
ConfigurationManager.ConnectionStrings["CustomerFederation"].ConnectionString);
try
{
//The initial catalog is the AdventureWorks3 database
using (SqlConnection connection = new SqlConnection(csb.ToString()))
{
connection.Open();
sbResults.AppendFormat("-- Open {0}\r\n\r\n", csb.ToString());
do
{
using (SqlCommand command = connection.CreateCommand())
{
string strCmd = String.Format("USE FEDERATION {0}({1}={2}) WITH RESET, FILTERING={3}",
CONST_FEDERATION_NAME,
CONST_DISTRIBUTION_NAME,
lFederationKey,
(bFilteringOn ? "ON" : "OFF"));
using (AWEFEntities dc = new AWEFEntities())
{
dc.Connection.Open();
sbResults.AppendFormat("-- EF Open {0}\r\n", dc.Connection.ConnectionString.ToString());
dc.ExecuteStoreCommand(strCmd, null);
sbResults.AppendFormat("-- EF ExecuteStoreCommand {0}\r\n", strCmd);
dataCustomerTemp = dc.Customers.ToList();
sbResults.AppendFormat("-- EF Query {0}\r\n", dc.Customers.CommandText);
dataCustomerAddressTemp = dc.CustomerAddresses.ToList();
sbResults.AppendFormat("-- EF Query {0}\r\n", dc.CustomerAddresses.CommandText);
dc.Connection.Close();
}
dataCustomer.AddRange(dataCustomerTemp);
dataCustomerAddress.AddRange(dataCustomerAddressTemp);
// Connection Routing to the specified Federated Member
command.CommandText = strCmd;
sbResults.AppendFormat("{0}\r\nGO\r\n", command.CommandText);
command.ExecuteNonQuery();
// Get next range high value
command.CommandText = "SELECT CAST(range_high as bigint) FROM sys.federation_member_distributions";
sbResults.AppendFormat("{0}\r\nGO\r\n\r\n", command.CommandText);
var key = command.ExecuteScalar();
if (key != System.DBNull.Value)
{
lFederationKey = Convert.ToInt64(key);
sbResults.AppendFormat("-- The next range_high is {0}\r\n\r\n", lFederationKey);
}
else
{
sbResults.Append("-- Done with fan-out.\r\n\r\n");
lFederationKey = long.MaxValue;
}
}
iFederatedMemberCount++;
} while (lFederationKey < long.MaxValue);
}
sbResults.AppendFormat("-- The number of Fan-Out Federated Members is {0}.\r\n\r\n", iFederatedMemberCount);
txtResults.Text = String.Format("----- {0} : ----- Fan-out federation members data -----\r\n{1}\r\n",
DateTime.Now.ToString(), sbResults.ToString()) + txtResults.Text;
this.dataGridViewCustomer.DataSource = dataCustomer;
this.dataGridViewCustomerAddress.DataSource = dataCustomerAddress;
}
catch (Exception ex)
{
txtResults.Text = String.Format("----- {0} : ----- Fan-out federation members data -----\r\n{1}\r\n",
DateTime.Now.ToString(), sbResults.ToString()) + txtResults.Text;
txtResults.Text = String.Format("----- {0} : ----- Fan-out federation members data -----\r\n{1}\r\n",
DateTime.Now.ToString(),
String.Format("-- Error:\r\n{0}\r\n{1}\r\n{2}", ex.Source, ex.Message, ex.StackTrace)) + txtResults.Text;
}
}
