Suppose you are writing a unit test for a stored proc which relies on a table that has a number of constraints – foreign keys, non-nullable columns, etc. It is often useful to mock up the table without all the extra baggage of these constraints so you can load the table with only the data that is actually needed to run the test. Sure you can do:
SELECT TOP (0) * FROM myTable
That will get rid of the FKs, but it will not help with non-nullable columns or with keeping identity columns (if you need them). These things often get in the way of writing unit tests quickly. This post will show how to clone a table without those constraints and how to integrate that into a database unit test project.
I will show parts of the solution in the post, but you can download the whole solution right here. So let’s jump right into the code. I could come up with some crazy dynamic SQL to get this job done, but it’s such a mess to debug and such an eyesore. So this solution starts relies on SMO. SMO gives us the relational database world as .NET objects. In this way, we can easily access each object and its properties to loop through and copy them to a new instance of that object. For example,
We can create a server object with
Server myServer = new Server(“localhost”);
From here, we can get a database with
Database myDb = myServer.Databases[“mydb”];
And now, a particular table as
Table tb = myDb.Tables[“myTable”];
And so it goes, we can get stored procs, views, functions, etc. from a database object and columns and other properties from a table object. The clone table method looks about like this:
p; Table newTable = new Table(db, newTableName, srcSchema);
The createColumns method loops through each column in the table and copies each attribute, optionally bypassing nullability and identity attributes. I created these methods as part of a TableCloner class, but they could just as well be a TestHelper class if you wanted to make it more generic.
In my solution, I have a SQL database project, database unit test project and the TableCloner class:
Note that the TableCloner class references a few libraries which are necessary for getting SMO to work.
Next, we create a test in the database test project and add references to the TableCloner Class and SMO libraries. For SQL devs, database unit tests are accessed through the designer in which we plop our SQL code and we are off and running. However, in this case we are going to right click on the database design form and select view code. The code page will contain something like this, to which we add the highlighted section: