More efficient way of writing information from multiple database tables in a text file

Question

I have two tables which are as follows:

Student:

CREATE TABLE [dbo].[Student] 
(
    [Id]   INT          NOT NULL,
    [Name] NVARCHAR(50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Class:

CREATE TABLE [dbo].[Class] 
(
    [Id]      INT          NOT NULL,
    [Teacher] NVARCHAR(50) NOT NULL,
    [Grade]   INT          NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I have created a simple UI in which the user can enter the class grade and student ID and have all the data associated with the typed class grade and student ID be written in a text file.

The code I have written successfully performs the function of writing table data to a text file but I feel it has a lot of room for improvement. The code is as follows:

private void searchInfo_Click(object sender, EventArgs e)
{
    string file = "write" + i + ".txt";

    using (StreamWriter writetext = new StreamWriter(file))
    {
        var searchCommand = "SELECT Student.ID, Student.Name FROM Student WHERE Student.ID = @sID;";

        using (connection = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(searchCommand, connection))
        {
            connection.Open();

            int S_ID = int.Parse(NudStudent.Value.ToString());

            cmd.Parameters.AddWithValue("@sID", S_ID);

            SqlDataReader da = cmd.ExecuteReader();

            while (da.Read())
            {
                writetext.WriteLine("Student ID: " + da.GetValue(0).ToString());
                writetext.WriteLine("Student Name: " + da.GetValue(1).ToString());
                writetext.WriteLine("---------------------------------");
            }

            connection.Close();
        }

        var searchCommand_1 = "SELECT Class.ID, Class.Teacher, Class.Grade FROM Class WHERE Class.Grade = @cID;";

        using (connection = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(searchCommand_1, connection))
        {
            connection.Open();

            int C_ID = int.Parse(NudClass.Value.ToString());

            cmd.Parameters.AddWithValue("@cID", C_ID);

            SqlDataReader da = cmd.ExecuteReader();

            while (da.Read())
            {
                writetext.WriteLine("Class ID: " + da.GetValue(0).ToString());
                writetext.WriteLine("Teacher: " + da.GetValue(1).ToString());
                writetext.WriteLine("Grade: " + da.GetValue(2).ToString());
                writetext.WriteLine("---------------------------------");
            }

            connection.Close();
        }

        MessageBox.Show("Data successfully written in " + file);

        i++;
    }
}

How can I make the code above less repetitive and more concise?

Answer 1

Split this task into several methods. We need two methods to get the text lines from students and classes. Note that they have the same parameter and return type. This will allows to pass them as delegate to another method later:

private static IEnumerable<string> GetStudentLines(SqlDataReader dr)
{
    while (dr.Read()) {
        yield return "Student ID: " + dr.GetValue(0).ToString();
        yield return "Student Name: " + dr.GetValue(1).ToString();
        yield return "---------------------------------";
    }
}

private static IEnumerable<string> GetClassLines(SqlDataReader dr)
{
    while (dr.Read()) {
        yield return "Class ID: " + dr.GetValue(0).ToString();
        yield return "Teacher: " + dr.GetValue(1).ToString();
        yield return "Grade: " + dr.GetValue(2).ToString();
        yield return "---------------------------------";
    }
}

Based on these methods, we can write a method that will be able to write any kind of data to a StreamWriter:

private static void WriteFromQuery(
    SqlConnection connection, string query,
    SqlParameter parameter, StreamWriter writer,
    Func<SqlDataReader, IEnumerable<string>> getLines)
{
    using var cmd = new SqlCommand(query, connection);
    cmd.Parameters.Add(parameter);

    using SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read()) {
        foreach (string line in getLines(reader)) {
            writer.WriteLine(line);
        }
    }
}

It assumes that the connection is open. It is written in a neutral way, so that it does work with different queries.

Now, the main method can be written as:

private void searchInfo_Click(object sender, EventArgs e)
{
    string file = $"write{i}.txt";
    using (var writetext = new StreamWriter(file)) {
        using var connection = new SqlConnection(connectionString);
        connection.Open();

        string searchCommand = "SELECT Student.ID, Student.Name FROM Student WHERE Student.ID= @sID;";
        int id = Int32.Parse(NudStudent.Value.ToString());
        var parameter = new SqlParameter("@sID", id);
        WriteFromQuery(connection, searchCommand, parameter, writetext, GetStudentLines);

        searchCommand = "SELECT Class.ID, Class.Teacher, Class.Grade FROM Class WHERE Class.Grade= @cID;";
        id = Int32.Parse(NudClass.Value.ToString());
        parameter = new SqlParameter("@cID", id);
        WriteFromQuery(connection, searchCommand, parameter, writetext, GetClassLines);
    }

    MessageBox.Show("Data successfully written in " + file);
    i++;
}

It creates the stream writer, the connection and opens it. Then it prepares the parameters required for our WriteFromQuery method and calls it for both students and classes.

Since the stream writer and the connection have been created in a using, they will both be closed and disposed automatically at the end of the using.

Note that I have used the new C# 8.0 using declarations syntax in some places.

---

We still have repetitions. Let us refactor the two Get...Lines methods from above:

private static IEnumerable<string> GetLines(SqlDataReader dr, params string[] lineHeadings)
{
    while (dr.Read()) {
        for (int i = 0; i < lineHeadings.Length; i++) {
            yield return lineHeadings[i] + dr.GetValue(i).ToString();
        }
        yield return "---------------------------------";
    }
}

private static IEnumerable<string> GetStudentLines(SqlDataReader dr)
    => GetLines(dr, "Student ID: ", "Student Name: ", "Grade: ");

private static IEnumerable<string> GetClassLines(SqlDataReader dr)
    => GetLines(dr, "Class ID: ", "Teacher: ", "Grade: ");