C# and Databases - Part 1 - High Level Intro to working with Databases from C#

I've thought a little bit about writing a tutorial series on databases, because it seems like the C# community here on this form is a little disconnected from the subject. Though I have no idea how many tutorials I will have to write to have everyone here at an expert level. For this tutorial, I'll focus on the high level stuff. You know, what typical components are involved, and how they work together.

Part of the problem with trying to do something like this in a forum, is that you need to understand at a high level, how components are related to one another, and you have to understand a certain amount about each component.

Also, did I mention that you need to have a working Database to really make use of it? So where does that leave me,, oh yes, we also need a tutorial on how to create a database, first to connect to.

Bear with me, read this, ask questions, and tell me what you want to know about databases, and I'll start spilling my guts. Odds are, some of you will become comfortable with the topic.

Understand that when you work with databases, that your database is not just a place you read and write information to. Like say, a text file. There are some key differences. Many differences are way out of scope of this small tutorial, but suffice to say, that your code that talks to the database, will not follow your normal flow of code. It's a conundrum, it's normally ugly, and well, deal with it. =)

Assume that you have a database. It's the database you want to work with. It's living somewhere, lets say, and SQL Server Express instance, that is running on your development machine. (Chances are, you will be able to connect to it right now)

The database is not managed by our application, and it's running somewhere under the control of another process. The how we talk to it, isn't really a concern for our application, ADO.NET hides a lot of that from us. It exposes an object, called a Connection, which is your 'context' to that database.

It all sounds very theoretical.. doesn't it?

That's alright, it isn't too bad, I promise.

we cannot work with the database with this connection object by itself, it really is just context to that database. In order to work with the Database, we need to use another ADO.NET object, called a Command.

A Command is used to execute an action against a Data source. We have context to that Data Source through the Connection Object. Is everyone still with me?

If you have these two things, you're golden.

There are basically 2 types of actions you can do against your Data Source, though a command.

You can Do something to the Data Source, or you can Read something from the Data Source.

The code for something like this (pseudo code) would look something like this:

Code:

string connection_string = "Data Source=localhost\SQLExpress;Initial Catalog=mydatabase;Integrated Security=SSPI;";

using (SqlConnection connection = new SqlConnection(connection_string)) {

        connection.Open();

        string query = "SELECT * FROM whatever;";

        using (SqlCommand command = new SqlCommand(query, connection)) {

               using (SqlDataReader reader = command.ExecuteReader()) {

                       while (reader.Read()) {

                               reader.GetValues(object_array);

                       }

               }

        }

}

So, this is overly simplified. And you see what I mean? The code kind of looks awkward, and I guess it is. This is why we typically build a set of Data Components, to hide logic like this from your application.

I'd like to move this on, and write something useful to you guys. How relevant is this information? I'd like to write tutorials on specifically, how to work with connections, Commands, data readers,
I'd even write a tutorial on how to create and do specific things with the database server itself (but it seems we don't have a Database Tutorial Section)...