My Sanctuary

Since I worked in my company, I was required to learn a lot of stuffs. One of the basic things to learn was to adapt from my mostly used Java programming language to C#, along with the proper MVC platform.

Of course, .NET MVC, being a platform (for vocabulary nazis, I use the term architecture and platform interchangeably), it is obviously tightly integrated. This was the thing that I dislike the most with the .NET framework, you can’t have full control (to some extent) as to what you do. And then my opinion suddenly changed when I was introduced to entity framework, namely, their code-first approach. Code-first approach gives you that flexibility to meld the code behind as you like, it gives you full control (again, to some extent) of what you can do.

My post today will show you how to simply execute a stored procedure and retrieve the results back. This is supposed to be simple if you were using database-first or model-first approach. Otherwise, Entity Framework 4.1 does not support a complex type mapping for stored procedure in code-first approach (at least for now). See this post for more information on that fact.

/// <summary>
/// Function to call the stored procedure in the database.
/// </summary>
/// <param name="param1">A string containing the username.</param>
/// <param name="param2">A string containing the password.</param>
public Guid CallProc(string param1, string param2)
{
    // Creating SqlParameters
    // Note that although SqlParameterCollection class exists,
    // it cannot be instantiated since it is an abstract class
    var userName = new SqlParameter
    {
        DbType = DbType.String,
        ParameterName = "UserName",
        Value = param1
    };

    var password = new SqlParameter
    {
        DbType = DbType.String,
        ParameterName = "Password",
        Value = param2
    };

    // This is the "correct" query that will return an entity object.
    // However since EF4.1 does not support a complex type mapping FOR
    // stored procedures, this is rendered unusable (for now).
    // return DbContext.Database.SqlQuery("FindUser @UserName, @Password",
    // parameters).ToList();

    // Returning the user ID (as Guid)
    Guid userId = DbContext.Database.SqlQuery("EXEC FindUser @UserName, @Password",
                  param1, param2).SingleOrDefault();

    return userId;
}

I have highlighted some of the important points in the code. Now, let’s start dissecting the code.

On line 6, this is your usual standard function in C#. Nothing special here, just a function taking two parameters and returning a Guid result. Please note that the function name does not have to be the same as the stored procedure name.

On line 13-15, we need to create SqlParameter, in order for the framework to translate the call to the correct data types. This also prevents SQL injection, and is actually a good practice. Like I have stated in the code, we can actually create SqlParameterCollection if we want to. For simplicity sake (and greater flexibility), we’ll just use the SqlParameter class.

The SqlParameter can take any supported data type. In the example, we used string, but we can also use Guid and other primitive data types. I have yet to use objects, however, I would have to assume that it is not supported.

On line 28-29, I have put the proper way to call and execute the stored procedure from within Entity Framework. However, it is not yet supported. In this commented code, I actually used SqlParameterCollection variable, parameters.

On line 32-33, is the actual call made by the framework to the database. This will call a stored procedure called FindUser, passes on two parameters (UserName and Password, respectively), and will return a Guid as the ID of the user.

Of course, it goes without saying that the database must have a stored procedure called FindUser which accepts two parameters, UserName, and Password as string or varchar. The stored procedure must also return Guid as a result. I have yet to use another data type, but in this case (using Guid as a return type) works.

So there you go. I admit this solution might not be the best solution, however it works for now. At least it solves the limitation of the EF 4.1 using the code-first approach. See you next post!