If you search for information about the C# Generics language feature, you see the same abstract code examples about making your own RedundantList<T> (but don't do this, it's just our example) over and over again. It's remarkably difficult to find content where someone shows how they actually used it to make their code more reusable.
Well, the topic of Generics came up in a discussion this week with my students in Day Cohort 26 of Nashville Software School so I went looking and realized that most of the content is unusable for a student. Of course, the purpose, and usage of Generics is largely beyond the capabilities of a student with 4 months of development experience to understand, but I wanted show them why they are used with a straightforward example.
I've been using a simple CLI app to teach them the basics of the language syntax, LINQ, classes, OOP, and SQL. I'm just about to connect the dots for them by using Dapper ORM to query a small SQLite database.
In order to create the tables, and seed them with initial data, so that they can practice SQL, I created a DatabaseInterface.cs file with a series of static methods in it to perform those tasks.
Data/DatabaseInterface.cs
...
/*
Purpose: Check the database to see if the `Exercise` table
has been defined. If not, create it and seed it.
*/
public static void CheckExerciseTable()
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
// Select the ids from the table to see if it exists
IEnumerable<Exercise> exercises = db.Query<Exercise>
("SELECT Id FROM Exercise");
}
catch (System.Exception ex)
{
/*
If an exception was thrown with the text "no such table"
then the table doesn't exist. Execute a CREATE TABLE
statement to create it.
*/
if (ex.Message.Contains("no such table"))
{
db.Execute(@"CREATE TABLE Exercise (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`Name` TEXT NOT NULL,
`Language` TEXT NOT NULL
)");
/*
Seed the table with some initial entries
*/
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'ChickenMonkey', 'JavaScript')");
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'Overly Excited', 'JavaScript')");
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'Boy Bands & Vegetables', 'JavaScript')");
}
}
}
...
I continue to do this for each table that I want in my database. For example, I then check the Instructors table.
Data/DatabaseInterface.cs
...
public static void CheckInstructorsTable()
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
IEnumerable<Instructor> instructors = db.Query<Instructor>
("SELECT Id FROM Instructor");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
db.Execute($@"CREATE TABLE Instructor (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`FirstName` TEXT NOT NULL,
`LastName` TEXT NOT NULL,
`SlackHandle` TEXT NOT NULL,
`Specialty` TEXT,
`CohortId` INTEGER NOT NULL,
FOREIGN KEY(`CohortId`) REFERENCES `Cohort`(`Id`)
)");
db.Execute($@"INSERT INTO Instructor
SELECT null,
'Steve',
'Brownlee',
'@coach',
'Dad jokes',
c.Id
FROM Cohort c WHERE c.Name = 'Evening Cohort 1'
");
db.Execute($@"INSERT INTO Instructor
SELECT null,
'Joe',
'Shepherd',
'@joes',
'Analogies',
c.Id
FROM Cohort c WHERE c.Name = 'Day Cohort 13'
");
db.Execute($@"INSERT INTO Instructor
SELECT null,
'Jisie',
'David',
'@jisie',
'Student success',
c.Id
FROM Cohort c WHERE c.Name = 'Day Cohort 21'
");
db.Execute($@"INSERT INTO Instructor
SELECT null,
'Emily',
'Lemmon',
'@emlem',
'Memes',
c.Id
FROM Cohort c WHERE c.Name = 'Day Cohort 21'
");
}
}
}
...
There are a total of five tables that I create and seed.
- Instructor
- Student
- Exercise
- Cohort
- StudentExercise
That's five methods that all do the exact same thing, but just work against a different resource. To make the code more reusable, I went through a three step process.
Step 1: Create and Seed Static Methods
The first step was to move the commands for creating a table, and seeding the table, to static methods on the database models. This helps me implement the Single Responsibiliy Principle. Otherwise, the DatabaseInterface class has myriad reasons to change.
/Models/Exercise.cs
using System.Collections.Generic;
using Dapper;
using Microsoft.Data.Sqlite;
namespace nss.Data.Models
{
public class Exercise
{
public int Id { get; set; }
public string Name { get; set; }
public string Language { get; set; }
public List<Student> AssignedStudents { get; set; }
public static void Create(SqliteConnection db)
{
db.Execute(@"CREATE TABLE Exercise (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`Name` TEXT NOT NULL,
`Language` TEXT NOT NULL
)");
}
public static void Seed(SqliteConnection db)
{
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'ChickenMonkey', 'JavaScript')");
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'Overly Excited', 'JavaScript')");
db.Execute(@"INSERT INTO Exercise
VALUES (null, 'Boy Bands & Vegetables', 'JavaScript')");
}
}
}
This makes the CheckExerciseTable() method in the DatabaseInterface class cleaner and the application is more modular.
Data/DatabaseInterface.cs
...
public static void CheckExerciseTable()
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
IEnumerable<Exercise> exercises = db.Query<Exercise>
("SELECT Id FROM Exercise");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
Exercise.Create(db);
Exercise.Seed(db);
}
}
}
...
Step 2: Generic CheckTable Method
Next, I started the process of making a single method that would check for any table's existence, and create/seed it if needed.
public static void CheckTable<T>()
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
IEnumerable<T> exercises = db.Query<T>("SELECT Id FROM Exercise");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
Exercise.Create(db);
Exercise.Seed(db);
}
}
}
By using the T generic type, I can now invoke the method and use the specified type for the List of results and the Dapper Query method.
Program.cs
DatabaseInterface.CheckTable<Exercise>();
My SQL statement is still querying the Exercise table, so I needed to pass the table name in as a parameter to make this method reusable. Then I refactored the SQL statement to use the parameter.
// When invoked with the code above, T = Exercise
public static void CheckTable<T>(string table)
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
// More generic! :)
IEnumerable<T> resources = db.Query<T>($"SELECT Id FROM {table}");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
// Still tightly coupled to Exercise class :(
Exercise.Create(db);
Exercise.Seed(db);
}
}
}
Step 3: Action Delegates to Create and Seed
The last part that needs to be reusable is invoking the Create() and Seed() static methods on the appropriate type. Unfortunately, it is illegal in C# to invoke a static method on a generic type.
// T represents the type used (i.e. Student, Exercise, etc.)
T.Create(db); // Illegal
T.Seed(db); // Also illegal
The only other option I could think of in this situation is making a delegate; specifically, an Action delegate. Why?
Encapsulates a method that has a single parameter and does not return a value.
That perfectly describes the Create() and Seed() static methods that I have on the data models.
// Hey, look! This method has a single parameter and is void
public static void Create(SqliteConnection db)
{
db.Execute(@"CREATE TABLE Exercise (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`Name` TEXT NOT NULL,
`Language` TEXT NOT NULL
)");
}
What this mechanism will allow me to do is pass the reference to the correct static method to the CheckTable<T> method. Take a look.
public static void CheckTable<T>(
string table,
Action<SqliteConnection> create, // <-- Delegate to the correct `Create()`
Action<SqliteConnection> seed // <-- Delegate to the correct `Seed()`
)
{
SqliteConnection db = DatabaseInterface.Connection;
try
{
IEnumerable<T> resources = db.Query<T>($"SELECT Id FROM {table}");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
create(db); // Invoke the Create() reference
seed(db); // Invoke the Seed() reference
}
}
}
Now I can use the CheckTable<T>() method to verify, create, and seed any database table that I want to use. I pass in the following parameters.
- A string holding the name of the table I want to use in the SQL
- A reference to a static
Create()method on the corresponding class - A reference to a static
Seed()method on the corresponding class
DatabaseInterface.CheckTable<Exercise>("Exercise", Exercise.Create, Exercise.Seed);
DatabaseInterface.CheckTable<Cohort>("Cohort", Cohort.Create, Cohort.Seed);
DatabaseInterface.CheckTable<Instructor>("Instructor", Instructor.Create, Instructor.Seed);
DatabaseInterface.CheckTable<Student>("Student", Student.Create, Student.Seed);
DatabaseInterface.CheckTable<StudentExercise>("StudentExercise", StudentExercise.Create, StudentExercise.Seed);
Simple and Real
No discussion about obscure data structures that nearly no one will ever use in their daily work. No abstract example code that doesn't convey any real value to a developer. Just a simple example of a real application used to train junior developers in the usage of C# and SQL, and how to use Generics to make the code reusable, and easier to read/maintain.
🍴Fork on Github: https://github.com/nss-day-cohort-26/student-exercises/tree/dapper-sql-advanced