Handling Relationships Part III – Many to Many Relationships

So far, the Mix 09 application allows each session to be associated with only one presenter.  A method is needed to associate with a presenter a session that has already been stored in the database.  The method I have chosen is but one of many, and certainly not the best.  I decided to add the AutoComplete Behaviour from the Ajax Control Toolkit to the textbox used for adding sessions to a presenter, and by this means to allow either a new session to be added, or an existing session to be selected.  Since this project is meant to illustrate ADO.NET Data Services, I decided to make the the AutoComplete behaviour retrieve its data from my Mix Data Service instead of from a normal web service.  The AutoComplete behaviour required four extra JavaScript files to be added to my web page:

AutoCompleteJSFiles

Before the AutoComplete Behaviour retrieves any data, it fires a Populating event.  The comments at the top of the raisePopulating method are helpful:

raisePopulatingComments

I just followed the instructions provided therein.  I created a function called onPopulating to handle the populating event:

onPopulating

As instructed above, the onPopulating function first cancels the populating event with the set_cancel method.  The text entered in the textbox associated with the AutoComplete behaviour is contained in the behaviour’s _currentPrefix property.  There seems to be no public method to access the contents of that property.  The get_completionSetCount method returns the number of items that should be returned by the web service – or, in this case, by the data service.  I wanted to return from the database Sessions whose names contained the current prefix.  To do this, I needed to use the filter parameter of the query string.  Numerous operators can be used within the filter parameter, and they are briefly described on the Using Microsoft ADO.NET Data Services page on MSDN.  The operator I needed was substringof, which takes two parameters, the string to be searched for, and the database field to be searched in.  In this case, I was searching for the Prefix text in the Name field.  The top parameter has been discussed in a previous post.  I set it to the completionSetCount.  Finally, I set the final parameter of the fetchData method, the userContext, to the current Prefix, as this will be needed later.

Assuming the call to the Data Service succeeds, we arrive at the ACSucceeded function:

ACSucceeded

The purpose of this function is convert the data from the Data Service into a form suitable for the Auto Complete Behaviour’s _update method, which the comments in the raisePopulating method said had to be called.  This _update method takes three parameters: first, a string containing the text sent to the Data Service, which we have passed to ACSucceeded in the context parameter; second, an array of strings, which contains the results from the data source; and third, a Boolean specifying whether these results should be cached.  The strings in the array of results can either be standard text or JSON strings.  If a string is JSON, it can have either one or two properties; and if it has two, the first is the text that will be displayed in the Auto Complete drop-down, and the second is the value associated with that text, which can be retrieved when, for example, the Auto Complete’s ItemSelected event is raised.  In our case, we simply construct an array of strings containing the names of the sessions that fulfilled the search criteria.

Behaviours that are added to elements with a DataView seem to be removed whenever the DataView is bound to a new object.  I therefore decided to attach the AutoComplete behaviour in the PopupShowing function, after the details DataView is bound to data:

PopupShowingWithAutoComplete

Once or twice, the AutoComplete was not removed when expected.  This might suggest a bug, but I have covered it up by testing for the existence of the AutoComplete before adding another.  This needs further investigation.  I set the serviceMethod and service Path to ‘Dummy,’ as the populating event was not raised when either was left null or empty.

We have now enabled the user to select the name of an existing session and then associate a session of that name with a presenter.  With the application in its current state, however, a new session is still added to the database, whereas we want the presenter to be associated with the existing session.  To accomplish this, we need to check, when a session is added to a presenter, whether the session is new, or whether it already exists in the database.  The method I have used to perform this check is less than ideal, but is satisfactory in the circumstances. 

OnSessionCommandWithExistenceCheck

When the Add button is clicked, and the onSessionCommand function is called, we now test whether a Session with the added name already exists.  This test is carried out in the FindSession function, which is discussed below.  If there is no such Session, a new one is created in the way described in the previous post.  If, however, such a Session does exist, there only needs to be created a new Link, which is then associated with the existing Session.

The FindSession function simply runs through all the items in the DataContext, checking first whether its type is Sessions, and then whether its name is the same as the name that was passed in as the function’s parameter:

FindSession

If it finds such a Session, it returns it.  Otherwise, it returns null.

The details view with AutoComplete in action:

DetailsWithAutoComplete

Now that one Session can belong to several Presenters, deletion presents a problem.  The database will not allow a record to be deleted if it is related to a record in another table.  In fact, the database enforces the very behaviour we want.  One presenter, for example, might well decide to pull out of giving a session, and leave his partner to give it alone.  On the other hand, we probably would not want a session to exist without a presenter, since a session without a presenter is not a session at all.  Well, I suppose one might as well say that a presenter without a session is not a presenter at all.  Nevertheless, for this application I have decided to delete any session not associated with a presenter.  In the onSessionCommand function, I call the Data Service to determine how many presenters are associated with a given session:

OnSessionCommandDelete

I have used the datacontext’s fetchDeferredProperty method to retrieve the Links property of the appropriate Session.  I have passed as userContext an object containing a reference to the Dataview and to the chosen session, as these will be needed in the deleteSession function, where the necessary deletions happen:

DeleteSessions

If the Session Links property contains more than one item, only the Link from the current Presenter to the Session is deleted.  Otherwise, both the Link and the Session are deleted, as before.

Handling Relationships Part II – Inserting and Deleting

To the PresenterDetails table, I added into the table tag a dataview:oncommand attribute with a value of onSessionCommand, which is the name of the function that will handle any command events that occur in the PresenterDetails Dataview:

PresenterDetailsWithOnCommand

I added an extra column and a footer to the table displaying the session names, and in the footer I put a input element of type text, and a button:

SessionsFooter

As we have seen before, the sys:command attribute, when added to a button within a Dataview, causes a click of that button to fire the oncommand event of the Dataview.  The value of the sys:command attribute is passed to the handling function as the command name:

OnSessionCommand

The handler, onSessionCommand, first checks the command name equals Add, which is the only possible value at this stage.  The sender argument is a reference to the PresenterDetails Dataview, and its data is retrieved with its get_data method.  The ASP.NET AJAX $get method is then used to find the textbox containing the name of the session to be added.  The code to add a session is longer than one might expect, because as well as creating new objects, one has to tell the datacontext how the objects are related.  The createEntity method of the datacontext creates an object with metadata corresponding to the value specified in its parameter.  In this case, a Links entity and a Sessions entity are created.  With the application in its current state, only the Name property of the session can be set, although it can also be given a description property.  The properties of the Links entity are a primary key and two foreign keys.  The primary key and at least one of the foreign keys will only be determined once the data has been submitted to the database.  Therefore, instead of setting keys directly, the objects, once they have been added to the datacontext with the insertEntity method, are associated with the setLink and addLink methods.  The parameters accepted by both methods are similar: a parent object, a property name, and a child object.  The addLink method, however, is used for one to many relationships, whereas the setLink method is used for one to one relationships.  One Presenter (represented in the code by data) can have many Links, but one Link can have only one Session.  The addLink method is therefore used to associate a Presenter with a Link, and the setLink method is used to associate a Link with a Session.

Sessions can now be added to existing Presenters.  To allow Sessions to be added at the same time as a new Presenter is created, a further change is necessary.  Previously, a new Presenter entity was only added to the datacontext when the OK button of the details popup was clicked.  For the current scenario, however, it is easier to create the new entity when the popup is displayed, and then delete it if the Cancel button in the popup is clicked:

RelationalPopupShowing

Once again, the datacontext’s createEntity method is used to create a Presenters entity, and the insertEntity method is used to add the entity to the datacontext.  It proved necessary to set the Links property of the newly created Presenters entity to an empty array, as otherwise submission to the database caused an error when no sessions had been added.

To allow Sessions to be deleted, I added a Delete button to each row in the table displaying the list of sessions:

SessionsWithDelete

This time, I set the command name to Delete, and the command argument to the Link entity to which the row is bound.  I again used the $dataItem parameter, which is described in an earlier post.  The click event of the Delete button is handled by the same onSessionCommand function we have just examined:

OnSessionCommandWithDelete

As expected, the Link entity and the Session entity have to be deleted, and this is done with the datacontext’s removeEntity method.  The connection between the Link entity and the Presenter entity with which it is associated must also be severed.  This is accomplished with the datacontext’s removeLink method.  In essence, this performs the opposite function to the addLink method that we saw above.

The Details view is now as follows:

RelationalDetails

Handling Relationships Part I – Reading and Updating

In demonstrations of master-details scenarios, it is common for the details view to show vertically what the master view had shown horizontally. In a real application, the details view is usually required to show more information than can be contained in the master view.  In the database of my Mix 09 application, there are tables for information about the presenters and the sessions at Mix 09, with a many-to-many relationship existing between the two.  So far, only data from the Presenters table is being displayed.  In the next couple of posts, I will look at handling data from multiple related tables.  To begin with, I will assume that each Session is given by only one presenter.  Afterwards, I will extend the solution to allow several presenters to give a single session.

To get the extra data now required for the details view, a request must be sent to the ADO.NET Data Service whenever a presenter is selected in the Presenters Dataview:

RelationalSelect

The use of the fetchData method is familiar from earlier posts.  In this case, a single record from the Presenters table is being requested, the uri for which is obtained from the metadata of the data item that is sent as the command argument to the onCommand function.  The use of the $expand attribute, however, is new.  This attribute tells the Data Service to return data from other database tables related to the table containing the record being requested.  The Presenters table, you might recall, is related to the Links table, which in turn is related to the Sessions table.  By setting the $expand attribute to “Links/Sessions,” the Data Service is told to return the relevant data from the Links table and the Sessions table.  The result of this request is handled by the ExpandedFetchSucceeded function:

ExpandedFetchSucceeded

Most of this code was previously contained in the onCommand function; but now, instead of directly setting the data of the PresenterDetails Dataview with its set_data method, I have created a binding between the PresenterDetails Dataview and the selected item in PresenterDataView.  This selected item, it should be noted, is automatically updated with the extra data just received from the Data Service.  By setting mode to Sys.BindingMode.twoWay, the data in one bound object is automatically made to reflect changes to the data of the other. 

The selected data now has as one of its properties an array of Links, each of which has as one of its properties a Session.  To display the Session data, I used a nested Dataview:

SessionsTable

I added an extra row to my details table, and put another table inside that row.  This was the quickest (if not the best) method.  The attributes needed to create a Dataview were described in a previous post.  It is important to notice here that the data property of this Dataview is set to Links, which, as just mentioned, is an array property of the object to which the parent Dataview is bound.  The child Dataview generates, in this case, a table row for each item in the Links array.  Each row contains an input element of type text, the value attribute of which is bound to the Name property of the session object, which is just a property of the Link object.  Nothing more is required to make the Dataview display the names of the sessions that a presenter is giving.  Even more surprising, changes to the information about the presenter and his sessions can be saved to the database simply by calling the saveChanges method of the datacontext:

RelationalUpdatesII

The use of a binding has therefore made saving updates even easier than before.  The only additions necessary are the RemoveBinding function, which is called whenever the popup is closed, and the call to the clearChanges method of the datacontext, which ensures that cancelled changes are not submitted to the database.

Implementing Delete

To make it possible to delete records from the database, I added a button to every row in the Presenters table.

TableWithDelete

The button has a sys:command attribute of Delete, and the sys:commandargument parameter is $dataItem, as it was for the Select command we looked at before.  The Presenters table now has an extra function, but is inferior in appearance:

MasterViewWithDelete

When a delete button is clicked, the onCommand function is called, as it is when a presenter’s name is selected.

FinalonCommand

The commandname is stored as a property of the args object, and is accessed through the get_commandName method.  If it equals Delete, the commandargument, which we have set to be the data to be deleted,  is retrieved with the get_commandArgument method.  A confirm box is displayed; and if the response equates to true, the data is deleted with the removeEntity method, and the changes are saved to the database with the saveChanges method.  Although the data has now been deleted, it is still visible in the DataView.  If the DataView was bound to the DataContext, the deleted item would be removed from the DataView, but it would only display nine items, which could cause problems with paging.  Instead, the SaveSucceeded function checks whether the context equals Delete; and if it does, it causes the DataView to refresh.

SaveSucceeded

Implementing Inserts

You might remember from an earlier post that the application already has a button for adding a new presenter to the database.  Clicking the button shows the popup window; but instead of displaying blank fields ready for new data, it shows the details of whichever presenter’s details we had just viewed.  In addition, clicking the OK button of the popup window calls a function that tries to update the information about a presenter who already exists.  To solve these problems, a means is needed to keep track of whether existing data is being altered, or whether new information is being added.  To achieve this, I first of all added a global variable IsNew, and set it equal to true.  When details about an existing presenter are being displayed, I want to set IsNew equal to false.  I therefore added the line “IsNew = false;” to the onCommand function:

onCommandwithIsNew

I then made two additions to the command initialising the ModalPopup: a handler for the popup’s cancel button, and a handler for its showing event.

FinalModalPopup

The showing event is raised just before the popup is displayed on the screen.  The PopupShowing function, which handles the event, is as follows:

PopupShowing

If the IsNew global variable equals true, the function binds the PresenterDetails DataView to an empty object, which has the effect of setting all its fields to null.  When the New Presenter button is clicked, the resulting Popup looks like this:

PopupWithNulls

Lots of nulls!  To remove them, a convert function can be used:

ConvertFunction

This function takes one parameter, and returns an empty string if it equals null, and returns the parameter unchanged if it is not equal to null.  This function is used in the details view as follows:

DetailsWithNullConverters

The popup window now looks somewhat better:

PopupWithoutNulls

The same converter can be used in the master view:

PresentersWithoutNulls

When the OK button in the popup window is clicked, the SubmitChange function is called.  When that function was described in the last post, it had only to update an existing record.  Now, it must first check whether it needs to perform an update or an insert.

FinalSubmitChange

The first two lines retrieve the data from the details view, which is required in either case.  If IsNew equals true, an insert is performed with the insertEntity method.  This takes two parameters, the data to be inserted, and the name of the data type, which matches the name of the database table into which the data should be inserted.  The saveChanges method (which was described in the previous post) is then called.  Whichever operation is performed, the value of IsNew must be reset to true.

If the cancel button in the popup window is clicked, the CancelChange function is called.  No changes need to be made to the data, but IsNew again needs to be reset to true.

CancelChange

In my last post, I said I had three reasons for not binding the details view to the master view, and I gave two of them.  The third reason relates to inserts.  With the two views bound together, when the fields in the details view are cleared, I assume the associated data in the data context will also be cleared.  It seemed to me that this was likely to cause problems, and I could not work out how to temporarily disable data binding.

Implementing Updates

The easiest way of implementing updates would have been to bind the details view to the master view.  This technique is used in an excellent video tutorial on Channel 9.  I decided to adopt a different method for three reasons, two of which I will mention here.  I wanted the user’s changes to be submitted by clicking the OK button in the popup window.  With data binding in place, however, the master view, which is visible in the background, would update whenever the user tabbed away from a field in the details view.  The user could be confused about whether his changes had already been submitted.  If the user clicked cancel in the popup window, I of course wanted any changes he had made to be forgotten; and yet I could not find an automatic way to undo the changes.  For these reasons, together with a third, more important one, I chose to perform the update manually.  As it turned out, this too was straightforward.  I added a handler to the OK button of my popup window by setting its OnOkScript parameter:

OnOkScript

In the application init handler, I also created AdoNetDataContext component with my Mix Data Service as it source.  I assigned this component to a global variable dataContext, as I wanted it to be available throughout my application.

dataContext

In my webpage, I changed the dataprovider of my DataView to this new dataContext:

ChangedDataProvider

The function handling the OK button is simple:

SubmitChangeFunction

I use the get_selectedIndex method of the Presenters DataView to find which item is being edited.  I then use the get_items method to access the array of items.  To inform the DataView of the changes being made to its data, it is necessary to use the Sys.Observer.setValue method.  This method takes three parameters: first, the object to be changed; second, the name of the property to be changed; and third, the new value of that property.  The setValue method changes the data in memory.  To store the new data in the database, the saveChanges method of our dataContext is used.  This method can be called without any parameters, but here I have added two callbacks, and set the third parameter, the context, to the name of the operation being performed.

Adding a Popup Details View

Most of the examples I have seen of Master-Details scenarios in ASP.NET AJAX 4 have had the master and details views visible simultaneously.  In some cases, however, the page might be too small to contain both views at the same time.  I decided to show the details view in a popup window.  For this project, I used the AJAX Control Toolkit’s Modal Popup control.  I copied the necessary javascript files into the MicrosoftAjax folder of my website, and added references to my page.  The files I required were listed on Matt Berseth’s blog, along with a comparison of its size with the size of the jQuery ThickBox control.  The files for the Modal Popup added up to 131Kb, over twice the size of its jQuery rival.  For a real website, a leaner solution might be preferable; but for this example, the Modal Popup was fine.

The referenced files:

ModalFiles

I am going to cheat at this point.  The Modal Popup is an ASP.NET AJAX behaviour, and needs to be attached to an html element to act as its trigger.  I had no suitable element at this stage, but I knew that later on I would need a button to add a Presenter to the database.  I therefore added a button to my webpage, and gave it the id NewPresenterButton and the type button.  I also added a div with id PopupDiv to be the actual popup window.

PopupDiv

In this div, I put a table to display data about a presenter, as well as OK and Cancel buttons.  To turn the table into a DataView, I added two attributes to the table tag:

  • sys:attach=”dataview”
  • class=”sys-template”

The binding syntax is the same as we used in a previous post, but this time it is used to specify the content of input and textarea controls.  This is because we want to be able to edit data in the details view.  To style the popup window, I just copied the css from the example given on the ASP.NET AJAX Control Toolkit samples page.

PopupStyle

To turn PopupDiv into a ModalPopup control, I added some code to the init event handler we saw earlier:

CreateModalPopup

I wanted the Modal Popup to show when I selected a presenter in my DataView.  To do this, I had to make some changes to my web page.

SelectEnabledDataView

Three parts of this web page are of interest.  First, I have replaced the First Name and Last Name columns with a single column called Name.  This can then form the element that when clicked will show the popup.  To display two fields from the database in a single table cell, I have used the {{ }} syntax, which evaluates arbitrary javascript code.  Furthermore, I have put the name inside an anchor tag.  Second, I have added a dataview:oncommand attribute to the tbody tag, and told it to call the onCommand function whenever a command event is raised in the DataView.  Third, I have added sys:command and sys:commandargument attributes to the anchor tag just mentioned.  The sys:command attribute causes a command event to be raised whenever the anchor tag is clicked.  Since I have set the value of sys:command to Select, the word Select will be passed as the commandName to the handling function.  The handling function can also be passed a commandArgument, by using the sys:commandargument attribute.  In this case, I have used the special $dataItem, which passes to the handling function the actual data item to which that item in the DataView is bound.

The onCommand function, which handles the command event of the DataView is as follows:

onCommandFunction

The commandName and commandArgument are contained in the args object, and can be retrieved by using the get_commandName and get_commandArgument methods.  First, we check whether the command name is Select.  If it is, we retrieve the associated data that we passed in as the commandArgument.  We then find the DataView in the popup window, and use its set_data method to bind it to the correct presenter.  We then find the ModalPopup control, and use its show method to make it visible.

When a presenters name is clicked, we now see this popup.

Popup