Introduction
Today we will look at a very common debate that occurs while developing a data access layer. This debate is to decide if we want to use stored procedures and keep our logic in the database, or if we want to use an ORM like Entity Framework and keep all logic in our middle tier C# code, using the database only as a data store. I will not be detailing the different types of ORMs and samples on using them as this information is widely available on the web. I will also not detail how to write and use stored procedures, as this information is also widely available for almost all types of databases. I will simply try to explain the pros and cons of using each and which and why I think it is a better solution.
What are Stored Procedures?
Let us begin by identifying what stored procedures are. Stored procedures are commonly used artifacts of relational databases, like SQL Server, Oracle, etc. These can include code to manipulate the data and are efficient in selecting, processing, and sending back data to the front-end application. These are written using SQL language, which is easy to understand and learn. Some of the major advantages of using stored procedures are that these are compiled, optimized, and run by the database engine and so can perform very quickly. For this reason, I have seen some architects strongly push the use of stored procedures.
What is an ORM?
An ORM means object relational mapping. This method allows us to create classes to represent our database tables, and then use a context or communication class to join these classes to our database tables. Then, we can use the full power of the middle-tier programming language, like C#, to write queries to select and update the data in our tables. Of course, an ORM does not end there, and we can also create other artifacts for stored procedures, etc. We can also merge classes from multiple tables and break a single table into multiple classes. However, the main idea is to use the database mainly as a store and keep all processing logic in the middle-tier code.
What to use today? Stored Procedures vs ORM
These days we have a much-advanced landscape for both stored procedures in various databases, and also great ORMs. For example, Entity Framework Core gives us many options and is easy to understand and implement. So, what should we use? The answer is that it depends upon the particular scenario. However, I would prefer to use an ORM, as in this way we can better abstract all business and processing logic into proper classes inside our middle-tier, and use the power of all libraries, NuGet packages, etc. available at this level. This also saves us from being tied to a particular database, and in the future, we can move to another data store quite easily. However, if we need to do some heavy programming at a database level and want to fully utilize the power of the database engine, using stored procedures would be the way to go. However, in this case, we might need to commit to a particular database for a longer period, especially if we are using a SQL dialect for that particular database to write our stored procedures.
Summary
In this article we looked at what to choose to build, our data access or data process layer. The answer depends on the coder's needs, and different people will have different opinions on it. However, from my personal point of view, I prefer to keep all logic in the middle-tier. This way the logic is better abstracted, and we can use the vast number of libraries available to process the data. Also, we can retrieve data in chunks and process it using parallel programming techniques and utilize the extremely powerful hardware we have these days. This also does not tie us to any particular database.