Stored Procedures Vs. No Stored Procedures

There is this debate raging among developers: “Is it better to use stored procedures or not use stored procedures”.  From first glance, this seems like a simple question, but there are some complicated implications around this question.  Here’s the basic pros and cons of using stored procedures in your system:

Pros

  • You can isolate table changes from your front-end.
  • Updating business code is easy, just update the stored procedure while your system is running (assuming you already tested the changes off-line and everything is good).
  • Many queries can be reduced to one call to the database making your system faster.

Cons

  • Your business processing is performed by your database which is expensive (license fees compared to web servers).
  • Unit testing is all but impossible.
  • Version control is difficult.
  • You are married to your database server technology (MS SQL cannot be easily ported to Oracle).
  • Stored Procedure languages are not well structured.  This leads to spaghetti code.

Now, let’s expand a bit and discuss each of these issues:

Isolate Table Changes

It may seem like overkill to setup all of your select, insert, update and delete queries to call a stored procedure, then have the stored procedure call the tables.  This can be a boon for situations where you might change your table structure in the future.  An example would be a situation where a table becomes too wide and you want to break the table into two tables.  Your stored procedures can have the same interface to your program and the code inside could handle all the details of where the data is stored and read from.  Removing a field from a table can also be safely done, since you can just remove it inside the stored procedure but leave the parameter called from your software until you can root out all calls to the database and change those (or leave the dead parameter).

Updating Business Code

Business code is the logic that computes and performs work on your database.  Your stored procedure might update one table, add an entry to a log table and remove corresponding records from another table.  Another use for a stored procedure is to pass filter and ordering information into a query for a list of data.  A dynamic query can be formed in a stored procedure and executed with all the parameters entered.  This relieves your front-end from the task of sorting and filtering data.  It can also reduce the amount of raw data returned from the database.

The point here is that your business code might need a change or an enhancement.  If there is a bug, then it can be fixed and deployed on the fly.  A .Net application must be compiled and carefully deployed on servers that are not accessed during the time of deployment.  A stored procedure can be changed on the fly.  If your website is a large monolithic application, this capability becomes a larger “pro”.

One Call to the Database

If a record edit requires a log entry, then you’ll be forced to call your database twice.  If you use a stored procedure, the stored procedure code can make those calls, which would be performed from the database itself and not over the wire.  This can reduce the latency time to perform such an operation.

Now for the list of cons…

Business Processing is Being Performed by the Database

MS SQL server and Oracle licensing is expensive.  Both licenses are based on number of CPUs and the price can get steep.  If you have a choice between performing your processing on a web server or a database server, it’s a no-brainer.  Do it on the web server.  Web servers, even an MS Server license is cheaper than a SQL Server license.  Initially your business will not feel the cost difference because your system is small.  However, once your system grows you’ll see your costs increase on an exponential scale.

Unit Testing

Unit testing is not available for stored procedures.  You could probably get creative and setup a testing environment in Visual Studio, but it would be custom.  Without unit tests, you are not able to regression test.  If your stored procedures contain logic that allows different modes to occur, it can be difficult to properly test each mode.  An example is a stored procedure that performs filter and sorting operations.  If you are building a dynamic query using “if” statements, then you’ll have a combination of different possible inputs.  How do you ensure that your dynamic query doesn’t have a missing “and” or a missing comma, or a union query with non-matching fields?  It’s difficult.  If this logic is in your website code, you can wrap each combination of inputs in unit tests to provide regression testing when you add a filter or sorting option or change an option.

Version Control

No matter how you divide your code, you’ll need to make sure you version control your database so you can keep a history of your changes and match those changes with your code.  Visual Studio allows you to define all your database objects in a project (see SQL Server Database Project type).  There are tools available to allow you to create change scripts from two Team Foundation Server versions.  This can be used to update multiple databases.  Versioning a database is not a common practice and that is why I’ve put this under the “con” list instead of the “pro”.  For companies that keep their database definitions in a version control system, they can take this off the “con” list.

Married to Your Database Server

Till death do you part!  If you ever decide to switch from one database server technology to another, you’ll discover how steep the hill is that you’ll need to climb.  Each stored procedure will need to be converted by hand one-by-one.  If your system doesn’t have stored procedures, then you’ll have an easier time converting.  Minor differences between triggers and indexes might be an issue between Oracle and SQL, and there’s the recursive query in Oracle that is different.  You might even have issues with left and right outer joins if you used the “(+)” symbol in Oracle.  Stored procedures will be your Achilles heel.

Spaghetti Code

Writing stored procedures is a lot like writing in Classic ASP.  It’s messy.  I see a lot of sloppy coding practices.  There is no standard for formatting queries or TSQL code.  Everybody has their own short-cuts.  Once a system grows to a point where it contains tens of thousands of stored procedures, you’re faced with a mess that has no hope.  C# code has the luxury of being able to be refactored.  This is a powerful capability that can be used to reduce entangled code.  Being able to break code into small and management chunks is also helpful.  If your database code is contained in a Visual Studio project, you can perform some of the same refactoring, but you can’t test on the fly.  So programmers prefer to change stored procedures on their test database where refactoring is not available.

Conclusion

Are there more pros and cons?  Sure.  Every company has special needs for their database.  Some companies have a lot of bulk table to table processing that must be performed.  That screams stored procedures, and I would recommend sticking with that technique.  Other companies have a website front-end with large quantities of small transactions.  I would recommend those companies keep their business logic in their website code where they can unit test their logic.  In the end, you’ll need to take this list of pros and cons and decide which item to give more weight.  Your scale may tip one way or the other depending on which compromise you want to make.

 

EF Code-First Stored Procedures With Parameters

Summary

In an earlier post I showed how to create a method that can access a stored procedure using Entity Framework Code-First.  In this post I’m going to show how to pass parameters and show a few tricks.

The Stored Procedure

First, you’ll need to create a new stored procedure in your APIUniversity database (see this post for the table definition and other stored procedure).

CREATE PROCEDURE [dbo].[select_one_room]
    @roomnum AS int
AS
BEGIN
    SELECT
id,name FROM Room WHERE roomnumber = @roomnum
END

New Method

Now create a new method named GetRoom:

public DbRawSqlQuery<RoomListResult> GetRoom(int roomNumber)
{
    var roomNumberParam = new SqlParameter(“roomnum“, roomNumber);

    return this.Database.SqlQuery<RoomListResult>(“EXEC select_one_room @roomnum“, roomNumberParam);
}

 First, you’ll notice that I added a SqlParameter for the parameter that will be fed into the SqlQuery call.  Second, you might notice that I had to put in the “EXEC” command in the stored procedure string.  Apparently, this can be left off, if you’re stored procedure doesn’t contain any parameters, but will complain with an error “Incorrect syntax near ‘select_one_room'” if it is not used in this case.

Make sure you put commas between multiple parameters.  You can also leave off the “@” symbol in the parameter name when used in the SqlParameter() method, but you’ll need the “@” symbol in the SqlQuery() method string.

To use this stored procedure, your query might look something like this:

using (var db = new MyContext())
{
    var results = db.GetRoom(1);

    foreach (var item in results)
    {
        Console.WriteLine(item.Name);
    }

    Console.ReadKey();
}

Bug in the Return Class Column Attribute

For tables, you can rename fields by using an attribute to declare what the column name is inside the database.  Here’s an example where I changed the “Room” class so that the “Name” field is now “RoomName”, but the field in the database remains “Name”:

[Table(“Room“)]
public class Room
{
    [Key]
    public int id { get; set; }

    [Column(“Name“)]
    public string RoomName { get; set; }
    public int RoomNumber { get; set; }
}

This works as expected.

Now, if you try this trick on the “RoomListResult” class, you’ll discover that the attribute is ignored.  This is a known bug (maybe sometime in the future I can amend this post if the bug gets fixed).  Your result will return null for any fields that do not match the field name returned in the stored procedure result.

 Where to Get the Sample Code

I have posted the sample code on my GitHub account.  You can download it by clicking here.  Be sure to search for “My Connection String” and replace with your own database connection string.

 

 

EF Code-First Stored Procedures

Summary

In this post I’m going to show the very basics of calling a stored procedure from Entity Framework using the Code-First technique.

The Database Setup

First, I’m going to create a database named “APIUniversity”.  If you’ve tried out the API projects from my previous posts, you probably already have this database in place and you can just use that.  Here’s the “Room” table:

CREATE TABLE [dbo].[Room](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [RoomNumber] [int] NULL,
 CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Some data you can use to populate the table:

insert into room (name,roomnumber) values (‘Gym‘,1)
insert into room (name,roomnumber) values (‘Math Room‘,100)
insert into room (name,roomnumber) values (‘Chemistry Lab‘,101)
insert into room (name,roomnumber) values (‘Lecture 1‘,102)
insert into room (name,roomnumber) values (‘Lecture 2‘,103)
insert into room (name,roomnumber) values (‘Art Room‘,104)


And finally, here’s the SQL script to create the stored procedure that we’ll use:

CREATE PROCEDURE [dbo].[select_rooms]
AS
BEGIN

    SELECT id,name FROM Room
END


First, we’ll need a class to define the record that will be returned from this stored procedure:

public class RoomListResult
{
    public int id { get; set; }
    public string Name { get; set; }
}

You don’t need any “usings” for this code. 

Now you’ll need to add your stored procedure read method to your context:

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace CodeFirstSample
{
    public class MyContext : DbContext, IDisposable
    {
        public MyContext()
            : base(“My Connection String“)
        {

        }

        public DbRawSqlQuery<RoomListResult> RoomList()
        {
            return this.Database.SqlQuery<RoomListResult>(“select_rooms“);
        }
    }
}

Technically, you can perform your stored procedure access from within your linq query, but this method keeps your code clean when you start to add parameters to stored procedures.

Here’s the code you can test in your console application to see the results:

class Program
{
    static void Main(string[] args)
    {
        using (var db = new MyContext())
        {
            var results = db.RoomList();

            foreach (var item in results)
            {
                Console.WriteLine(item.Name);
            }

            Console.ReadKey();
        }
    }
}

You should see a list of room names like this:



I didn’t cover how to handle parameters. I’m saving that for a later post.

The Code

You can download the entire project at my GitHub account by clicking here.  Included in the project is the SQL script you’ll need to generate the table, stored procedure and populate your table with the seed data.

 

Fluent NHibernate Execute Stored Procedure

Summary

I’ve demonstrated stored procedures a couple of times on this blog, but this time I’m going to show you how to execute a stored procedure that doesn’t need to return a value.  You can use the same syntax to execute an update, insert or delete query (in cases where you might want to make bulk changes).


The Setup

I’m going to use my facultydata database from previous blog posts.  Here’s the ERD:



My stored procedure will look like this:

USE [facultydata]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pSetClassForTeacher]
@TeacherId int,
@ClassId int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE class
    SET teacherid=@TeacherId
    WHERE id=@ClassId

END


This is just a simple example.  It’s not very useful, but easy to understand.


The Project

First, I’m not going to do any mappings or table classes.  I’m going to create a console application with a session factory and just call the stored procedure.  Here’s the complete code (minus the session factory):

using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery(“exec pSetClassForTeacher 

        @TeacherId=:TeacherId, @ClassId=:ClassId“)
        .SetParameter(“TeacherId“, 3)
        .SetParameter(“ClassId“, 84)
        .ExecuteUpdate();
}


Don’t forget that SQL Server uses a comma between variables.  

This stored procedure will update the class table and put a 3 in the teacher id field (make sure you’re using valid values.  My database has records for class with an id of 84 and and a teacher id of 3).  

If you have a long running stored procedure and you need to set the timeout longer, you can use the SetTimeout() method:

 using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery(“exec pSetClassForTeacher 

        @TeacherId=:TeacherId, @ClassId=:ClassId“)
        .SetParameter(“TeacherId“, 3)
        .SetParameter(“ClassId“, 84)

        .SetTimeout(100)
        .ExecuteUpdate();
}


Keep in mind that the set timeout method is measured in seconds.


Executing SQL Queries

You can use the same command to execute a query directly.  Maybe you want to delete all records of a given foreign key, or perform the same update that my stored procedure performed, without creating a stored procedure.  You can do it using this syntax:


 using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery(“UPDATE facultydata..class SET teacherid=:TeacherId WHERE id=:ClassId“)
        .SetParameter(“TeacherId“, 3)
        .SetParameter(“ClassId“, 84)

        .SetTimeout(100)
        .ExecuteUpdate();
}


You can download the sample project here:

 FluentNHibernateStoredProcedure.zip

 

Fluent NHibernate More Stored Procedures Without Mapping

I did a sample post on how Fluent NHibernate executes a stored procedure, but I didn’t dig into the subject and show some details.  The sample I showed returned a list of tables that were already mapped and it made the whole sample easy, but not realistic.  In most cases we want to execute a stored procedure to return some result that is not already mapped.  So I’m going to show how this is done.


The Stored Procedure:

Use this code to generate an MS SQL stored procedure in your sampledata database (assuming you have one):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[ReadPersonNames]

AS
BEGIN
SET NOCOUNT ON;

SELECT first, last FROM person

END


The Stored Procedure Return Class

You’ll need to create a class to contain the data type that will be returned.  Here’s what I created for this example:

public class PersonNameRecord
{
    public virtual string first { get; set; }
    public virtual string last { get; set; }

}


Call the Stored Procedure

Here’s my call to the stored procedure:

var personNameList = db.CreateSQLQuery(“exec ReadPersonNames“)
 .SetResultTransformer(Transformers.AliasToBean<PersonNameRecord>())
    .List<PersonNameRecord>()

    .ToList();


You’ll need to include a “using” statement at the top to make this work:

using NHibernate.Transform;


That’s it.  You can download the sample code here:

FluentNHibernateStoredProc2.zip

 

Fluent NHibernate Stored Procedure

Summary

This week I’m going to give a very simple example of how to execute a stored procedure.  The stored procedure will only do a select query, but I’ll pass a variable to demonstrate how it’s done and I’ll leave it up to the reader to alter the code to execute other types of stored procedures.  I’m going to build on my last post, so I’ll use all the same code except for the main method of the console application.  If you haven’t read my previous post on NHibernate go here: Fluent NHibernate using Multiple Databases and setup your database to match the example.


The Stored Procedure

Copy this SQL command into your query window of MS SQL server, and execute it.  It should generate a stored procedure in your database.  Make sure this stored procedure is in the facultydata database.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[pSelectClassPerTeacher]
@TeacherId int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM class WHERE teacherid = @TeacherId 
END

The Code

The only change I made to the Main method of program.cs is this:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = session.CreateSQLQuery(“exec pSelectClassPerTeacher :TeacherId“)
        .AddEntity(typeof(Class))
        .SetParameter(“TeacherId“, 2)
        .List<Class>();

    foreach (var item in query)
    {
        Console.WriteLine(“ClassName=” + item.Name.Trim() + ” TeacherId=” + item.TeacherId.ToString());
    }

    Console.ReadKey();

}

That’s it.  Now run it.  It should produce a list of class names with teacher number 2.  One of the reasons why my example was so simple is that I selected all records from the class table.  This produced the same output as the “Class” object that defines that table.  You can do any fancy query that produces other types of output, followed up by defining a special return type class that has all the expected fields.  Also, each parameter must be accounted for inside the string and it must have a matching “.SetParameter()” method call.

I know, this is a short post, but this turns out to be an easy subject.