ADO .NET Part 1: Introduction

Hi everyone, and welcome to this mini-series on ADO .NET, a fundamental API that lets us connect to and work with relational databases from our .NET code. It’s been around since the earliest versions of the .NET Framework.

What is ADO .NET?

The meaning of “ADO” is not really important, since it comes from a bygone era when dinosaurs ruled the land. What’s important is that it lets us connect to a database, run queries, update data, execute stored procedures, manage transactions, and all the things you would expect to do when working with a relational database. The part we’ll be covering is called the connected layer.

There is another aspect called the disconnected layer which we won’t be covering here. Using things like DataSets and DataTables, it essentially allows you to work on an in-memory copy of part of your database, and handles connections for you. Since it needs to model database schema constructs, it ends up using a lot more memory than simply retrieving your data using the connected layer.

Why Bother?

Over the course of my career, I occasionally found myself teaching ADO .NET to colleagues who fell into one of the following categories:

  • They just graduated, so they haven’t had the chance to learn ADO .NET yet
  • They’ve been using Entity Framework all along, and never needed ADO .NET
  • They’ve been living under a rock

ADO .NET is worth learning because it is the core database connectivity technology for .NET. Object Relational Mappers (ORMs) like Entity Framework (EF) or NHibernate use ADO .NET underneath. ADO .NET is very easy to use, as we shall see in this mini-series, and gives you all the flexibility and performance you can get. This means you can resort to it even for things that ORMs can’t handle.

As it happens, nowadays, it seems like many people automatically resort to Entity Framework by default, without considering the tradeoffs or alternatives. Entity Framework has quite a learning curve and there is a lot you might be paying in terms of performance if you don’t learn to tame it and work in the way it expects. This might be overkill if I just want to run some queries against a database.

Consider this: with Entity Framework I have to at a minimum translate the query from SQL to LINQ or lambda syntax, and then translate it back to SQL every time I need to troubleshoot or optimise it. I don’t see what we gain by representing SQL in OOP style. If anything, it’s a leaky abstraction (try using string.IsNullOrEmpty() with Entity Framework 6) with a lot of baggage.

There are two main things that Entity Framework can help us with: keeping hardcoded SQL strings out of our logic, and mapping result data to our OOP models. The former can easily be achieved by using a repository pattern (which you should be doing anyway even with EF), and the latter can be facilitated by a micro-ORM called Dapper, as we’ll see in the last article in this mini-series.

So while Entity Framework or other ORMs might have their appeal, and ADO .NET might sound like something ancient and low-level, it’s actually helpful to understand this core technology, add it to your toolkit, and understand how it compares to similar technologies.

Opening a Connection

Enough talk! Let’s open a connection against a database. Assuming we’re working with SQL Server (we’ll see how to work with other databases later in this mini-series), we’ll need the following using statement:

using System.Data.SqlClient;

The above namespace is part of the .NET Framework (so no extra steps necessary there), but if you’re using .NET Core, you’ll need to intall the System.Data.SqlClient package first.

            const string connStr = @"Server=.\SQLEXPRESS; Integrated Security=True";

            using (var conn = new SqlConnection(connStr))
            {
                conn.Open();
                Console.WriteLine("Connected!");
            }

The above code (actually, just the two highlighted lines) is all you need to establish a connection to a local SQL Server Express instance (no specific database) using the default Windows Authentication. If it’s successful, you’ll see “Connected” written to the console window. Otherwise, it will throw an exception. We’re not doing any exception handling for the sake of brevity, but ovbiously, you should have that in real production code. Also, Open() has an asynchronous counterpart (OpenAsync()) which we’ll talk about later in this mini-series.

This trivial example is not only a starting point that we will build on in the next articles. It also stands on its own as a simple application to test basic database connectivity when no other tools are available.

Connection Pooling

In the connected layer of ADO .NET, we obviously need to manage connections, and it might not be immediately evident how this should be done.

The good news is that it’s easier than you might think. ADO .NET internally has a connection pooling mechanism. This means that connections you close aren’t necessarily closed (at least not immediately) and can internally be reused, avoiding certain overheads with opening and closing connections.

This means that you’re encouraged to open and close connections as much as you need, especially because ADO .NET objects such as SqlConnection (and others we’ll see later) are not thread-safe; thus reusing instances can lead to severe problems in environments such as web applications where multiple requests may need to access the database concurrently.

Summary and Next Steps

In this article, we’ve discussed why it is useful to learn ADO .NET if you don’t know it already, and we’ve limited the scope to the connected layer only. This mini-series is meant to teach you the essentials as concisely a possible, and is not intended to be a comprehensive reference.

In the second half of the article, we saw how easy it is to open a connection, and discussed the connection pooling mechanism that ADO .NET automatically applies whenever we create or dispose of a connection object.

This lays the foundation for the next article, in which we’ll learn how to run different types of queries.

Visual Studio Bug with Spaces in Path

About a year ago, I ran into a weird bug. After having cloned a repository from Azure DevOps, I was baffled to see that the Solution Explorer in Visual Studio was not showing any files, even though there were plenty:

This problem still annoys my colleagues to this day. What happened in every case was that there was a space in the name of the project in Azure DevOps, so cloning the repo resulted in a folder that included %20 in the name. As I’m sure you’re aware, that’s the URI-encoded version of a space.

Unfortunately, for whatever reason, Visual Studo does not like this very much. It’s not a big deal though. Simply change that %20 to an actual space, and the files suddenly show up in Solution Explorer:

This problem seems to apply only to .NET Core projects. I haven’t been able to replicate it for older .NET Framework projects.

Analysing Binary Files using xxd

When you’re trying to make sense of a binary file format, a good hex viewer or hex editor is an invaluable tool. As shown in “Ultima 1 Reverse Engineering: Decoding Savegame Files“, a typical workflow involves viewing a hex dump of the binary data, making some small change, taking a second hex dump, and comparing the differences. If you’re lucky, you might even be able to observe patterns in the data directly.

Tandy 1000 graphics data for the space fighters in Ultima 1 can be easily observed in a hex editor such as Okteta.

While a good visual hex editor (such as Okteta under Linux or xvi32 under Windows) is essential to view the binary data in hex and also make direct changes to the data files, a command-line hex viewer for Linux called xxd also exists. As with most things in a Linux environment, a lot of power comes from being able to combine command line tools in a way that produces the results we want. As we shall see, one of the benefits is that although most hex editors don’t have tools to compare hex dumps, we can leverage existing command-line diff tools for Linux.

Reverse Engineering the Ultima 1 Savegame File Format

We’ve already seen in “Ultima 1 Reverse Engineering: Decoding Savegame Files” how to analyse how the bytes in the Ulltima 1 savegame files change in response to actions in the game world. Let’s see how we could do this more easily using Linux command-line tools.

First, we start a new game, and take note of a few things such as the player’s position and statistics:

Ultima 1 when starting a new game.

We can use xxd take a hex dump of the savegame, which by default goes to standard output:

A hex dump of an Ultima 1 savegame file

By redirecting the output to a file (which we’re arbitrarily calling before.hex), we can save this for later comparison:

xxd PLAYER1.U1 > before.hex

Next, we move a couple of spaces to the right and save the game. Again, we take note of the situation in the game world (i.e. that we have moved to the right, and food has decreased by 1):

Ultima 1 after moving a couple of spaces to the right.

We can now take a new hex dump:

xxd PLAYER1.U1 > after.hex

Now that we have a record of the savegame before and after having moved, we can compare the two dumps using popular Linux diff tools such as diff or vimdiff:

vimdiff before.hex after.hex
Comparing hex dumps with vimdiff

In this case, simply moving two steps to the right has changed four different things in the savegame file: the player’s position, food, move count, and tile in the overworld map. It takes a bit more patience to reduce the number of variables at play and come to some conclusions about what the bytes actually represent, but you can hopefully appreciate how well these command-line tools play together.

Analysing The Savage Empire Dialogues

The Ultima 1 savegame is particularly easy to analyse and compare because it’s got a fixed size of 820 bytes, and each field in the game state has a fixed place within that file. Not all binary files provide this luxury.

For instance, the dialogues of The Savage Empire are LZW-compressed and are written using a proprietary scripting language. However, we can still use command-line tools to extract some interesting insights.

Using tools from the Nuvie project, you can extract each character’s dialogue into a separate binary file, numbered from 0 to 76 with some gaps. We can thus write a simple loop in bash syntax from 0 to 76 and invoke xxd on each file, using the parameters -l 16 to print out only the first 16 octets:

for i in $(seq -f "%03g" 0 76)
do
    echo -n "$i "; xxd -l 16 "path_to_dialogue_files/$i.dat"
done

The result is that we can identify an increasing NPC number as well as the NPC’s name and a part of their description within those first few bytes, indicating that although the structure of the data may be complex, there is still a deterministic pattern to it:

First few bytes of the first several NPC dialogues in The Savage Empire.

Conclusion

Whether analysing binary files using hex tools is your thing or not, I hope at this stage you can appreciate how much you can get out of combining a few simple command-line tools together.

Quick Mount in DOSBox under Linux

DOSBox is incredibly handy to run old games. In “DOSBox for Dummies“, I covered basic usage, as well as how to write a Windows batch file to automate some of the more repetitive operations (such as mounting). I also explained how to extend this to games requiring a CD in “Running Games Requiring a CD in DOSBox“.

If your games are all under the same folder, you might want to consider automatically mounting your DOS games folder using a dosbox.conf file. Otherwise, you can resort to scripting via batch files (Windows) or shell scripts (Linux).

For those one-off situations where you just want to try out a game quickly without setting anything up, regardless of where it resides on the filesystem, you can run the following (in Linux) from the folder where your game is:

dosbox -c "mount c $(pwd)" -c "C:"

This is the same method I used in previous articles to pass commands to DOSBox. The only difference is that here I’m taking advantage of command substitution in bash (as seen in “Scripting Backups with bash on Linux“) to pass in the current directory via the pwd command. That way, no matter where your game folder is on the filesystem, DOSBox will start in the right location. Then, all you’ll need to do is invoke the right executable.

Happy retro gaming!

SDL2 Drag and Drop

Hi everyone! It’s been a while since my last SDL2 article (see all SDL2 articles). Today, I’m going to show how you can use the mouse to drag an object across an SDL2 window.

Drag and drop is nowadays a standard feature of any Multiple Document Interface (MDI). Although MDI as a user interface layout has fallen out of fashion in mainstream applications (even GNOME seems to think we don’t need it), it has enabled so many things ranging from windows in operating system GUIs to inventory containers in games.

The source code for this article is available in the SDL2DragDrop folder at the Gigi Labs BitBucket repository.

Note: any & in the code should be just an &. Sorry about that. I’m still waiting for this to be fixed.

Displaying an Empty Window

Let’s start out by displaying an empty window. We can use the code below for this:

#include <SDL2/SDL.h>
 
int main(int argc, char ** argv)
{
    // variables
    
    bool quit = false;
    SDL_Event event;
    
    // init SDL
    
    SDL_Init(SDL_INIT_VIDEO);
    SDL_Window * window = SDL_CreateWindow("SDL2 Drag and Drop",
        SDL_WINDOWPOS_UNDEFINED, SDL_WINDOWPOS_UNDEFINED, 640, 480, 0);
    SDL_Renderer * renderer = SDL_CreateRenderer(window, -1, 0);
    
    // handle events
    
    while (!quit)
    {
        SDL_Delay(10);
        SDL_PollEvent(&amp;event);
    
        switch (event.type)
        {
            case SDL_QUIT:
                quit = true;
                break;
        }
    
        SDL_SetRenderDrawColor(renderer, 242, 242, 242, 255);
        SDL_RenderClear(renderer);
        
        SDL_RenderPresent(renderer);
    }
    
    // cleanup SDL
    
    SDL_DestroyRenderer(renderer);
    SDL_DestroyWindow(window);
    SDL_Quit();
    
    return 0;
}

Most of this should be familiar from “Showing an Empty Window in SDL2“, but there are a few differences:

  • The #include on the first line is different from that of many of my previous SDL2 articles. That’s because I’m now using SDL2 on Linux (see “How to Set Up SDL2 on Linux“).
  • We’re using SDL_RenderClear() to give the window a background colour (as we’ve done in a few earlier articles).
  • We’re using SDL_PollEvent() to check for events all the time (combined with SDL_Delay() to occasionally give the CPU a break). This is also something we’ve done a few times before.

On Linux, assuming my file is called main.cpp, I can compile this from the command-line as follows:

g++ main.cpp -lSDL2 -lSDL2main -o sdl2exe

Just to be super clear (as this has been a point of confusion in earlier articles), I’m using the C++ compiler. We’ll be using a couple of C++ features, so please don’t try to compile this article’s code as-is with a C compiler.

Once this compiles successfully, I can run the executable that was produced as a result:

./sdl2exe

…and I get an empty window:

Adding Rectangles

We’re going to need something we can drag around, so let’s add a couple of rectangles. Again, we’ve done this before (see “SDL2 Bounding Box Collision Detection“), but we’ll improve a little by storing our rectangles in a C++ STL list.

First, add the necessary #include at the top of the file:

#include <list>

With this in place, we can now create a couple of SDL_Rects and put them in a list. The following code goes before the event loop.

    SDL_Rect rect1 = { 288, 208, 100, 100 };
    SDL_Rect rect2 = { 50, 50, 100, 80 };
    
    std::list<SDL_Rect *> rectangles;
    rectangles.push_back(&amp;rect1);
    rectangles.push_back(&amp;rect2);

Towards the end of the event loop, we can add code to draw those rectangles. Since we’re using a list, we can add more rectangles if we want, and this part of the code isn’t going to change. Note that I’m also using a little C++11 shortcut to iterate through the list.

        SDL_SetRenderDrawColor(renderer, 242, 242, 242, 255);
        SDL_RenderClear(renderer);
        
        for (auto const&amp; rect : rectangles)
        {
            SDL_SetRenderDrawColor(renderer, 0, 255, 0, 255);
            SDL_RenderFillRect(renderer, rect);
        }
        
        SDL_RenderPresent(renderer);

If we compile and run this now, we get a couple of green rectangles:

Changing Colour on Click

Before we get to actually dragging those rectangles, we need a way to select one when it gets clicked, both in code and visually. For this, we’ll add a selectedRect variable somewhere before the event loop:

SDL_Rect * selectedRect = NULL;

By default, no rectangle is selected, which is why this is set to NULL.

We also need a couple more variables somewhere at the beginning of the program to help us keep track of mouse events:

    bool leftMouseButtonDown = false;
    SDL_Point mousePos;

In the switch statement within the event loop, we can now start adding mouse event handlers. We’ll start with one for SDL_MOUSEMOTION, which keeps track of the mouse coordinates in the mousePos variable we just declared:

            case SDL_MOUSEMOTION:
                mousePos = { event.motion.x, event.motion.y };
                break;

Adding another event handler for SDL_MOUSEBUTTONUP, we clear the leftMouseButtonDown flag and the selected rectangle when the left mouse button is released:

            case SDL_MOUSEBUTTONUP:
                if (leftMouseButtonDown &amp;&amp; event.button.button == SDL_BUTTON_LEFT)
                {
                    leftMouseButtonDown = false;
                    selectedRect = NULL;
                }
                break;

Finally, we add another event handler for SDL_MOUSEBUTTONDOWN, which uses SDL_PointInRect() to identify the rectangle being clicked (if any), and sets it as the selected rectangle:

            case SDL_MOUSEBUTTONDOWN:
                if (!leftMouseButtonDown &amp;&amp; event.button.button == SDL_BUTTON_LEFT)
                {
                    leftMouseButtonDown = true;
                    
                    for (auto rect : rectangles)
                    {
                        if (SDL_PointInRect(&amp;mousePos, rect))
                        {
                            selectedRect = rect;
                            break;
                        }
                    }
                }
                break;

At this point, we can add some conditional logic in the rendering code to draw the selected rectangle (i.e. the one being clicked) in blue instead of green:

        SDL_SetRenderDrawColor(renderer, 242, 242, 242, 255);
        SDL_RenderClear(renderer);
        
        for (auto const&amp; rect : rectangles)
        {
            if (rect == selectedRect)
                SDL_SetRenderDrawColor(renderer, 0, 0, 255, 255);
            else
                SDL_SetRenderDrawColor(renderer, 0, 255, 0, 255);
            
            SDL_RenderFillRect(renderer, rect);
        }
        
        SDL_RenderPresent(renderer);

If we compile and run this now, we find that rectangles become blue when clicked:

Drag and Drop

That might have seemed like a lot of work just to highlight a rectangle when clicked, but in fact we have already implemented much of what we need for drag and drop. To finish the job, we’ll start by adding a new variable near the beginning of the program:

SDL_Point clickOffset;

This clickOffset variable will store the point within the rectangle (relative to the rectangle’s boundary) where you clicked, so that as we move the rectangle by dragging, we can keep that same spot under the mouse pointer.

In fact, when the left mouse button is pressed, we will now store this location so that we can use it later in the code:

            case SDL_MOUSEBUTTONDOWN:
                if (!leftMouseButtonDown &amp;&amp; event.button.button == SDL_BUTTON_LEFT)
                {
                    leftMouseButtonDown = true;
                    
                    for (auto rect : rectangles)
                    {
                        if (SDL_PointInRect(&amp;mousePos, rect))
                        {
                            selectedRect = rect;
                            clickOffset.x = mousePos.x - rect->x;
                            clickOffset.y = mousePos.y - rect->y;
                            
                            break;
                        }
                    }
                }
                break;

Then, while the mouse is moving, we update the selected rectangle’s position accordingly:

            case SDL_MOUSEMOTION:
                {
                    mousePos = { event.motion.x, event.motion.y };
                    
                    if (leftMouseButtonDown &amp;&amp; selectedRect != NULL)
                    {
                        selectedRect->x = mousePos.x - clickOffset.x;
                        selectedRect->y = mousePos.y - clickOffset.y;
                    }
                }
                break;

…and that is all that is needed to allow the user to click and drag those rectangles:

Wrapping Up

In this article, we’ve mostly built on concepts covered in earlier articles. By manipulating mouse events, we were able to add interactivity to rectangles. They change colour when clicked, and can be dragged around using the mouse.

There are a few things you’ll notice if you’re diligent enough:

  • If you drag the rectangles around really fast, they lag a little behind the mouse pointer. I’m not really sure how to fix that.
  • There isn’t really a proper z-index implementation, so it looks bizarre when you can drag a rectangle underneath another. This can probably be fixed by changing the order in which the rectangles are rendered, so that the selected one always appears on top of the rest.
  • Using a list is okay for a few items, but if you have a lot of objects in your window, you might want to use a more efficient data structure such as a quadtree.

"You don't learn to walk by following rules. You learn by doing, and by falling over." — Richard Branson