Cliffs: Is there a known pattern for passing a standard "filter" type to a stored procedure to encapsulate stardate/enddate/pagesize/pagenum parameters?
Not sure the correct place for this question. I'm exploring the idea of passing a filtering object parameter to a stored procedure which encapsulates our common filtering parameters (startdate, enddate, pagenumber, pagesize, list of int's, etc). The reason for this is to reduce the amount of similar parameters and boilerplate SQL spread around our procedures. This would give us a more standard interface and starting point for each procedure right from the start. I haven't been able to find much info on the topic.
Pattern I've noticed - when first building most SP's they start with a single id parameter used in the where clause. At some point later, you may need to add parameters for date range parameters (startdate, enddate or dynamic ranges "ytd, mtd, dtd"). If the data set is large enough you also may need to introduce pagesize/pagenum for server side paging. After some time you may realize that you need results for a list of id's rather than a single id, so you add a CSV or XML parameter to envelope the IDs.
Ultimately many stored procedures end up with a lot of similar boilerplate and (hopefully) identical parameters for handling these standard filtering parameters. I'm trying to research known patterns for passing an encapsulated filter object parameter to my procedures, that ideally would be strongly typed on the C# side. This would be particularly useful when managing a group of procedures that power reports which all require the same filtering options (in addition to the report-specific query parameters).
My goal is to reduce the number of parameters required to the bare minimum needed for the WHERE clause, and create a standard mechanism for passing the generic filtering options into a procedure and using those values while inside a procedure. How could this be achieved through XML or CLR or UDT parameters?
For context of this question, I'm using SQL Server 2008 via ADO.Net from C# 2.0. Unfortunately LINQ/EF is not an option for this project at this point, and we must stick with our existing RDBMS. If there is a known pattern that requires changing technologies I would be interested in hearing about it.
Edit: Appreciate the replies so far. I've added a bounty for 50pts that I'll let run for a few more days to try to promote some more discussion. If my question isn't clear enough just leave a comment..
I personally think that you're overthinking or trying to reduce something that doesn't need to be reduced. You're probably better off leaving stored procedure parameters alone, or trying to create some base classes and helper functions that can append sets of parameters to a command object.
However, that being said, I'll throw a solution to your question out there and see if it fits your needs:
I suggest using TSQL user defined types. Create one or more types. Maybe one for date ranges, and one for paging and sorting. I use a similar process for passing multi-row data to stored procedures. (Some of this code might need to be tweaked a bit, as I'm just modifying some code I've already written and I haven't worked with DataTable fields in quite some time.)
Ultimately, all this does is shorten the list of parameters in the application method and matching stored procedure. The stored procedure would be responsible for extracting or joining the information in the table variable. The classes listed below do provide the ability to keep these parameters strongly typed on the .NET application side.
if not exists (select * from INFORMATION_SCHEMA.DOMAINS where DOMAIN_SCHEMA = 'dbo' and DOMAIN_NAME = 'DateRange' and DATA_TYPE = 'table type')
begin
create type dbo.DateRange as table
(
StartDate datetime2 null
,EndDate datetime2 null
)
end
go
if not exists (select * from INFORMATION_SCHEMA.DOMAINS where DOMAIN_SCHEMA = 'dbo' and DOMAIN_NAME = 'Paging' and DATA_TYPE = 'table type')
begin
create type dbo.Paging as table
(
PageNumber int null
,PageSize int null
,SortField sysname null
,SortDirection varchar(4) null
)
end
go
The SQL user defined types can be represented as strongly typed objects in a .NET application. Start with a base class:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Runtime.Serialization
Namespace SqlTypes
<Serializable()> _
<System.ComponentModel.DesignerCategory("Code")> _
Public MustInherit Class SqlTableTypeBase
Inherits DataTable
Public Sub New()
MyBase.New()
Initialize()
End Sub
Public Sub New(ByVal tableName As String)
MyBase.New(tableName)
Initialize()
End Sub
Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)
MyBase.New(tableName, tableNamespace)
Initialize()
End Sub
Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)
MyBase.New(info, context)
End Sub
''' <summary>
''' Implement this method to create the columns in the data table to match the SQL server user defined table type
''' </summary>
''' <remarks></remarks>
Protected MustOverride Sub Initialize()
Public Function CreateParameter(parameterName As String) As SqlParameter
Dim p As New SqlParameter(parameterName, SqlDbType.Structured)
p.Value = Me
Return p
End Function
End Class
End Namespace
Create an implementation for the SQL types:
Imports System
Imports System.Data
Imports System.Runtime.Serialization
Namespace SqlTypes
<Serializable()> _
<System.ComponentModel.DesignerCategory("Code")> _
Public Class DateRange
Inherits SqlTableTypeBase
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal tableName As String)
MyBase.New(tableName)
End Sub
Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)
MyBase.New(tableName, tableNamespace)
End Sub
Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)
MyBase.New(info, context)
End Sub
'TODO: throw some more overloaded constructors in here...
Public Sub New(startDate As DateTime?, endDate As DateTime?)
MyBase.New()
Me.StartDate = startDate
Me.EndDate = endDate
End Sub
Public Property StartDate As DateTime?
Get
Return CType(Me.Rows(0)(0), DateTime?)
End Get
Set(value As DateTime?)
Me.Rows(0)(0) = value
End Set
End Property
Public Property EndDate As DateTime?
Get
Return CType(Me.Rows(0)(1), DateTime?)
End Get
Set(value As DateTime?)
Me.Rows(0)(1) = value
End Set
End Property
Protected Overrides Sub Initialize()
Me.Columns.Add(New DataColumn("StartDate", GetType(DateTime?)))
Me.Columns.Add(New DataColumn("EndDate", GetType(DateTime?)))
Me.Rows.Add({Nothing, Nothing})
End Sub
End Class
End Namespace
And:
Imports System
Imports System.Data
Imports System.Runtime.Serialization
Namespace SqlTypes
<Serializable()> _
<System.ComponentModel.DesignerCategory("Code")> _
Public Class Paging
Inherits SqlTableTypeBase
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal tableName As String)
MyBase.New(tableName)
End Sub
Public Sub New(ByVal tableName As String, ByVal tableNamespace As String)
MyBase.New(tableName, tableNamespace)
End Sub
Protected Sub New(ByVal info As SerializationInfo, ByVal context As StreamingContext)
MyBase.New(info, context)
End Sub
'TODO: throw some more overloaded constructors in here...
Public Sub New(pageNumber As Integer?, pageSize As Integer?)
MyBase.New()
Me.PageNumber = pageNumber
Me.PageSize = pageSize
End Sub
Public Sub New(sortField As String, sortDirection As String)
MyBase.New()
Me.SortField = sortField
Me.SortDirection = sortDirection
End Sub
Public Sub New(pageNumber As Integer?, pageSize As Integer?, sortField As String, sortDirection As String)
Me.New(pageNumber, pageSize)
Me.SortField = sortField
Me.SortDirection = sortDirection
End Sub
Public Property PageNumber As Integer?
Get
Return CType(Me.Rows(0)(0), Integer?)
End Get
Set(value As Integer?)
Me.Rows(0)(0) = value
End Set
End Property
Public Property PageSize As Integer?
Get
Return CType(Me.Rows(0)(1), Integer?)
End Get
Set(value As Integer?)
Me.Rows(0)(1) = value
End Set
End Property
Public Property SortField As String
Get
Return CType(Me.Rows(0)(2), String)
End Get
Set(value As String)
Me.Rows(0)(2) = value
End Set
End Property
Public Property SortDirection As String
Get
Return CType(Me.Rows(0)(3), String)
End Get
Set(value As String)
Me.Rows(0)(3) = value
End Set
End Property
Protected Overrides Sub Initialize()
Me.Columns.Add(New DataColumn("PageNumber", GetType(Integer?)))
Me.Columns.Add(New DataColumn("PageSize", GetType(Integer?)))
Me.Columns.Add(New DataColumn("SortField", GetType(String)))
Me.Columns.Add(New DataColumn("SortDirection", GetType(String)))
Me.Rows.Add({Nothing, Nothing, Nothing, Nothing})
End Sub
End Class
End Namespace
Instantiate the objects and set the values in the constructor, then simply get the parameter from the object, and append it to the stored procedure command object's parameter collection.
cmd.Parameters.Add(New DateRange(startDate, endDate).CreateParameter("DateRangeParams"))
cmd.Parameters.Add(New Paging(pageNumber, pageSize).CreateParameter("PagingParams"))
EDIT
Since this answer revolves around the strong typing, I thought I should add an example of strong typing in the method signature:
'method signature with UDTs
Public Function GetMyReport(customParam1 as Integer, timeFrame as DateRange, pages as Paging) as IDataReader
'method signature without UDTs
Public Function GetMyReport(customParam1 as Integer, startDate as DateTime, endDate as DateTime, pageNumber as Integer, pageSize as Integer)
