Cannot rollback DB after running a unit test function

[jinho929]

I use NUnit to write a class to test some webservice functions. I want the database rollback after executing each test functions.

using System.EnterpriseServices;
using NUnit.Framework;

namespace AutoTest.UnitTest
{
[TestFixture]
[Transaction(TransactionOption.Required)]
public class GDBWSTest : ServicedComponent
{
[TearDown]
public void TransactionTearDown()
{
if (ContextUtil.IsInTransaction)
{
ContextUtil.SetAbort();
Console.WriteLine("call setabort");
}
}

[Test]
public void MyFunc3()
{
DbCommand cmd = null;
string _Connection = "Data Source=PCLANHO;Initial Catalog=RMC_Test;Persist Security Info=True;User ID=abce;Password=1234";
string _providerName = "System.Data.SqlClient";

DbProviderFactory _dbProvider = DbProviderFactories.GetFactory(_providerName);
DbConnection _dbConnection = _dbProvider.CreateConnection();
_dbConnection.ConnectionString = _Connection;
SqlParameter sqlParam = new SqlParameter("Name", "LanTest14");

cmd = _dbProvider.CreateCommand();
cmd.CommandText = "uspCreateHolidayType";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(sqlParam);
cmd.Connection = _dbConnection;
cmd.Connection.Open();
int result = cmd.ExecuteNonQuery();
Assert.AreEqual(1, result);
}

[Test]
public void TestMyWS()
{
My.Service1 service = new My.Service1();
int result = service.InsertData();
Assert.AreEqual(1, result);
}
}
}

And here is my WebService code:
namespace MyWS
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
public class Service1 : System.Web.Services.WebService
{
[WebMethod]
public int InsertData1()
{
DbCommand cmd = null;
string _Connection = "Data Source=PCLANHO;Initial Catalog=RMC_Test;Persist Security Info=True;User ID=abce;Password=1234";
string _providerName = "System.Data.SqlClient";

DbProviderFactory _dbProvider = DbProviderFactories.GetFactory(_providerName);
DbConnection _dbConnection = _dbProvider.CreateConnection();
_dbConnection.ConnectionString = _Connection;
SqlParameter sqlParam = new SqlParameter("Name", "LanTest14");

cmd = _dbProvider.CreateCommand();
cmd.CommandText = "uspCreateHolidayType";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(sqlParam);
cmd.Connection = _dbConnection;
cmd.Connection.Open();
return cmd.ExecuteNonQuery();
}
}
}

After testing MyFunc3, database can rollback (the test pass but no record is inserted into db)

But after testing TestMyWS, database cannot rollback (the test pass but there is a new record inserted into db)

Can anybody explain this to me and show me solution to solve this problem.

Thank you very much.

[jinho929]

I know the reason, because webservice is called via HTTP protocol. HTTP is a stateless protocol and there's no way to rollback database. I have 2 solution to solve this issue:
- Clone each function you wanna test. In this function create a TrasactionScope, invoke real function of webservice.
- Add reference to the assembly of webservice (not web reference).