Extending a Legacy Application by Associating Local Data with External Data

Overview

This weekend I was reading Robert Green’s recent post Using Both Remote and Local Data in a LightSwitch Application.

The example of connecting to an existing table and developing an application which uses its own tables intrigued me.

In this post I’ll discuss how I solved the problem a little differently than Robert did and the problems I encountered.

I see potential of using this idea as a way to write an application to add onto functionality of an existing application. There are many users of business applications like Microsoft Dynamics SL and Business Portal who may use this technique as a way to quickly generate an application that adds to out of the box behavior.

Some users may want their existing application customized, using the customization features provided in Dynamics SL. But they may discover the customization work is much more expensive in time resources than using a tool like LightSwitch to create an add-on application to provide an enhancement.

Steps

I started stepping through Robert’s post to learn more and test the concept.

In my exploration, I used a scenario similar to Robert’s example of extending a Customer table.

In my contrived example, I want new customer attributes. In some applications, like Microsoft Dynamics SL, extra fields are added to a table with the intention of allowing application customization by use the extension fields.  But often, an application doesn’t have additional fields for customization, or the fields are already in use from early customization work. 

How well will this idea work in having an application create its own table and appear to seamlessly bind the tables together into a consistent user view?

Some of this post is a duplication of what Robert discussed and as well as shown in the How Do I? videos. Hopefully some readers will find, where there’s overlap, that the content here is useful in the context of seeing the problem solved a little differently.

In this scenario, I’m adding attributes of gender, age, and ethnicity to the customer table. As I said, it’s contrived, but it demonstrates what I want to show in this post.

I started by creating a simple environment.

First I opened SQL Server Management Studio and created a new database, called CustExtTest (Customer Extension Test)

Then I created a single table Customer using the following script:

Then, in the Object Explorer pane in SQL Management Studio, I right mouse clicked on the Customer table and selected the context menu item Edit top 200 rows.

I entered the following three rows into the Customer table:

With the external database setup, I started Visual Studio, created a LS project, and connected to the external database.

Next I right mouse clicked on the Data Sources folder in the project and selected Add a Table from the pop context menu. I added a table called CustomerAttributes and added three fields to the table– Gender, Age, and Ethnicity.

If you’re following along in Visual Studio, your project should appear similar to this:

Next I added a relationship between the Customer table and the CustomerAttributes table, similar to Robert’s example.

Then I added the CustomerSearch and CustomerDetails screens.

Like Robert explained in his post, I changed the Model Window Picker to a Vertical Stack. Notice that LightSwitch adds text boxes for the fields in CustomerAttributes.

The ViewModel, the left hand pane of my CustomerDetail’s screen, looks like this:

Notice the link between the Customer.ID and the CustomerAttributes.ID.

I hit F5 to run the application. Then clicked SearchCustomer and selected a customer in the results grid. The CustomerDetail screen appears with the three fields from CustomerAttributes disabled (read only):

Exactly the expected behavior, as described in Robert’s post.

Ok, great!

So far this has been pretty much a duplication of what Robert blogged and what you find in the How Do I? videos.

Divergence

This is where we start to diverge from Robert’s post.

As Robert described, in order to enable these fields , you have to add a new object ( the CustomerAttributes object) to a Customer object.

Since I’m using C#, the code looks a little different than Robert’s example in the Customer_Created method.

The first time I entered the C# code in Customer_Created method, Visual Studio indicated, via the red highlighting, it had no reference to the CustomerAttributes object:

Whaaat??@!? 

CTRL-SHIFT-B to build produced a boat load of errors:

Ok, expected, but .. WHAAT?@@@??? 

I did some exploration but couldn’t find a solution to this problem of unknown references.

It got to be meal time,, so I closed the solution, left my office, and grabbed a bite.

Returning later feeling refreshed, I opened the solution, and well, well, well, well…. Check this out:

On reopening Visual Studio, the with no code changes, now Visual Studio indicates no missing references.

I did a build, but still got errors.

WHAT!*#(??!?  

The build error indicated a namespace error with CustomerAttributes.

“Well, isn’t that special”, I said to myself, because Visual Studio didn’t indicate an error condition until the build.

Ok, Whatever…

I changed the code to the following and got a clean build: 

“Ok”, I mumble under my breadth, “This IS a beta release.”

(as well, as mumbling a few times, “Bite me”)

Don’t forget LS is a beta release. Assuredly you will be reminded if you ever forget it.

As a former coworker use to say when we worked with early C++ compilers, “Isn’t bleeding edge fun?”

So I’m happy, and progress to another step Robert discussed in his post—how to handle an existing customer record.

Robert posted the following code fragment (Without my annotations in his code J )

I don’t know if in my naiveté I overlooked a step in Robert’s blog, but I could not figure out how to get a CustomerQuery object .

What I did find is a DataWorkwspace object.

I couldn’t find documentation about a DataWorkspace object. Perhaps I’m not looking in the right places? (or should I chalk it up to this IS a beta product and the documentation is not fully cooked.)

I found some discussion about Data workspaces here, here, and here.

Enough to give me a better understanding than what Intellisense provides

The following is the code I came up with:

In step 1, the customer entities read from the database are obtained.

Step 2 gets the customer entity selected in grid of the SearchCustomer screen.

Step 3 constructs a new CustomerAttributes object and assigns it to the selected customer. It also sets the CustomerAttributes.Customer property to the selected customer.

Just for exploration, I left the code in the bottom of the screen shot as an example of iterating the customer collection.

So that’s it. The implementation is not exactly as Robert showed in his posting, but it appears to get the job done.

I welcome,- nay, encourage—feedback if you see this implementation is problematic.

If someone can point out what I missed in not seeing a CustomerQuery as Robert described in his blog, my thanks in advance.