Call SQL Server’s stored procedure with input parameters using PetaPoco
Table of contents
We've started using PetaPoco - micro ORM on our projects about one year ago. What we like about PetaPoco are the following facts:
- It's a single C# file which can be easily added to the project
- It works with strongly typed POCOs
- It's very fast because it uses dynamic method generation during the assignment of column values to properties
- It has an opportunity to work with strictly undecorated POCOs
- It contains helper methods for CRUD operations (Insert/Delete/Update/Save and IsNew)
- It has transaction support
- It includes T4 templates to automatically generate the POCO classes
- It includes SQL builder class, which largely eases the generation of the query. It also helps in avoiding SQL injection attacks
- There are hooks available for logging exceptions
- It supports: SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle
- It's open source
More information about PetaPoco can be found on the official web page http://www.toptensoftware.com/petapoco/.
We have realized that we have been using a lot of calls to the stored procedures. What we firstly recognized as a possible solution was to use the standard query for calling the stored procedure using PetaPoco's Sql object:
var sqlCommandPetaPoco = new Sql();sqlCommandPetaPoco.Append(string.Format(";EXEC {0} @{1}", WarehouseDbResourcesNames.StoredProcedures.Customer.GetByCode, WarehouseDbResourcesNames.StoredProceduresParameters.Customer.Code), new { code });return _db.SingleOrDefault<Customer>(sqlCommandPetaPoco);
The code variable has to be the same as the stored procedure's input variable name. Using this implementation, we need to take care about the naming of our parameters and the passing arguments.
Another option is to use a different syntax:
var sqlCommandPetaPoco = new Sql(); sqlCommandPetaPoco.Append(string.Format(";EXEC {0} @@{1} = @0", WarehouseDbResourcesNames.StoredProcedures.Customer.GetByCode, WarehouseDbResourcesNames.StoredProceduresParameters.Customer.Code), return _db.SingleOrDefault<Customer>(sqlCommandPetaPoco);
In the Sql command we are passing the real name of the stored procedure's parameter(s). The arguments are passing as an array of objects. So, we still need to be familiar with any additional information such as the exact names of the parameters.
Basically, PetaPoco supports several DB types. If we are using the syntax like the one we have used, we should always be careful regarding the right syntax based on the DB type which we are going to use.
I have come up with an idea that we can extend PetaPoco in order to support calling the stored procedures in a simple and more abstract manner.
The signatures of the additional methods are:
public T SingleOrDefaultWithStoredProcedure<T>(string spName, params object[] args);public List<T> FetchWithStoredProcedure<T>(string spName, params object[] args);int ExecuteWithStoredProcedure<T>(string spName, params object[] args);
It is clear that we have defined the methods which are as simple as possible. We are passing only the stored procedure name and the arguments. PetaPoco has to know how to covert this call to the right command based on the DB type.
Let's take a look at the implementation of the defined methods for the SQL Server DB type:
public List<T> FetchWithStoredProcedure<T>(string spName, params object[] args){ switch (_dbType) { case DBType.SqlServer: { var sqlCommandPetaPoco = new Sql(); sqlCommandPetaPoco.Append( string.Format(";EXEC {0} {1}", spName, GetSqlServerParametersQueryStringBySpNameAndArgs(spName, args)), args); return Fetch<T>(sqlCommandPetaPoco); } break; default: break; }}
public T SingleOrDefaultWithStoredProcedure<T>(string spName, params object[] args){ switch (_dbType) { case DBType.SqlServer: { var sqlCommandPetaPoco = new Sql(); sqlCommandPetaPoco.Append(string.Format(";EXEC {0} {1}", spName, GetSqlServerParametersQueryStringBySpNameAndArgs(spName, args)), args); return SingleOrDefault<T>(sqlCommandPetaPoco); } break; default: break; } }
public int ExecuteStoredProcedure(string spName, params object[] args){ switch (_dbType) { case DBType.SqlServer: { var sqlCommandPetaPoco = new Sql(); sqlCommandPetaPoco.Append(string.Format(";EXEC {0} {1}", spName, GetSqlServerParametersQueryStringBySpNameAndArgs(spName, args)), args); return Execute(sqlCommandPetaPoco); } break; default: break; }}
private string GetSqlServerParametersQueryStringBySpNameAndArgs(string spName, object[] args){ ObjectCache cache = MemoryCache.Default; var spQueryStringCacheKey = string.Concat("pp_sp_", spName); var retVal = cache[spQueryStringCacheKey] as string; if (retVal == null) { var queryBuilder = new StringBuilder(); var sqlCommandPetaPoco = new Sql(); sqlCommandPetaPoco.Append( string.Format(@"SELECT * FROM {0} WHERE specific_name='{1}' ORDER BY ORDINAL_POSITION", "information_schema.parameters", spName)); var items = Fetch<StoredProcedureParameter>(sqlCommandPetaPoco); // check if the number of the passed arguments matches the real number of // the stored procedure parameters. if (items.Select(i => i.ParameterMode.Equals("IN")).Count() != args.Length) throw new InvalidOperationException("The passed number of arguments doesn't meet the real number of the stored procedure's parameters"); var iterator = 0; foreach (var storedProcedureParameter in items) { if (storedProcedureParameter.ParameterMode.Equals("IN")) { if (iterator != 0) queryBuilder.Append(", "); queryBuilder.Append("@"); queryBuilder.Append(storedProcedureParameter.ParameterName); queryBuilder.Append(" = @"); queryBuilder.Append(iterator++); } } retVal = queryBuilder.ToString(); cache.Set(spQueryStringCacheKey, retVal, DateTime.Now.AddMinutes(10)); } return retVal;}
The following step can be the implementation of support for all of the DB types. This can be achieved easily by extending the switch with additional cases for each supported DB type.
The current implementation supports the calling of the stored procedures which only have input parameters (in our case it's more than enough). Further extension should support the calling of the stored procedures which have output parameters as well.
This is a useful link if you need to use a stored procedure with output parameters:
http://www.enkelmedia.se/blogg/2014/5/3/stored-procedures-with-output-parameters-using-petapoco.aspx
Please don't hesitate to contact me if you have any questions or suggestions regarding this post.
