我个人认为你在思考过多或试图简化不需要简化的东西。你最好将存储过程参数保持原样，或尝试创建一些基类和帮助函数，可以将一组参数附加到命令对象上。
不过，话虽如此，我还是会提供一个解决方案给你，看看是否符合你的需求：
我建议使用TSQL用户定义类型。创建一个或多个类型。也许一个用于日期范围，一个用于分页和排序。我使用类似的方法将多行数据传递给存储过程。（一些代码可能需要稍作修改，因为我只是修改了一些我已经编写过的代码，而且我已经很长时间没有与DataTable字段一起工作了。）
最终，所有这些只是缩短应用程序方法和匹配存储过程中参数列表的长度。存储过程将负责在表变量中提取或连接信息。下面列出的类确实提供了在.NET应用程序端强类型参数的能力。
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
SQL用户定义类型可以在.NET应用程序中表示为强类型对象。从一个基类开始：
```
SQL用户定义类型可以在.NET应用程序中表示为强类型对象。从一个基类开始：
```
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
为SQL类型创建一个实现：
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
并且：
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
在构造函数中实例化对象并设置值，然后仅从对象获取参数，并将其附加到存储过程命令对象的参数集合中。
cmd.Parameters.Add(New DateRange(startDate, endDate).CreateParameter("DateRangeParams"))
cmd.Parameters.Add(New Paging(pageNumber, pageSize).CreateParameter("PagingParams"))
编辑
由于这个答案涉及到强类型，我想添加一个在方法签名中使用强类型的示例：
'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)
