Using PetaPoco with SQL Server Stored Procedures

For quite a while now I've been using PetaPoco for almost all of my data access needs in .Net, and it's been a great experience. For those who may not be familiar with PetaPoco, it is a light weight micro object relational mapper which helps you work with a database and map the results to your own POCO objects.

The big advantage of PetaPoco and other micro ORM's is that they are very simple compared to other "heavier" products like NHibernate or EntityFramework. Of course there is nothing wrong with NHibernate or EntityFramework, however, there is a much bigger learning curve with these technologies and many things you can do with them you can also accomplish much easier with a micro ORM. Some other micro ORM's that I have had success with are Simple.Data and Service Stack ORM Lite.

PetaPoco works great and the examples are excellent when you are working with SQL that you generate in your C# classes. However when you start to work with stored procedures it can get a little tricky figuring out the exact syntax that PetaPoco requires. Therefore I put together a few GISTS to demonstrate how to call SQL Server stored procedures from PetaPoco:


Using PetaPoco to call a SQL Server stored procedure and return 1 result to your poco object:

var result = _db.Query<YourPocoType>(string.Format(";exec dbo.[procName] '{0}', '{1}'", var1, var2)).FirstOrDefault();

view raw gistfile1.cs hosted with ❤ by GitHub

Using PetaPoco to call a SQL Server stored procedure and return a list of strings:

var result = _db.Query<string>(string.Format(";exec dbo.[procName] '{0}'", param1));

view raw gistfile1.cs hosted with ❤ by GitHub

Using PetaPoco and calling a SQL Server stored procedure that returns a value with return statements in TSQL:

	var sql = new Sql().Append("declare @@returnParamter int;")
	.Append(string.Format("exec @@returnParameter = dbo.[procName] '{0}', '{1}';",var1, var2))
	.Append("select @@returnParamter");
	var returnValue = _db.ExecuteScalar<int>(sql);

view raw gistfile1.cs hosted with ❤ by GitHub