Archive for January, 2010

Exporting SQL 2008 to earlier version

Posted: 25th January 2010 in SQL

To allow an earlier version of SQL Server to recognise the data from 2008 it is necessary set the database compatibility level to the correct version.

Transact-SQL Method
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

eg. to use in 2005 run

ALTER DATABASE my_db SET COMPATIBILITY_LEVEL = 90

The versions are as follows:

80 : 2000
90 : 2005
100: 2008

Advertisements

Problem : You have two database tables which map on a one to one relationship with the same primary key. In turn, both of these table have foreign key relationships to other, different, tables. If you had control over the database, you would probably move all the columns into the same table. However, in this scenario, you don’t have any control over the database, and if you changed it, there are other existing application which use it.

Tow Entities sharing a primary key

Solution : After creating an entity model from the existing database you need to make one entity out of two tables and remap the existing association to the new entity columns.

As can be seen in the NetworkLocation and the Location entities both share the common primary key LocationCode. Our first step is to move the existing columns in NetworkLocation to the Location entity, this can be done using the following steps:

  1. Copy and paste all the columns, except LocationCode from NetworkLocation to Location
  2. Delete NetworkLocation from the diagram
  3. Right click on the Location entity and select table mapping
  4. in the mapping details window, scroll to the bottom where it says “Add a Table or View”
  5. Select the drop down and choose the table which mapped to the entity you have just deleted
  6. Remap the entity to properties to the underlying table, as can be seen below

remap the entitiy to old table

Now, as can be seen below, the entity properties have been moved, but the reference to the NMC entity has been lost. To allow us to remap this, we need to look at the underlying XML.

Properties of the entity have been moved

To do this, in solution explorer, right click on the edmx file and choose “open with”. In the displayed dialog box, choose “XML Editor” and you should now see the underlying XML.

One thing to remember is that when you delete the entity from the diagram, all you have done is delete it from the conceptual model. The relationship in storage model still exists.

So, bearing that in mind, we need to navigate to the conceptual model part of the the XML. Now, we need to add XML fragments, in four places as described below:

  1. Firstly, copy the existing AssociationSet from the Storage Model to the Conceptual Model in the entity container section and remove the word “Store” from the association attribute value. Then rename the EntitySet to the declared in the EntitySet Element (highlighted in yellow in the diagram below)
    Adding AssociationSet to XML
  2. Next, scroll to the bottom of the conceptual model and add in the association, just before the schema tag.
    re-adding association
    Note: the name in the type attribute should be the same as the name of your entity.
  3. Now, you have these two in place you should be able to open the model and see the two entities reconnected. Give this a try before going any further.

  4. Once you have, this relationship in place, you need to add the navigations back in. These need to be added in two places: to the location entity and to the NMC entity, as seen below
    Remapping naviation properties

    Okay, that’s us finished with the XML and we can do the rest through the GUI

  5. Select either of the navigation properties and remap the initial table columns to the entity by adding the table and choosing the columns from the drop down.
    re-map existing table to new associations

Returning Generic List from a WCF service

Posted: 13th January 2010 in WCF

By default a WCF or ASMX service returns list as arrays. However, this can be overcome by amending the properties of the service proxy.

Right click on the service reference and choose configure service reference and you should be presented with a screen similar to the one below.
Configuring a service to return a generic list

Choose System.Collections.Generic.List from the drop down.