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?
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?
Code:
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.
Code:
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.
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 : 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.
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) {
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.
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.
Code:
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.
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.
Code:
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.
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.
Code:
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.
Your Data Class should look like this.
Code:
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:
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:
Code:
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.
First of all, you'll have to adjust the declaration, to this.
Code:
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.
Code:
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.
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.
Code:
<appSettings>
<add
key="db.connections.default" value="default"/>
</appSettings>
Now, you need to modify your Constructor in People to
support this.
Code:
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.
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.
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);
}
}
}
}
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,
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,
Code:
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.
if you think back, that extract_person method looked like this.
Code:
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.
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.
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) {
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!
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!
No comments:
Post a Comment