My latest contract has seen my development time focus strongly on Sql Server, Xml and C# server side code.
However, lately I’ve been playing around with all the new Asp.Net 3.5 extensions (MVC) and Linq with VS 2008 and found that I could use a refresher course in all things Javascript - particularly Ajax and JSON.
I found this awesome guide which gives you a quick reminder of the useful techniques required to author some top notch client side code when building web apps.
If you want to run sql server scripts through code, you can’t just run a standard ExecuteNonQuery() with a SqlCommand. There’s problems with ExecuteNonQuery() recognizing multiple sql statements.
Don’t fear though, if you reference the Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo assemblies you get access to some goodies that’ll help you on your way!
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBName"].ConnectionString);
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(File.ReadAllText("SqlScript.sql"));
A classic problem to unit testing code that modifies the underlying data in the database tables is that you need to find a way to revert back to the original database state so that the tests can be run over and over again.
Using the transaction capabilities provided with the .Net 2.0 framework and a unit testing framework you can achieve this easily.
The process:
using System.Data;
[TestClass]
public class DataProviderTest {
private readonly DataProvider _dp = new DataProvider();
private TransactionScope _transactionScope;
[TestInitialize]
public void TestInitialize() {
_transactionScope = new TransactionScope();
}
[TestCleanup]
public void TestCleanup() {
_transactionScope.Dispose();
}
[TestMethod]
public void InsertTest()
{
// modify the database
...
}
}
It’s been a while since I’ve had the opportunity to catch up on what’s been happening in the Ent.Lib space.
I was pleasantly surprised to find two awesome new application blocks included in Ent.Lib 3.x versions:
The policy injection app block addresses Aspect Oriented programming concepts - the ability to affect code across systems using policies.
Check out a great introduction powerpoint show here.
The validation app block allows you to configure validation logic around members and methods.
You can find some details about the Validation App Block and all the latest Ent.Lib features here.
Recently I showed you how to pull sql query result sets into object instances using the XmlReader and deserialization. This article aims to show you the opposite process - persisting an object instance into a database table using serialization and Sql Server xml parsing.
The same Channel database table and entity class will be used for all examples.
/// Represents a media channel (e.g. web, mobile, iphone, etc.)
public class Channel {
private int _id;
private string _type, _description;
public Channel(){}
public Channel(string type, string description) {
_type = type;
_description = description;
}
/// Get/Set the channel identifier
public int Id {
get { return _id; }
set { _id = value; }
}
/// Get/Set the channel type (e.g. web, mobile, iphone, etc.)
public string Type {
get { return _type; }
set { _type = value; }
}
/// Get/Set the description of the channel.
public string Description {
get { return _description; }
set { _description = value; }
}
}
The sproc takes in an sql server xml datatype parameter. This is provided via a normal string representation of the xml. Within the SPROC you prepare the document for querying, pull out the values you require and perform the insert.
To understand the following SPROC, you need to have have a basic understanding of:
a) how your object instance xml serialization format looks
<?xml version="1.0" encoding="utf-16"?>
<Channel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Id>0</Id>
<Type>web</Type>
<Description>800x600 or larger browsers</Description>
</Channel>'
and
b) an understanding of xpath and xquery for pulling the values out from the xml.
Check out the - Resources - Sql Xml - for some links that might help you out.
CREATE PROCEDURE [usp_PersistChannel]
@xmldoc XML
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@iDoc int,
@type varchar(50),
@description varchar(255)
EXEC sp_xml_preparedocument
@iDoc OUTPUT,
@xmldoc,
'The following is a convenience method to help aid the serialization process of an object instance.
/// Serialize an instance of an object to a string.
public string Serialize(object instance)
{
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
XmlSerializer serializer = new XmlSerializer(instance.GetType(), null, new Type[0], null, null);
serializer.Serialize(sw, instance);
return sb.ToString();
}
/// Persist a Channel.
public void Persist(Channel channel)
{
channel.Id = Int32.Parse(
Database.ExecuteScalar(
Database.GetStoredProcCommand("usp_PersistChannel", Serialize(channel))
).ToString()
);
}
The following article tries to outline an approach for unit/integration testing using the Visual Studio Team System testing framework.
The article is long, but hopefully worth the read.
The following is taken from our good friends at Wikipedia.
Unit testing is a procedure used to validate that individual units of source code are working properly. A unit is the smallest testable part of an application, in OO the smallest unit is a method; which may belong to a base/super class, abstract class or derived/child class.
Ideally, each test case is independent from the others; mock objects and test harnesses can be used to assist testing a module in isolation. Unit testing is typically done by developers and not by end-users.
Integration testing is the phase of software testing in which individual software modules are combined and tested as a group. It follows unit testing and precedes system testing.
Integration testing takes as its input modules that have been unit tested, groups them in larger aggregates, applies tests defined in an integration test plan to those aggregates, and delivers as its output the integrated system ready for system testing.
System testing of software is testing conducted on a complete, integrated system to evaluate the system’s compliance with its specified requirements. System testing falls within the scope of black box testing, and as such, should require no knowledge of the inner design of the code or logic. [1]
As a rule, system testing takes, as its input, all of the “integrated” software components that have successfully passed integration testing and also the software system itself integrated with any applicable hardware system(s). The purpose of integration testing is to detect any inconsistencies between the software units that are integrated together (called assemblages) or between any of the assemblages and the hardware. System testing is a more limiting type of testing; it seeks to detect defects both within the “inter-assemblages” and also within the system as a whole.
Unit Tests typically are usually written with intimate knowledge of the unit being tested – which allows all paths within the unit to be covered by the tests ensuring complete code coverage.
Mocking frameworks such as Rhino provided utilities to help assert code paths are tested.
For more information, see http://en.wikipedia.org/wiki/Whitebox_testing
Dependency injection aims to solve a particular problem in designing and constructing data structures dependent on other pieces of code, in a way that minimizes the coupling between them.
Dependency injection aids in helping to test particular units of code, substituting in your own versions of the dependency, removing the need to test the dependent code.
For more information, see
Dependency Injection & Testable Objects
Inversion of Control Containers and the Dependency Injection pattern
Using the Dependency Injection pattern doesn’t require the use of a mocking framework to by-pass dependencies. It’s entirely plausible to create your own implementations and pass through. This however, will require a lot of time writing the code and cause your projects to bloat. A mocking framework should provide the facility to create dependency implementations (mocks) dynamically, taking the hard work away from the unit tester. The framework also, should provide functionality to help you assert that the mocked objects are used the way you expect them to behave.
There are various mocking frameworks out in the wild, I personally have used NMock and Rhino Mock. My experience with both favours Rhino Mock for flexibility, ease of use and performance. Type.Mock seems to get a lot of high praise too.
For more information, see:
Rhino Mocks - Introduction
Rhino Mocks - Documentation
We have a CustomerServices class that provides business functions for a Customer entity. The CustomerServices class uses a CustomerDataAccess object to persist/read information from a database. Therefore, the CustomerServices class has a dependency on the CustomerDataAcess class.
A first cut of the two classes might look like this:
public class CustomerDataAccess : ICustomerDataAccess {
public void InsertCustomer(string name)
{
// long running dependency
Thread.Sleep(10000); // 10 seconds.
}
}
public class CustomerServices {
public void AddCustomer(Customer customer){
new CustomerDataAccess().InsertCustomer(customer.Name);
}
}
As you can see when we write a unit test for the CustomerServices.AddCustomer method it will make a call to the CustomerDataAccess class and take 100 seconds to complete.
[TestMethod]
public void TestAddCustomer () {
Customer customer = new Customer();
customer.Name = "Ben";
new CustomerServices().AddCustomer(customer);
// To verify the test worked, a database lookup would be required.
}
Now we should already have appropriate unit tests for the CustomerDataAccess.InsertCustomer() method, so in our CustomerServices tests we can remove this dependency as it has already been tested.
Let’s re-write the CustomerServices class to utilize the dependency injection pattern:
public class CustomerServices {
private readonly ICustomerDataAccess _customerDataAccess;
public CustomerServices() : this (new CustomerDataAccess()) {}
// using internal prevents the constructor from being publicly
// available to other assemblies.
internal CustomerServices(ICustomerDataAccess customerDataAccess)
{
_customerDataAccess = customerDataAccess;
}
public void AddCustomer(Customer customer) {
_customerDataAccess.InsertCustomer(customer.Name);
}
}
NOTE: To allow our unit testing project to access the internal members of the project we’re testing, an attribute is added to the AssemblyInfo.cs file.
#if DEBUG
[assembly: InternalsVisibleTo("CustomerServiceTests ")]
#endif
Now we have the ability to insert our own CustomerDataAccess object, we’ll create our own mock object and use it to remove the dependency – this is for illustration purposes only.
public class MockCustomerDataAccess : ICustomerDataAccess {
private int _insertCustomerCount = 0;
public void InsertCustomer(string name) {
_insertCustomerCount++;
}
public int InsertCustomerCount {
get { return _insertCustomerCount; }
}
}
[TestMethod]
public void TestAddCustomerWithStaticMock() {
Customer customer = new Customer();
customer.Name = "Ben";
ICustomerDataAccess mock = new MockCustomerDataAccess();
CustomerServices _customerServices = new CustomerServices(mock);
_customerServices.AddCustomer(customer);
Assert.AreEqual(1, mock.InsertCustomerCount);
}
As you can see here, creating our own mocks and keeping metrics will become tiresome and mean code bloat – especially as you might need many instances of the same interface for differing code paths etc.
Don’t worry though there is a way forward – using a mocking framework like Rhino Mocks.
[TestMethod]
public void TestAddCustomerWithDynamicMock() {
// create the Rhino mock repository
_mockRepository = new MockRepository();
// get a dynamic instance of the ICustomerDataAccess interface
_dynamicMock = (ICustomerDataAccess) _mockRepository.DynamicMock(typeof(ICustomerDataAccess));
_customerServices = new CustomerServices(_dynamicMock);
// Expect the CustomerDataAccess.InsertCustomer method to be called exactly once.
_dynamicMock.InsertCustomer(null);
LastCall.IgnoreArguments().Repeat.Once();
_mockRepository.ReplayAll();
Customer customer = new Customer();
customer.Name = "Ben";
_customerServices.AddCustomer(customer);
// ensure the asserts are correct
_mockRepository.VerifyAll();
}
Using Rhino Mocks we don’t need to create hard-coded mock objects for our tests and we get built in metric checking and a whole lot more – all for free.
1. Any non-trivial piece of code.
2. All code is non-trivial.
3. Then again, something’s just aren’t worth your time.
4. Confused?
For every method that has some meat on it, there should be [TestMethod]s that test all execution paths.
Public properties that are just get/set would be a situation of overkill.
The VSTS code coverage tool is excellent for seeing what you’ve tested and what’s been missed.
To use the VSTS code coverage tool, open up VS 2005 and edit the selected test configuration run:
Click for Full Size Image
Then select the assemblies you wish to monitor:
Then all you have to do is run your tests (but not in debug mode) for it to calculate the metrics. Once finished open up the Code Coverage Window and inspect the results.
Click for Full Size Image
We can still use the VSTS unit testing framework to write integration tests. The difference though, comes from the point of view that the test methods will not substitute in mocks for the dependencies, allowing the tests to run end-to-end testing a module/function of code right through.
These days there seems to be an infinte number of ways to take data returned from the database and map them into your object model.
This article aims to simple show you how to generically map the sql select result set into a list of object instances in your code.
First things first, the code illustrated here requires Sql Server 2005, and .Net 2.0+ framework.
It doesn’t matter whether you write you queries in code or by using Sql Server SProcs, simple by adding - ‘FOR XML Path(’ElementName’) - the following to the end of you Select statement, Sql Server will return your result sets in Xml.
Syntax:
SELECT * FROM [TableName] WITH (nolock) FOR XML PATH('TableName')
Example:
SELECT * FROM [Channel] WITH (nolock) FOR XML PATH('Channel')
Results:
<Channel>
<Id>1</Id>
<Type>web</Type>
<Description>standard internet</Description>
</Channel>
<Channel>
<Id>3</Id>
<Type>mobile</Type>
<Description>mobile</Description>
</Channel>
The parameter passed into the Path() statement determines the top level element name - allowing you to shape the xml.
Using the .Net built in xml to class deserialization process, the returned xml will map the top level element to a class name and each sub-element to a public property/member on the object class.
Let’s create the Plain Old C# Object to enable deserialization.
/// Represents a media channel (e.g. web, mobile, iphone, etc.)
public class Channel {
private int _id;
private string _type, _description;
public Channel(){}
public Channel(string type, string description) {
_type = type;
_description = description;
}
/// Get/Set the channel identifier
public int Id {
get { return _id; }
set { _id = value; }
}
/// Get/Set the channel type (e.g. web, mobile, iphone, etc.)
public string Type {
get { return _type; }
set { _type = value; }
}
/// Get/Set the description of the channel.
public string Description {
get { return _description; }
set { _description = value; }
}
}
NOTE: If there are differences in your database schema naming conventions to how you name things in your classes you will need to shape the xml somewhere to make the deserialization process work.
You have two options: you can decorate your class with appropriate Xml Serialization Attributes OR you can change your Sql Query.
Example: the Channel table has a column name desc but you need to map to the public property name Description.
Scenario 1: Shape the xml using the [XmlAttribute]
[XmlAttribute("desc")]
public string Description {
get { return _description; }
set { _description = value; }
}
OR
Scenario 2: Shape the xml in your Sql Query
SELECT [Id], [Type], [Desc] as Description
FROM [Channel]
WITH (nolock)
FOR XML PATH('Channel')
Ok. You know how to get the data back from the DB in Xml. Now it’s time to read the results into a list of the entity POCOs.
private static readonly IDictionary xmlSerializers = new Dictionary();
/// Generic helper method for reading sql server table rows into entities.
internal IList GetEntities(DbCommand dbCommand) {
IList entities = new List();
using (XmlReader reader = ((SqlDatabase) Database).ExecuteXmlReader(dbCommand)) {
while (!reader.EOF) {
if (reader.IsStartElement()) {
entities.Add(Deserialize(reader.ReadOuterXml()));
}
}
}
return entities;
}
/// Deserialize an xml fragment into the specified Type.
public T Deserialize(string xml) {
using (StringReader sr = new StringReader(xml)) {
if (!xmlSerializers.ContainsKey(typeof (T))){
xmlSerializers.Add(typeof (T), new XmlSerializer(typeof (T), null, new Type[0], null, null));
}
XmlSerializer serializer = xmlSerializers[typeof (T)];
return (T) serializer.Deserialize(sr);
}
}
NOTE: I cache an XmlSerialzer instance for each Type because the .Net framework generates a class dynamically at runtime when the new XmlSerializer() constructor is called. If you have thousands of results coming back from the DB this is timely and memory consuming.
/// Get a specific Channel.
public Channel GetChannel(string name) {
IList list = GetEntities(Database.GetStoredProcCommand(”usp_GetChannels”, 0, name));
return (list != null && list.Count == 1) ? list[0] : null;
}
/// Get a list of all Channels.
public IList GetChannels() {
return GetEntities(Database.GetStoredProcCommand(”usp_GetChannels”, 0, null));
}
That’s it!
Update: 18-Feb-08 I’ve written a followup article that shows how to do the reverse process - Persisting objects into database using xml serialization
