Execute stored procedure for viewmodel class returntype

Question

i am trying to write some code in mvc to get the userdata by id. Here i am using Viewmodel class that contains all the parameters as Database table. and i am using enterprise library to execute the stored procedure. Here is the code in my Data Access Layer i tried..

public UserEntity GetUserByID(int userID)
    {
        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        Database db = factory.Create("MySqlConnection");       
        DbCommand dbc = db.GetStoredProcCommand("uspGetUserByID");
        dbc.CommandType = CommandType.StoredProcedure;
        db.AddInParameter(dbc, "userID", DbType.Int32, userID);
        return db.ExecuteDataSet(dbc).Tables[0];         
    }

here UserEntity is my viewmodel class. here Executedataset doesn't work because the returntype is not dataset but viewmodel class. i want the code to execute the stored procedure.. thank you..

Answer 1

If you want to return your custom type without manually constructing it (either from a DataSet or an IDataReader) then you can use accessors.

In your case it would look like this:

public UserEntity GetUserByID(int userID)
{
    DatabaseProviderFactory factory = new DatabaseProviderFactory();
    Database db = factory.Create("MySqlConnection");
    IEnumerable<UserEntity> results = db.ExecuteSprocAccessor<UserEntity>("uspGetUserByID", userID);

    return results.FirstOrDefault();
}

This assumes that the UserEntity can be mapped from the stored procedure result set (e.g. column names are the same name as the property names). If not, then you would need to create a custom mapper. See Retrieving Data as Objects from the Developer's Guide for more information about accessors. Also if there is an issue passing in the userID parameter then you may have to create a parameter mapper as in Defining Parameter Mappers.