Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>However, that being said, I'll throw a solution to your question out there and see if it fits your needs:</p> <p>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.)</p> <p>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.</p> <pre><code>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 </code></pre> <p>The SQL user defined types can be represented as strongly typed objects in a .NET application. Start with a base class:</p> <pre><code> Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Runtime.Serialization Namespace SqlTypes &lt;Serializable()&gt; _ &lt;System.ComponentModel.DesignerCategory("Code")&gt; _ 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 ''' &lt;summary&gt; ''' Implement this method to create the columns in the data table to match the SQL server user defined table type ''' &lt;/summary&gt; ''' &lt;remarks&gt;&lt;/remarks&gt; 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 </code></pre> <p>Create an implementation for the SQL types:</p> <pre><code>Imports System Imports System.Data Imports System.Runtime.Serialization Namespace SqlTypes &lt;Serializable()&gt; _ &lt;System.ComponentModel.DesignerCategory("Code")&gt; _ 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 </code></pre> <p>And:</p> <pre><code>Imports System Imports System.Data Imports System.Runtime.Serialization Namespace SqlTypes &lt;Serializable()&gt; _ &lt;System.ComponentModel.DesignerCategory("Code")&gt; _ 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 </code></pre> <p>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.</p> <pre><code>cmd.Parameters.Add(New DateRange(startDate, endDate).CreateParameter("DateRangeParams")) cmd.Parameters.Add(New Paging(pageNumber, pageSize).CreateParameter("PagingParams")) </code></pre> <p><strong>EDIT</strong> Since this answer revolves around the strong typing, I thought I should add an example of strong typing in the method signature:</p> <pre><code>'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) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POT-SQL procedure - filter parameter as Object/CLR/Xml/UDT
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USulty4life
    1. USulty4life
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POT-SQL procedure - filter parameter as Object/CLR/Xml/UDT
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThank you for taking the time to comment and give code examples. While a lot of the answers revolve around UDT, yours stands out because of the SQL and C# examples.
      singulars
      1. PO
      1. USmellodev
    2. COI agree that , the interface should be what it is if we do need those parameters.
      singulars
      1. PO
      1. USzinking
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload