Share via

Calling Stored Procs in your LightSwitch Databases using Web API

  • Article

Last post I showed you a new LightSwitch feature in Visual Studio 2013 Preview that let’s you use SSDT database projects to manage your intrinsic (internal) LightSwitch database. You can use database projects to do all sorts of things to the intrinsic database that aren't supported via the data designer, like add indexes, write stored procs, or manage data. I showed an example of how to add stored procs to your database, if you missed it:

Adding Stored Procs to your LightSwitch Intrinsic Database

Also check out Intrinsic Database Management with Database Projects and Managing Data in a LightSwitch Application for more info.

At the end of that article I promised to write a follow-up to show how to call the stored proc by using ASP.NET Web API – a framework for building HTTP web APIs on top of the .NET Framework. (For more info on Web API see these Getting Started guides.)

Although I’ll be using it in this post, it’s not required to have Visual Studio 2013 in order to use Web API with LightSwitch. Starting in Visual Studio 2012 Update 2 (LightSwitch V3), we added the ability to use the ServerApplicationContext on the middle-tier so you can create custom web services that utilize all the business and data logic inside LightSwitch. This makes it easy to reuse your LightSwitch business logic & data investments and extend the service layer exactly how you want.

Calling a stored procedure in your LightSwitch intrinsic database from a custom web method is pretty easy one you understand the pieces. You can use this technique to call stored procs in external databases as well. Let’s see how!

Creating the Web API

First, we need to add the proper references to our LightSwitch Server project to get going with Web API. The easiest way to do that is to have Visual Studio add them automatically for you. Continuing from the example we started in the last post, create a folder to organize things a bit. Right-click on the LightSwitch Server project and add a new folder, I’ll name it “api” – this is where our Web API controllers will reside.

Next, add a controller – Right-click on the api folder, select Add, New Item, then select the Web API controller class (I’ll use Web API version 2). For this example I named it TableCountsController.

image

This gesture will add the references you need to the Server project, most notably System.Web.Http. The default code template for your controller will have method stubs for GET, POST, PUT and DELETE http verbs. In order to retrieve data from our stored procedure, we’ll write some code in the GET method. But first, we need to add a route so that we can call our api.

To do that, add a Global Application Class (Global.asax) to the server project (right-click on the Server project folder, select Add, New Item). Import the following namespaces:

VB:

 Imports System.Web.Routing
Imports System.Web.Http

C#:

 using System.Web.Routing;
using System.Web.Http;

Then map the route in the Application_Start method.

VB:

 Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)

    RouteTable.Routes.MapHttpRoute(
    name:="DefaultApi",
    routeTemplate:="api/{controller}/{id}",
    defaults:=New With
              {
                  Key .id = System.Web.Http.RouteParameter.Optional
              })
End Sub

C#:

 protected void Application_Start(object sender, EventArgs e)
{
    RouteTable.Routes.MapHttpRoute(
    name: "DefaultApi",
    routeTemplate: "api/{controller}/{id}",
    defaults: new
    {
        id = System.Web.Http.RouteParameter.Optional
    });       
}

Calling the Stored Procedure

Now we need to write some code to call our stored proc. We can use the ConfigurationManager to get the connection string to our database from our Web.config and then we can write some standard SqlClient code to call the stored proc. First add a reference to System.Configuration from the Server project.

image

Next, write code in the GET method of the controller to return our data. The method will return a JSON serialized list of Name/Count pairs, where Name is the database table name and Count is the row count coming from our stored proc. However, I only want to return this sensitive data if the logged in user has permission to see it.

Add the code in bold below to your controller. Notice that we use the LightSwitch ServerApplicationContext to get at the Application.User object to check permissions before executing the stored proc. We can also use the context to get access to the DataWorkspace which has all our data sources and business logic. There are a ton of possibilities here, like returning projections of our entities using LINQ, accessing modeled queries, etc. 

VB:

 Imports System.Net
Imports System.Web.Http
Imports System.ConfigurationImports System.DataImports System.Data.SqlClientImports Microsoft.LightSwitchImports 



LightSwitchApplication

Public Class TableCountsController
    Inherits ApiController

    ' GET api/<controller>
    Public Function GetValues() As Object

    Dim reportResult As Object = Nothing


 
        Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()         'Only return this sensitive data if the logged in user has permission         If context.Application.User.HasPermission(Permissions.SecurityAdministration) Then              'The LightSwitch internal database connection string is stored in the <br>              ' web.config as "_IntrinsicData". In order to get the name of external data <br>              ' sources, use: context.DataWorkspace.*YourDataSourceName*.Details.Name                Using conn As New SqlConnection(                  ConfigurationManager.ConnectionStrings("_IntrinsicData").ConnectionString)                    Dim cmd As New SqlCommand()<br>                    cmd.Connection = conn<br>                    cmd.CommandText = "uspGetTableCounts"                    cmd.CommandType = CommandType.StoredProcedure<br>                    cmd.Connection.Open()                    'Execute the reader into a new named type to be json serialized                    Using reader As SqlDataReader =<br>                        cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)<br>                        reportResult = (From dr In reader.Cast(Of IDataRecord)()                                        Select                                            Name = dr.GetString(0),<br>                                            Count = dr.GetInt32(1)<br>                                       ).ToList()                    End Using<br>                End Using<br>            End If<br>            Return reportResult        



















End Using<br>    End Function
End Class

C#:

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Configuration;using System.Data;using System.Data.SqlClient;using 


Microsoft.LightSwitch; 

namespace LightSwitchApplication.api
{
    public class TableCountsController : ApiController
    {
        // GET api/<controller>
        

public object Get()<br>        {            object reportResult = null;            using (ServerApplicationContext context =                 ServerApplicationContext.CreateContext())                // Only return this sensitive data if the logged in user has permission               if (context.Application.User.HasPermission(Permissions.SecurityAdministration)) <br>                { <br>                    {                     //The LightSwitch internal database connection string is stored in the <br>                     // web.config as "_IntrinsicData". In order to get the name of external data <br>                     // sources, use: context.DataWorkspace.*YourDataSourceName*.Details.Name                        using (SqlConnection conn =                               new SqlConnection(ConfigurationManager.ConnectionStrings<br>                                  ["_IntrinsicData"].ConnectionString))<br>                        {                                                                            SqlCommand cmd = new SqlCommand();<br>                            cmd.Connection = conn;<br>                            cmd.CommandText = "usp_GetTableCounts";<br>                            cmd.CommandType = CommandType.StoredProcedure;<br>                            cmd.Connection.Open();                            // Execute the reader into a new named type to be json serialized                            using (SqlDataReader reader = <br>                                cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))<br>                            {<br>                                reportResult = reader.Cast<IDataRecord>()<br>                                                .Select(dr => new                                                {<br>                                                    Name = dr.GetString(0),<br>                                                    Count = dr.GetInt32(1)<br>                                                }<br>                                                    ).ToList();<br>                            }<br>                        }                                            }<br>            }            return 














reportResult;<br>        } 
    }
}

In this case we don’t have any parameters to pass to our stored proc but it’s easy to write an api that accepts parameters. For an example of how to do that see Steve’s post here.

Displaying the Results

Now that we have the data we can display it any way we want. For this example, let’s see how we can use a simple JQueryMobile 1.3 reflow table in our LightSwitch HTML client. (Note that if you are using Visual Studio 2012 you will need to install the LightSwitch client runtime update 1 which uses JQueryMobile 1.3.)

I’ll add a browse screen called “Admin” but I won’t select any entity data in this case because our data will be coming from our stored proc.

image

Next add a custom control to the screen, specify “Screen” as the data (the default), and then name it “TableCounts” in the properties window.

image

Then drop down the Write code button at the top of the designer and overwrite the _render code. We can use the JQuery getJSON method to call our api and build a custom table with the results.

 myapp.Admin.TableCounts_render = function (element, contentItem) {
    // Write code here.
    var myTable = $('<table class="ui-responsive table-stroke" data-role="table" />');
    var myHeader = $('<thead><tr><th>Table Name</th><th>Row Count</th></tr></thead>');
    var myBody = $('<tbody/>');

    myHeader.appendTo($(myTable));
    myBody.appendTo($(myTable));

    $.getJSON("../api/TableCounts/",
            function (data) {
                if (data!=null) {
                    $.each(data, function (key, val) {
                        var myRow = $("<tr/>");
                        $("<td/>").text(val.Name).appendTo($(myRow));
                        $("<td/>").text(val.Count).appendTo($(myRow));
                        myRow.appendTo($(myTable));
                    });
                }               
            })

    myTable.appendTo($(element));
};

When we run the application we now see the table counts displayed in our table.

image

Wrap Up

There are other ways to call stored procedures in LightSwitch, but using Web API with LightSwitch gives you the flexibility of creating custom web methods that can take advantage of all the data and business logic in your LightSwitch middle-tier via the ServerApplicationContext. If you have LightSwitch version 3 or higher (VS2012 Update 2+ or VS2013) then you are ready to unlock the possibilities.

For more possibilities, like creating reporting dashboards, see: Create Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext

Enjoy!

Comments

  • Anonymous
    July 31, 2013
    Can we call stored procedures in Desktop Client?

  • Anonymous
    August 12, 2013
    I know I'm missing something simple, but I cannot figure out why the route I defined to the api will not register. My global.asax has been added and I have the route defined, but the code to register the route is never run.

  • Anonymous
    August 23, 2013
    @Farhan - check out this possible solution: blogs.msdn.com/.../calling-asp.net-web-api-from-a-lightswitch-silverlight-client.aspx

  • Anonymous
    November 14, 2013
    The comment has been removed

  • Anonymous
    November 21, 2013
    How do I update the element of the custom control from an execute event: Below Jquery codes compiles but the reflow table does not update with any data this codes does not seem to be working:     myTable.appendTo($(sresults).element); myapp.Search.SearchValue_execute = function (screen) {    // Write code here.    var myTable = $('<table class="ui-responsive table-stroke" data-role="table" />');    var myHeader = $('<thead><tr><th>MBL</th><th>ShipmentNo</th></tr></thead>');    var myBody = $('<tbody/>');    myHeader.appendTo($(myTable));    myBody.appendTo($(myTable));    var searchvalue = "../api/Search/?ShipRef=" + screen.findContentItem("SearchValueProp").value.toString();    $.getJSON(searchvalue,            function (data) {                if (data != null) {                    $.each(data, function (key, val) {                        var myRow = $("<tr/>");                        $("<td/>").text(val.MBL).appendTo($(myRow));                        $("<td/>").text(val.ShipmentNo).appendTo($(myRow));                        myRow.appendTo($(myTable))                    });                }            })    var sresults = screen.findContentItem("ScreenResults")    myTable.appendTo($(sresults).element);

  • Anonymous
    April 07, 2015
    How to call the sql server stored proc