Program With Dave

If you have not read Part 1 and Part 2 of this network tutorial series, please do so now. The code in this post will build upon code from those posts.

The Connection Class: Beginning Network Code

We are going to get into the fun part of the network code in this part of the series. This post will teach you how to use TcpClient to send and receive data over the network. TcpClient is a class in Microsoft Dot Net which instantiates a TCP connection and can send and receive data in the form of byte arrays.

Step 1: Create our connection class. Right-click on our Chat class library and add a new class. Call that class “Connection”. This class will use our Packet class to send and receive data.

Step 2: Include the correct using statements. We need to include certain dot net classes to make our coding job easier. Add these using statements to the top of Connection.cs.

1
2
3

using System;
using System.Net.Sockets;
using System.Net;

The TcpClient class we are going to use is included by using these statements. This will make our coding easier since we don’t have to fully qualify each time we use the TcpClient class.

Step 3: Instantiate our TcpClient object. Inside of your Connection class, add the following line of code which will create our TcpClient class. By creating one here, it will be instantiated with our Connection object and always be available.

1
2
3

    public class Connection
    {
        TcpClient c = new TcpClient();

We now have a client object named “c” to work with in our network code.

Step 4: Constructors. Let’s create a couple of constructors for our connection class. One constructor should take a TcpClient object as a parameter, so that it can accept one that’s already created. Another constructor should just take a server to connect to and the port to connect to, and open the connection.

1
2
3
4
5
6
7
8
9

        public Connection(TcpClient client)
        {
            c = client;
        }
 
        public Connection(string server, int port)
        {
            c.Connect(server, port);
        }

As you can see from the second constructor, opening a TCP connection to a given server with a given port is very easy to do. The call to Connect is a “blocking” operation, which as you should remember from Part 1 of this series means it will wait until the connection has been created successfully before continuing with any further code.

If the connection fails for any reason, an exception will be thrown. Any client code written using the Connection class should be able to handle that exception. As a matter of fact, every function in this class could possibly throw an exception, so your client code should always wrap network calls in a try/catch statement.

I had a situation today where I wanted to return a user’s name using Linq. Sure, I could have easily written some code for this, but for the sake of keeping my code simple and readable I wanted Linq to handle the building of the name.

Users Table

The Linq seemed easy:

1
2
3
4
5
6
7
8
9
10

public static string GetNameOfUser(int userID)
        {
            DatabaseDataContext dc = new DatabaseDataContext();
 
            var user = from u in dc.Users
                       where u.UserID == userID
                       select u.FirstName + " " + u.LastName;
 
            return (user.Count() == 0) ? "Unknown User" : user.First();
        }

However my results were not correct. Bob was left out because he did not have a last name value (it’s NULL in the database.) You will get results like this if you run that for each user (one at a time, but I put them in a table here.)

Results

In SQL it is very common to use the COALESCE() function to handle problems with null values. But how can I do that in Linq? I didn’t want to write extra code to check for nulls and concatenate the values myself.

The answer is simple: ??. Yes, that’s right. Two question marks is the “coalesce” operator in Linq. It’s a good idea to wrap what you are testing in coalesce inside parenthesis as well.

What follows is my new function.

1
2
3
4
5
6
7
8
9
10

public static string GetNameOfUser(int userID)
        {
            DatabaseDataContext dc = new DatabaseDataContext();
 
            var user = from u in dc.Users
                       where u.UserID == userID
                       select (u.FirstName ?? "") + " " + (u.LastName ?? "");
 
            return (user.Count() == 0) ? "Unknown User" : user.First();
        }

As you can see, I’ve added ?? “” after both FirstName and LastName. This translates to:

coalesce(FirstName, ”)
coalesce(LastName, ”)

Which means that if FirstName is null you will get ”, which adds correctly in SQL to another string. Same goes for LastName.

Here are my new results:

Results

Today I had to create a Linq-To-SQL query which joined two tables. These tables had compound primary keys (table simplified for example):

Table Referrers

Table ReferrerInfo

So I wanted to join these together using a Linq-To-SQL query. The first thought that occured to me was to use a join:

1
2
3

var referrers = from r in Referrers
               join ri in ReferrerInfo on r.ServerConnectionID equals ri.ServerConnectionID ..... ??????
               select r;

So, apparently in C# you cannot do multiple columns in your join. I have to join on both ServerConnectionID and ReferrerID.

This page: http://www.onedotnetway.com/linq-to-sql-join-on-multiple-conditions/ explains one method to do so, but unfortunately that Linq query only works in VB.

So, I ended up writing a Linq-To-SQL that just uses the old style of joining, by using the where clause!

1
2
3
4
5

var referrers = from r in Referrers
                from ri in Referrer_Info
                where r.ServerConnectionID == ri.ServerConnectionID &&
                   r.ReferrerID == ri.ReferrerID
                select r;

This successfully executes my multi-column join.

I had a situation recently where I had a table full of people records, where the people were divided into families. The business logic that needed to be followed was that I had to assign a “Twin Code” to each record. This meant that for each family in the database, if two or more members were born on the same day they should be treated as twins. The twins should be assigned a number enumerating them in order of birth. If the member was not a twin, they should just receive the twin code of 1.

Here’s an example table:

There are lots of ways to achieve the desired result, but the simplest is to just use a simple SELECT statement combined with the ROW_NUMBER() function with a couple parameters as to how to number the rows!

ROW_NUMBER() provides you with the number of a row in a given recordset, where you provide details on how to number the records. For example, if I just had to number the records above based solely upon the date of birth (ignoring families) then I would use this query:

1
2
3
4
5
6
7
8
9
10
11

SELECT
     [PersonID]
    ,[FamilyID]
    ,[FirstName]
    ,[LastName]
    ,[DateOfBirth]
    ,ROW_NUMBER() over (ORDER BY DateOfBirth) AS Number
FROM
	People
ORDER BY 
	PersonID

This just tells the ROW_NUMBER() function to order its numbering ascending by DateOfBirth. Notice that I apply an order myself later on in the query, which is different than the row_number() order. I would get these results:

The number field that is assigned to each record is in the order of DateOfBirth.

Ordering my numbering by DateOfBirth is just half of the picture. I also need to “group” the records by the FamilyID. This is where a clause in T-SQL that you might not be very familiar with comes into play: “PARTITION BY”. The PARTITION BY clause allows us to group the results within the call to ROW_NUMBER() without grouping them ourselves via a GROUP BY. It just tells the ROW_NUMBER what groupings to use when it does its counting.

Here is our final SQL statement, which achieves the business logic we wanted to implement.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT 
       [PersonID]
      ,[FamilyID]
      ,[FirstName]
      ,[LastName]
      ,[DateOfBirth]
      ,ROW_NUMBER() over(PARTITION BY FamilyID, 
                         CONVERT(NVARCHAR(25), DateOfBirth, 111) 
                         ORDER BY DateOfBirth ASC) TwinCode
 
  FROM [People]
ORDER BY
	PersonID

In the ROW_NUMBER function above, I am doing several things. I’m grouping on FamilyID, and also grouping on a converted DateOfBirth. I convert the DateOfBirth to an nvarchar using the 111 conversion code, because that gets results like ’2009/10/11′ and ’2009/10/12′ which can easily be grouped by to achieve distinct dates.

Grouping on the Family, DateOfBirth, and then sorting by DateOfBirth ascending achieves the desired result for the ROW_NUMBER. Here are the results of the query:

As you can see, the two people who qualify as twins above (Karly and Kacy) are enumerated correctly, with Karly receiving a 1 and Kacy receiving a 2. All the records that are not twins properly receive a 1.