running stored procedures into own model with servicestack ormlite

Question

Is there any examples to be found for running a stored procedure on serviceStack MVC using ormlite? mythz ? seen this block of code:

var results = new List<EnergyCompare>
                    {dbFactory.Exec(dbCmd =>
                       {
                          dbCmd.CommandType = CommandType.StoredProcedure;
                          dbCmd.Parameters.Add(new SqlParameter("@id", 1));
                          dbCmd.CommandText = "GetAuthorById";
                          return dbCmd.ExecuteReader().ConvertTo<EnergyCompare>();
                       }
                    )};

but came with the text of never worked on the google groups!

i can also write this:

using(var db = new SwitchWizardDb())
            {
             var results2 = db.dbCmd.ExecuteProcedure()   
            }

but not sure how to complete this with parameters, and in the source code I looked at, it said obsolete?

thanks

Answer 1

Looks like ServiceStack.ORMLite has been updated to make this easier:

List<Poco> results = db.SqlList<Poco>("EXEC GetAnalyticsForWeek 1");
List<Poco> results = db.SqlList<Poco>("EXEC GetAnalyticsForWeek @weekNo", new { weekNo = 1 });

List<int> results = db.SqlList<int>("EXEC GetTotalsForWeek 1");
List<int> results = db.SqlList<int>("EXEC GetTotalsForWeek @weekNo", new { weekNo = 1 });

This example is on the front page of the github repo.

Answer 2

Well I figured it was best to roll my own handler so have created this, any thoughts would be most welcome, especially with how I could pass over params in some kind of func or something:

I have a main class to deal with easy access to my connection object:

public class DatabaseNameSp : IDisposable
{
    private readonly SqlConnection _spConn = new SqlConnection(DatabaseNameSp .dbConString);
    public readonly SqlCommand SpCmd;

    public DatabaseNameSp (string procedureName)
    {
       _spConn.Open();

        SpCmd = new SqlCommand
                    {
                        Connection = _spConn,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = procedureName
                    };
    }

    public void Dispose()
    {
         _spConn.Close();
         SpCmd.Dispose();
    }
}

usage:

using (var db = new DatabaseNameSp ("procedurenname"))
            {
                db.SpCmd.Parameters.Add(new SqlParameter("@Id", 1));

                var rdr = db.SpCmd.ExecuteReader(CommandBehavior.CloseConnection);

                var results = new List<CustomDTO>();
                while (rdr.Read())
                {
                    results.Add(new CustomDTO { Name = rdr["name"].ToString(), Id = rdr["id"].ToString() });
                }
                return new CustomDTOResponse { Results = results };
            }

Any thoughts !

thanks