在NUnit、Sql Server和UI测试中通过回滚事务来维护数据库已知状态的正确方法

Question

[SetUp]
public void TestSetUp()
{
    _scope = new TransactionScope();
}

[TearDown]
public void TearDown()
{
    _scope.Dispose();
}

[Test]
public void SomeTest()
{
    Utilities.SomeDeleteTransaction(companyCode);    
}

Answer 1

数据库快照！

/* Create a database snapshot */

USE master;
CREATE DATABASE Your_Database_Snapshot ON
( 
    NAME = Your_Database, 
    FILENAME = 'C:\Snapshots\Your_Database_Snapshot.ss' 
)
AS SNAPSHOT OF Your_Database;
GO

USE master;
RESTORE DATABASE Your_Database from 
DATABASE_SNAPSHOT = 'Your_Database_Snapshot';
GO

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

[SetUp]
public void TestSetUp()
{
    string sqlConnectionString = @"server=test.database.com;uid=your_db_username;pwd=your_db_password;database=Your_Database;";

    string script = File.ReadAllText(@"~/create_db_snapshot.sql");
    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));
    server.ConnectionContext.ExecuteNonQuery(script);
}


[TearDown]
public void TearDown()
{
    string sqlConnectionString = @"server=test.database.com;uid=your_db_username;pwd=your_db_password;database=Your_Database;";

    string script = File.ReadAllText(@"~/restore_db_from_snapshot.sql");
    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));
    server.ConnectionContext.ExecuteNonQuery(script);
}

快照文档:https://msdn.microsoft.com/en-us/library/ms175158.aspx

执行.sql文件的代码信用:https://stackoverflow.com/a/1728859/3038677

/* Kill all current connections to Your_Database */

use master;
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('Your_Database')