Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

C# and Databases - Part 3 - Connection Objects

connection

  • Please log in to reply
7 replies to this topic

#1 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 23 February 2011 - 06:17 AM

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.

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.

<?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.

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.

using System;
using System.Collections.Generic;
[COLOR="red"]using System.Configuration;[/COLOR]
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.

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.

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.

using System;
using System.Collections.Generic;
using System.Configuration;
[COLOR="red"]using System.Data.SqlClient;[/COLOR]
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.

internal SqlConnection create_connection() {
    try {
        return new SqlConnection(m_connection_string);
    } catch (Exception) {
        throw;
    }
}

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

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.

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
[COLOR="red"]using Super.Data;[/COLOR]

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.

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).

Part 4: http://forum.codecal...r-database.html

Edited by sam_coder, 23 February 2011 - 10:39 AM.
Adding Link to part 4

  • 0

#2 TNP

TNP

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 25 February 2011 - 09:20 AM

nice tut :c-laugh::c-laugh::c-laugh:
  • 0

#3 Tonchi

Tonchi

    Helping the world with programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1249 posts
  • Location:Zagreb
  • Programming Language:C#, Others
  • Learning:C, C++, Python, JavaScript, Transact-SQL, Assembly

Posted 06 April 2011 - 09:45 AM

nice tut but i need code for forms...writeline and readline are for console app
  • 0

#4 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 06 April 2011 - 10:02 AM

Hey Tonchi,
You have to keep going through them, I use console applications because they are easy to use in this format. There aren't many assumptions you need to make when you read a Console.ReadLine() statement for example.
All of this is 100% compatible with Windows Forms applications.

Let me know when you get through the last tutorial, and I'll then send you an example of using it in a windows forms application.

I mean this code here:
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();
        }


could technically work like this:
static void ButtonClickEvent(object sender, EventArgs args) {
            
            People people = new People();
            if (people.ConnectionWorks()) {
                lblResult.Text = "Connection Factory Works!";
            } else {
                lblResult.Text= "Connection Factory does NOT work!";
            }
            return
        }


Again, you will find that as you work through these tutorials, it's a pretty well rounded example, you will not be having to manage connection states from anywhere in your code, you'll just interact with people objects, and windows forms app or console app wont make a difference. All the Database logic is hidden from your application logic.
  • 0

#5 R4mes

R4mes

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 07 March 2012 - 01:50 AM

I cannot get this to work, it keeps saying connection does not work.
I am on visual studio 2010 (still using .net 3.5), and SQL server compact.

I tried changing the app.config file to from "SQLExpress" under connection string to just "SQL" and "SQLCompact".

I have an assumption that the problem is the connection string, but I really don't know what is wrong :S
  • 0

#6 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 07 March 2012 - 01:21 PM

i imagine you are right about the connection string. but there is likely also a change required for the provider.
the easiest thing to do would likely be to use express edition. unless you have a specific purpose for using the compact one.

anywyas. im on my phone right now. ill lok into it on my computer later.

sorry for typos or autoorrect errors. lol
  • 0

#7 R4mes

R4mes

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 08 March 2012 - 02:34 AM

I cannot just change to visual express, as this is a work computer, and we use compact :S I just cant see what the difference should be, and I do not really see anything which it would make sense to change other than the connection string.
  • 0

#8 marcelbo

marcelbo

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts
  • Learning:C#, (Visual) Basic

Posted 11 February 2013 - 12:11 PM

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.



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.

<?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.

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.

using System;
using System.Collections.Generic;
[COLOR="red"]using System.Configuration;[/COLOR]
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.

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.

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.

using System;
using System.Collections.Generic;
using System.Configuration;
[COLOR="red"]using System.Data.SqlClient;[/COLOR]
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.

internal SqlConnection create_connection() {
    try {
        return new SqlConnection(m_connection_string);
    } catch (Exception) {
        throw;
    }
}

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

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.

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
[COLOR="red"]using Super.Data;[/COLOR]

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.

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).

Part 4: http://forum.codecal...r-database.html

VS 2010 keeps giving me compile errors

Error 1 The namespace 'Super.Data' already contains a definition for 'People' 
 E:\Data\Visual Studio Projects\Super\Super\Data\People.cs 8 11 Super
Error 2 The type or namespace name 'ConnectionFactory' could not be found (are you missing a using directive or an assembly reference?) 
 E:\Data\Visual Studio Projects\Super\Super\Data\ConnectionFactory.cs 13 17 Super
 


  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download