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 4 - Pulling Data from your Database!

extract

  • 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 - 08:35 AM

Part of the intent this tutorial series is to enable you to work with databases, the other aspect of this tutorial series, is to enable you to do it well. And by "do it well", I'm talking about efficiency. Not just that it performs well, but also that you're somewhat agile when it comes to adding functionality to you r application, and when it comes to tracking down bugs, and fixing them.

Normally what I do is I define a basic class that contains the logistics of working with databases. So, I'm going to first ask you to create a new class in your Data folder, call this class Data. (By now, you should know how to do this)

Your class looks something like this right?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Super.Data {
    class Data {
    }
}

Right away, there are a few things we need to do to it. Let's mark it internal, and abstract. We will mark it abstract, so it cannot be instantiated directly, and gives us more flexibility, in loosely defining contracts.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Super.Data {
    internal abstract class Data {
    }
}

Let's just leave this class here for now. We will fill in the blanks shortly. You should gut your People Class as well. And while you're at it, might as well extend the Data Class we just made. Make your people class 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 : Data {


    }
}


Pretty straight forward right? Also, clear out your program.cs file, because People no longer support its connection test call. Make your Program.cs file look like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Super.Data;

namespace Super {
    class Program {
        static void Main(string[] args) {
            

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

When we work with the data that comes out of this table, we are going to want it to be as friendly as possible right? Let's give our project some context, to a record in the people table.

Add yet another class to your Data folder, and call this class 'Person'. And add fields to the class that match the fields in the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Super.Data {
    
    internal class Person {

        internal int id { get; set; }

        internal string name_first { get; set; }

        internal string name_last { get; set; }

        internal string email { get; set; }

    }
}

For simplicity sake, I've named the fields exactly the same as we did in the people table. But in reality, it doesn't matter; and I'll show you why.

If you go back to your people class, let's define a method in there that can translate a record into this object. So add a method that looks something like this to your People Class.

private Person extract_person(IDataReader reader) {
    Person person = new Person();
    person.id = reader.GetInt32(0);
    person.name_first = reader.GetString(1);
    person.name_last = reader.GetString(2);
    if (reader.IsDBNull(3))
        person.email = null;
    else
        person.email = reader.GetString(3);
    return person;
}

I have a few things to say about this. First of all, we know that the id, name_first and name_last require data. So there is no way they will be null. So no sense in testing for it.

The email field on the other hand, is allowed to be null, and we need to test for it. In ADO.NET, we can't test for null in a natural sense, we always test it as DBNull. That alone makes this new format, as a Person object, a lot cleaner to work with.

Now, when it comes to building these Data Classes, I personally like to make it as simple and as automatic as possible, while retaining the most amount of code re-use.

I'm going to modify that Data Class. Not sure if you're familiar with Generics, but we're going to use them! (that might warrant another tutorial, but I'll sneak em' in anyways)

Add a <T> to the declaration of your Data Class, so the declaration line looks like this.

internal abstract class Data<T> {

Now, I'm going to sneak in a few methods, to make our lives all the easier. We will provide a method to extract a single Person, multiple people, or just a single arbitrary value.

Your Data Class should look like this.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Super.Data {
    internal abstract class Data<T> {

        protected T generate_result(Func<IDataReader, T> extract_func, IDataReader reader) {
            using (reader) {
                if (!reader.Read())
                    return default(T);
                return extract_func(reader);
            }
        }

        protected T[] generate_results(Func<IDataReader, T> extract_func, IDataReader reader) {
            using (reader) {
                List<T> results = new List<T>();
                while (reader.Read())
                    results.Add(extract_func(reader));
                return results.ToArray();
            }
        }

        protected NT extract_scalar<NT>(IDataReader reader) {
            using (reader) {
                if (reader.Read())
                    return (NT)reader.GetValue(0);
                else
                    return default(NT);
            }
        }

    }
}

Take a second to look at what I've written.These functions are expecting a Data Reader, Which is a crucial component to ADO.NET, which allows us to extract the results from our queries.

We also have this Func<IDataReader, T> extract_func parameter. This is actually expecting a method. It's expecting a method, that takes a data reader as a parameter, and returns the same type our Data class is templated out to use.

Ok, I know I probably lost some of you, but let's trudge on, please feel free to ask questions about that in the comments section.

You know, we forgot to include that whole connection factory bit. We should include that in this class, so we dont need to include it in every data class. Again, any code we don't need to duplicate, makes maintaining this all the easier.

This really doesn't have to be any more difficult than adding a protected attribute to this class, and a constructor. like this:

protected ConnectionFactory m_connection_factory;

protected Data(string connection_name) {
    m_connection_factory = ConnectionFactory.new_instance(connection_name);
    return;
}

But, if your keen, you may have realized that this just broke our People Class. But, do you know why? If you look at the declaration of our People Class, you can see that we are extending Data. Well Data is now generic, and it no longer has a default constructor. We need to handle both those problems.

First of all, you'll have to adjust the declaration, to this.

internal class People : Data<Person> {

That is essentially saying, that we want to base this class, off of Data, assuming Data is working with Person objects. Almost english right? That's what I call maintainable! But you still have an error. This is because Data no longer has an implied 0 parameter constructor. We can fix that. Try adding a constrcutor to People.

internal People() : base("default") {
    return;
}

note, that I am forcing default in here, but it might be a great idea to have that configurable, using an app setting or something. In fact, let's do that. Lets add that App Setting, might as well have you guys building solid stuff.

You already have all the support built into your application, you just need to add the setting to your App.Config. Add this to your app.config.

<appSettings>
    <add key="db.connections.default" value="default"/>
  </appSettings>

Now, you need to modify your Constructor in People to support this.

internal People() : base(ConfigurationManager.AppSettings["db.connections.default"]) {

Don't forget to add your System.Configuration Namespace, in your usings section.

now, FINALLY, on to the good stuff! I did tell you there was some stuff you had to understand before really being able to get onto this though. =)

We have everything we need now to build easy to use functions to work with our people table. Lets start with a simple 'get_all_people' function. To pull the contents you populated your table with.

I'll just blurt out an example mthod, and then talk about it.

internal Person[] get_all_people() {
    string sql_query = "SELECT id, name_first, name_last, email FROM people";
    using (SqlConnection connection = m_connection_factory.create_connection()) {
        connection.Open();
        using (SqlCommand command = new SqlCommand(sql_query, connection)) {
            using (IDataReader reader = command.ExecuteReader()) {
                return base.generate_results(extract_person, reader);
            }
        }
    }
}

What this does is allows us to stick in our SQL Query. It creates a connection using our fancy factory. And it opens the connection.
Then it creates a command, with the query, and attaches it to the connection we just created.
Then it executes our query, and returns a Data Reader, allowing us to parse out the contents.

The using statements are a marvel. They allow us to be sloppy and keep the code clean. Wait.. does that make sense? =) using will automatically call the dispose method of the object declared in it, when we leave that level of brace. Regardless of how it leaves. So it guarentees our resources are cleaned up, and we don't leave any leaks of any kind.

You'll notice the return statement is calling base.generate_results. This is of course the generate_results function in the Data Class. Tje extrac_person is the method defined in People. And the reader is the very same reader we just created.

If you jumped back out to the Data Class, and took a look at generate_results,

protected T[] generate_results(Func<IDataReader, T> extract_func, IDataReader reader) {
    using (reader) {
        List<T> results = new List<T>();
        while (reader.Read())
            results.Add(extract_func(reader));
        return results.ToArray();
    }
}

you can see, that while there is data to read, it will keep calling the extract function you passed in against the reader. And add the reslts to a collection.

if you think back, that extract_person method looked like this.

private Person extract_person(IDataReader reader) {
    Person person = new Person();
    person.id = reader.GetInt32(0);
    person.name_first = reader.GetString(1);
    person.name_last = reader.GetString(2);
    if (reader.IsDBNull(3))
        person.email = null;
    else
        person.email = reader.GetString(3);
    return person;
}

so it takes the current record in the reader, and uses it to create a person object, and returns it.

So once all the records are read, the generate_results will return an array with all the people objects.

Even if you don't understand it, it's no reason not to try it out. =)

head on over to your Program.cs file. Modify it to look like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Super.Data;

namespace Super {
    class Program {
        static void Main(string[] args) {
            People people = new People();
            foreach (Person person in people.get_all_people()) {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}", person.id, person.name_first, person.name_last, person.email);
            }

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

Notice how nice and clean it is? No specific database logic in there at all! Your code stays easy to read, and all your data logic is tucked away. And when you need to modify that, you should be able to, being reasonably sure your consuming code (program.cs) will remain unchanged!

Run it!

I was planning on doing a full CRUD explanation in this tutorial, but it took so much space to write this all out, I think I'll do the insert, update, delete stuff in the next tutorial. It all really works the same from an ADO.NET perspective.

Thanks for reading!
http://forum.codecal...g-database.html

Edited by sam_coder, 23 February 2011 - 06:14 PM.
Adding link to part 5

  • 0

#2 gacanepa

gacanepa

    CC Newcomer

  • Member
  • PipPip
  • 21 posts

Posted 18 May 2011 - 12:14 PM

Where do you insert the following code?
internal Person[] get_all_people() {
    string sql_query = "SELECT id, name_first, name_last, email FROM people";
    using (SqlConnection connection = m_connection_factory.create_connection()) {
        connection.Open();
        using (SqlCommand command = new SqlCommand(sql_query, connection)) {
            using (IDataReader reader = command.ExecuteReader()) {
                return base.generate_results(extract_person, reader);
            }
        }
    }
}
I keep getting this error:
Error 1 The namespace 'Super.Data' already contains a definition for 'People'
I'd appreciate it if you can lend me a hand here.
Thanks in advance!
  • 0

#3 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 20 May 2011 - 05:42 AM

yea, have you read the tutorials 1, 2 and 3 already?

This method should be added to the Super.Data.People class. I'd have to take a look at your project to know exactly why you're getting that error. If you could zip it up and attach it to a response, I'd take a look at it.
  • 0

#4 gacanepa

gacanepa

    CC Newcomer

  • Member
  • PipPip
  • 21 posts

Posted 20 May 2011 - 05:50 AM

yea, have you read the tutorials 1, 2 and 3 already?

This method should be added to the Super.Data.People class. I'd have to take a look at your project to know exactly why you're getting that error. If you could zip it up and attach it to a response, I'd take a look at it.

sam_coder thanks a lot for taking the time to write and for your willingness to help a newbie!
Yes, I read (thoroughly, I think :)) Part 1, 2 and 3. But I will go through them all over again, just in case. At first I tried typing all the code myself but afterwards decided to copy and paste your code in case I was mistyping something.
I'll try once more right from the beginning and if I'm not successful, I'll zip the project and attach it to this thread.
Thanks a lot again!
  • 0

#5 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 20 May 2011 - 06:03 AM

Hey, no problem! Let me know.
  • 0

#6 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 20 May 2011 - 06:49 AM

So, I've taken the time to stick this in a project, and upload it. (attached to this response)
Take a look, and you should be able to see where maybe you went wrong.

Note, the connection string in this does not follow the same as in the tutorial. So if you want to run this, change the connection string in the App.config. I just have it set to point to my SQL Server Virtual Machine.

Attached Files


  • 0

#7 gacanepa

gacanepa

    CC Newcomer

  • Member
  • PipPip
  • 21 posts

Posted 23 May 2011 - 05:45 AM

So, I've taken the time to stick this in a project, and upload it. (attached to this response)
Take a look, and you should be able to see where maybe you went wrong.

Note, the connection string in this does not follow the same as in the tutorial. So if you want to run this, change the connection string in the App.config. I just have it set to point to my SQL Server Virtual Machine.

It worked like a charm! I will go through my own code to know where I made a mistake, and I'll let you know. Thanks a lot!
  • 0

#8 sam_coder

sam_coder

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 380 posts

Posted 23 May 2011 - 05:50 AM

oh glad to hear!
So when you go through tuitorial 5, the insert, update and delete methods will be added to the People Class. So when you create your People object, you can access like this.

Data.People people_controller = new Data.People();
people_controller.insert("Sammy", "Samsam", "something@somewhere.com");
Person[] people = people_controller.get_all();
foreach (Person person in people) {
   person_controller.delete(person.id);
  // or maybe
   person_controller.update(person.id, person.name_first + "ABC", person.name_last + "ABC", + person.email + "ABC");
}


The whole point would be to use that pattern, and add any other domain related methods to the People class. Or to create additional data classes for different units of functionality. Keeping your data access code as simple as that above in other parts of your application.

Thanks again for working through the tutorial series.
  • 0





Also tagged with one or more of these keywords: extract

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