Writing SQL commands in C# - Part 3 - Editing records and maintainable dedicated clas

[ArekBulski]

Welcome all! My previous two turorials about inserting, reading and deleting records were so welcomed that I decided to write another one. If you have read them then let's assume we already have a solution with a database attached. We also got few records in a table.

Today however, I will focus on something more important than working code. I want you to see how awesome prototypes and dedicated classes are. First one will give us immediate feedback about how good the code is, second one will be a very well designed code that can be maintained over ages.

Editing existing records: using SQL UPDATE statement

Here is a piece of code that executes yet another SQL statment. It will modify as many records as you want, only the conditions you specify will make which records are changed. Warning: If you don't specify that WHERE clause then it will eat all your data in the table. I would rather not want to make a mistake like this and mess up my company database.

I just copied this code from the code above, and modified one line. I strongly believe that it is a very good way to make a prototype and immediately know does it work or not. That is what a prototype is for.

SqlConnection connection1 = new SqlConnection(
    Properties.Settings.Default.some_numbersConnectionString);

SqlCommand insertCommand = new SqlCommand(
    "UPDATE NumbersTable SET number = 1003, description = 'changed my mind' WHERE number = 1002",
    connection1);

connection1.Open();
insertCommand.ExecuteNonQuery();
connection1.Close();

MessageBox.Show("Records 1002s have been changed to 1003s. Please check your table data again. :)");

Structure your code: dedicated class for database operations

As you see here, most of SQL operations are very schematic (even reading). They are copied again and again and I just change one line that carries the SQL statment. Look at the full code and think about is this something you would like show to your employers. My honest opinion is that this code is too redundant. Every method alone works great. But making a new connection for every query seems wrong.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void InsertButton_Click(object sender, EventArgs e)
        {
            SqlConnection connection1 = new SqlConnection(
                Properties.Settings.Default.some_numbersConnectionString);

            SqlCommand insertCommand = new SqlCommand(
                "INSERT into NumbersTable values (1002, 'will delete this one')",
                connection1);

            connection1.Open();
            insertCommand.ExecuteNonQuery();
            connection1.Close();

            MessageBox.Show("Record 1002 inserted. Please check your table data. :)");
        }

        private void ReadButton_Click(object sender, EventArgs e)
        {
            SqlConnection connection1 = new SqlConnection(
                Properties.Settings.Default.some_numbersConnectionString);
            SqlCommand selectCommand = new SqlCommand(
                "SELECT * FROM NumbersTable",
                connection1);

            connection1.Open();
            SqlDataReader reader = selectCommand.ExecuteReader();
            string fetchedRecords = string.Empty;

            while (reader.Read() == true)
            {
                fetchedRecords += "Record: (number) " + reader[0] + " (descriptio) " + reader[1] + " \n";
            }

            connection1.Close();
            MessageBox.Show("Found following records: \n \n" + fetchedRecords);
        }

        private void DeleteButton_Click(object sender, EventArgs e)
        {
            SqlConnection connection1 = new SqlConnection(
                Properties.Settings.Default.some_numbersConnectionString);

            SqlCommand insertCommand = new SqlCommand(
                "DELETE NumbersTable WHERE number = 1002",
                connection1);

            connection1.Open();
            insertCommand.ExecuteNonQuery();
            connection1.Close();

            MessageBox.Show("Records 1002s deleted. Please check your table data again. :)");
        }

        private void EditButton_Click(object sender, EventArgs e)
        {
            SqlConnection connection1 = new SqlConnection(
                Properties.Settings.Default.some_numbersConnectionString);

            SqlCommand insertCommand = new SqlCommand(
                "UPDATE NumbersTable SET number = 1003, description = 'changed my mind' WHERE number = 1002",
                connection1);

            connection1.Open();
            insertCommand.ExecuteNonQuery();
            connection1.Close();

            MessageBox.Show("Records 1002s have been changed to 1003s. Please check your table data again. :)");

        }

    }
}

Now the suprising part. I really think this way of coding is very *good*. You might ask "is this a good practice? this is crap". And I would agree. This code is a prototype. Making it took me less time (also because I copied and copied) so it saved me time on designing something that I would change later anyway. It also gave me a real insight into how my code should look like.

What I would like to do now, is put all our database operations into a dedicated class. We already have a *working* prototype, shall we make a second *maintainable* solution?

private void button1_Click(object sender, EventArgs e)
        {
            string message = "Here are logs for all operations made: \n";

            message += "Adding a 1002 record twice. \n";
            DatabaseOperations.ExecuteSqlStatement("INSERT into NumbersTable values (1002, 'will delete this one')");
            DatabaseOperations.ExecuteSqlStatement("INSERT into NumbersTable values (1002, 'will delete this one')");


            message += "Reading all records present: \n";
            SqlDataReader reader = DatabaseOperations.ExecuteSqlReader("SELECT * FROM NumbersTable");
            while (reader.Read() == true)
                message += "  Record: (number) " + reader[0] + " (description) " + reader[1] + " \n";
            reader.Close();


            message += "Editing all 1002 records into 1003s. \n";
            DatabaseOperations.ExecuteSqlStatement("UPDATE NumbersTable SET number = 1003, description = 'changed my mind' WHERE number = 1002");


            message += "Reading all records present (again): \n";
            reader = DatabaseOperations.ExecuteSqlReader("SELECT * FROM NumbersTable");
            while (reader.Read() == true)
                message += "  Record: (number) " + reader[0] + " (description) " + reader[1] + " \n";
            reader.Close();


            MessageBox.Show(message);
        }

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

namespace WindowsFormsApplication1
{
    /// <summary>
    /// This class performs the database operations. Pass on SQL statements and 
    /// it will just return the data for you. 
    /// </summary>
    public static class DatabaseOperations
    {
        /// <summary>
        /// This connection will do everything. Better to not throw connections 
        /// on left and right.
        /// </summary>
        private static SqlConnection connectionForAllOperations;

        /// <summary>
        /// This method executes any non-reader command. Use it for INSERT, UPDATE 
        /// and DELETE. It does not work for SELECT, sorry.
        /// </summary>
        public static void ExecuteSqlStatement(string sql)
        {
            if (connectionForAllOperations == null)
            {
                /// The connection will be opened only once, just before first operation.
                connectionForAllOperations = new SqlConnection(
                    Properties.Settings.Default.some_numbersConnectionString);
                connectionForAllOperations.Open();
            }

            SqlCommand anyCommand = new SqlCommand(sql, connectionForAllOperations);
            anyCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// This method executes a reader command. Use for SELECT queries. 
        /// </summary>
        public static SqlDataReader ExecuteSqlReader(string sql)
        {
            if (connectionForAllOperations == null)
            {
                /// The connection will be opened only once, just before first operation.
                connectionForAllOperations = new SqlConnection(
                    Properties.Settings.Default.some_numbersConnectionString);
                connectionForAllOperations.Open();
            }

            SqlCommand anyCommand = new SqlCommand(sql, connectionForAllOperations);
            SqlDataReader anyReader = anyCommand.ExecuteReader();
            return anyReader;
        }

    }
}

I would like to take an opportunity to make a breaking point in the way coding is conducted. I think every manager would say that I should skip the first crap and proceed to working on the solution, probably to save time. I would totally disagree here. Here are my arguments.

I made a prototype really quickly, almost in no time. That is because I could do as I want and no code quality was needed. I also made the solution really quickly. You might not believe me but I copied most of the stuff and just renamed and refactored it. At the same time...

Class design is completely new. I eliminated the big flaws that were existing in the prototype. Therefore the solution is very maintainable. And that is worth some employees time, sweat and earnings in the future.

Also building a prototype gave me a real insight into how SQL queries work. That knowledge is very important when building a solution (first or not), as much as when solving bugs. It will also pay off when implementing all the details that a solution would require.

The manager could say: "Then design the software well in the beginning". Of course it sounds like a great idea. But how will I design something I never saw on my own eyes so far? I am pretty sure that the folks at NASA really took their time to design their first space rockets, those ones with higher oxygen level inside. They had the design, but not experience. Let the astronauts rest in peace.

[Jordan]

Very nice! That first image is broken for me. +rep

[WingedPanther]

Nice job +rep