Back up and restore a single table with foreign keys using SQL Server

Today I needed to backup a single database table, then test a change to the data, then if the change failed, restore the original data.

Below is how I did this.

What I used

I used SQL Server Management Studio to do all of the following steps.
I performed the steps on a database running on a SQL 2012 database server.

Part 1 – Backup the table

SELECT *
INTO MyTable_Bak
FROM MyTable;

Note: This will work usually, however, it won’t work if you have a calculated column. If you have a calculated column, create the table first, then specify the columns you are inserting. I didn’t have a calculated column, so I didn’t take time to figure this out.

Part 2 – Restoring the table

Step 1 – Finding the Foreign Key Constraints

SELECT Name, Object_Name(parent_object_id) as [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('MyTable')

The results were like this:

Name                    Table
FKDDED6AECAD1D93C0      MyOtherTable1
FK166B6670AD1D93C0      MyOtherTable2

Step 2 – Get the Drop and Create for each Foreign Key

In SQL Management Studio Express, I went to each table in the above list, and did the following:

  1. Locate the foreign key under Database | MyDb | Tables | dbo.MyTable | Keys.
  2. Right-click on the Foreign Key and choose Script Key as | Drop and Create to | Clipboard.
  3. Paste this into the query window.
  4. Delete the USING MyDb statement and separate the DROP statement from the two ALTER TABLE statements.
  5. Repeat for the next foreign key constraint, grouping the DROP statements and the ALTER TABLE statements together.

Step 3 – Run the DROP statements

Run the two DROP statements created above.

ALTER TABLE [dbo].[MyOtherTable1] DROP CONSTRAINT [FKDDED6AECAD1D93C0]
GO
ALTER TABLE [dbo].[MyOtherTable2] DROP CONSTRAINT [FK166B6670AD1D93C0]
GO

Step 4 – Restore the table

I used this query to restore the table from the backup.

SELECT * FROM MyTable
SET IDENTITY_INSERT dbo.MyTable ON; 
TRUNCATE TABLE MyTable ;
INSERT INTO MyTable (Id, Col1, Col2, Col3) -- Specify all columns here
SELECT (Id, Col1, Col2, Col3)              -- Specify all columns again here
FROM MyTable_Bak

Step 5 – Restore the foriegn key constraints

Run the ALTER TABLE scripts you grouped together from Step 2.

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FKDDED6AECAD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FKDDED6AECAD1D93C0]
GO

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FK166B6670AD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FK166B6670AD1D93C0]
GO

Conclusion

Your table is restored.

Observations

  1. This is a simple process for a table with only a few foriegn key constraints but could be difficult if you have dozens of foreign key constraints.
  2. Also, this process might not work perfectly with calculated columns without changes to the above process.
  3. It should be easier to do this.

If you know of a better way that doesn’t require $oftware, let me know.

Cleaning a customer folder in Visual Studio project

I have a Plugins directory in a Visual Studio project. I would like to delete all files from it when I clean.

Here is how I did this:

  1. Edit the .csproj file.
  2. Add the following to the very bottom of the .csproj file, just above the terminating </Project> tag.
      <Target Name="afterClean">
          <ItemGroup>
            <FilesToDelete Include="Plugins\**\*"/>
        </ItemGroup>
        <Delete Files="@(FilesToDelete)" />
      </Target>
    
  3. Save the .csproj file.

That should do it.

Update 12/14/2016:
You should be aware that when doing a custom clean, that in Visual Studio choosing Clean and then Build in two steps is not the same as doing Rebuild.

Clean <-- Cleans all projects Build <-- Builds all projects Rebuild <-- For each project, clean the project, then rebuild the project.So Rebuild is a huge problem and here is why: Since my code is pretty decoupled, my plugins don't reference the project hosting the plugins. So there is not dependency to guarantee the plugin-hosting project cleans and builds first. So when running reubild, a plugin might clean and build and then copy its files to the plugin directory. This could happen before the plugin-hosting project cleans and builds. So you can imagine that once the plugin-hosting project cleans and builds, the newly copied plugin files are cleaned. To fix this, I had to manually add a dependency or just not use Rebuild.

Constructor Injection Hell

So I am a fan of dependency injection (DI), inversion of control (IoC), and the way DI and IoC allow for simplistic methods and Unit Tests. With DI, you can do method injection, property injection, or constructor injection. I don’t care which one a project uses, as long as they keep it simple.

Constructor Injection

This article is focussing on constructor injection. Constructor injection seems to be very popular, if not the most popular method of DI. Constructor Injection is considered to have a benefit because it requires the instantiator to provide all the dependencies an object needs in order to create an instance of it.

An Example of Constructor Injection Hell

Recently, I started working with NopCommerce, which uses DI heavily. They use Autofac and register objects with Autofac so it can provide concrete instances of any interfaces.

I am going to use NopCommerce as an example of what not to do. Now before I do this, I want to explain that NopCommerce overall has a very good architecture. Better than most. Finding something that I consider a “what not to do” in a project should not steer you away from NopCommerce. In fact, their plugin model and architecture works quite well.

Below is an example of constructor injection gone wrong from the OrderProcessingService.cs file in NopCommerce.

#region Ctor

        /// <summary>
        /// Ctor
        /// </summary>
        /// <param name="orderService">Order service</param>
        /// <param name="webHelper">Web helper</param>
        /// <param name="localizationService">Localization service</param>
        /// <param name="languageService">Language service</param>
        /// <param name="productService">Product service</param>
        /// <param name="paymentService">Payment service</param>
        /// <param name="logger">Logger</param>
        /// <param name="orderTotalCalculationService">Order total calculationservice</param>
        /// <param name="priceCalculationService">Price calculation service</param>
        /// <param name="priceFormatter">Price formatter</param>
        /// <param name="productAttributeParser">Product attribute parser</param>
        /// <param name="productAttributeFormatter">Product attribute formatter</param>
        /// <param name="giftCardService">Gift card service</param>
        /// <param name="shoppingCartService">Shopping cart service</param>
        /// <param name="checkoutAttributeFormatter">Checkout attribute service</param>
        /// <param name="shippingService">Shipping service</param>
        /// <param name="shipmentService">Shipment service</param>
        /// <param name="taxService">Tax service</param>
        /// <param name="customerService">Customer service</param>
        /// <param name="discountService">Discount service</param>
        /// <param name="encryptionService">Encryption service</param>
        /// <param name="workContext">Work context</param>
        /// <param name="workflowMessageService">Workflow message service</param>
        /// <param name="vendorService">Vendor service</param>
        /// <param name="customerActivityService">Customer activity service</param>
        /// <param name="currencyService">Currency service</param>
        /// <param name="affiliateService">Affiliate service</param>
        /// <param name="eventPublisher">Event published</param>
        /// <param name="pdfService">PDF service</param>
        /// <param name="rewardPointService">Reward point service</param>
        /// <param name="genericAttributeService">Generic attribute service</param>
        /// <param name="paymentSettings">Payment settings</param>
        /// <param name="shippingSettings">Shipping settings</param>
        /// <param name="rewardPointsSettings">Reward points settings</param>
        /// <param name="orderSettings">Order settings</param>
        /// <param name="taxSettings">Tax settings</param>
        /// <param name="localizationSettings">Localization settings</param>
        /// <param name="currencySettings">Currency settings</param>
        public OrderProcessingService(IOrderService orderService,
            IWebHelper webHelper,
            ILocalizationService localizationService,
            ILanguageService languageService,
            IProductService productService,
            IPaymentService paymentService,
            ILogger logger,
            IOrderTotalCalculationService orderTotalCalculationService,
            IPriceCalculationService priceCalculationService,
            IPriceFormatter priceFormatter,
            IProductAttributeParser productAttributeParser,
            IProductAttributeFormatter productAttributeFormatter,
            IGiftCardService giftCardService,
            IShoppingCartService shoppingCartService,
            ICheckoutAttributeFormatter checkoutAttributeFormatter,
            IShippingService shippingService,
            IShipmentService shipmentService,
            ITaxService taxService,
            ICustomerService customerService,
            IDiscountService discountService,
            IEncryptionService encryptionService,
            IWorkContext workContext,
            IWorkflowMessageService workflowMessageService,
            IVendorService vendorService,
            ICustomerActivityService customerActivityService,
            ICurrencyService currencyService,
            IAffiliateService affiliateService,
            IEventPublisher eventPublisher,
            IPdfService pdfService,
            IRewardPointService rewardPointService,
            IGenericAttributeService genericAttributeService,
            ICompanyService companyService,
            ShippingSettings shippingSettings,
            PaymentSettings paymentSettings,
            RewardPointsSettings rewardPointsSettings,
            OrderSettings orderSettings,
            TaxSettings taxSettings,
            LocalizationSettings localizationSettings,
            CurrencySettings currencySettings)
        {
            this._orderService = orderService;
            this._webHelper = webHelper;
            this._localizationService = localizationService;
            this._languageService = languageService;
            this._productService = productService;
            this._paymentService = paymentService;
            this._logger = logger;
            this._orderTotalCalculationService = orderTotalCalculationService;
            this._priceCalculationService = priceCalculationService;
            this._priceFormatter = priceFormatter;
            this._productAttributeParser = productAttributeParser;
            this._productAttributeFormatter = productAttributeFormatter;
            this._giftCardService = giftCardService;
            this._shoppingCartService = shoppingCartService;
            this._checkoutAttributeFormatter = checkoutAttributeFormatter;
            this._workContext = workContext;
            this._workflowMessageService = workflowMessageService;
            this._vendorService = vendorService;
            this._shippingService = shippingService;
            this._shipmentService = shipmentService;
            this._taxService = taxService;
            this._customerService = customerService;
            this._discountService = discountService;
            this._encryptionService = encryptionService;
            this._customerActivityService = customerActivityService;
            this._currencyService = currencyService;
            this._affiliateService = affiliateService;
            this._eventPublisher = eventPublisher;
            this._pdfService = pdfService;
            this._rewardPointService = rewardPointService;
            this._genericAttributeService = genericAttributeService;
            this._companyService = companyService;

            this._paymentSettings = paymentSettings;
            this._shippingSettings = shippingSettings;
            this._rewardPointsSettings = rewardPointsSettings;
            this._orderSettings = orderSettings;
            this._taxSettings = taxSettings;
            this._localizationSettings = localizationSettings;
            this._currencySettings = currencySettings;
        }

        #endregion

Problems in the Constructor Injection Implementation

So what is wrong with the above constructor? Well, a lot. Look, this is just bad code. While constructor injection is a good idea, taking it to this extreme is not a good idea. In fact, it is a terrible idea.

  1. The Constructor has too many parameters. While there is no limit, there is a best practice. See this stack overflow post: How many parameters are too many?
  2. The Constructor breaks the 10/100 rule. The constructor, with comments, method parameters, and method body is 126 lines of code. The method itself is far more than 10 lines of code, it is 39 lines of parameters and 39 more lines of member assignments, and is 80 lines of code.
  3. The Constructor breaks the keep it super simple (KISS) principle. Having to new up 39 concrete instances of the parameters in order to create an object is not simple. Imagine mocking 39 interface parameters in a Unit Test. Ugh!
  4. This constructor is a hint that the entire class is doing too much. The class is 3099 lines and clearly breaks the single responsibility principle. It is not the OrderProcessingService’s responsibility to store 39 dependent services.
  5. The constructor breaks the Don’t Repeat Yourself (DRY) principle. Almost all other classes in NopCommerce use constructor injection to access services.

Options for Refactoring

Option 1 – Container object

You could create a container that has all of these dependecies, a dependency model object for the OrderProcessingService. This object would house the 39 dependent services and settings. But Option 2 would be better.

Option 2 – Accessor objects

Looking at this from the Single Responsibility Principle, shouldn’t there be one class and interface, a ServiceAccessor : IServiceAccessor that allows one to access any dependent service? Instead of passing in 30 services, wouldn’t it make more sense to pass in a single object called a ServiceAccessor that implements IServiceAccessor? Should there be a ServiceAccessor of some sort? Turns out there is a static: EngineContext.Current.Resolve(). Since it is a static, maybe you could wrap it in a ServiceAccessor : IServiceAccessor object.

There are also a lot of “settings” objects passed into the constructor? Shouldn’t there be a SettingsService? Well, there is. One can pass in the ISettingsService and then call _settingService.LoadSetting().

Instead of passing in 39 parameters, methods with a single responsibility to fetch a service should be used.

Option 3 – Refactor the class

Since the class is 3099 lines. If the class were broken into logical pieces, naturally, the constructor for each smaller piece would have less parameters.

How to convert a string to an enum in C#?

Use this extension method:

using System;

namespace Rhyous.Extensions
{
    public static class StringExtensions
    {
        public static T AsEnum<T>(this string str, T defaultValue)
        {
            try { return (T)Enum.Parse(typeof(T), str, true); }
            catch { return defaultValue; }
        }
    }
}

So imagine you have this enum:

public enum LogLevel
{
   Debug,
   Information,
   Warning,
   Error,
   Fatal
}

Call it like this:

var levelStr = "Error";
LogLevel level = levelStr.AsEnum(LogLevel.Info);

Unit testing calls to complex extension methods

This article isn’t about unit testing an extension method. That is pretty straight forward. This article is about unit testing and object that calls an extension method where the extension method is difficult to test. Likely the method is difficult to test because it touches an external system, such as a database or a remote web service.

If you have an extension method that is simple and doesn’t touch and external system, it is easy to unit test. Look at the example below. There is nothing blocking you from Unit Testing code that calls this method.

public static int Add(this int left, int right) 
{
    return left + right;
}

Now image the extension method is more complex, say for a shopping cart.

public static void PlaceOrder(this Order order) 
{
    SaveToDb(Order);
    ChargeCreditCard(Order.CreditCardDetails);
}

How are you going to unit test code that calls an extension method that place an order and charges a customer’s Credit Card. Yikes. That is little harder to Unit Test, right?

How to Unit Test a call to an complex extension method

Imagine you have the following code:

  1. An object you are test called ObjectUnderTest
    public class ObjectUnderTest
    {
        private void MyObject = new MyObject();
    
        public object SomeFunction() 
        {
            return myObj.DoWork(val);
        }
    }
    
  2. An dependent object MyObject : IMyObject
    public class MyObject : IMyObject
    {
     // ... some code
    }
    
  3. An extension method on IMyObject: DoWork(this IMyObject obj, string value).
    public static object DoWork(this IMyObject obj, string value)
    {
        // really complex stuff and touches external systems
    }
    

You need Unit Tests for SomeFunction(). Imagine that all other code is 100% unit tested. But you are struggling with how to Unit Test SomeFunction because it has two dependencies:

  1. MyObject
  2. DoWork

The Unit Tests should not call the real DoWork because it does really complex stuff and touches external systems. However, you need the parent method to provide a valid return value.

Well, you could just drop the ExcludeFromCodeCoverageAttribute on the method and move on. But what if there are a half-dozen other objects that call the parent method that also need to be tested and they need a return value from SomeFunction()? It would be best to solve this in this object as so you only change one class file, not a half-dozen.

One option to resolve this is to use dependency injection. Dependency Injection (DI) simply means that any dependencies can be injected. When some people hear DI, they think they immediately need the huge overhead of an IoC Container. IoC containers are nice and have their uses. But using an IoC container only to allow unit tests substitute a dependency is a huge overkill. If your project already has an IoC container, feel free to use it. Otherwise, I recommend you use a simpler option. I prefer an internal lazy injectable property.

Creating a Lazy Injectable Property

An internal lazy injectable property is a property that is instantiated on first use if it is null, but always for code with internal access to swap out the property value. Here is the syntax:

Note: This assumes your unit tests references your project already, has InternalsVisibleTo configured, and has Moq from NuGet applied to the test project.

    internal IMyObject MyObjectProperty
    {
        get { return _MyObject ?? (_MyObject = new MyObject()); }
        set { _MyObject= value; }
    } private List<object> _MyObject;

Look how simple the above code is. If _MyObject is null, the first time MyObjectProperty is called, it is instantiated to a new MyObject().It is internal because only the unit test will every replace it. I don’t really want this property exposed elsewhere. We can use InternalsVisibleTo to allow the Unit Tests access.Now my ObjectUnderTest will look like this:

public class ObjectUnderTest
{
    internal IMyObject MyObjectProperty
    {
        get { return _MyObject ?? (_MyObject = new MyObject()); }
        set { _MyObject= value; }
    } private IMyObject _MyObject;

    public object SomeFunction()
    {
        var val = "doesn't matter for this example";
        return MyObjectProperty.DoWork(val);
    }
}

Now, in the unit test, the MyObjectProperty can be replaced with a mock IMyObject.

[TestMethod]
public void SomeFunctionTest()
{    // Arrange
    var mockMyObject = new Mock<IMyObject>();
    var objUnderTest = new ObjectUnderTest();
    objectUnderTest.MyObjectProperty = mockMyObject.Object;

    // More to come . . .
}

However, it is questionable whether this is even necessary. Does MyObject do anything that requires this level of abstraction? Not in this example. It isn’t the object itself that is complex, it is the extension method that really needs to be injectable.

Creating a Lazy Injectable Property for a method

You might be asking yourself, “What about the extension method? It is a method not an object. How can I inject that?” Well, you can. Remember, even methods can be treated as objects. The answer doesn’t change much. The only difference is understanding how to treat a method as an object.You can objectify methods using multiple objects such as Action, Func, Predicate, delegate, etc. I am not going to go into how to do that here beyond the minimal needed to accomplish this task.

Quick tip: Use Action for void methods, Predicate for methods return bool, Func for methods with any return value, delegate if you have ref or out paramters.

Here are the steps:

  1. Create the following Lazy Injectable Property inside ObjectUnderTest:

    Note: I am using Func because it has a return value of object. (See the Quick Tip a few lines up.) Since I have two paramters and a return type, I will specifically use the generic Func.

        internal Func<IMyObject, string, object> DoWorkMethod
        {
            [ExcludeFromCodeCoverage]
            get { return _DoWorkMethod ?? (_DoWorkMethod = (obj, val) => { return obj.DoWork(val); }); }
            set { _DoWorkMethod = value; }
        } private Func<IMyObject, string, object> _DoWorkMethod;
    
  2. Change SomeFunction() to run the method via the Action object instead of running the method directly.
        public object SomeFunction()
        {
            var val = "doesn't matter for this example";
            return DoWorkMethod.Invoke(MyObjectProperty, val);
        }
    
  3. In your Unit Test, you can create your ObjectUnderTest. Then you can swap out the DoWork method object.
    [TestMethod]
    public void SomeFunctionTest()
    {
        // Arrange
        var mockMyObject = new Mock<IMyObject>();
        var objUnderTest = new ObjectUnderTest();
        objUnderTest.MyObjectProperty = mockMyObject.Object;
        bool methodWasCalled = false;
        objUnderTest.DoWorkMethod = (obj, val) => {
            methodWasCalled = true;
            return new object();
        };
            
        // Act
        var result = objUnderTest.SomeFunction();
    
        // Assert
        Assert.IsTrue(methodWasCalled);
    }
    

You are now 100% covered. The only code we can’t cover is the lambda call to obj.DoWork because we can’t Unit Test that as it touches an external system. Which is why we marked it with the ExcludeFromCodeCoverageAttribute.