Program With Dave

One thing that you may encounter frequently is a situation where you want to count the records in a table which are of a certain type. You want to do this as efficiently as possible, instead of running a new count query for each type. Let’s use the below table for reference.

We have above a table of animals. How can we determine the count of each type of animal? The simplest (and most inefficient) is to run a count() query for each type of animal. This would mean we have 3 queries to run, as there are 3 types of animals.

1
2
3
4
5
6
7
8
9

declare @numdogs int
declare @numcats int
declare @numferrets int
 
SELECT @numdogs = COUNT(*) FROM animals WHERE type = 'Dog'
SELECT @numcats = COUNT(*) FROM animals WHERE type = 'Cat'
SELECT @numferrets = COUNT(*) FROM animals WHERE type = 'Ferret'
 
SELECT @numdogs AS NumDogs, @numcats AS NumCats, @numferrets AS NumFerrets

We will get results from that query like this.

As you can imagine, this is terribly inefficient. A good answer in this case is to use a “conditional” sum in the SQL query. So rather than running COUNT(*) we will be running SUM(…). We will combine the SUM() call with a CASE statement, so that it counts correctly. This is the trick I hope to teach you. You can place CASE … END statements inside of aggregate functions.

Throw away all of our temporary variables, the 4 select statements involved, and let’s replace them with this single select.

1
2
3
4
5
6

SELECT 
	SUM(CASE WHEN type='Dog' then 1 else 0 end) AS NumDogs,
	SUM(CASE WHEN type='Cat' then 1 else 0 end) AS NumCats,
	SUM(CASE WHEN type='Ferret' then 1 else 0 end) AS NumFerrets
FROM 
	animals

We get these results:

Which is exactly what we got previously, but using a much more efficient method. The trick was to insert a CASE … END statement inside the SUM. The case will return a 1 when the type is the type we are looking for, and a 0 otherwise. The sum will then execute to add up all of those 0s and 1s. A 0 will not add anything, so we will effectively end up counting the values which match the type we are looking for.

If you have not read Part 1 of this Multi-Post tutorial, check it out now. This tutorial will rely on the code written in that tutorial.

This is the second installment of a multi-post tutorial on writing network software using the dot net framework and the C# programming language. In this installment we will be going over the ChatPacket class which implements the Packet abstract class.

The ChatPacket Class

If you recall, our Packet class had three important elements.

1. Type Code: This is an integer value which identifies the type of packet. It is transmitted across the connection for every packet, so that the receiving end knows how to interpret the packet it is receiving.
2. Converts to Byte Array: Every packet which inherits from our base packet class should be able to convert itself into a byte array.
3. Parses a Byte Array: The packet classes which inherit from the base class should implement a parse function which will take a byte array and read it into the proper packet structure.

These capabilities should be implemented by any class which inherits from Packet, so that the library stays consistent.

Let’s begin by creating a new class. Right-click on your Chat class library and add a new class called ChatPacket.cs. This file will be our main class for sending chat data back and forth.

Step 1: Inherit from Packet base class. Every packet class we create should inherit from the base Packet class. This is because the server and client code we will be using will use the base class in all of its operations.

1

public class ChatPacket : Packet

Step 2: Create the chat data property. Chat data is going to be just a string in our chat packet class. Let’s add a new property to our ChatPacket class.

1

public string ChatData { get; set; }

Chatdata is going to basically be the text of each chat packet that gets sent around. When you type in text to send out to the server (and the server sends it to the other clients) then this is the data that gets sent out.

Step 3: Constructor should set the Type of Packet. The next thing that must be done is that each packet must identify its type. This is so that the end point which receives every packet needs to know how to decode it into the correct class. In our constructor for each packet, we are going to tell the packet which type it is.

1
2
3
4

        public ChatPacket() 
        {
            _packetTypeCode = 1;
        }

Keep in mind that the type code should be unique to this type of packet. If we created another packet type in this application, its typecode should be set to 2 for each instance. ChatPacket is going to be packet type 1.

We are only going to send around ChatPackets, but it would be a good idea in a more complex program to use constants to make sure your packet types are consistent integer values.

Step 4: Implement ToByteArray() method. This is the method that will be called by our server to convert a packet into a stream of bytes. The network code will then send the bytes of the network, and the code at the other end will reconstruct the bytes into a packet.

1
2
3
4

        public override byte[] ToByteArray()
        {
            return Encoding.ASCII.GetBytes(ChatData);
        }

The above code uses the built-in ASCII encoding capabilities of dot net to convert our string of packet data into an array of bytes which can be transmitted.

Step 5: Implement the Parse operation which reconstructs packets. The Parse function takes as input a series of bytes and should return the reconstructed packet object.

1
2
3
4
5
6
7
8
9
10

        public static new ChatPacket Parse(byte[] packetData)
        {
            ChatPacket p = new ChatPacket();
 
            if(packetData.Length != 0) {
                p.ChatData = Encoding.ASCII.GetString(packetData);
            }
 
            return p;
        }

As you can see, the above function is fairly simple. It just does the opposite of the ToByteArray function, in that it uses the built-in ASCII Encoding classes to construct a string from bytes rather than deconstruct a string into bytes.

It returns a new packet object.

Completing the ChatPacket Class

So what we have done here is to create a class which will our packet class. ChatPacket is a very simple packet class, because the only data that is sent in the packet is the text of the chat message. It is a good idea to keep your packets very simple, so they can be easily and quickly packaged and processed.

In the next portion of this tutorial series, we are going to move on to implement the Server class and program, which is where this all gets interesting!

I had to work on a project recently which involved accessing a web page from a blackberry device. In order to know what it will look like exactly, I decided to write the page software on my machine, and use a Blackberry simulator from Research In Motion to pull up the page so I’ll know how it will look on the client phones.

Downloading the simulator was easy! You can get the Blackberry Simulator software here. But I ran into an issue: I couldn’t access the internet! All I would see when I opened the browser and tried to access a webpage was a “Requesting…” progress bar at the bottom. No internet access!

I would then receive the message “Unable to connect to the selected Mobile Data Service, please try again later.”

No Internet On The Simulator!

After a little bit of research I determined that what I needed is the “Blackberry Email and MDS Services Simulator.” You can download the MDS Services Simulator here. I downloaded and installed the simulator, but then I ran into another problem.

Error on the MDS Simulator

The internet on my blackberry simulator still was not working. I looked through the MDS output on the command prompt window, and I noticed an error: “java.net.BindException: Address Already In Use: JVM_Bind:8080″. Now, if you’ve read my sockets tutorial network primer, you know that a port can only be bound for listening once. So apparently I had another process on my PC which was bound to listen on port 8080.

So how can I tell which process was running on 8080? I stopped IIS, restarted MDS but I still got the error. I know from working in IT for so long, however, how I can tell what process is using what port.

Pull up a command prompt window and type the command:

C:\> netstat -a -b

You will get output like the image below. As you can see, there is a java process already using port 8080. All I did from there was kill that java process using the Task Manager, and restarted MDS.

Java.exe is LISTENING on port 8080.

Restarting MDS and the Blackberry Simulator after killing that java process made the blackberry simulator work. I was connecting to the internet just fine.

The simulator can hit the internet.

This is the first part of a multiple-post tutorial which will explain how to use the dot net TcpClient and TcpListener classes to create a multi-threaded network application. This result will manifest itself in a chat server and chat client.
read more »

I have a project I’ve worked on recently which involved several database.

1. Database 1 is the “master” database to which a web interface interacts.
2. Database 2 is an “intermediate” database, which is set up for merge replication, to publish down to handheld clients running Windows Mobile.
3. Database 3 is the database on the handhelds which subscribe to database 2.

Data can be entered in the web interface (and therefore directly to database 1) or it can be entered on the handheld, and replicated up to database 2. The handhelds can’t tie directle to database 1, because some transformations have to be applied.

To tie data back and forth between database 1 and 2, I have an SSIS package which runs every half hour to do the following:

1. The package imports new data from the intermediate database.
2. The package pushes any changes down from database 1 to database 2, to be replicated out to the handhelds.

Here is the problem I encountered. Any new record I put into the web interface would not make it down to the mobile handhelds, even after the SSIS package put the data into database 2 and the handheld synchronized. I would have to re-create the snapshot and then re-subscribe the handheld in order to see new records. That just won’t work!

After pulling my hair our for about an hour trying to figure out the problem, and a lot of web searching, I finally came across this amazing page about SQL replication which solved the problem immediately.

When populating a merge-replication published database using SSIS, DO NOT USE “FAST LOAD”. Fast load does not run the triggers on the tables which are being replicated, and those triggers have to run in order for replication to do its work. I just changed it from using “Fast Load” to using the normal method, and my replication worked like a charm!

I had to write a function today which will take an arrayof bytes and convert those bytes into a hexidecimal string representation. This was part of an encryption algorithm. There are a couple on the net but they seem to be more complicated. Here is what I came up with (and this seems the simplest way to me, so there may be similar ones out there):

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

        public string ToHexString(byte[] array)
        {
            string vals = "0123456789ABCDEF";
            StringBuilder result = new StringBuilder();
 
            for (int i = 0; i < array.Length; i++)
            {
                result.Append(vals[array[i] >> 4]);
                result.Append(vals[array[i] & 15]);
            }
 
            return result.ToString();
        }