Categories
C#

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

<ArrayOfProject>
  <Project>
    <ProjectId>1</ProjectId>
    <ProjectName>First Project</ProjectName>
    <OrderNo>123456</OrderNo>
    ...
    <RowVer>AAAAAAA5MPA=</RowVer>
    <UpdatedByName>jonnus</UpdatedByName>
  </Project>
</ArrayOfProject>

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(
    ResourceFileName.Project));
var reader = new StreamReader(stream);
var serializer = new XmlSerializer(typeof(
    Collection<Project>));
IEnumerable<Project> projects = 
    (Collection<Project>)serializer.Deserialize(reader);

(Source code inspired by https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/)

Categories
C#

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 : 
    BaseRepository<Delivery_Schedule>, 
    IDeliveryRepository {

    private readonly IUpdateableRepository<Delivery>
      _updateableRepository;

    public DeliveryScheduleRepository() {
      _updateableRepository = new 
        UpdateableRepository<Delivery>();
    }

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

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 = 
            HttpContext.Current.User.Identity.Name;
  
          db.Set<T>().Attach(model);

          db.Entry(model).State = EntityState.Modified;
        }
        db.SaveChanges();
      }
      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.

Categories
Uncategorised

Chat Bot v0.1

After playing around this evening by following this Channel 9 tutorial – I got the following up and running.

If you’re interested, you should say hello, or ask about the weather in a place, or ask about “jonnus”

I intend to progress this further following information at this link

Categories
Process

The Scrum Board

Here is an example of how or Scrum Board looks. It’s not the real one as they’re not a fan of taking pictures at work, bit it gives you the idea. While the focus on Scrum Board is always on moving post-its from “To Do” to “Completed”, there’s more to it than that if you want to effectively communicate the progress of the Sprint.

Categories
Process

Agile Scrum, and how it is used in Practice

As anyone with experience of Agile Scrum knows, there’s no one size fits all solution to applying it to your project.  As a Scrum master with nearing a year’s worth of practical experience, here I aim to describe how we are using Agile SCRUM to develop applications within our business.

Categories
SSRS Uncategorised

Set Default values but don’t Run the Report

You’ve set up your report and it looks exactly as required, but due to your pre-defined parameters your report returns every record in the table when it first runs (just in case), and your users will probably want to filter the options.

This means there is an unnecessary wait for the report to first render, only for it to change.

Categories
SSRS Uncategorised

How to use Lookup in SSRS

You may be faced with a problem similar to this, where the user wants to display a particular currency symbol that is different for each locality.  It could be possible to alter the underlying dataset to return this information as well as the actual value, but it is entirely possible from within SSRS, as follows

Categories
SSRS Uncategorised

How can I work out Row numbers when Row_Number doesn’t work?

For a variety of reasons (such as this one, where the alteration of the underlying dataset is not allowed) it is sometimes not possible to just calculate the row number for a table using Row_Number.

In these instances you can instead use a combination of CountDistinct and RunningValue, as I mentioned in my original answer.

Categories
SSRS Uncategorised

Use Rectangles to layout your data

Can’t using SSRS be limiting at times?  There you are trying to draw a simple rectangle in a table and the cell just keeps setting the rectangle to be the size of the cell – but you want it another size!  As originally posted.

The trick here is to remember there is nothing stopping you adding another rectangle inside this first rectangle