Easy way to run SQL stored procedure in C# using dynamics

I don’t like standard way of calling stored procedures in .NET with SqlCommand. So when I was starting one of projects, I thought that maybe I could use some helper that would allow me to easier way of calling procedures. As I didn’t find anything interesting, I’ve created one by myself. And this is one of this moments, when you feel why dynamics in C# are so great thing.
Now if I want to save some data using stored procedure I can use code like that:

SqlHelper.SaveData("SP_SAVE_IMPORTANT_DATA", new
{
    firstParameter= "This is value",
    allData = 42
});

To get data from SQL using stored procedures I can use this:

IDictionary<string, object> dict = SqlHelper.GetRow("SP_GET_LATEST_REQUEST", new
{
    requstId = 3
});

Or this:

Int32? number = SqlHelper.GetScalar("SP_HOW_MANY_USERS", new {userGroup = "some"});

How to achieve such functionality? At first, every time when any above function is called, we have to create SqlCommand object:

private static SqlCommand GetCommand(string sprocName)
{
    SqlConnection connection;
    SqlCommand command;

    connection = SqlHelper.SetUpConnection();
    command = new SqlCommand(sprocName);
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = connection;

    return command;
}

and then we have to update its parameters (with the ones we passes in dynamic object):

private static void UpdateParameters(ref SqlCommand command, dynamic parameters)
{
    foreach (var prop in parameters.GetType().GetProperties())
    {
        var value = prop.GetValue(parameters, null);
        if (value == null)
        {
            value = DBNull.Value;
        }
        command.Parameters.AddWithValue("@" + ConvertNotation(prop.Name), value);
    }

}

As you might have noticed, we are converting name of parameters before we call procedure. That is because in C# we usually use camel case notation, and in SQL NAME_WITH_UNDERSCORE notation is more popular. So there is additional function to convert between this notations:

private static string ConvertNotation(string name)
{
    StringBuilder sb = new StringBuilder();

    foreach (char letter in name.ToCharArray())
    {
        if (letter.CompareTo('A') >= 0 && letter.CompareTo('Z') <= 0)
        {
            sb.Append('_');
        }
        sb.Append(letter);
    }
    return sb.ToString().ToUpper();
}

Full code of above SqlHelper could be found on GitHub.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *