A LightSwitch Project is very easy way to visualize and manipulate information directly from one of our ADO.NET Providers. But when it comes to executing the Stored Procedures, it can be a bit more complicated. In this article, we will demonstrate how to execute a Stored Procedure in LightSwitch. For the purposes of this article, we will be using the RSSBus Email Data Provider, but the same process will work with any of our ADO.NET Providers.

Creating the RIA Service.

• Step 1: Open Visual Studio and create a new WCF RIA Service Class Project.

• Step 2:Add the reference to the RSSBus Email Data Provider dll in the (ProjectName).Web project.

• Step 3: Add a new Domain Service Class to the (ProjectName).Web project.

• Step 4: 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 we are executing is called SendMessage. The parameters we will need are as follows:

public class NewMessage{
    [Key]
    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; }
}

• Step 5: Create a new method that will executed when the insert event fires. Inside this method you can use the standards ADO.NET code which will execute the stored procedure.

[Insert]
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));
	comm.ExecuteNonQuery();
    } catch (Exception exc) {
	Console.WriteLine(exc.Message);
}
}

• Step 6: Create a query method. We are not going to be using getNewMessages(), so it does not matter what it returns for the purpose of our example, but you will need to create a method for the query event as well.

[Query(IsDefault=true)]
public IEnumerable<NewMessage> getNewMessages() {
	return null;
}

• Step 7: Rebuild the whole solution.

Creating the LightSwitch Project.

• Step 8: Open Visual Studio and create a new LightSwitch Application Project.
• Step 9: On the Data Sources, add a new data source. Choose a WCF RIA Service

• Step 10: Choose to add a new reference and select the (Project Name).Web.dll generated from the RIA Service.

• Step 11: Select the entities you would like to import. In this case, we are using the recently created NewMessage entity.

• Step 13: On the Screens section, create a new screen and select the NewMessage entity as the Screen Data.

• Step 14: 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. If you create a screen to check the sent messages, you can refresh this screen to see the mail you sent.

Sample Project

To help you with get started using stored procedures in LightSwitch, download the fully functional sample project. You will also need the RSSBus Email Data Provider to make the connection. You can download a free trial here.