June 26, 2011

.Net Remoting - Client activated remoting objects

1. Introduction

In the previous article, I had written about "Server Activated" remote objects. Also provided a separate article for Single Call and Singleton on the Server activated remote objects. In this post, we will explore how do we use the "Client activated" remote objects.

Before we go on Client Activated remote objects a short note about what is Activation and where the object lives. The obvious thing is whether it is server activated or client activated, the remote object lives in the "remote pool" of the server.  Client activation means, the client creates the object on the server’s remote pool using the operator new. So, if you are developing a class for the Client Activated Remote object, you have the liberty of using the overloaded constructors.

In Server activated – Single call technique, the remote object is created during the call and destroyed when the call is over.
In Server activated – Singleton technique, a single object is maintained for client requests between the call. All clients share the object state
In Client Activated technique, the client creates the object on the remote pool the object is dedicated to the client who created it. So here the object state is different for objects created by client/clients.

Now let us go ahead start creating an example for the Client Activated remote object.

2. About the Sample

Below is the screen shot:

Fig 1: Sample Application

The console window is the server exe that waits for the client’s activation request to host the dedicated object in the remote pool. The client is a windows application, which demonstrates the use of the client activation.

Set First Ac button creates a remote object on the remote pool and Display Ac1 retrieves the Object’s state information from the remote machine. Similarly, the other button pair second account does the same. This will give an idea that each object created by the client maintains its own state as Display Ac1 and Display Ac2 button clicks are going to retrieve different information.

3. Remote Server – Account Class

The remote server is created as the C# console application project. The project is named as ServerCA (spell mistake! What to do?). After creating the project dot reference for system.runtime.remoting is added to the project.

1) First, the required namespace provided in the top. Below is the code:

//001 ServerCA : Name space for Client Activated Server
using System.Runtime.Remoting;

2) Then the Account class is created. And, this class is derived from the MarshalByRefObject to support the remoting of the object created from it. Then some variables related to accounts are declared. These variable (in objects world, the object states) are useful to check the each cleint activated objects mantains their states.

//002 ServerCA : Remote Object, that will be Activated by Client
public class Account : MarshalByRefObject
 private int AcNumber;
 private string AcHolderName;
 private int AcBalance;

3) The constructor shown below initializes the data members of the Account class. I hope no more explanation is required here.

//003 ServerCA : Initilize the Ac with zero, noname
public Account()
 AcNumber = 0;
 AcHolderName = "";
 AcBalance = 0;
 Console.WriteLine("Account Object Created - Default Ctor");

4) One more constructor is provided for the Account class that helps to initialize the data members when the object is created. Notice that the Server Activated remote class does not support these kind of overloaded constructors. Refer my previous articles to know about the server activated remote objects. As the object of this class is going to be activated by the client (using the new operator) it is now possible to have the overloaded constructors. That means your class can have constructors that accepts parameters to it. Below is the code for it:

//004 ServerCA : Accout with Data. Note Constructor is Overloaded as the activation
// type we are going to set is Client
public Account(int AcNo, string Name, int Bal)
    AcNumber = AcNo;
    AcHolderName = Name;
    AcBalance = Bal;
    Console.WriteLine("Account Object Created - Overloaded Ctor");

5) OK. Now the account class has data members and constructors to initialize the data memebers. When the default contructor that is a constructor without any parameters is invoked there should be a way to initialize the members. So we will provide a get and set methods for the members. Below is the code:

//005 ServerCA : Set methods for the Private data members
public void SetAcNumber(int AcNo)
    AcNumber = AcNo;
public void SetAcName(string Name)
    AcHolderName = Name;
public void SetBalance(int Bal)
    AcBalance = Bal;

//006 ServerCA : Get methods for the Private data members
public int GetAcNumber()
    Console.WriteLine("Account Number Requested");
    return AcNumber;

public string GetAcName()
    Console.WriteLine("Account Name Requested");
    return AcHolderName;

4. Remote Server – Program Main

1) In the program.cs file, required namespaces are first included

//007 ServerCA: Required Namespaces
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Http;

2) Register the required communication channel. Here the sample code took the Http communication channel and registers the port 10038 for communication.

//008 ServerCS : Setup the Http Channel and Register it
HttpChannel port = new HttpChannel(10038);
ChannelServices.RegisterChannel(port, false);

3) RegisterActivatedServiceType function call tells the server that the Account class can be activated from the client.  Now our account class will kept in the remote pool when the client creates the object of it using the new keyword. Application name is used by the client to make a service request of the server object. Below code registeres the Account object as a client activated remote object:

//009 ServerCS : Set the App Name and Register the object type that
// need to be activated by the client.
RemotingConfiguration.ApplicationName = "ServerCA";

4) Finally, the server waits for the key stroke after printing the information message on the console window to terminate itself.

//010 ServerCS : Inform Server is ready
Console.WriteLine("Server is ready to Demo Client Activated Objects...");

The server is ready now. First we created the Account class, which a remote class as it is derived from the MarshalByRefObject. We provided some data members to it and get and set function to those members. Also we provided two constructors to it as it is going to be a client activated remote object. Then in the main program entry we registered an http communication channel claiming a port 10038 for dedicated use. Right! Let us move to the client and see how it activates the Account object from this running server.

5. Client Project

The client is a windows application. I named the project ClientCA. To know the form setup, download the code and check the properties for each control that appears in bold. What the client will do is already explained in section 2 of this article.

1) As a first step, the project is created on the same server project using File->Add->New project. Then the dot net runtime for remoting is provided through the reference. The reference to System.Runtime.remoting is done through the .Net tab of the reference dialog. Also for this windows project, add sever project is added as the reference using the same Add reference dialog’s projects tab.

2) In the form main class, the following name spaces are included.

//ClientCA 001: Set the Required Namespaces.
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Http;
using System.Runtime.Remoting.Activation;
using SercerCA;

3) Two instances of Account class are declared in the Form class. Note that we already provided the Server project as a reference. When the solution (Created for you when you added the second project) is built, it will give two exes. One for Client (Windows App) and another one is for Server (Console App). You may deploy the application in server and client machines to test it.

//ClientCA 002 : Declare the Objects that need to be created on server
//and activation are done by the client
Account account1, account2;

4) In the constructor for the form provided by the IDE, the client application registers the Account class of the Remote server. Note that the server already registered the Account class as the Client Activated by using the utility function RegisterActivatedService type in Section 4 point 3. The below specified client code claims the server that it can activate the Account class any time using the utility function RegisterActivatedClientType. Once both and client are agreed upon the activation of the Account, the client can instantiate the Account object just like how it creates the other normal class objects.

//ClientCA 003 : Register the Client Activated type from the remote Pool.
//Note, Server already registered Account as the Activated Type.

6) Set First Ac Button Click – Client

The first account is created using the default constructor and then setting the variables through the set functions. Note the Account object in the server is activated and kept in the remote pool when the client creates the object using the new keyword. So now there is an Account object in the server remote pool, which is activated by the client. Read my Previous articles to know how the client requests Server Activated remote object.

private void btnAc1Set_Click(object sender, EventArgs e)
    //ClientCA 004 : Create the Account Object1
    account1 = new Account();
    account1.SetAcNumber( Int32.Parse(txtAcNo.Text));

    //Clear the Fields and Say Account created
    txtAcName.Text = "";
    txtAcNo.Text = "";
    txtBalance.Text = "";
    MessageBox.Show("Account Created");

7) Set Second Ac Button Click – Client

The second account is created using the overloaded constructor. And this object also sits in the remote pool of the server. So now, there are two objects in the server and their life are activated by the client. Also, note that these two objects maintain their object state independently. That is, for Example, Account1 may have a balance of 1000 and Account2 may have a different balance of 1200. To see that, next two button click events are handled.

private void btnAc2Set_Click(object sender, EventArgs e)
    //ClientCA 005 : Create the Account object2 using overloaded Constructor
    int AcNo = Int32.Parse(txtAcNo.Text);
    int Bal = Int32.Parse(txtBalance.Text );
    account2 = new Account(AcNo, txtAcName.Text, Bal);

    //Clear the Fields and Say Account created
    txtAcName.Text = "";
    txtAcNo.Text = "";
    txtBalance.Text = "";
    MessageBox.Show("Account Created");

8) Retrieving the State information of the two Accounts

The next two button events make use the accounts created previously to get the account details. The code is given below:

private void btnAc1Get_Click(object sender, EventArgs e)
    //ClientCA 006 : Get the Details of First Object.
    txtAcName.Text = account1.GetAcName();
    txtAcNo.Text = account1.GetAcNumber().ToString();
    txtBalance.Text = account1.GetBalance().ToString();

private void btnAc2Get_Click(object sender, EventArgs e)
    //ClientCA 007 : Get the Details of First Object.
    txtAcName.Text = account2.GetAcName();
    txtAcNo.Text = account2.GetAcNumber().ToString();
    txtBalance.Text = account2.GetBalance().ToString();

9) Testing the App

On the Same Machine

1) Right click the solution and Click rebuild all.
2) Now navigate to both the exe using windows explore.
3) Run the Console Server
4) Run the Windows application
5) Click the button on the Windows application and Observe the result on the Client. 

On Different Machines

1) Copy Server to a Machine says Mahesh. Here, Mahesh is the name of the machine.
2) Have the client on your machine itself. Before that Search for the keyword Localhost and change it to Mahesh.
3) Run the server at machine Mahesh.
4) Run the client on your machine
5) Click the Get Details button.

You may have a Question that when will these client activated object removed from the remote pool. How does the GC work?. Well, Collecting the information for Remote Object Life time. See you in the next article.

Note: The samples are created using the VS2005 IDE

Source Code and Running App Demo: Download

Extracted Zip gives you Source Code and Video of Testing the App. To see the video, set the resolution to 800x600 and use full-screen option from the video.

June 9, 2011

SQL 2005 - Table Joins: Inner Join, Self Join and Outer Join with execution sequence and join order

1. Table Joins

"Table Joins" are useful for bringing data together from different tables based on their database relations. First, we will see how the join operates between tables. Then we will explore the Order of Execution when Join and where condition both exists. Finally, we will move our exploration to the Importance of the Join order.

2. Run the attached script

Before you begin, download the attached script and the downloaded script has T-SQL for creating three tables and data for this article. You should also run the Northwnd script as some example here uses the NorthWnd DB. Once you downloaded the script CreateObject.zip run the script in the NorthWnd Db.

Below is the content of three tables created by the Script:

Fig 1. Table required for this article

We are going to use these tables to perform the joins. These tables are just for demo purpose only and so I do not have proper table relationship in terms of Primary key and Foreign keys. OK, Let us move on.

3. Cartesian Product of Table

Usually, join will be performed between two tables based on the key columns between two tables those together constitutes the database table relationship. For Example, DeptId in the employee table and DeptId in the Department table make the relationship between these two tables.

The below example is joining two tables without using any key columns. Here, TableA and TableB are clubbed together to form the whole result-set based on "Cartesian Product". The Cartesian product will take a single record in the first table and attaches it with all the records in the second table. Then takes the Second records in the first table and attaches it with all the records the second table and this continue till the end of the record in the first table.

The result of the Cartesian Join is shown below:

Fig 2. Cartesian Product of two tables

4. Joining Two tables

When joining two tables to avoid the bulk number of records that results as shown in the previous example, we should chose a join column from both the tables. The example given below joins Table_A and Table_B based on the column called ID. Since column mapping is established between two tables, we will reduce huge records when compared to Cartesian Product.

Below is the Result of the Join:

Fig 3. Mapping column for the table join

Note that the Row Number 1 and Row number 5 are returned as the join result as they satisfy the mapping condition A.Id = B.Id. In the query, it is shown in the Red Box. You see, mapping produces the sub-set of the Cartesian Join. 

5. Joining multiple tables

In the above example, two tables are participated in the join. To join multiple tables, we should use the result of the previous join (Table 1 join Table 2) and pick a column from it (Join result), then pick a column in the third table then specify the join condition as in the previous example. This way we can join multiple numbers of tables. Consider whatever joint so far as the single table and join it with the third one.

Fig 4. Joining more than two tables - Example

First Table_A joins with Table_B, which is nothing but the previous example. Then the joint result of A and B is considered as single table say AB. Then this AB is joint with the Table_C forming the join of three tables. This is shown in the below picture:

Fig 5. How multiple join works

6. SQL Join Types

There are three types of join available based the way we join columns on two different tables.

  • Full Join
  • Inner Join
  • Left outer Join
  • Right outer Join

What we saw in the previous two sections are the inner joins. If we join the same table we call it as Self join and it is special category do not get confuse it with the join types. Let us see an example for the join types in next coming examples.

Before we go into those examples, remember that the result computed so for is considered as LEFT and the new table coming to join the existing result is RIGHT. This is useful when we are joining multiple tables with a different type of joins.

7. Full Join Example

A full join is somewhat different from the Cartesian product. Cartesian product will get all the possible row combination between the two joining tables. Full join takes the matching columns plus all table rows from the left table that doest match the right and all tables rows in the right that does not match the left. It applies null for unmatched row on the other end when doing so. The below example shows the "full join" between Table_A and Table_C

Fig 6. Content of Table_C

Fig 7. Full Join - Example

  1. In the above picture, the Blue Row is the matching row on both the table.
  2. Second row (Green First, red next) is the unmatched one. Row exists on the Left table and null substituted for all the columns in the Right.
  3. Third row (Red First, Green next) is also the unmatched one. Row exists on the Right side table, null returned for the left one.

Look at the from clause,

The Table_A is taken first and joint with Table_C. Here, The result set computed so for always treated as Left side of join (Table_A here) and the new table going to be joint (Table_C) is treated as a Right side of the join.

8. Left Join Example

Left join makes sure to take all the rows on the left table by placing the null entries for the table joining on the right side when there is an unmatched row on the right side.

Fig 8. Left Join - Example

In the above example, Id value of 2 in the Left table does not exist on the right side table Table_C.Id. But, we still got the 2,BBB row from the Table_A by placing the null entries for the right side table. This is shown in Green and red boxes above.

Also note that when SQL is processing, it takes the rows for the Table_A first (So Table is Left) then joins it with the Table_C (Right  side). It does not matter whether we provide joint condition as A.Id = C.Id or C.Id = A.Id

9. Right Join Example

It is the reverse of the left join. It implies take all the rows on the right side of the table by placing the null on the left table for unmatched rows. Below is the example for it:

Fig 9. Right join example

Blue Box : Matched rows.
Green : Row exits on the right side table Table_B and match (Based on Id column) not available on the left
Red : Null placement for the columns of Table_A

10. Inner Join Example

In inner join, only the matched rows are retrieved. Please refer section four. Inner join returns same result and hence one no need to worry about the placing a table on left or right.

11. Self Join Example

Joining the table with the same table is called the "Self Join". To explain this let us go the table on the Northwnd database (Attached with this article). Have a look at the columns in the employee table. The EmployeeId column is the Primary key column and each row belongs to a single employee. The reports to column refer some other row in the same table stating that referred row is the manager for the referring row (Employee). But the referred row (manager) is also an employee possibly having a valid entry on its ReportsTo column. So in the NorthWnd database this relationship achieves a hierarchical reporting structure.

Fig 10. Primary Key and Foreign key On Sample Table

Now have look at the below example:

Fig 11. Self Join Example

Here, the row pointed by ReportTo column is Manager. So the table on the left-hand side is employee and table on the Right-hand side are Manager. When the FirstName is picked from the left table of the joint result, it is Employee name and the same FirstName is picked from the right table of the join result is Manager name.

12. Execution Sequence of Table Joins

When the query involves the "combination of the outer and inner join" the execution sequence is important. If you have only inner join the execution sequence is not important as they are going to provide the same result. Well, What I am taking about?

Let us say you have a query, which has both inner join and outer join (Left or Right). Also, let us assume that you have where clause that filters the records and mapping column does not participate in the where clause. Now., which operation is performed first. We have two options:

  1. Apply the where clause record filter first then perform the table join
  2. Apply the Table Join first then perform the Where Clause filter

The above two option returns same result when all the joins involved are inner joins. But the result may differ when we have at least one outer join. OK. SQL chose the second option. Let us examine and prove this.

Given below are an Example and the result:

Fig 12. Table join condition versus Where Clause

How the Sequence differs is shown below:

Option 1

Fig 13. Execution Sequence - Option 1

Option 2:

Fig 14. Execution Sequence - Option 2

So keep in mind that the operation sequence as SQL first completes the join first then applies the where clause when the query has one or more outer joins.

13. Order of the Joins

Like the Operation sequence the "Order of the join" also important when you want to mix the inner joins with outer (Left or Right) joins. Again, if the entire join involved between the tables are inner joins then the join order is not important. But it matters when we mix the inner and outer joins.

What is Order of the Join?  If my query joins three tables like [X inner Y] Left Z, the order here is inner join performed first, and then the left join

OK. Let us go back to the NorthWnd Database again. The result you want to achieve is Get all customer names whether they have ordered or not. Also, list the quantity of order placed by the customer if they actually placed at-least one order.

Look at the Query and result below: [Outer Join then Inner Join]

Fig 15. Join Order - Outer join and then inner join

From the above query, you can see the order of join as mentioned below:
1) A right join between Orders and Customers. SQL first queries the Orders table (As it appears first) and treats the result as Left. Then it queries the Customers table next and treats the result set as Right. Finally from both the result set Right join is performed that means SQL ensures you that it will not lose any rows on the Right side result set that is it will not lose any rows from the Customers table. So you will get all customers including the two who don’t place any orders and since a matching records for those two rows are not available you will get null columns for the Orders. Now the resulting join result is available for the next join and this join result is now treated as Left.

2) The above returned result (Left side) is joint with the Order Details table. SQL knows it already has the Left result set so it query the table Order Details to have the Right part of the join. Finally an Inner join is performed between Left and Right based on the Order Id. But note that we have two null entries for the ordered column for which there are no corresponding customers in the Left side result. So the Inner join just skips those records. Thus, we got a total of 2155 rows skipping the two customers who does not place any orders. This is not the result we need. Read the Underlined text at the top of this section.

Now look at the Query and Result below: [Inner Join then Outer Join]

Fig 16. Join Order - Inner join and then outer join

Here, Inner join based on the OrderId between Orders and Order Details is performed first. This result (Left side) is then Right joint against the Customers table [Right].

Now let us analyse how this is giving the result we want.

The inner join between Order and Order Details brings all the matching records based on the order id. Note that we are not losing any order id here by null values. Then by keeping this already brought result on the left, Customers table is queried and kept in the Right. Then the right join is performed between Customers and Left side result based on the Customer Id. Now we get all the customers including the two for which we don’t have any matching records on the Left side table.

Keep in mind that join order is important when you mix the inner join with an outer join.

14. Other way of achieving the same result

When I had a chat with one of my office friend (VaraPrasad), he told that the result you are expecting could be achieved without using the Right Join. How? That is the question I asked him. He told that Crystal reports do it, I will show you. Good, now this section is added to this article based on what I got from him.

Fig 17. Join Order - Change priority using Parenthesis

OK. Now let us see how this works and gives the expected result of not losing any customers. Note that the Rule remains same, whatever computed so for is Left and the Joining table is on the Right.

  1. SQL first queries the table Customers and keeps it as the result on the Left.
  2. It reads the Open parenthesis and queries the table Orders and keeps it Left again. Why? SQL Says “Boss, I know that I should not join this table now and the right side table is not yet ready because of the Open parenthesis encountered. So I kept this also on the Left side. Now, I need two right side table to complete the join.
  3. Now the Order Details table is queried and kept as a Right side of join as a Left side is already available.
  4. A join between Order and Order Details is performed based on the Order Id. The resultant records are treated as right because the Customer table is already queried and kept in the Left. Now the left join between the Left and Right side of result set brings all customers as the join type left outer join.

Note: The scripts for creating the Demo tables and NorthWnd database is available as a download.

Sample script for Download 

Like this site? Tell it to your Friend :)