C# – Is it considered an anti pattern to write SQL in the source code

csql

Is it considered an anti pattern to hardcode SQL into an application like this:

public List<int> getPersonIDs()
{    
    List<int> listPersonIDs = new List<int>();
    using (SqlConnection connection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["Connection"].ConnectionString))
    using (SqlCommand command = new SqlCommand())
    {
        command.CommandText = "select id from Person";
        command.Connection = connection;
        connection.Open();
        SqlDataReader datareader = command.ExecuteReader();
        while (datareader.Read())
        {
            listPersonIDs.Add(Convert.ToInt32(datareader["ID"]));
        }
    }
    return listPersonIDs;
}

I would normally have a repository layer etc, but I have excluded it in the code above for simplicity.

I recently had some feedback from a colleague who complained that SQL was written in the source code. I did not get chance to ask why and he is now away for two weeks (maybe more). I assume that he meant either:

  1. Use LINQ
    or
  2. Use stored procedures for the SQL

Am I correct? Is it considered an anti pattern to write SQL in the source code? We are a small team working on this project. The benefit of stored procedures I think is that SQL Developers can get involved with the development process (writing stored procedures etc).

Edit
The following link talks about hard coded SQL statements: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/hard-coded-sql-statements. Is there any benefit of preparing an SQL statement?

Best Answer

You excluded the crucial part for simplicity. The repository is the abstraction layer for persistence. We separate out persistence into its own layer so that we can change the persistence technology more easily when we need to. Therefore, having SQL outside of the persistence layer completely foils the effort of having a separate persistence layer.

As a result: SQL is fine within the persistence layer that is specific to a SQL technology (e.g. SQL is fine in a SQLCustomerRepository but not in a MongoCustomerRepository). Outside of the persistence layer, SQL breaks your abstraction and thus is considered very bad practice (by me).

As for tools like LINQ or JPQL: Those can merely abstract the flavours of SQL out there. Having LINQ-Code or JPQL queries outside of an repository breaks the persistence abstraction just as much as raw SQL would.

Another huge advantage of a separate persistence layer is that it allows you to unittest your business logic code without having to set up a DB server.

You get low memory-profile, fast unit tests with reproducible results across all platforms your language supports.

In an MVC+Service architecture this is a simple task of mocking the repository instance, creating some mock-data in memory and define that the repository should return that mock data when a certain getter is called. You can then define test data per unittest and not worry about cleaning up the DB afterwards.

Testing writes to the DB is as simple: verify that the relevant update methods on the persistence layer have been called and assert that the entities were in the correct state when that happened.

Related Topic