Still updating my Mix 09 Silverlight 3 + RIA Services talk with more fun stuff. While many of the RIA Services examples we have shown thus far do CRUD directly against database tables, I certainly recognize that many scenarios require using stored procedures in the database to encapsulate all data access.
You can see the full series here.
The demo requires (all 100% free and always free):
Check out the live site Also, download the full demo files
In this example, I am going to encapsulate all our data access in stored procedures. I will continue to use Entity Framework to access these stored procs, but you could of course use LinqToSql or ADO.NET directly.
Add Stored Procedures
The first step is to create a set of stored procedures in the database.
In Server Explorer open up the Database, select the Stored Procedures node and “Create New Stored Procedure”
These are very, very basic. My goal here is to show you how to get started, you can then add additional logic as needed while following the same basic overall pattern.
Let’s start by adding some query stored procedures:
ALTER PROCEDURE dbo.CoolSuperEmployees
ASSELECT SuperEmployees.Name, SuperEmployees.EmployeeID,SuperEmployees.Gender,SuperEmployees.Origin,SuperEmployees.Issues,SuperEmployees.Publishers,SuperEmployees.LastEdit,SuperEmployees.SitesFROM SuperEmployees WHERE SuperEmployees.Issues Between 10 And 99999
RETURNNotice this stored proc does not directly support paging. You could take a paging information if that is required.
and to get a particular employee
ALTER PROCEDURE dbo.GetSuperEmployee
(
@EmployeeID int )
AS SELECT SuperEmployees.Name, SuperEmployees.EmployeeID,SuperEmployees.Gender,SuperEmployees.Origin,SuperEmployees.Issues,SuperEmployees.Publishers,SuperEmployees.LastEdit,SuperEmployees.SitesFROM SuperEmployees WHERE SuperEmployees.EmployeeID = @EmployeeIDRETURNUpdate:
ALTER PROCEDURE dbo.UpdateSuperEmployee
(
@EmployeeID int, @Name nvarchar(MAX),@Gender nvarchar(50),
@Origin nvarchar(10),
@Issues int,@Publishers nvarchar(10),
@LastEdit datetime,
@Sites nvarchar(MAX))
ASUpdate SuperEmployeesSetName = @Name,
Gender = @Gender,
Origin = @Origin,
Issues = @Issues,
Publishers = @Publishers,
LastEdit = @LastEdit,
Sites = @Sites
Where EmployeeID = @EmployeeID
Insert:
ALTER PROCEDURE dbo.InsertSuperEmployee
@Name nvarchar(MAX),@Gender nvarchar(50),
@Origin nvarchar(10),
@Issues int = 0,@Publishers nvarchar(10),
@LastEdit datetime = null, @Sites nvarchar(MAX)ASInsert into SuperEmployees(
Name,
Gender,
Origin,
Issues,
Publishers,
LastEdit,
Sites
)
Values(
@Name,
@Gender,
@Origin,
@Issues,
@Publishers,
@LastEdit,
@Sites
)
Select SCOPE_IDENTITY() as Id
And finally delete:
ALTER PROCEDURE dbo.DeleteSuperEmployee
(
@EmployeeID int )
ASDelete From
SuperEmployees
Where EmployeeID = @EmployeeID
Update the Entity Framework Model
Now let’s create an Entity Framework model that knows how to access the data via these Stored Procs. Let’s start by creating a new Entity Framework Model
Then we select the SuperEmployees table and all of the storedprocs we created above
Next, I like to set the properties on the SuperEmployee entity to make the naming more clear in the .NET world.
Next, we wire up the CUD (create, update, delete) operations for this table to go through the storedprocs we just wrote.
First we setup the Insert function to map to the “InsertSuperEmployees” storedproc
Visual Studio automatically sets up the mapping, if you need to tweak this based on your stored procs, you certainly can.
Repeat this for Update and Delete, such that they are all mapped.
Now we need to do the same thing for the query methods. Open the Model Browser and find the stored proc for “CoolSuperEmployee”. Right click and select “Create Function Import”.
Then we set up a mapping to return SuperEmployees
And repeat for the GetSuperEmployee (int employeeID)…
Now we have our model all setup, let’s go back to our DomainService and update it to use these settings.
Update the Domain Service
The DomainService allows you to create custom business logic and easily expose this data to the Silverlight client. The good news is this looks almost exactly like the pervious examples.
First, let’s look at the query methods.
1: [EnableClientAccess()]2: public class SuperEmployeeDomainService :
3: LinqToEntitiesDomainService<NORTHWNDEntities> 4: { 5: public IList<SuperEmployee> GetSuperEmployees()
6: { 7: var q = Context.CoolSuperEmployees();8: return q.ToList();
9: } 10: 11: public SuperEmployee GetSuperEmployee(int employeeID)
12: {13: return Context.GetSuperEmployee(employeeID).FirstOrDefault();
14: 15: }Notice in line 5, we return an IList rather than an IQueryable.. this means that query composition from the client composes at the stored proc level, rather than all the way into the database. This is goodness because we funnel all requests through that stored proc, but it has the costs of maybe returning more data to the mid-tier than the client really needs. You can of course add paging to the stored proc or you can do direct table access for read only scenarios, but still use stored procs for CUD.
Then we have Insert and Update..
1: public void InsertSuperEmployee(SuperEmployee superEmployee)
2: { 3: Context.AddToSuperEmployees(superEmployee); 4: } 5: 6: public void UpdateSuperEmployee(SuperEmployee currentSuperEmployee)
7: {8: this.Context.AttachAsModified(currentSuperEmployee, this.ChangeSet.GetOriginal(currentSuperEmployee));
9: }Notice they look just like our previous example, but now these methods eventually call into our stored procs rather than direct table access.
We run it and it works great! Exactly like pervious examples, but this time all data access is via stored procs.
For more information on working with stored procedures with Entity Framework or LinqToSql see:
http://blogs.msdn.com/bags/archive/2009/03/12/entity-framework-modeling-action-stored-procedures.aspx
http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/17/ado-net-entity-framework-tools-stored-procedures.aspx
http://msdn.microsoft.com/en-us/library/bb384469.aspx
world live. 
Wednesday 30th September (Finland)
