Generell metod för att köra stored procedure

Blev lite trött på att det är mycket upprepning av kod när jag gör anrop till databas, det här är en typisk metod i en static-class (dbSelect, anropet blir alltså dbSelect.RoleId(/.../)) jag gjort vars metoder hämtar olika saker från databasen:

public static string RoleId(string roleName)
{
    SqlConnection conn = DbUtils.getConnection();
    SqlCommand sqlCom = new SqlCommand("getRoleId", conn);
    sqlCom.CommandType = CommandType.StoredProcedure;
    sqlCom.Parameters.AddWithValue("@roleName", roleName);
    
    conn.Open();
    object returnValue = sqlCom.ExecuteScalar();
    conn.Close();
    
    return returnValue.ToString();
}

Jag har flera metoder som ser i princip likadana ut, enda skillnaden är returtyp, namn på stored procedure, antal och vilka parametrar som krävs samt vilken typ av exekvering som ska göras på SqlCommand-objektet (fetstilt ovan). Så jag gjorde en generell metod enligt följande:

public enum ExecutionType
{
    Scalar,
    Reader,
    NonQuery
}

public static object RunStoredProcedure(string storedProcedure, ExecutionType executionType, params SqlParameter[] parameters)
{
    SqlConnection conn = DbUtils.getConnection();
    SqlCommand sqlCom = new SqlCommand(storedProcedure, conn);
    sqlCom.CommandType = CommandType.StoredProcedure;

    foreach (SqlParameter parameter in parameters)
    {
        sqlCom.Parameters.Add(parameter);
    }

    conn.Open();
    object returnValue = null;

    switch (executionType)
    {
        case ExecutionType.Scalar:
            returnValue = sqlCom.ExecuteScalar();
            conn.Close();
            break;
        case ExecutionType.NonQuery:
            sqlCom.ExecuteNonQuery();
            conn.Close();
            break;
        case ExecutionType.Reader:
            returnValue = sqlCom.ExecuteReader(CommandBehavior.CloseConnection);
            break;
    }

    return returnValue;
}

Till den här metoden kan jag alltså skicka in namnet på den stored procedure jag vill köra, vilken typ av exekvering via den egna enumeratorn ExecutionType samt valfritt antal (tack vare params-nyckelordet) SqlParameter-objekt. Metoden returnerar ett Object som jag får typa om till det som förväntas av den Stored Procedure jag anropar. Alla metoder i dbSelect-klassen har specifik returtypning så när metoderna väl anropas finns det inga generella Object-objekt att hantera.

Så metoderna i min dbSelect-klass kan nu slimmas ner till detta:

public static string RoleId(string roleName)
{
    object returnValue = DbUtils.RunStoredProcedure("getRoleId", ExecutionType.Scalar, new SqlParameter("@roleName", roleName));
    return returnValue.ToString();
}

Angående varianten då SqlCom.ExecuteReader(...) körs så skickar jag in parametern CommandBehavior.CloseConnection och conn-objektet stängs inte. Detta måste jag göra eftersom jag kommer vilja returnera hela SqlDataReader-objektet till den plats där metoden anropas och där hantera den data som finns i SqlDataReader-objektet. För att det ska gå måste conn-objektet vara öppet. Samtidigt måste jag ha möjlighet att stänga conn-objektet när jag är klar med SqlDataReadern och genom att skicka in CommandBehavior.CloseConnection när reader-objektet skapas så kommer conn-objektet automatiskt att stängas när jag anropar Close() på reader-objektet. Tänk dig att jag har en metod som returnerar en SqlDataReader med alla användare, den anropas så här och stängs när jag är färdig med den:

SqlDataReader reader = dbSelect.Users();
while(reader.Read())
{
    //Hantera varje post
}
reader.Close(); //Här stängs både reader-objektet och det conn-objekt
                //som skapades och öppnades i metoden RunStoredProcedure.

Angående CommandBehavior.CloseConnection, se detta .Net Tip of The Day.