Executing Stored Procedures in Visual Studio LightSwitch.
- by dataintegration
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; }
}
Note: The created class must have an ID which will serve as the key value.
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.
