How to call StoredProcedure in LightSwitch for custom search screen validation

I recently came across requirement where user asked to perform complex validation before query is fired to return search screen data. So here is my code snippet you can call inside yourqueryname_ProcessQuery

Create stored procedure for validation

First thing we will do for validation is create custom stored proc which will be called before data retrieval query is fired.

create proc uspValidateOrderSearchScreen
	 @pEmployeeID int
	,@pCustomerList varchar(1000)
as
	if @pEmployeeID>5
		raiserror('Invalid Employee ID',17,1)

	if @pCustomerList like '%X%'
		raiserror('Invalid Customer ID - Starts with X',17,1)
	
	return 0
go

Add reference to System.Configuration dll

Once you create validation stored procedure our next step is to add reference to dll which will be used in custom VB/C# code.
By default solution explorer is showing logical view of your project but to add reference to extra dll you need to switch to “File View” as below.

Now right click on Server node and click “Add Reference”

On Reference dialogbox check System.Configuration (you can use search box to locate it easily) and click OK

Now once reference is added you can switch back to logical view

Add validation for search screen (using stored procedure call)

Now we will click on our Custom Query which needs validation and switch to code view.

Click here to learn more about changing default query with custom code

In the code view type below code

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data

Namespace LightSwitchApplication
    Public Class NorthwindDataService
        Private Sub OrdersSearchQuery1_PreprocessQuery(pCustomerList As String, pStartDate As System.Nullable(Of Date), pEndDate As System.Nullable(Of Date), pEmployeeID As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Order) _
                                                      )
            ValidateParameters(pCustomerList, pStartDate, pEndDate, pEmployeeID)

            '//
            '// Your Code to Change Default Query Goes Here
            '//

        End Sub
        Private Sub ValidateParameters(pCustomerList As String _
                               , pStartDate As System.Nullable(Of Date) _
                               , pEndDate As System.Nullable(Of Date) _
                               , pEmployeeID As System.Nullable(Of Integer))

            Using connection = New SqlConnection

                '//change NorthwindData to whatever is your project name
                Dim connectionStringName = DataWorkspace.NorthwindData.Details.Name

                '//ConfigurationManager class need reference to System.Configuration [In solution explorer switch to file view and under Server add reference to assemply System.Configuration]
                connection.ConnectionString = ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString

                Dim procedure = "uspValidateOrderSearchScreen"
                Using command = New SqlCommand(procedure, connection)
                    command.CommandType = CommandType.StoredProcedure

                    command.Parameters.Add(New SqlParameter("@pEmployeeID", pEmployeeID))
                    command.Parameters.Add(New SqlParameter("@pCustomerList", pCustomerList))

                    connection.Open()
                    command.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
End Namespace

Test validation

Now execute application and try to enter some invalid search criteria and you will error coming from stored proc.