The Task

The first step is to brainstorm a list of tests that are needed for completion. The task overall is defined as building a read-only data access layer to support the retrieval of a Recording entity and all its associated entities. The existing database schema (described in Chapter 4) is shown in Figure 5-1.

Figure 5-1: Recordings data model

So, given this schema and the task definition, what are the types of things we should test?

• Connecting to the database

• Individual entities in isolation (Recording, Label, Review, Reviewer, Track, Artist, and Genre )

• Relationships between entities (Review-Reviewer, Track-Genre, Track-Artist, Recording-Label, Recording-Reviews, Recording-Artist, Review-Recording, and Track-Recording)

• Retrieve a Recording and all its associated entities by specifying the recording id

The detail associated with this test list is much more typical than what was shown in Chapter 2, Test-Driven Development in .NET ”By Example. As we elaborate the solution, we will augment the list with more detail. Looking at the list, we have to be able to connect to the database to do anything, so we will begin by writing tests to connect to the database first.

Connecting to the Database

ADO.NET provides a set of classes to facilitate the database access, as well as several provider-specific packages that implement database access. Because our database is SQL Server 2000, we will use the SQL Server-specific provider that ships with the standard .NET framework distribution. Following is the code that establishes a database connection expressed as a test:

 using System; using System.Data; using System.Data.SqlClient; using NUnit.Framework; [TestFixture] public class SqlConnectionFixture {    [Test]    public void ConnectionIsOpen()    {       SqlConnection connection =           new SqlConnection(            @"server=(local);database=catalog;Trusted_Connection=true");       connection.Open();       Assert.AreEqual(ConnectionState.Open, connection.State);       connection.Close();    } } 

Let s look at this test in detail. First, we construct a SqlConnection object by passing a connection string to the constructor; the syntax of this connection string is provider-specific, and in our example it contains the server information and the authentication method. Second, the database connection is not considered opened when it is constructed and needs to be opened explicitly. After the connection has been opened, we can verify its state by accessing the State property on the connection object; the ConnectionState enumeration defines the common connection states. When we finish with the connection, we use the Close method to close the connection.

Note  

With connection pooling in place, closing the connection does not actually tear down the established link to the database; the connection is simply returned to the pool, in which it stays opened until either it is requested by the application again or, after a period of being inactive, it is closed by the pool manager.

A Small Improvement

As you probably noticed, the connection string has hard-coded connection information, which is environment-specific and should not be hard-coded in the test or application code. There are a variety of ways to pass this information to the application. One of the more common methods is to read the connection string from a configuration file using the ConfigurationSettings class. Let s alter the test code to use the configuration file:

 using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using NUnit.Framework; [TestFixture] public class SqlConnectionFixture {    [Test]    public void ConnectionIsOpen()    {       string connectionString =           ConfigurationSettings.AppSettings.Get("Catalog.Connection");       Assert.IsNotNull(connectionString);       SqlConnection connection =           new SqlConnection(connectionString);       connection.Open();       Assert.AreEqual(ConnectionState.Open, connection.State);        connection.Close();    } } 

This test compiles, but it fails when we try to run it because we need to create a configuration file that contains the connection string. For example

 <?xml version="1.0" encoding="utf-8" ?> <configuration>    <appSettings>       <add key="Catalog.Connection"          value="server=(local);database=catalog;Trusted_Connection=true" />     </appSettings> </configuration> 

An application configuration file can have many sections. In this example, we placed the connection string in the appSettings section. This section contains a collection of key-value pairs. We placed the connection property under the Catalog.Connection key.

When you are using NUnit and configuration files, the name of the configuration file must be the same as the name of the assembly with the .config suffix appended. For example, the assembly that contains the tests is named DataAccessLayer.dll. The name of the configuration file must be named DataAccessLayer.dll.config and it must be placed in the same directory as the assembly itself for the NUnit executable to locate it.

We re Not Finished Yet

Looking at the test, you will notice that there are two unrelated assert methods: one to verify that a connection string is retrieved from the configuration file and the other to check whether we can open the connection. Because these two things test two different things, they should be separate tests.

Separating the Tests     We need to move the test that verifies that the connection string is read correctly from the configuration file into a new test. The following is the new test:

 [Test]    public void CanRetrieveConnectionString()    {       string connectionString =           ConfigurationSettings.AppSettings.Get("Catalog.Connection");       Assert.IsNotNull(connectionString);    } 

Creating an additional test forces us to duplicate the retrieval of the connection string from the configuration file in each test. Therefore, we need to refactor the code so there is no duplication. We move the retrieval to a new method named RetrieveConnectionString and mark it with the SetUp attribute, which ensures that the connection string will be retrieved from the configuration file prior to each test being executed by NUnit. After we move the retrieval to the RetrieveConnectionString method, we need to modify the test methods to use it. Here is the code after the refactoring is completed:

 [TestFixture] public class SqlConnectionFixture {    private string connectionString;     [SetUp]    public void RetrieveConnectionString()    {       connectionString =           ConfigurationSettings.AppSettings.Get("Catalog.Connection");    }    [Test]    public void CanRetrieveConnectionString()    {       Assert.IsNotNull(connectionString);    }    [Test]    public void ConnectionIsOpen()    {       SqlConnection connection =           new SqlConnection(connectionString);       connection.Open();       Assert.AreEqual(ConnectionState.Open, connection.State);        connection.Close();    } } 

Now that we have established the connection to the database, we can move on to accessing the individual entities in the database.

Individual Entities in Isolation

The next item in the test list is to read individual entities from the database and check to see whether they are correct. To satisfy the need for executing the tests in a well-known state, we first have to insert an entity into the database, retrieve it, and then delete it after we are finished. We want to be able to look at each entity without having to create all the other entities, so we will use a typed DataSet to achieve this isolation. The following section describes how to construct a typed DataSet for the recording database.

Defining a Typed DataSet for the Recording Database

In this step, we will create a typed DataSet to capture our data schema. Because we are working with an existing database schema and we already know all the data tables and all the relationships between the entities, we will define the typed DataSet in one step instead of building it up incrementally. Figure 5-1 shows the relational data model we described in Chapter 4. The typed DataSet s schema is defined and stored in an eXtensible Schema Definition (XSD) schema file. (A good discussion of XSD schemas can be found in Essential XML Quick Reference by Aaron Skonnard and Martin Gudgin . ) The schema file can then be used by the xsd.exe tool to generate the code for the typed DataSet class.

The typed DataSet schema file can be produced either completely manually or with the help of the VisualStudio.NET DataSet Wizard. (There is a good discussion on how to create a typed DataSet in Pragmatic ADO.NET . We will not go into details of explaining how the wizard works, but will rather outline the process we went through to create the typed DataSet for the recording database.)

The Recording entity is central to the existing data model. It represents information about the recording and all its associated/dependent objects. We start by creating a blank DataSet named RecordingDataSet . The next step is to drag and drop the tables from the database server catalog onto the design palette. We then give meaningful names to the primary key columns for our tables. The wizard will create the tables using only the information available in the database schema; we can further refine the definition of our typed DataSet by annotating the elements with additional information about the data. The xsd.exe tool will understand and use these additional attributes when it generates the code. These additional attributes are defined in a special namespace: urn:schemas-microsoft-com:xml-msprop . Following is a fragment from the schema file that describes the Review table:

 <xs:element name="Review" codegen:typedName="Review"  codegen:typedPlural="Reviews">    <xs:complexType>       <xs:sequence>          <xs:element name="id" type="xs:long" codegen:typedName="Id" />          <xs:element name="reviewerid" type="xs:long" minOccurs="0"  codegen:typedName="ReviewerId"/>          <xs:element name="recordingid" type="xs:long" minOccurs="0"  codegen:typedName="RecordingId"/>          <xs:element name="rating" type="xs:int" codegen:typedName="Rating" />          <xs:element name="review" type="xs:string" minOccurs="0"  codegen:typedName="Content" />       </xs:sequence>    </xs:complexType> </xs:element> 

A few elements here need a bit more explanation:

• The attribute codegen:typedName="Review" is an example of an annotation added to instruct the xsd.exe tool to use the name Review for a row of data in the typed DataSet .

• The attribute codegen:typedName="Id" is used to define the name for the id property of the Review row. Without this attribute, the property would be called id (which violates a C# naming convention for properties ”the names should begin with a capital letter).

• The attribute minOccurs="0" on the review element indicates that this column allows null values in the database.

After the data tables are defined, we can add relationships. The relationships are not created automatically when we import the tables from the database ”we need to do it manually. There are a couple of ways to define relationships between the tables: using key constraints or using unconstrained relationships. A DataSet object is designed to look and behave very similarly to the database, and the relationships in the database can be either enforced (using referential integrity constraints) or simply defined (using foreign key/primary key references). There are many benefits to enforcing the relationships in the database. (A high degree of data integrity is one of them.) However, enforcing the relationships in the typed DataSet makes the process of incremental code development very difficult because the objects are harder to isolate from their relationships. We want to be able to test individual objects in separation and then to test the relationship that exists between objects. For this reason, we decided not to enforce the relationships on the typed DataSet level and limit the referential integrity rules to the database. Because we will be writing plenty of tests to verify the correctness of the relationships between the objects in the typed DataSet , we will ensure that we do not break anything.

To define the relationships in our typed DataSet that are not enforced, we will use elements from the namespace urn:schemas-microsoft-com:xml- msdata . Following is an example of the relationship between the Recording and its Label:

 <msdata:Relationship name="LabelOfTheRecording" msdata:parent="Label"  msdata:child="Recording" msdata:parentkey="id" msdata:childkey="labelid" /> 

After we have completed the definition of the schema for the typed DataSet , we can generate the code for the RecordingDataSet . VisualStudio.NET automatically runs the DataSet code-generation tool when the project is built. Now we re ready to write code that uses this typed DataSet .

Artist Gateway

Artist is the first database table we will work on. What we want to have is an object that contains all the SQL for accessing the Artist table. This includes selects, inserts , updates, and deletes. The rest of the code will call the methods in this object for all the interaction with this table. Encapsulating all the access into a single object is a common design pattern, which is called a Table Data Gateway. For more detailed information, see Patterns of Enterprise Application Architecture . The first test inserts a new Artist into the database with known values, retrieves the newly inserted Artist , and verifies that the data from the database matches the values that were used to create the entity.

Primary Key Management     In thinking about this test, we realize that we need to decide how to handle the primary keys for our data. There are several strategies to handle the primary keys for relational tables; the two most common ones are the following:

• Use an existing field that is unique (for example, use SSN as primary key for a person) or use a unique combination of existing fields (for example, a person s first name, last name, date, and place of birth).

• Use a synthetic primary key ”this is the numeric column added to the table to serve as the primary key.

We will use synthetic primary keys because they are more efficient (indexing on a single numeric column is very fast and space-efficient), and our data model does not have a good candidate for native unique keys.

After we decide to use the synthetic primary keys, we need to decide how to generate them. Once again, there are several typical approaches:

• Use the database server facilities to manage primary key generation; most database servers have such capabilities, but they are all vendor- specific. For example, Oracle uses sequences for generation of unique ids, and SQL Server uses AutoIncrement columns.

• Let the application manage generation of the primary key values; this approach is more generic and allows possible implementations of different strategies for id generation.

We decided to go with the second option because we have an opportunity to better isolate our data objects from the database. We will be using a separate table, PKSequence, in the database to keep track of the ids for our data tables. This table has two columns:

• tableName The name of the data table for which we keep the id.

• nextid The value for the next id.

Having this separate table allows us to manage the range of ids for each data table. There is one downside to choosing this approach ”we will need at least two trips to the database when we are inserting a new entity. The first trip is needed to retrieve the unique id. The second trip is to insert the record into the database. Even with this as a known issue, let s proceed with this strategy to see how it works out. Let s write the test now. Remember, it has to insert an Artist into the database, retrieve it and verify that it is correct, and then remove it.

ArtistFixture.cs

 using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using DataAccessLayer; using NUnit.Framework; [TestFixture] public class ArtistFixture {    [Test]    public void RetrieveArtistFromDatabase()    {       string artistName = "Artist";       SqlConnection connection =           new SqlConnection(             ConfigurationSettings.AppSettings.Get( "Catalog.Connection"));       connection.Open();       ArtistGateway gateway = new ArtistGateway(connection);       long artistId =           gateway.Insert(new RecordingDataSet(),artistName);              RecordingDataSet loadedFromDB = new RecordingDataSet();       RecordingDataSet.Artist loadedArtist =           gateway.FindById(artistId, loadedFromDB);       Assert.AreEqual(artistId,loadedArtist.Id);       Assert.AreEqual(artistName, loadedArtist.Name);          connection.Close();    } } 

As you can see in the test code, the ArtistGateway is not responsible for establishing the connection or for the creation of the RecordingDataSet; the callers of the gateway are responsible for setting up these objects and passing them to the gateway. The reasoning is that we want to be able to control the lifetime of the RecordingDataSet that the gateway works with. In the test, we created two RecordingDataSet objects: one to create a new Artist and one to load an Artist from the database. We did not use the same RecordingDataSet object because we wanted to be sure that we actually loaded the new artist from the database and that we did not work with a cached in-memory version of it. As you will see, being able to control the RecordingDataSet outside of the gateway is important when we add relationships between objects. We also pass a connection object to the gateway because we want to be able to control the transactional boundaries outside of the gateway. For example, our test may want to roll back the transaction to undo the changes we made to the database. See Chapter 10 for a detailed description of working with transactions.

The following is the ArtistGateway implementation needed to support the first test:

 using System; using System.Data; using System.Data.SqlClient; namespace DataAccessLayer {    public class ArtistGateway    {       private SqlDataAdapter adapter;        private SqlConnection connection;       private SqlCommand command;       private SqlCommandBuilder builder;       public ArtistGateway(SqlConnection connection)       {          this.connection = connection;          command = new SqlCommand(             "select id, name from artist where id = @id",             connection);          command.Parameters.Add("@id",SqlDbType.BigInt);          adapter = new SqlDataAdapter(command);          builder = new SqlCommandBuilder(adapter);       }       public long Insert( RecordingDataSet recordingDataSet, string artistName)       {          long artistId =  GetNextId(recordingDataSet.Artists.TableName);                    RecordingDataSet.Artist artistRow =              recordingDataSet.Artists.NewArtist();          artistRow.Id = artistId;          artistRow.Name = artistName;          recordingDataSet.Artists.AddArtist(artistRow);          adapter.Update(recordingDataSet,           recordingDataSet.Artists.TableName);          return artistId;       }       public RecordingDataSet.Artist           FindById(long artistId, RecordingDataSet recordingDataSet)       {          command.Parameters["@id"].Value = artistId;          adapter.Fill(recordingDataSet,              recordingDataSet.Artists.TableName);          DataRow[] rows = recordingDataSet.Artists.Select(             String.Format("id={0}",artistId));                    if(rows.Length < 1) return null;          return (RecordingDataSet.Artist)rows[0];        }       public long GetNextId(string tableName)       {          SqlTransaction transaction =              connection.BeginTransaction(             IsolationLevel.Serializable, "GenerateId");          SqlCommand selectCommand = new SqlCommand(             "select nextId from PKSequence where tableName = @tableName",                  connection, transaction);          selectCommand.Parameters.Add("@tableName",              SqlDbType.VarChar).Value=tableName;          long nextId = (long)selectCommand.ExecuteScalar();          SqlCommand updateCommand = new SqlCommand(          "update PKSequence set nextId = @nextId where tableName=@tableName",             connection, transaction);          updateCommand.Parameters.Add("@tableName",              SqlDbType.VarChar).Value=tableName;          updateCommand.Parameters.Add("@nextId",              SqlDbType.BigInt).Value=nextId+1;          updateCommand.ExecuteNonQuery();          transaction.Commit();          return nextId;       } 

At first glance, you might think that is a lot of code for just one test. It is, we do not disagree , but there are two factors at play. The first is that conceptually we are testing one thing: the ability to retrieve an Artist from the database. The fact that we need all this code to do that is an issue, but it is not a big enough issue that we would want to split this up into multiple tests. Second, if the tests were more granular, we would be testing the ADO.NET API, which is something that we do not recommend. Let s drill down into the code to see what s really going on:

• The Insert method must be supplied with all the required data values so that all columns in the database that do not allow nulls can be set properly.

• The id field is required but is not supplied in the signature of the Insert method; because we decided to use the synthetic primary keys approach, the Insert method will generate a new key and return it after a new record is successfully created.

• The FindById method refreshes the data in the RecordingDataSet with data from the database by calling Fill on the DataAdapter .

• The GetNextId method does two database calls wrapped in one database transaction ( select and update ), which is necessary to ensure the data consistency. We will be talking more about concurrency and transactions in later chapters when we describe updating data.

The test runs and it is successful, but we decided to spot check the database and discovered that we did not clean up the database after the test was run. A brief examination of the test code reveals that we did not delete the artist that we inserted. As mentioned at the beginning of this chapter, testing code that has persistent side effects is harder because we have to remove the inserted entities explicitly. In this case, we have to provide the ability to delete an artist, even though we do not have a customer requirement. Let s modify the test to delete the inserted record. The change is in boldface in the following code:

 [Test]    public void RetrieveArtistFromDatabase()    {       string artistName = "Artist";       SqlConnection connection =           new SqlConnection(             ConfigurationSettings.AppSettings.Get(                "Catalog.Connection"));       connection.Open();       ArtistGateway gateway = new ArtistGateway(connection);       long artistId =           gateway.Insert(new RecordingDataSet(),artistName);              RecordingDataSet loadedFromDB = new RecordingDataSet();       RecordingDataSet.Artist loadedArtist =           gateway.FindById(artistId, loadedFromDB);       Assert.AreEqual(artistId, loadedArtist.Id);       Assert.AreEqual(artistName, loadedArtist.Name);  gateway.Delete(loadedFromDB, artistId);  connection.Close();    } 

The test calls a function on the ArtistGateway called Delete to remove the entity from the database. Here is the implementation:

 public void Delete(RecordingDataSet recordingDataSet, long artistId)    {       RecordingDataSet.Artist loadedArtist =           FindById(artistId, recordingDataSet);       loadedArtist.Delete();                       adapter.Update(recordingDataSet,       recordingDataSet.Artists.TableName);     } 

We compile and run the test, and it passes . We also looked at the Artist table in the database and verified that the test data was not left in the database. Let s now write a test that explicitly verifies that the Delete method works.

 [Test]    public void DeleteArtistFromDatabase()    {       string artistName = "Artist";       SqlConnection connection =           new SqlConnection(          ConfigurationSettings.AppSettings.Get(          "Catalog.Connection"));       connection.Open();       ArtistGateway gateway = new ArtistGateway(connection);       long artistId =           gateway.Insert(new RecordingDataSet(),artistName);              RecordingDataSet emptyDataSet = new RecordingDataSet();       long deletedArtistId =           gateway.Insert(emptyDataSet,"Deleted Artist");       gateway.Delete(emptyDataSet, deletedArtistId);       RecordingDataSet.Artist deleletedArtist =           gateway.FindById(deletedArtistId, emptyDataSet);       Assert.IsNull(deleletedArtist);       connection.Close();    } 

We compile and run the tests, and they both pass. Finally, we spot-check the database again just to make sure there are no leftover remnants. Before we move on to the next test, it looks like the test code has several code duplications, so it s time to refactor to remove the duplication. The duplications include the open and closing of the connection and the insertion of the test Artist into the database. We need to use the SetUp/TearDown capability of NUnit to remove this duplication. We will put the opening of the connection and the insertion of the test data into the SetUp method and the deletion of the test data and the closing of the connection into the TearDown method. We do this type of refactoring so often that we refer to it as a SetUp refactoring. Here is the ArtistFixture after the refactoring:

 [TestFixture] public class ArtistFixture {    private static readonly string artistName = "Artist";    private SqlConnection connection;    private ArtistGateway gateway;     private RecordingDataSet recordingDataSet;    private long artistId;     [SetUp]    public void SetUp()    {       connection = new SqlConnection(          ConfigurationSettings.AppSettings.Get(          "Catalog.Connection"));       connection.Open();       recordingDataSet = new RecordingDataSet();       gateway = new ArtistGateway(connection);       artistId = gateway.Insert(recordingDataSet,artistName);    }    [TearDown]    public void TearDown()    {       gateway.Delete(recordingDataSet, artistId);       connection.Close();    }    [Test]    public void RetrieveArtistFromDatabase()    {       RecordingDataSet loadedFromDB = new RecordingDataSet();       RecordingDataSet.Artist loadedArtist =           gateway.FindById(artistId, loadedFromDB);       Assert.AreEqual(artistId,loadedArtist.Id);       Assert.AreEqual(artistName, loadedArtist.Name);       }    [Test]    public void DeleteArtistFromDatabase()    {       RecordingDataSet emptyDataSet = new RecordingDataSet();       long deletedArtistId = gateway.Insert(emptyDataSet,"Deleted Artist");       gateway.Delete(emptyDataSet,deletedArtistId);       RecordingDataSet.Artist deleletedArtist =           gateway.FindById(deletedArtistId, emptyDataSet);       Assert.IsNull(deleletedArtist);    } } 

Now that we have tests to retrieve and delete an Artist , let s add a test to update an artist already in the database.

 [Test] public void UpdateArtistInDatabase() {    RecordingDataSet.Artist artist = recordingDataSet.Artists[0];    artist.Name = "Modified Name";    gateway.Update(recordingDataSet);    RecordingDataSet updatedDataSet = new RecordingDataSet();    RecordingDataSet.Artist updatedArtist =     gateway.FindById(artistId, updatedDataSet);    Assert.AreEqual("Modified Name", updatedArtist.Name); } 

This test specifies a new method in the ArtistGateway named Update , which needs to be added. The following is the implementation of Update :

 public void Update(RecordingDataSet recordingDataSet)    {       adapter.Update(recordingDataSet,          recordingDataSet.Artists.TableName);    } 

We compile the code and run the tests, and they all pass. Just because we are paranoid , we do check the database to make sure that we cleaned up correctly and there were no leftover test artists. That is all the work that we will do on the Artist table for now. Let s move on to the Genre table.

Genre Gateway

Let s write the same first test that we did for Artist . As you recall, we need to insert a Genre into the database, read it back, verify the contents, and then delete it. We will use the ArtistFixture code as a model because it is very likely that we will need to do similar tests for Genre that we did for Artist . Here is the GenreFixture with the RetrieveGenreFromDatabase test implemented:

 [TestFixture] public class GenreFixture {    private static readonly string genreName = "Rock";    private SqlConnection connection;    private GenreGateway gateway;     private RecordingDataSet recordingDataSet;    private long genreId;     [SetUp]    public void SetUp()    {       connection = new SqlConnection(          ConfigurationSettings.AppSettings.Get(          "Catalog.Connection"));       connection.Open();       recordingDataSet = new RecordingDataSet();       gateway = new GenreGateway(connection);       genreId = gateway.Insert(recordingDataSet, genreName);    }    [TearDown]    public void TearDown()    {       gateway.Delete(recordingDataSet, genreId);       connection.Close();    }    [Test]    public void RetrieveGenreFromDatabase()    {       RecordingDataSet loadedFromDB = new RecordingDataSet();       RecordingDataSet.Genre loadedGenre =           gateway.FindById(genreId, loadedFromDB);       Assert.AreEqual(genreId, loadedGenre.Id);       Assert.AreEqual(genreName, loadedGenre.Name);       } } 

In a similar fashion, we will use the code from ArtistGateway as a model for the GenreGateway . Here is the code that is needed for the first test:

 public class GenreGateway    {       private SqlDataAdapter adapter;        private SqlConnection connection;       private SqlCommand command;       private SqlCommandBuilder builder;       public GenreGateway(SqlConnection connection)       {          this.connection = connection;          command = new SqlCommand(             "select id, name from Genre where id = @id",             connection);          command.Parameters.Add("@id",SqlDbType.BigInt);          adapter = new SqlDataAdapter(command);          builder = new SqlCommandBuilder(adapter);       }       public long Insert(RecordingDataSet recordingDataSet,  string genreName)       {          long genreId = GetNextId(recordingDataSet.Genres.TableName);                    RecordingDataSet.Genre genreRow =              recordingDataSet.Genres.NewGenre();          genreRow.Id = genreId;          genreRow.Name = genreName;          recordingDataSet.Genres.AddGenre(genreRow);          adapter.Update(recordingDataSet,  recordingDataSet.Genres.TableName);          return genreId;       }       public RecordingDataSet.Genre           FindById(long genreId, RecordingDataSet recordingDataSet)       {          command.Parameters["@id"].Value = genreId;          adapter.Fill(recordingDataSet,  recordingDataSet.Genres.TableName);          DataRow[] rows = recordingDataSet.Genres.Select(             String.Format("id={0}",genreId));                    if(rows.Length < 1) return null;          return (RecordingDataSet.Genre)rows[0];       }       public void Delete(RecordingDataSet recordingDataSet,  long genreId)       {          RecordingDataSet.Genre loadedGenre =              FindById(genreId, recordingDataSet);          loadedGenre.Delete();          adapter.Update(recordingDataSet,  recordingDataSet.Genres.TableName);       }       public long GetNextId(string tableName)       { /* same as in ArtistGateway */ }    } 

There is some code duplication present in the GenreGateway as well as in the GenreFixture . The first and most obvious duplication is the GetNextId method, which is identical in the GenreGateway and the ArtistGateway classes. We need to move it out of the two gateway classes into something that can be shared between the two classes. The first step in the extraction is to write a test that tests the GetNextId method explicitly. We are putting this test in a separate fixture named IdGeneratorFixture because it is no longer logically part of the ArtistFixture or GenreFixture .

 [TestFixture] public class IdGeneratorFixture {    private SqlConnection connection;    [SetUp]    public void OpenConnection()    {       connection = new SqlConnection(          ConfigurationSettings.AppSettings.Get(          "Catalog.Connection"));       connection.Open();    }    [Test]    public void GetNextIdIncrement()    {       SqlCommand sqlCommand = new SqlCommand(          "select nextId from PKSequence where tableName=@tableName",          connection);                 sqlCommand.Parameters.Add(       "@tableName",SqlDbType.VarChar).Value="Artist";              long nextId = (long)sqlCommand.ExecuteScalar();       long nextIdFromGenerator =        IdGenerator.GetNextId("Artist", connection);       Assert.AreEqual(nextId, nextIdFromGenerator);       nextId = (long)sqlCommand.ExecuteScalar();       Assert.AreEqual(nextId, nextIdFromGenerator + 1);    }    [TearDown]    public void CloseConnection()    {       connection.Close();    } } 

Now that we have a failing test, we can continue the process and extract the GetNextId method from the GenreFixture into its own class named IdGenerator .

 public class IdGenerator {    public static long GetNextId(string tableName,  SqlConnection connection)    {       SqlTransaction transaction = connection.BeginTransaction( IsolationLevel.Serializable, "GenerateId");       SqlCommand selectCommand = new SqlCommand(          "select nextId from PKSequence where tableName = @tableName",          connection, transaction);       selectCommand.Parameters.Add("@tableName",           SqlDbType.VarChar).Value=tableName;       long nextId = (long)selectCommand.ExecuteScalar();       SqlCommand updateCommand = new SqlCommand(          "update PKSequence set nextId = @nextId where tableName=@tableName",          connection, transaction);       updateCommand.Parameters.Add("@tableName", SqlDbType.VarChar).Value=tableName;       updateCommand.Parameters.Add("@nextId", SqlDbType.BigInt).Value=nextId+1;       updateCommand.ExecuteNonQuery();       transaction.Commit();       return nextId;    } } 

When we run this test, it passes, so we complete the refactoring by having the ArtistFixture and GenreFixture use this method directly. After the changes are made, we recompile the code and run all the tests again. They all succeed, so this refactoring is complete. Let s move on to the next duplication, which is in the test code.

If you examine the test code in GenreFixture , ArtistFixture , and IdGeneratorFixture , you will see that they all follow the same pattern. They all open a connection, do something specific to the type of fixture, and then close the connection. The best way to implement this solution is to create a common superclass that each of these fixtures would inherit from. The following is an abstract fixture that can be used to perform this function.

ConnectionFixture.cs

 using System; using System.Configuration; using System.Data.SqlClient; using NUnit.Framework; [TestFixture] public abstract class ConnectionFixture {    private SqlConnection connection;    [TestFixtureSetUp]    public void OpenConnection()    {       connection = new SqlConnection(          ConfigurationSettings.AppSettings.Get("Catalog.Connection"));       connection.Open();    }    [TestFixtureTearDown]    public void CloseConnection()    {       connection.Close();    }    public SqlConnection Connection    {       get { return connection; }    } } 

This fixture is meant to be inherited from to manage the connection to SqlServer for all the tests. It uses TestFixture SetUp/TearDown because the connection needs to be opened only one time for all the tests. This is different from SetUp/TearDown , which is executed prior to each test being run. The following code is the IdGeneratorFixture , which has been modified to use the ConnectionFixture . The SetUp/TearDown methods have been removed because they are now being handled in the ConnectionFixture . The rest of the changes are in boldface in the following code:

IdGeneratorFixture.cs

 using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using NUnit.Framework; using DataAccessLayer; [TestFixture] public class IdGeneratorFixture : ConnectionFixture {    [Test]    public void GetNextIdIncrement()    {       SqlCommand sqlCommand =           new SqlCommand(             "select nextId from PKSequence where tableName=@tableName",  Connection  );       sqlCommand.Parameters.Add("@tableName",SqlDbType.VarChar).Value="Artist";              long nextId = (long)sqlCommand.ExecuteScalar();       long nextIdFromGenerator = IdGenerator.GetNextId("Artist",  Connection  );       Assert.AreEqual(nextId, nextIdFromGenerator);       nextId = (long)sqlCommand.ExecuteScalar();       Assert.AreEqual(nextId, nextIdFromGenerator + 1);    } } 

After reviewing the changes and making sure that all the tests pass, it seems as if we have removed the duplication. Let s finish up the tests for the Genre . We need to add a test for deletion and a test for update. They are very similar to the tests that were written for Artist , so they are not included here.

Finishing Up

Writing the tests and implementation for the rest of the tables ” Label , Reviewer , Review , Track , and Recording ” is very similar to what we have done for Artist and Genre . The only difference that we encountered was in testing updates; we chose to write a separate test for each field that we wanted to update instead of having one test that updates all the properties and then performs the verification. Once again, the point we are trying to get across with tests is isolation. We want each test to have only a single reason to change. Because the implementations did not provide any additional feedback into the design, they are left as an exercise to the reader.