Eric Wise

I received a comment from my previous post from Joey asking the following:

"In light of the recent Frans vs. Peter debates on SProcs vs. PQs (parameterized queries), I'm curious which route you took with Easy Assets?  In your quote 'modify the sql to suit your needs' in #4 above, it sounds like you may have went the PQ route.  So which route did you take, if you don't mind sharing?"

The answer is: I use both.  Here's the pattern why:

Stored Procedures

My insert, update, and delete are all stored procedures.  This is partly because of the ease of generating them via codesmith and partly because I prefer to handle transactions inside stored procedures as opposed to in my objects.  So the rule of thumb for me is that if codesmith generates it easily or it needs to perform several updates (cascading delete) then I use a stored procedure.

There aren't any queries in the application that I would call "processor intensive" so the performance isn't really an issue for me.  It's just more convenient.

Parameterized Queries

That being said, I do have the need to build dynamic queries for user searches based on zero to many parameters.  The way my pattern works is for example say you want a list of employees at your organization.  Employees happen to have a First Name, LastName, departmentID, and a JobDescriptionID.  Searchers may want to filter on any one of these fields so in this case I build a parameterized query.

My BaseDAO object that every data access class inherits from exposes some wrapper functions for the Enterprise library (Execute Reader, Scalar, NonQuery, Dataset).  I have 3 wrappers for each function to handle:

1. Unparameterized query- straight sql execution.  I don't really use this but it will be available if you really want it.
2. Stored Procedure- Takes a procedure name and arraylist of sql parameters
3. Parameterized Query

Here's a snippet of the execute reader code in the BaseDAO, notice line 21 is the function you would call to run a parameterized query by passing in a type of "text":

    1     Public Function ExecuteNonQuery(ByVal SQL As String) As Integer
    2         Return DatabaseFactory.CreateDatabase(_InstanceName).ExecuteNonQuery(CommandType.Text, SQL)
    3     End Function
    4  
    5     Public Function ExecuteNonQuery(ByVal ProcedureName As String, ByVal Params As ArrayList) As DBCommandWrapper
    6         Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
    7         Dim cw As DBCommandWrapper = db.GetStoredProcCommandWrapper(ProcedureName)
    8  
    9         For Each p As SqlClient.SqlParameter In Params
   10             If p.Direction = ParameterDirection.Input Then
   11                 cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
   12             ElseIf p.Direction = ParameterDirection.Output Then
   13                 cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
   14             End If
   15         Next
   16  
   17         db.ExecuteNonQuery(cw)
   18         Return cw
   19     End Function
   20  
   21     Public Function ExecuteNonQuery(ByVal SQL As String, ByVal Params As ArrayList, ByVal type As CommandType) As DBCommandWrapper
   22         Dim db As Database = DatabaseFactory.CreateDatabase(_InstanceName)
   23         Dim cw As DBCommandWrapper
   24  
   25         Select Case type
   26             Case CommandType.StoredProcedure
   27                 cw = db.GetStoredProcCommandWrapper(SQL)
   28             Case CommandType.Text
   29                 cw = db.GetSqlStringCommandWrapper(SQL)
   30         End Select
   31  
   32         For Each p As SqlClient.SqlParameter In Params
   33             If p.Direction = ParameterDirection.Input Then
   34                 cw.AddInParameter(p.ParameterName, p.DbType, p.Value)
   35             ElseIf p.Direction = ParameterDirection.Output Then
   36                 cw.AddOutParameter(p.ParameterName, p.DbType, p.Size)
   37             End If
   38         Next
   39  
   40         db.ExecuteNonQuery(cw)
   41         Return cw
   42     End Function