The Right Way to Maintain Database State Using Transaction Rollback in NUnit, Sql Server, and UI Testing

Question

I am trying to do the following to automate user interface testing:

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

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

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

I am trying to execute one Update and [Test] request and do some things with the user interface and cancel this transaction in [TearDown] , which starts after the test. I am not sure that I am doing it right. But I'm trying to (possibly commit) this transaction so that I can see its effect on the interface and rollback the same transaction, so my database remains the same. Can I accomplish this with TransactionScope or another class?

Edit

. , , // sql script, db , Selenium, Teardown ( NUnit), , db .

Answer 1

!

script "create_db_snapshot.sql"

/* 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

, script "restore_db_from_snapshot.sql"

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: fooobar.com/questions/58466/...

script restore_db_from_snapshot.sql...

/* 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')

Answer 2

Selenium -. , . , , . , #usp_restore_snapshot, unit test SQL, .

Answer 3

- https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx, , , .

:

[Test]
public void SomeTest() 
{
    using (TransactionScope scope = new TransactionScope())
    {
        // here comes your test
    }
}

"" "" - , , TearDown:

[TestFixture]
public class YourFixture
{
    private TransactionScope scope;

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

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


    [Test]
    public void SomeTest()
    {
        // here comes your test 
    }
}

? NUnit - , TearDown .

Answer 4

, , TearDown . , .

Answer 5

:

• / ... , , . - , db, ..
• DTC Teardown... , .

# 1 , RAM-, . , . DTC , 1000 , , .

/:

public void Reset()
{
if (!this.initialized || !this.connectionString.Contains("(local)"))
    return;
TestDbManager.CopyNewFiles(this.remoteDatabaseSourceFolder, this.localDatabaseFilesCacheFolder);
this.Detach(this.database);
TestDbManager.CopyNewFiles(this.localDatabaseFilesCacheFolder, this.localSqlServerWorkingFolder);
this.ReAttach(this.database, this.localSqlServerWorkingFolder);
}

, (a) db (b) db. , a b, .

/ ...

exec sp_attach_db @dbname = '{0}'"
exec sp_detach_db @dbname = '{0}'"

, , .

Answer 6

, .

, SNAPSHOT ( :)

SNAPSHOT START, REVERT END

... , , , . .

CREATE DATABASE database_snapshot_name    
    ON     
    (    
        NAME = logical_file_name,    
        FILENAME = 'os_file_name'     
    ) [ ,...n ]     
    AS SNAPSHOT OF source_database_name

SNAPSHOT

USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from 
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';
GO

MSDN

https://msdn.microsoft.com/en-us/library/ms189281.aspx

https://msdn.microsoft.com/en-us/library/ms175876.aspx

Answer 7

, # SQL. SQL. , , .

Begin Tran
Update table1 
set col1=val2
where col1=val1

select col1 from table1 WITH (NOLOCK);

rollback