When I'm developing an application I need a database to store all user data, and to retrieve information fast. It doesn't matter if it's a web application or a desktop one, a database is always needed. So I built a layer that manages the access to the information. The closest layer to the database tables is the stored procedures layer. I use SQL Server as a database server, so the client side is base in SQLClient for .NET. Later on I'll explain how these stored procedures will work and how they are used from the client side.

There's an important thing to explain about these stored procedures, they are software generated with a CASE tool that I've developed. From an XML file I create the storeds, this XML file is also created from the table definition, I only add extra business logic but I don't start from 0, the tool generates a basic scaffolding that helps me a lot.

From a table I create the next storeds:

• GET: to retrieve one (based on the primary key) or more rows (based in some criteria).
• GETTOP: the same as GET but with a row limit.
• DEL: to delete a row.
• INS: to insert a new row.
• UPD: to update a row.
• QUERY: to get a determined number of rows, filtered by some criteria, ordered by a column and paginated.

This is created for each table, so I get a ton of stored procedures that are like "tablename_GET", "tablename_GETTOP", "tablename_DEL", "tablename_INS", "tablename_UPD" and "tablename_QUERY".

In future posts I will detail how those stored procedures are built. As a recap here is this image: