LightSwitch provides for creating Data Sources in several ways. In the case of an existing database you would typically either specify the database itself or a WCF RIA Service.

But sometimes neither is feasible. I was confronted with a database with no primary keys and incredibly wide tables – so wide that I got error messages about it. And you can’t really do much with a database that has no primary keys. Also modifying the schema was not an option.

This actually turned out to be quite liberating. The reason we wanted to access the database outside of the legacy application it was designed for was to

Provide views of the data that the legacy application did not.

Add additional columns

By extracting the data into another database we could

Eliminate literally hundreds of columns (Yes, I do mean literally)

Create primary keys

Create relationships with referential integrity through foreign keys

So what does the legacy database look like? Sorry, that’s proprietary, or at least I’m not taking any chances. Fortunately, it just doesn’t matter, the principles are the same for any situation. So I made up a very simplistic database I call ShoppingCenterMgmt. The fictional "Shopping Center Management” application it supports is so old that going to the mall was unknown unless you were in Washington D.C. You can’t get much more legacy than that.

It consists of three tables

shopping_centers

stores

customers

Each shopping center exists at a specific physical location and contains one or more stores, which is identified by unit within shopping_center_id. If a store currently has an occupant it’s indicated by an entry in the customer_id field.

As you can see there are no primary keys, let alone foreign keys, therefore no referential integrity. The only thing the DBMS will prevent is duplicate unique keys. There’s nothing to prevent deletion of shopping centers that have stores or customers who currently occupy stores. Or you could add a store with an invalid shopping_center_id or customer_id. The opportunities are endless.

So we would like to not only capture the data in the columns shown above for these tables, but we would also like to add primary and foreign keys so we can achieve referential integrity and everything that goes with it. The first thing we need are target tables to import into.

The schema for the shopping_centers table looks like this:

We’ll create a table named Malls to receive the data.

We retain shopping_center_id as a unique index and add a Summary field which I’ll explain later.

Before moving on to the other tables, lets look at how the import works. We could write a stored procedure that specifically handles copying data from the shopping_centers table to the Malls table, but in a real-world scenario we’ve got several tables and a lot of columns so that will get pretty tedious. Far better to create tables to hold the desired table and column names and the screens to enter them.

It’s pretty simple, really, if you just take it one step at a time. To start with, we know we have to specify the name of the legacy table name, so we begin with that.

To begin with we just provide a column for the table name and require that it be unique.

We create a table name LegacyShadowTables for the target table names.

Now we need a way to pair up each legacy table with the appropriate shadow table.

This creates a many-to-many relationship between LegacyTable and LegacyShadowTable. Although our mapping will always be one-to-one, it doesn’t have to be.

NOTE: Since I’m still running under debug, all the data I’m entering over the next few screens will be going into the debug database. This means it will have to be re-entered into the ‘real’ database after publishing. There’s not very much, but you may want to wait until then to enter it.

Starting with the Legacy Tables List Detail screen, we enter the first name, shopping_centers.

You’ve probably noticed there’s no validation of the table names that are entered. That’s more trouble than it’s worth In this initial effort to get the import up and running. Any errors in table or column names will result in exceptions that will be logged when the stored procedure runs.

Here are some examples from when I was debugging my attempts to get the procedure working:

Ok, we have our first pair defined and three convenient screens for visualizing the relationships:

Next we need to specify the column mappings between the tables.

Each Leg

acyShadowTablePair has multiple pairs of column mappings, so there’s a one-to-many relationship between LegacyShadowTablePair and LegacyShadowColumnPair. To prevent duplicating the column pairs for a particular table pair, LegacyShadowTablePair, LegacyTableColumn, and ShadowTableColumn are specified as ‘Include in Unique Index’. The Columns computed property is analogous to the ‘Tables’ computed column in the LegacyShadowTablePair table.

We can add a LegacyShadowColumnPair screen to enter the column names, but it’s far more convenient to use the Legacy Shadow Table Pair List Detail Screen. To do so you’ll need to modify the screen by Adding the Legacy Shadow Column Pairs collection to the screen

Click the line shown above, then drag the resulting query onto the screen.

Now we can maintain the column names by table pairs.

At this point we’re going to need to publish so we have a SQL database to import the data into. Strictly speaking you could use a different database as the target and continue to

run LightSwitch under debug, but if you want to use the LightSwitch database for the data then you have to publish now.

We’re going to set Access Control to ‘Use Forms Authentication’. This will cause the publishing process to create a Site Administrator logon.

You publish from the Application Type selection.

We’re hosting on IIS Server and I have the Prerequisites installed, which I recommend:

Note: If you have problems try unchecking the ‘IIS Server has the LightSwitch Deployment Prerequisites installed’. I was tearing my hair out last night trying to figure out why my Application Administrator settings were not being applied. I still don’t know if having this checked was the cause, because now it works fine both ways.

I publish directly to the server whenever possible:

HTTPS off, unless you want it on:

The easiest approach is to create the new database during the publish process. The User ID must be a login that serves in public and sysadmin server roles. As a note of interest, after publishing, if you take a look with SSMS, the database owner will show as dbo. If you look at the mapping for the login you specified, you will see that for the database you created, dbo maps to that login.

Application Administrator fields. You only have to do this once:

Note: If your credentials don’t work when you try to log in take a look at web.config as shown below. The comment “<!—If no admin user exists…” is incorrect. It should say, “<!—If no admin user with this user name exists…”. Don’t bother looking in the database right after you publish – it’s only during the first log on after you publish that these settings are used to update the database.

<configuration>

<appSettings>

...

<!-- If no admin user exists, create an admin user with this user name -->

<addkey="Microsoft.LightSwitch.Admin.UserName"value="MallsMgr2"/>

<!-- When creating an admin user with Forms auth, use this as the full name -->

NOTE: If you have problems publishing check out this Beth Massi article. It’s from 6/8/2012 but seems to have the most recent information that isn’t just a description of each Wizard step, such as this article, helpful to 0 out of 1. Sigh.

Let’s start simple by writing a simple stored procedure to just copy the rows from the shopping_centers table to the Malls table:

We’ll create a test row by manually inserting one into the shopping_centers table:

Then manually execute the stored procedure:

And the results:

Which can be seen in the Malls List Detail screen:

Since we’re deleting the contents of the Malls table before we do the inserts, we can run the stored procedure over and over again. But let’s see what happens if we take out the Delete and try to run it more than once.

The return value from the procedure is now –4:

And the Messages tab shows that we’re trying to insert a duplicate in the Malls table.

Here’s the row in ProcErrorLog:

We can add the ProcErrorLog table to LightSwitch:

And a screen to view it:

Now we’ll replace the insert with dynamic sql using hard-coded values:

So if we set @debug to 1 instead of zero and run the procedure again we can see a display of the T-SQL code executed by sp_executesql, resulting in the Incorrect syntax error.

As you can see, we mis-spelled INSERT, so we fix it and run it again.

The code looks right and we don’t get any errors when it’s executed.

Ok, we have some working dynamic sql and we can use PRINT to debug errors. Now let’s start making it table driven instead of just plugging in hard-coded values. First we’ll add another table pair to make it a little more interesting. We add another legacy table, stores.

Our Malls don’t have any mundane old stores, they have Shops

So we’re mapping the stores table from out tired old shopping_centers database to the Shops table in our new, scintillating LS_Malls3 database. (The database names can also be table-driven, but for now we just hard-code them). Below we see that we’ve paired up stores with Shops.

Let’s take a look at the actual tables.

You can see below that the foreign keys in the table pair table point to the appropriate rows in the LegacyTables and LegacyShadowTables tables.

And the foreign key in the LegacyShadowColumnPairs table points to the approriate row in the LegacyShadowTablePairs table. As I mentioned before, each LegacyShadowTablePairs row can point to multiple LegacyShadowColumnPairs rows.

So for our first task, let’s get the table names and id in the first LegacyShadowTablePairs row. Later we’ll do this in a loop, but for now we’ll keep it simple by dealing with the first pair we encounter.

Specifying the unique index so we can update existing records instead of having to clear the shadow tables every time. After all, we are going to add columns and we don’t want to lose them.

Creating foreign key relationships.

Testing the shadowed columns to see if any have actually changed. If we update blindly we’ll end up with unnecessary concurrency messages in LightSwitch, and ultimately we’re going to fire off the procedure once a minute.

Firing off the procedure from LightSwitch

Logging detail information when we fire manually and summary information when we run once a minute

Detecting rows in the legacy table or shadow table that don’t have corresponding rows on the other side.

How soon will Part 2, featuring the perennial roadblock I encounter, "Firing off the procedure from LightSwitch" (successfully), be published, please, Richard?

We have tons of SP's and new "command-style" SPs that we're itching to let loose via LightSwitch but have yet to successfully find a reliable way of firing them off. We haven't time, and aren't daft enough, to replicate the data rules into LS from the working SPs. Old UI is Flex/Flash and we desperately need to get off it into HTML5, hence LS.

Stephen: Is your problem specifically with launching the stored procedure. If so I'll try to get you something before the next post, which I'm going to try to start in the next couple of days. So let me know if having just that code would be helpful.Regards,Richard