Over a million developers have joined DZone.

Linq to SQL with Windows Communication Foundation in a Multi Tiered Environment - Part 2

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

In the first part of this article, I tried to define a Users & Favorites scenario and the things to keep in mind about Linq to SQL. In this post I’ll continue building that application and show its implementation in different tiers connected with WCF. 

Here are the sources for the article.

Service Layer Design (Cont’d from Part 1)

Service Host (Web Service in our case)

This is a host project (a plain Web project) needed to host our web service. It has our .svc files and needed configuration. In .svc file we have the mapping from contract to the implementation:

<%@ ServiceHost Language=”C#” Debug=”true” Service=”ServiceImplementations.UsersService” %>

And the endpoint configuration goes as follows:

<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name=”FavoritesServiceBehavior”>
<serviceMetadata httpGetEnabled=”true” />
<serviceDebug includeExceptionDetailInFaults=”false” />
</behavior>
<behavior name=”UsersServiceBehavior”>
<serviceMetadata httpGetEnabled=”true” />
<serviceDebug includeExceptionDetailInFaults=”false” />
</behavior>
</serviceBehaviors>
</behaviors>
<services>
<service behaviorConfiguration=”FavoritesServiceBehavior”
name=”ServiceImplementations.FavoritesService”>
<endpoint address=”" binding=”wsHttpBinding”
name=”IFavoritesService_Endpoint”
contract=”ServiceContracts.IFavoritesService”>
<identity>
<dns value=”localhost” />
</identity>
</endpoint>
</service>
<service behaviorConfiguration=”UsersServiceBehavior”
name=”ServiceImplementations.UsersService”>
<endpoint address=”" binding=”wsHttpBinding”
name=”IUsersService_Endpoint”
contract=”ServiceContracts.IUsersService”>
<identity>
<dns value=”localhost” />
</identity>
</endpoint>
</service>
</services>
</system.serviceModel>

Service Clients (Consumers)

The client layer is a very thin façade to invoke the requested methods from the channel. Clients are meant to be called through controllers if you are using MVC, and in our case our web application will consume the service so the endpoint configurations will live in web tier:

<system.serviceModel>
<client>
<endpoint binding=”wsHttpBinding” bindingConfiguration=”"
contract=”ServiceContracts.IFavoritesService”
address=”http://localhost/WebServiceHost/FavoritesService.svc”
name=”FavoritesClient”>
<identity>
<dns value=”localhost” />
<certificateReference storeName=”My” storeLocation=”LocalMachine”
x509FindType=”FindBySubjectDistinguishedName” />
</identity>
</endpoint>
<endpoint binding=”wsHttpBinding” bindingConfiguration=”"
contract=”ServiceContracts.IUsersService”
address=”http://localhost/WebServiceHost/UsersService.svc”
name=”UsersClient”>
<identity>
<dns value=”localhost” />
<certificateReference storeName=”My” storeLocation=”LocalMachine”
x509FindType=”FindBySubjectDistinguishedName” />
</identity>
</endpoint>
</client>
</system.serviceModel>

Presentation

The challenge in the presentation tier is we need to maintain the state of the each entity according to the user interaction. For this purpose, I put 2 GridViews , one for Users and One for favorites to enable insert, update, delete and select operations.

We will bind strongly typed collections (IList<User> and IList<Favorite>) to our GridViews and we will use the ID fields of the objects to associate with the gridview, and then use them in the code behind:

Here is the definition for Users GridView:

<asp:GridView ID=”usersGrid” runat=”server”
AutoGenerateColumns=”False” CellPadding=”4″
ForeColor=”#333333″ GridLines=”None”
DataKeyNames=”UserId”
OnRowDeleting=”usersGrid_RowDeleting”
OnRowUpdating=”usersGrid_RowUpdating”
OnSelectedIndexChanged=”usersGrid_SelectedIndexChanged”
OnSelectedIndexChanging=”usersGrid_SelectedIndexChanging”
OnRowCancelingEdit=”usersGrid_RowCancelingEdit”
OnRowEditing=”usersGrid_RowEditing”>
<RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
<Columns>
<asp:CommandField ShowDeleteButton=”True” />
<asp:TemplateField HeaderText=”First Name”>
<ItemTemplate>
<asp:Label ID=”firstNameLabel” runat=”server”
Text=’<%# Bind(”FirstName”) %>’></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”firstNameTextBox” runat=”server”
Text=’<%# Bind(”FirstName”) %>’></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Last Name”>
<ItemTemplate>
<asp:Label ID=”lastNameLabel” runat=”server”
Text=’<%# Bind(”LastName”) %>’></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”lastNameTextBox” runat=”server”
Text=’<%# Bind(”LastName”) %>’></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton=”True” />
<asp:CommandField ShowSelectButton=”True” />
</Columns>
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
<HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<EditRowStyle BackColor=”#999999″ />
<AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
</asp:GridView>

The one for Favorites is pretty much the same so I’ll go over Users grid.

Let’s go to code behind which is more important to us. We are going to do a batch update and send List of Users, and each user in the list will have their favorites. All the entities will have their latest status in their Status field.

Here is a sequence diagram to make things easier and more clearer to understand:

[img_assist|nid=3541|title=|desc=Picture 1. Sequence diagram of what’s happening|link=none|align=none|width=521|height=391]

Now, in the page load, we are going to populate the Users GridView:

if (!IsPostBack)
{
try
{
if (SessionStateUtility.Users == null)
{
// error may occur during disposal, not caring for the time being
using (UsersClient client = new UsersClient())
{
SessionStateUtility.Users = client.GetAllUsers().ToList<User>();
}
}
BindUsersGrid(SessionStateUtility.Users, -1);
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}

In the grid, user can update and delete users from session. For insert, there is a separate panel included at the bottom with an add button. In the add button what we are doing is quite simple, just adding a new user to the session:

protected void addUserButton_Click(object sender, EventArgs e)
{

Debug.Assert(sender != null);
Debug.Assert(e != null);
User u = new User()
{

FirstName = firstNameTextBox.Text,
LastName = lastNameTextBox.Text,
EMail = emailTextBox.Text,
Status = EntityStatus.New,
UserId = SessionStateUtility.NextUserId,
};
SessionStateUtility.Users.Add(u);

BindUsersGrid(SessionStateUtility.Users, -1);
}

You’ll notice 2 things here, one of them is the Status is set to Entity Status.New . The other one is the SessionStateUtility class. This acts as a provider and a helper for User lists. The Users list that it provides is the below:

/// <summary>
/// Gets or sets the users.
/// </summary>
/// <value>The users.</value>

public static List<User> Users
{

get
{

Debug.Assert(HttpContext.Current != null);
Debug.Assert(HttpContext.Current.Session != null);
return HttpContext.Current.Session[“Users”] as List<User>;

}
set
{
Debug.Assert(HttpContext.Current != null);
Debug.Assert(HttpContext.Current.Session != null);
HttpContext.Current.Session[“Users”] = value;
}
}

And it provides another method to get NextUserId. This is necessary because since there can be multiple new records in the screen, we will need to identify them. Next User Id brings the next highest negative number that is available:

/// <summary>
/// Gets the next id.
/// </summary>
/// <value>The next id.</value>

public static int NextUserId
{
get
{
if (SessionStateUtility.Users.Count == 0)
{
return -1;
}
int minId = SessionStateUtility.Users.Min<User>(user => user.UserId);
if (minId > 0)
{
return -1;
}
return –minId;
}
}

And then we need to handle the grid events. I wrote a helper function to Get the User object from Selected row index in the grid (it retrieves from session):

private User GetUserFromRowIndex(int index)
{
int userId = usersGrid.DataKeys[index].Value as int? ?? 0;

//retrieve the instance in the session
User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);

return user;
}

Another helper function is there for just to get user’s full name formatted:

 private string GetFullNameForUser(User u)
{

return String.Format(CultureInfo.InvariantCulture, “{0} {1}”, u.FirstName, u.LastName);

}

And this one updates the UI fields for a selected user:

private void UpdateUiForUser(User u)
{

if (u != null)
{
favoritesPanel.Visible = true;
userNameLabel.Text = GetFullNameForUser(u);
BindFavoritesGrid(u.Favorites.ToList<Favorite>(), -1);
}
}

And of course one method for binding the grid:

private void BindUsersGrid(IList<User> users, int editIndex)
{
usersGrid.DataSource = users
.Where<User>(usr=>usr.Status != EntityStatus.Deleted);// only bind non deleted ones
usersGrid.EditIndex = editIndex;
usersGrid.DataBind();
}

As you can see we are not binding the deleted ones but we are still keeping them in the session because we need to know what is deleted when we send them back to the data tier.

Then within the light of these methods, here goes the SelectedIndex_Changing event handler. It updates the favorite’s grid for the selected user:

protected void usersGrid_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
Debug.Assert(sender != null);

Debug.Assert(e != null);
usersGrid.SelectedIndex = e.NewSelectedIndex;

User u = GetUserFromRowIndex(e.NewSelectedIndex);
UpdateUiForUser(u);
}

And when the row is being edited, following event handler will get executed:

protected void usersGrid_RowEditing(object sender, GridViewEditEventArgs e)
{
Debug.Assert(sender != null);
Debug.Assert(e != null);

usersGrid.SelectedIndex = e.NewEditIndex;
BindUsersGrid(SessionStateUtility.Users, e.NewEditIndex);
}

And after user clicks edit, when he/she clicks update following handler will run:

protected void usersGrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Debug.Assert(sender != null);

Debug.Assert(e != null);

int userId = usersGrid.DataKeys[e.RowIndex].Value as int? ?? 0;

//retrieve the instance in the session
User user = SessionStateUtility.Users.Single<User>(usr => usr.UserId == userId);
user.FirstName = (usersGrid.Rows[e.RowIndex].FindControl(“firstNameTextBox”)
as TextBox).Text;

user.LastName = (usersGrid.Rows[e.RowIndex].FindControl(“lastNameTextBox”)
as TextBox).Text;
user.Status = user.Status == EntityStatus.New ?

EntityStatus.New :EntityStatus.Updated; // manage the state

BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
}

As you see if the edited users’ current status is already new, then we are not modifying it. But else, the state is changed to the updated.

A similar situation also exists for deletion. Have a look at the handler below:

protected void usersGrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Debug.Assert(sender != null);

Debug.Assert(e != null);

User user = GetUserFromRowIndex(e.RowIndex);

// If user is new and deleted now, we shouldnt send it over the wire again
if (user.Status == EntityStatus.New)
{
SessionStateUtility.Users.Remove(user);
}
else
{
user.Status = EntityStatus.Deleted;
}

BindUsersGrid(SessionStateUtility.Users, -1);// back to plain mode
}

We have done our work as a presentation layer, and we are now sending all the data through the service to data layer along with all the information needed for it to manage the generation of the SQL Statements (fingers crossed)

Data Layer Design

Since we are going to implement the IUsersDataAccess contract, we need to implement 4 methods: But I’ll focus on 2 of them especially. First one is GetAllUsers:

/// <summary>
/// Gets all users.
/// </summary>
/// <returns>The list of all users along with their favorites.</returns>

public IList<User> GetAllUsers()
{
using (FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<User>(u => u.Favorites);

context.LoadOptions = options; // load with favorites
context.ObjectTrackingEnabled = false; // retrieving data read only

return context.Users.ToList<User>();
}
}

As you see, we are telling the context to load every user with their favorites. This can cause some damage if these tables are very big, and there are methods to enhance this experience.

The UpdateUsers(IList) method is a bit more complicated. Here are the list of things that we are going to do:

  • Attach the users to the context who have their status “Updated’ – obvious one

  • Attach the users to the context who have their status “Deleted’ – since the context does not know about an object that is not attached, we need to attach them too.

  • We aren’t going to attach the objects to insert, because Data Context doesn’t need to know about the objects those are being added.

  • Call the relevant of one of those by looking at their status: InsertAllOnSubmit, DeleteAllOnSubmit

  • Do the same for the child entities of each. (Keep in mind that we need to delete all children regardless of their status if their parent is deleted)

So now hopefully the following implementation will be more understandable:

/// <summary>
/// Updates the users list.
/// </summary>
/// <param name=”updateList”>The list of users to perform the operations.</param>

public void UpdateUsers(IList<User> updateList)
{
using(FavoritesEntitiesDataContext context = new FavoritesEntitiesDataContext())
{
context.Users.AttachAll<User>(
updateList.Where<User>(
usr=>usr.Status == EntityStatus.Updated ||
usr.Status == EntityStatus.Deleted), true);

context.Users.InsertAllOnSubmit<User>(
updateList.Where<User>(
usr=>usr.Status == EntityStatus.New));

context.Users.DeleteAllOnSubmit<User>
(updateList.Where<User>(usr => usr.Status == EntityStatus.Deleted));

// do the same for the children
// If the parent is deleted, to prevent orphan records we need to delete
// children too

foreach (User user in updateList)
{
context.Favorites.AttachAll<Favorite>

(user.Favorites.Where<Favorite>

(fav=>fav.Status == EntityStatus.Updated || fav.Status == EntityStatus.Deleted || fav.User.Status == EntityStatus.Deleted || fav.User.Status == EntityStatus.Updated));

//we shouldnt insert the new child records of deleted entities
context.Favorites.InsertAllOnSubmit<Favorite>
(user.Favorites.Where<Favorite>
(fav => fav.Status == EntityStatus.New
&& fav.User.Status != EntityStatus.Deleted));
context.Favorites.DeleteAllOnSubmit<Favorite>
(user.Favorites.Where<Favorite>
(fav => fav.Status == EntityStatus.Deleted ||
fav.User.Status == EntityStatus.Deleted));
}
context.SubmitChanges();
}
}

That’s the end of fun(!) folks. As you have seen already, there is some work involved with making Linq to SQL work in Multi Tiered architecture, but it is doable still. Again, download the sources and please don’t hesitate to post any comments, criticisms here.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:

Published at DZone with permission of sidar ok. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}