Executing Stored Procedures in Visual Studio LightSwitch

The CData ADO.NET Data Providers enable you to visualize and manipulate information from disparate data sources in LightSwitch applications. This entry demonstrates how to execute stored procedures in LightSwitch. This article uses the CData Email Data Provider, but the same process will work with any CData ADO.NET Data Provider.

Create the RIA Service

  1. Open Visual Studio 2010 and create a new WCF RIA Services Class Library Project.
  2. Add a new Domain Service Class to the .Web project.
  3. In the new Domain Service Class, create a new class with the attributes needed for the stored procedure's parameters. In this demo, the stored procedure you will execute is called SendMessage. The parameters you will need are as follows:
    public class NewMessage{
      public int Id { get; set; }
      public string FromEmail { get; set; }
      public string ToEmail { get; set; }
      public string Subject { get; set; }
      public string Text { get; set; }
    Note: The created class must have an Id, which will serve as the key value.
  4. In the Domain Service class, create a new method that will be executed when the insert event fires. Inside this method you can use standard ADO.NET code to execute the stored procedure.
    public void SendMessage(NewMessage newMessage) {
      try {
        EmailConnection conn = new EmailConnection(connectionString);
        EmailCommand comm = new EmailCommand("SendMessage", conn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        if (!newMessage.FromEmail.Equals(""))
          comm.Parameters.Add(new EmailParameter("@From", newMessage.FromEmail));
        if (!newMessage.ToEmail.Equals(""))
          comm.Parameters.Add(new EmailParameter("@To", newMessage.ToEmail));
        if (!newMessage.Subject.Equals(""))
          comm.Parameters.Add(new EmailParameter("@Subject", newMessage.Subject));
        if (!newMessage.Text.Equals(""))
          comm.Parameters.Add(new EmailParameter("@Text", newMessage.Text));
      } catch (Exception exc) {
  5. Create a query method. The getNewMessages method will not be executed so it does not matter what it returns for the purpose of the example. But you will need to create a method for the query event.
    public IEnumerable<NewMessage> getNewMessages() {
      return null;
  6. Rebuild the solution.

Create the LightSwitch Project

  1. Open Visual Studio and create a new LightSwitch Application Project.
  2. Click Attach to external Data Source and select the WCF RIA Service data source type.
  3. Choose to add a new reference and select the your-project-name.Web.dll generated from the RIA Service.
  4. Select the entities you would like to import. In this case, the recently created NewMessage entity.
  5. Create a new screen and select the NewMessage entity as the Screen Data.

After you run the project, you will be able to add a new record and save it. This will execute the stored procedure and send the new message.

You can refresh the screen you created to see the mail you sent.

Sample Project

To get started using stored procedures in LightSwitch, download the fully functional sample project.