Originally published at Steve Brownlee
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
{
// This part still tightly coupled to Exercise type :(
IEnumerable<Exercise> exercises = db.Query<Exercise>
("SELECT Id FROM Exercise");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
// Et voilà... Very cleaner! Much modular! :)
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
{
// Coupling to Exercise type eliminated, but SQL still coupled :(
IEnumerable<T> exercises = db.Query<T>("SELECT Id FROM Exercise");
}
catch (System.Exception ex)
{
if (ex.Message.Contains("no such table"))
{
// Made it modular, but still coupled to Exercise type :(
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! Coupling eliminated here! :)
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 type :(
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. Much sad.
T.Seed(db); // Also illegal. Still frown.
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
Top comments (2)
Either I don't understand your point or I disagree. I think it does make sense to explain the concept of generics with plain lists, as you will use them on a daily basis (and I have to disagree with you: the SQL stuff won't be daily real world code for everyone or probaly even the majority). A list is very easy to understand and you can show that you either have to cast types a lot or implement one list or class to be contained otherwise. With generics, it's one implementation for all. And it's an isolated pattern explained and not combined with actions, static methods, SQL and ORM in the mix. After understand this in isolation, the students can start to learn this in combination with other code and other patterns.
Thanks for the comment, Philip! Explaining concepts in isolation rarely helps students achieve understanding. Adult students need to know how something helps solve problems they are working on. My students happened to be working on that code, thus the example above.