C# and Databases - Part 5 - Modifying Database

When we've covered a lot of ground. You now have really basic read support in your application for your new database. I'll hit more advanced reading topics, like returning single records, searching, pagination etc. But for now, let's grow a broader knowledge base. Let's talk executing statements against the database.

This section of the tutorial will focus on teaching you how to execute statements against the database. But not so much the specifics of SQL, that's a whole other beast. And to be truthful, regardless of what you're throwing against the database, it's all done the same way. This way! So read on!

We won't be gutting anything in your project this time. We're going to add functionality. Let's add support to add a new person to your database.

Obviously, since we're adding functionality to the Data/People domain, we will be modifying the People Class.

To keep your head straight, your People class should look something like this:

Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

namespace Super.Data {

    internal class People : Data<Person> {

        internal People()

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

            return;

        }

        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;

        }

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

                    }

                }

            }

        }

    }

}

Let's add a new method definition, called insert_people. I'll follow the same convention, and write the method, then explain it.

Code:

internal void insert_person(string name_first, string name_last, string email) {

    string sql_query = "INSERT INTO people (name_first, name_last, email) VALUES (@name_first, @name_last, @email)";

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

        connection.Open();

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

            command.Parameters.AddWithValue("@name_first", name_first);

            command.Parameters.AddWithValue("@name_last", name_last);

            if (email != null)

                command.Parameters.AddWithValue("@email", email);

            else

                command.Parameters.AddWithValue("@email", DBNull.Value);

            command.ExecuteNonQuery();

        }

    }

}

This is actually very similar to the reading data, isn't it? Except in this case, we aren't pulling any data back. We're just executing the statement, and being done with it.

I have a couple of things to say about this actually. First of all, I talked a lot about not re-implementing the same logic all over. You will notice that I actually do in a way. This pattern of creating the connection object, the command, adding parameters and executing it is repeated a lot. I could refractor this into the Data class somewhat, but it would make the code much less readable.

When it comes to code re-use, you need to remember that you do it to make it more maintainable. If the code gets too cryptic, it becomes difficult to maintain, so that aspect of the optimization is lost in good intent gone wrong. There is however a likely file size optimization to be obtained, but I would never go there personally... =)

Moving on.

You will notice that I added these @name parameters to my query, instead of concatenating the original query string. I could have written that like this....

Code:

internal void insert_person_alt(string name_first, string name_last, string email) {

    string sql_query = "INSERT INTO people (name_first, name_last, email) VALUES ('" + name_first

        + "', '" + name_last + "', '" + email + "')";

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

        connection.Open();

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

            command.ExecuteNonQuery();

        }

    }

}

First of all, I find the first easier to read. Second of all, the second is vulnerable to SQL Injection Attacks. Thirdly, there is a bug! But that may not be as apparent to someone who hasn't spent all night looking for a bug like this.

We allowed email to be null by design. And this leaves you open to a possible null reference exception, or even if I did test for null, a null entry would be submitted as an empty string.

That actually has two implications. First of all, a null and an empty string are not the same thing. So when I read records, you will notice I have test for DBNull, that would never be hit. So any additional logic possibly built, like:

Code:

if (person.email == null) { throw new Exception("No Email Address"); }

This would be invalid. These types of things can waist a lot of time. I'll bet that first example is starting to look a lot better right about now too.. Isn't It.? =)

So, do you want to give this a try? Let's refractor that Program.cs some. I've modified my Program.cs 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 People people = new People();

        static void Main(string[] args) {

            print_people();

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

            Console.ReadLine();

        }

        static void print_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);

            }

            return;

        }

    }

}

Now, let's hook up to the new insert function. Create a new method for grabbing user input, and submit it. Something like this maybe...

Code:

static void create_new_person() {

    string first_name, last_name, email;

    Console.Write("First Name: "); first_name = Console.ReadLine();

    Console.Write("Last Name: "); last_name = Console.ReadLine();

    Console.Write("Email: "); email = Console.ReadLine();

    if (email.Trim().Length == 0)

        email = null;

    people.insert_person(first_name, last_name, email);

}

This code isn't very robust mind you; it only really tests for an empty string for the email address. But that's fine; it would at least allow us to test if insert supports a null and a value properly. So, let's give this a go. Add the call to create_new_person() before the print_people in your Main Function.

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Super.Data;

namespace Super {

    class Program {

        static People people = new People();

        static void Main(string[] args) {

            create_new_person();

            print_people();

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

            Console.ReadLine();

        }

        static void print_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);

            }

            return;

        }

        static void create_new_person() {

            string first_name, last_name, email;

            Console.Write("First Name: "); first_name = Console.ReadLine();

            Console.Write("Last Name: "); last_name = Console.ReadLine();

            Console.Write("Email: "); email = Console.ReadLine();

            if (email.Trim().Length == 0)

                email = null;

            people.insert_person(first_name, last_name, email);

        }

    }

}

When you run this, you will of course be prompted to enter a first and last name, as well as an email address. If the email address is entered as empty, a record will be inserted as null. You can verify this using SQL Server Management Studio. Either view the contents of the table, or open new query windows against your database, and fire the following command.

Code:

SELECT * FROM people

Fantastic! So you can repeat this process for as many different types of methods that you want, Insert, as you've seen, Update and Delete. Rather than go through each of these methods, I'll just post one possible solution. I'll write more tutorials, explaining, SELECT, INSERT, UPDATE and DELETE. Promise!

Repeating the same process you've seen, we could add all the basic support for INSERT, UPDATE and DELETE just like this.

Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

namespace Super.Data {

    internal class People : Data<Person> {

        internal People()

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

            return;

        }

        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;

        }

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

                    }

                }

            }

        }

        internal void insert_person(string name_first, string name_last, string email) {

            string sql_query = "INSERT INTO people (name_first, name_last, email) VALUES (@name_first, @name_last, @email)";

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

                connection.Open();

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

                    command.Parameters.AddWithValue("@name_first", name_first);

                    command.Parameters.AddWithValue("@name_last", name_last);

                    if (email != null)

                        command.Parameters.AddWithValue("@email", email);

                    else

                        command.Parameters.AddWithValue("@email", DBNull.Value);

                    command.ExecuteNonQuery();

                }

            }

        }

        internal void update_person(int id, string name_first, string name_last, string email) {

            string sql_query = "UPDATE people SET name_first = @name_first, name_last = @name_last, email = @email WHERE id = @id";

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

                connection.Open();

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

                    command.Parameters.AddWithValue("@id", id);

                    command.Parameters.AddWithValue("@name_first", name_first);

                    command.Parameters.AddWithValue("@name_last", name_last);

                    if (email != null)

                        command.Parameters.AddWithValue("@email", email);

                    else

                        command.Parameters.AddWithValue("@email", DBNull.Value);

                    command.ExecuteNonQuery();

                }

            }

        }

        internal void delete_person(int id) {

            string sql_query = "DELETE FROM people WHERE id = @id";

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

                connection.Open();

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

                    command.Parameters.AddWithValue("@id", id);

                    command.ExecuteNonQuery();

                }

            }

        }

        internal void delete_all() {

            string sql_query = "DELETE FROM people ";

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

                connection.Open();

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

                    command.ExecuteNonQuery();

                }

            }

        }

    }

}

This is enough information between the first 5 tutorials for you guys to start experimenting, however, I plan on going into more detail on the types of queries discussed, before going into some of the more useful parts of Database Systems, like aggregate processing and record grouping, ordering, pagination, Multiple Tables, multiple Data domains, Table Joins, Stored Procedures, SQL Functions and well, I can keep writing this stuff.