ORMLite Stored Procedures

[quaffapint]

I'm attempting to use a SQL Server Stored Procedure with ORMLite.

I found this site...

http://yobriefca.se/blog/2011/06/18/microorms-for-dotnet-stored-procedures/

...which provided a format like...

Author author = dbFactory.Exec(dbCmd => {

    dbCmd.CommandType = CommandType.StoredProcedure;

    dbCmd.Parameters.Add(new SqlParameter("@id", 1));

    dbCmd.CommandText = "GetAuthorById";

    return dbCmd.ExecuteReader().ConvertTo<Author>();

});

...But, using that same format, I can't get it to execute the stored procedure.  I keep getting -1 affected records.  I double-checked that the stored procedure is correct with the passed input.

Any ideas?

Thanks.

[mythz]

When you're accessing the raw IDbCommand directly, OrmLite doesn't actually doing anything (i.e. being called). i.e. the only time it really gets used is in the .ConvertTo<Author>() extension method.

You could try it without the ConvertTo call, and see if anything comes back (I suspect it wont).

Other than that I would make sure the dbFactory is initialized correctly i.e. pointing to correct SqlServer db. Do you have that anywhere?

It does look right, so can't think of anything else that could be wrong.

Cheers,

[quaffapint]

Thanks for the reply.

The connection is there in the init, and it does seem to be correct, because if I change the server host to something made up, I get an error back that it can't connect.

I tried it without the ConvertTo call and nothing comes back (-1 records affected), as you suspected.

So, do you have any suggestions of how to best integrate a stored procedure?  I want maximum performance, thus I try to do everything through stored procedures.  Mostly, I guess I'm looking for the best way to integrate my models to the same structure returned from the stored procedure (which is why I wanted to use ConvertTo).  For the other stored procedures (inserts, etc), I can just use the 'old fashion' way.

Thanks for any advice.

[Demis Bellot]

For the OrmLiteConnection make sure it includes the 'SqlServerOrmLiteDialectProvider.Instance' so it knows that its talking to an Sql Server database.

var dbFactory = new OrmLiteConnectionFactory(connStr, SqlServerOrmLiteDialectProvider.Instance);

Well a Micro ORM is definitely the way around the overheads of the heavy ORMs (http://servicestack.net/benchmarks/)

You could also try Dapper, it's very fast and they have a an API for executing Stored Procs: http://code.google.com/p/dapper-dot-net/

Author author = conn.Query<Author>(
    "GetAuthorById",
    new { Id = 1 },
    commandType: CommandType.StoredProcedure
).First();

But I'd be surprised if it wasn't just using the same APIs underneath.

Both Dapper and OrmLite work very similar in that they're effectively just Extension methods on ADO.NET interfaces, the difference being Dapper as Ext methods of IDbConnection whilst OrmLite has Extension methods off IDbCommand.

Cheers,

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[quaffapint]

I did make sure it was using 'SqlServerOrmLiteDialectProvider.Instance'.  Not sure why it won't work.

Thanks for pointing out Dapper, I'll give that a try in mapping my sproc's to my models.  I started using Entity Framework, and while that worked, it seemed very heavy and not supper peppy. Hopefully one of these micro ORMs will be a good fit.

Thanks, again.

[Oz]

How can we add our CustomTypes to StoredProcedure as parameter?

 string mot1 = user.Friends.ToJsv(); // Adding _type to string
 string mot = OrmLiteConfig.DialectProvider.GetQuotedValue(user.Friends, typeof(ICollection<Friend>)); // Adding Quote to string

Is there any consistence approach that will not be affected next versions of ServiceStack.OrmLite?
 

[Zachary Yates]

Looks like service stack was updated to make this easier:

http://stackoverflow.com/a/15124948/8360