C# and Databases - Part 3 - Connection Objects

Now that you have a database created, and you have a certain set of knowledge, or at least a good concept of what's going on , let's get you all set up to interact with that Database from C#. This is after all what this tutorial series is all about. =)

So, open Visual Studio, and create a new Console Application. Call it Super, just like the Database; it will help keep my head straight. (For those using different versions of Visual Studio, I'm actually going to target Framework 3.5)

Ok, so before anything in your code, let's get this out of the way, so we can see results. Make your Program.cs file, look like this.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace Super {

    class Program {

        static void Main(string[] args) {

            Console.WriteLine("Press <Enter> to terminate...");

            Console.ReadLine();

        }

    }

}

This is just so when we display results, we can actually read it, before it disappears.

Anyways, we will NOT be adding any database logic to this Program.cs, because in the real world, you will almost never do anything like that. Nope, in this tutorial, I'll show you how to do it properly. (Goosebumps? =) )

In your project explorer, right click References, and select "Add Reference". In the list of references that will eventually appear (under the .NET tab), I want you to locate 'System.Configuration'. Double Click it, or highlight it and press OK. (Note, you can make this faster by selecting any item, and then just starting to type, System.Config, well you get the idea.)

You will now see that reference in the references list. Great! Collapse that list and forget about it for now.

Right click your project (the Super node), and select Add->New Item. The template Selector will pop up. Look for the 'Application Configuration File' template, and leave the name as App.Config, and just press OK.

This is an XML file, where we will describe any and all application settings. This is perfect for most configuration scenarios, and if I hear any of you setting basic configuration settings in anything but, just because you wanted to create it, prepare to have your fingers slapped. You've been warned...

So, make your App.Config look like this.

Code:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="default" connectionString="Server=localhost\SQLExpress;Database=Super;Trusted_Connection=True;"

         providerName="System.Data.SqlClient"/>

  </connectionStrings>

</configuration>

If you've followed the previous tutorial, and have a default setup, this should work for you. The wonderful thing about setting this in your App.Config, is that you can change the location of your database, physically, and then configure your application after it's installed. No need to change any hard coded strings and rebuilding.

In another tutorial, I'll explain how to achieve really high levels of DB invariance, but for this tutorial, where you really want to lean how to work with ADO.NET, I'll show you how to build your own Connection Factory.

So, right click your project 'Super' and select Add->New Folder. Call that folder Data. This is going to be where we will stick all of our Database logic. So Right click that Folder and select Add->Class, and in the template selector, class will already be selected. So name the Class 'ConnectionFactory', and press OK.

I'm going to try to keep these tutorials short enough actually read in one sitting, so I'm going to keep our first implementation's explanation on the low. But please, ask questions! Blow your teachers' and profs' minds with your knowledge of the language, and all it can do for you... Ok, we won't be blowing anyone's minds with this... =)

Our Connection Factory will not be used by anything outside this assembly, so as best practice, we will mark the factory as internal. And we're going to protect our constructor. Make ConnectionFactory look something like this.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace Super.Data {

    internal class ConnectionFactory {

        private string m_connection_string;

        private ConnectionFactory(string connection_string) {

            m_connection_string = connection_string;

            return;

        }

           

    }

}

We will not provide a default constructor, because the factory will have to know the connection string to be able to create connections for us.. Right?

But wait, we already know the connection string; it's in the configuration file.

Right, so let's get that. You will need to add the "System.Configuration" namespace to you're path, so.. Add the appropriate using statement.

Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Linq;

using System.Text;

And now we add a static method, to call our private constructor. Since the constructor is marked private we need to have a member call it for us. It might not make sense right now, why we don't just make the constructor visible, but rather than walk you down that road, just do as I say.. =)

Add something like this to your class.

Code:

internal static ConnectionFactory new_instance() {

    try {

        return new ConnectionFactory(ConfigurationManager.ConnectionStrings["default"].ConnectionString);

    } catch (Exception) {

        throw;

    }

}

I just stuck an exception handling pattern in here, to show you what it looks like. I'm sure many of you aren't accustomed to using them, but it's a nice habit, and in a later tutorial, I'll show you how to leverage them.

Let's add an override, just in case; you want to provide alternate connection strings in your configuration.

Code:

internal static ConnectionFactory new_instance(string connection_string_name) {

    try {

        return new ConnectionFactory(ConfigurationManager.ConnectionStrings[connection_string_name].ConnectionString);

    } catch (Exception) {

        throw;

    }

}

So, now we are able to create our connection factory, but our factory still doesn't really do anything. Well, if you read part 1, you would know that a Connection provides context to our database. So this object will be used to create our connection objects.

First, you will need another namespace added to our path. So add System.Data.SqlClient.

Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

And now that we have the appropriate namespace added, we want to add a method that will pop out SqlConnection objects.

Code:

internal SqlConnection create_connection() {

    try {

        return new SqlConnection(m_connection_string);

    } catch (Exception) {

        throw;

    }

}

So, collectively, your ConnectionFactory Class should look like this.

Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

namespace Super.Data {

    internal class ConnectionFactory {

        private string m_connection_string;

        private ConnectionFactory(string connection_string) {

            m_connection_string = connection_string;

            return;

        }

        internal static ConnectionFactory new_instance() {

            try {

                return new ConnectionFactory(ConfigurationManager.ConnectionStrings["default"].ConnectionString);

            } catch (Exception) {

                throw;

            }

        }

        internal static ConnectionFactory new_instance(string connection_string_name) {

            try {

                return new ConnectionFactory(ConfigurationManager.ConnectionStrings[connection_string_name].ConnectionString);

            } catch (Exception) {

                throw;

            }

        }

        internal SqlConnection create_connection() {

            try {

                return new SqlConnection(m_connection_string);

            } catch (Exception) {

                throw;

            }

        }

    }

}

Seems like a lot of work doesn't it? Don't worry, once you get your bearings, it won't be. And to be honest, one of the wonderful things about these types of patterns is that they are very repeatable, and all this code so far is 100% re-usable. You can pretty much just pop it into any project and use it.

So, now that we can generate our Connection, we need to have a way of testing it. Probably a great place to start would be to have a class that relates to our People domain, remember our Super Database? It has one table, People. So right click the Data Folder, and select Add->Class, name it People, and press OK.

Probably the first thing I would do would be to add the System.Data and System.Data.SqlClient namespaces, since this will be the class we can use to test our ConnectionFactory. And note, that in the next tutorial, we will back out some on this class, since I'm going to show you how to build multi-table systems, with as little code-duplication as possible.

Anyways, let's make a simple function that tells us if the connection worked. I've taken the liberty of implementing the factory pattern here, and a basic function which should test if that connection works or not. Make your Connection Factory look like this.

Code:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

namespace Super.Data {

    internal class People {

        private ConnectionFactory m_factory;

        internal People() {

            m_factory = ConnectionFactory.new_instance();

            return;

        }

        internal bool ConnectionWorks() {

            bool worked = false;

            try {

                using (SqlConnection connection = m_factory.create_connection()) {

                    connection.Open();

                    worked = true;

                }

            } catch (Exception) {

                worked = false;

            }

            return worked;

        }

    }

}

There is actually a property on the connection called State, which allows me to test various states of the connection. I could more thoroughly check whether the connection is opened, or in an erroneous state, but I think this would suffice for the purposes of this tutorial.

So, now the fun stuff, let's test our connection. Go back to your Program.cs, and add the Super.Data namespace.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Super.Data;

And create a People object, so we can test that connection. It should be obvious that in the next tutorial, we will be actually adding logic to work with data in that table, from our application. Something like this should work.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Super.Data;

namespace Super {

    class Program {

        static void Main(string[] args) {

            ConsoleColor dc = Console.ForegroundColor;

            ConsoleColor vc = ConsoleColor.Green;

            ConsoleColor ec = ConsoleColor.Red;

           

            People people = new People();

            if (people.ConnectionWorks()) {

                Console.ForegroundColor = vc;

                Console.WriteLine("Connection Factory Works!");

            } else {

                Console.ForegroundColor = ec;

                Console.WriteLine("Connection Factory DOES NOT Work!");

            }

            Console.ForegroundColor = dc;

            Console.WriteLine("Press <Enter> to terminate...");

            Console.ReadLine();

        }

    }

}

If you've done everything ok, you should see a nice, happy green message. And if something is disconnected you should see a nasty red one. I would encourage you to try and break it just with the app.config. Try changing something in your connection string, watch how it behaves.

You can add additional connection strings, in the same fashion, naming them differently, and going into the People class, you can change how it's constructed, and provide an alternate name. Can you think of how you could improve you're design to make the specific connection string used throughout the application also configurable post install?

Anyways, hope you guys are enjoying this more in depth introduction to databases and C#, next tutorial I'm going to explain CRUD (Create, Read, Update, Delete).