{"id":420,"date":"2013-08-30T11:58:56","date_gmt":"2013-08-30T15:58:56","guid":{"rendered":"http:\/\/binaryworld.net\/blogs\/?p=420"},"modified":"2013-08-30T12:27:43","modified_gmt":"2013-08-30T16:27:43","slug":"how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/","title":{"rendered":"How to call StoredProcedure in LightSwitch for custom search screen validation"},"content":{"rendered":"<p>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 <\/p>\n<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">Contents<\/p><ul class=\"toc_list\"><li><a href=\"#Create_stored_procedure_for_validation\"><span class=\"toc_number toc_depth_1\">1<\/span> Create stored procedure for validation<\/a><\/li><li><a href=\"#Add_reference_to_SystemConfiguration_dll\"><span class=\"toc_number toc_depth_1\">2<\/span> Add reference to System.Configuration dll<\/a><\/li><li><a href=\"#Add_validation_for_search_screen_using_stored_procedure_call\"><span class=\"toc_number toc_depth_1\">3<\/span> Add validation for search screen (using stored procedure call)<\/a><\/li><li><a href=\"#Test_validation\"><span class=\"toc_number toc_depth_1\">4<\/span> Test validation<\/a><\/li><\/ul><\/div>\n<h2><span id=\"Create_stored_procedure_for_validation\">Create stored procedure for validation<\/span><\/h2>\n<p>First thing we will do for validation is create custom stored proc which will be called before data retrieval query is fired.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: []; html-script: false\">\r\ncreate proc uspValidateOrderSearchScreen\r\n\t @pEmployeeID int\r\n\t,@pCustomerList varchar(1000)\r\nas\r\n\tif @pEmployeeID&gt;5\r\n\t\traiserror(&#039;Invalid Employee ID&#039;,17,1)\r\n\r\n\tif @pCustomerList like &#039;%X%&#039;\r\n\t\traiserror(&#039;Invalid Customer ID - Starts with X&#039;,17,1)\r\n\t\r\n\treturn 0\r\ngo\r\n<\/pre>\n<h2><span id=\"Add_reference_to_SystemConfiguration_dll\">Add reference to System.Configuration dll<\/span><\/h2>\n<p>Once you create validation stored procedure our next step is to add reference to dll which will be used in custom VB\/C# code.<br \/>\nBy default solution explorer is showing logical view of your project but to add reference to extra dll you need to switch to &#8220;File View&#8221; as below. <\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\"><img decoding=\"async\" src=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\" alt=\"\" title=\"lightswitch-add-reference0\" class=\"alignnone size-full wp-image-422\" \/><\/a><\/p>\n<p>Now right click on Server node and click &#8220;Add Reference&#8221;<\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference1.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"446\" height=\"447\" src=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference1.jpg\" alt=\"\" title=\"lightswitch-add-reference1\" class=\"alignnone size-full wp-image-422\" srcset=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference1.jpg 446w, https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference1-150x150.jpg 150w, https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference1-300x300.jpg 300w\" sizes=\"(max-width: 446px) 100vw, 446px\" \/><\/a><\/p>\n<p>On Reference dialogbox check System.Configuration (you can use search box to locate it easily) and click OK<\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference2.jpg\"><img decoding=\"async\" src=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference2.jpg\" alt=\"\" title=\"lightswitch-add-reference2\" class=\"alignnone size-full wp-image-422\" \/><\/a><\/p>\n<p>Now once reference is added you can switch back to logical view<\/p>\n<h2><span id=\"Add_validation_for_search_screen_using_stored_procedure_call\">Add validation for search screen (using stored procedure call)<\/span><\/h2>\n<p>Now we will click on our Custom Query which needs validation and switch to code view. <\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-create-eventhandler.jpg\"><img decoding=\"async\" src=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-create-eventhandler.jpg\" alt=\"\" title=\"lightswitch-processquery\" class=\"alignnone size-full wp-image-422\" \/><\/a><\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/2013\/08\/how-to-allow-comma-seperated-values-for-search-parameter-in-lightswitch\/\" target=\"_blank\">Click here to learn more about changing default query with custom code<\/a> <\/p>\n<p>In the code view type below code <\/p>\n<pre class=\"brush: vbnet; gutter: true; first-line: 1; highlight: []; html-script: false\">\r\nImports System.Data.SqlClient\r\nImports System.Configuration\r\nImports System.Data\r\n\r\nNamespace LightSwitchApplication\r\n    Public Class NorthwindDataService\r\n        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) _\r\n                                                      )\r\n            ValidateParameters(pCustomerList, pStartDate, pEndDate, pEmployeeID)\r\n\r\n            &#039;\/\/\r\n            &#039;\/\/ Your Code to Change Default Query Goes Here\r\n            &#039;\/\/\r\n\r\n        End Sub\r\n        Private Sub ValidateParameters(pCustomerList As String _\r\n                               , pStartDate As System.Nullable(Of Date) _\r\n                               , pEndDate As System.Nullable(Of Date) _\r\n                               , pEmployeeID As System.Nullable(Of Integer))\r\n\r\n            Using connection = New SqlConnection\r\n\r\n                &#039;\/\/change NorthwindData to whatever is your project name\r\n                Dim connectionStringName = DataWorkspace.NorthwindData.Details.Name\r\n\r\n                &#039;\/\/ConfigurationManager class need reference to System.Configuration [In solution explorer switch to file view and under Server add reference to assemply System.Configuration]\r\n                connection.ConnectionString = ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString\r\n\r\n                Dim procedure = &quot;uspValidateOrderSearchScreen&quot;\r\n                Using command = New SqlCommand(procedure, connection)\r\n                    command.CommandType = CommandType.StoredProcedure\r\n\r\n                    command.Parameters.Add(New SqlParameter(&quot;@pEmployeeID&quot;, pEmployeeID))\r\n                    command.Parameters.Add(New SqlParameter(&quot;@pCustomerList&quot;, pCustomerList))\r\n\r\n                    connection.Open()\r\n                    command.ExecuteNonQuery()\r\n                End Using\r\n            End Using\r\n        End Sub\r\n    End Class\r\nEnd Namespace\r\n<\/pre>\n<h2><span id=\"Test_validation\">Test validation<\/span><\/h2>\n<p>Now execute application and try to enter some invalid search criteria and you will error coming from stored proc.<\/p>\n<p><a href=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-parameter-validation.jpg\"><img decoding=\"async\" src=\"http:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-parameter-validation.jpg\" alt=\"\" title=\"lightswitch-parameter-validation\" class=\"alignnone size-full wp-image-422\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 Contents1 Create stored procedure for validation2 Add reference &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\"> <span class=\"screen-reader-text\">How to call StoredProcedure in LightSwitch for custom search screen validation<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":423,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49,76,65],"tags":[162,50],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"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 Contents1 Create stored procedure for validation2 Add reference &hellip; How to call StoredProcedure in LightSwitch for custom search screen validation Read More &raquo;\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2013-08-30T15:58:56+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2013-08-30T16:27:43+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"358\" \/>\r\n\t<meta property=\"og:image:height\" content=\"173\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\",\"name\":\"How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\",\"datePublished\":\"2013-08-30T15:58:56+00:00\",\"dateModified\":\"2013-08-30T16:27:43+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg\",\"width\":\"358\",\"height\":\"173\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to call StoredProcedure in LightSwitch for custom search screen validation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/","og_locale":"en_US","og_type":"article","og_title":"How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog","og_description":"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 Contents1 Create stored procedure for validation2 Add reference &hellip; How to call StoredProcedure in LightSwitch for custom search screen validation Read More &raquo;","og_url":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/","og_site_name":"BinaryWorld Blog","article_published_time":"2013-08-30T15:58:56+00:00","article_modified_time":"2013-08-30T16:27:43+00:00","og_image":[{"width":"358","height":"173","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg","type":"image\/jpeg"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/","url":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/","name":"How to call StoredProcedure in LightSwitch for custom search screen validation - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg","datePublished":"2013-08-30T15:58:56+00:00","dateModified":"2013-08-30T16:27:43+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2013\/08\/lightswitch-add-reference0.jpg","width":"358","height":"173"},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/how-to-call-storedprocedure-in-lightswitch-for-custom-search-screen-validation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"How to call StoredProcedure in LightSwitch for custom search screen validation"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/420"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=420"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/420\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/423"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}