Get XML Data for mocking a Database Table

When creating a mock of a Database Table, it is helpful to have some pre-existing data rather than creating it all from scratch.
This can be accomplished by outputting the current content of a table to XML, then using this XML as the data source to test against.
To get hold of the data, in this example for the Project Table, for projects 1 to 5, one can use the following code

DECLARE @xmldata XML;
SET @xmldata = (
     SELECT *
     FROM [Project]
     WHERE ProjectId IN (1, 2, 3, 4, 5)
     FOR xml AUTO, ROOT ('ArrayOfProject'), ELEMENTS
SELECT @xmldata AS returnXml;

This will output the following data

    <ProjectName>First Project</ProjectName>

Note that the table name in the XML must match the name of the class in the data layer for it t map properly. This exported XML data can then be imported into a Resources (resx) file with the key “Project”, and used as the seed to create a list of projects for use by your unit tests as follows

var stream = new MemoryStream(Encoding.UTF8.GetBytes(
var reader = new StreamReader(stream);
var serializer = new XmlSerializer(typeof(
IEnumerable<Project> projects = 

(Source code inspired by

Using a generic Update method to reduce repeated code

The problem

I have an application that is using Entity Framework to connect to the underlying SQL database. A number of these tables allow items to be updated, but not all of them. Each of the tables that can be updated have the following properties

  • dbo.Delivery
    • Columns
      • Id (PK, int, not null)
      • Project_Id (FK, int, not null)
      • Project_Date (FK, datetime, not null)

      • Updated_By_Name (nvarchar(100), null)
      • Updated_By_Date (datetime, null)
      • RowVer (timestamp, not null)

How am I to create a generic class that will allow me to update the tables that have these properties – but not other tables in the database? And without breaking the DRY principle by creating duplicate repetitive code in each of the repositories?

The approach I have taken is to declare a new interface IUpdateable, that all the updateable tables have applied to them, and defines the fields that they must have

namespace Application.Data.Interfaces {
   public interface IUpdateable {
     string Updated_By_Name { get; set; }
     DateTime? Updated_By_Date { get; set; }

As the model is created and updated using the edmx save operation, I can’t rely on any changes I make to the classes themselves being maintained. An approach would be to update the tt file to detect if the properties listed above exist in the table, but this is slightly out of scope for the moment.

Instead I have created partial classes that extend the automatically generated ones, for example

namespace Application.Data {
  public partial class Delivery : IUpdateable {

This means that the Delivery class implements the IUpdateable interface.

To perform the update itself I can then declare in my Repository an object of the UpdateableRepository that will handle objects of the type Delivery, and call the UpdateAll method upon it, for example

namespace Application.Data.Repositories {
  public class DeliveryRepository : 
    IDeliveryRepository {

    private readonly IUpdateableRepository<Delivery>

    public DeliveryScheduleRepository() {
      _updateableRepository = new 

    public bool UpdateAll(List<Delivery> modelList) {
      return _updateableRepository.UpdateAll(

Finally, I can create the generic class UpdateableRepository that will contain my common UpdateAll method

namespace Application.Data.Repositories {
  public class UpdateableRepository<T> 
    where T : class, IUpdateable {
    public bool UpdateAll(List<T> modelList) {
     using (PIMMSEntities db = new PIMMSEntities()) {
        foreach (T model in newItemsList) {
          model.Updated_By_Date = DateTime.Now;
          model.Updated_By_Name = 

          db.Entry(model).State = EntityState.Modified;
      return true;

In this code the line

where T : class, IUpdateable

mandates that the item of type T must be a class, and it must implement IUpdateable.

Due to this restriction we are then allowed to use the common properties of IUpdateable Updated_By_Date and Updated_By_Name within the generic method as all classes that attempt to call this code must have these properties.

Now this might not look like it’s worth the effort at the moment, but if I want to make changes to this UpdateAll method I now only have to do it in one place. For example in my actual application the UpdateAll method first performs a concurrency check against an existing list of items using the RowVer column described above (that is also added to the IUpdateable interface), adds new items if they don’t already exist, and a clearing of a cache if it exists. All of this would have to be repeated multiple times without using this approach.

A similar approach can be taken to adding deletion features to specific tables, rather than making this entirely generic and able to be used by repositories that should not allow data to be deleted from them.

In the actual application I have also extended this approach to assist with Unit testing by ensuring that the UpdateableRepository implements its own interface IUpdateableRepository<T> which declares the UpdateAll method as mandatory.