Example #1
0
        //====================================================================================================//
        //=== 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;
            }
        }
Example #2
0
        //====================================================================================================//
        //=== 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;
            }
        }