如何在不写入真实数据库的情况下对C#中的Admin数据插入方法进行单元测试？

Great question! The core issue with your current code is tight coupling to concrete database classes like SqlConnection and SqlCommand, plus direct instantiation of dependencies (like AdminDatabaseWriter in your static method). To test this without a real database, we need to decouple dependencies and simulate database interactions using mocks or fake implementations.

Step 1: Abstract Dependencies with Interfaces #

First, we'll extract interfaces for the components we need to mock. This lets us swap out real implementations for test doubles.

1.1 Create an Interface for AdminDatabaseWriter #

This makes your business code testable and lets you mock the writer itself if needed:

public interface IAdminDatabaseWriter
{
    void Insert(Admin admin);
}

// Update your existing class to implement this interface
public class AdminDatabaseWriter : IAdminDatabaseWriter
{
    // Your existing Insert method remains mostly the same—we'll tweak it next
}

1.2 Abstract Database Helper Logic #

Your DatabaseHelper.CreateNewSqlCommandWithStoredProcedure is a hard dependency. Let's abstract that too:

public interface IDatabaseHelper
{
    IDbCommand CreateNewSqlCommandWithStoredProcedure(string procedureName, IDbConnection connection);
}

// Update your existing DatabaseHelper to implement this
public class DatabaseHelper : IDatabaseHelper
{
    public IDbCommand CreateNewSqlCommandWithStoredProcedure(string procedureName, IDbConnection connection)
    {
        var command = new SqlCommand(procedureName, connection as SqlConnection);
        command.CommandType = CommandType.StoredProcedure;
        return command;
    }
}

1.3 Refactor AdminDatabaseWriter to Use Dependency Injection #

Modify the writer to accept dependencies via constructor injection (instead of creating them internally):

public class AdminDatabaseWriter : IAdminDatabaseWriter
{
    private readonly string _connectionString;
    private readonly IDatabaseHelper _databaseHelper;

    public AdminDatabaseWriter(string connectionString, IDatabaseHelper databaseHelper)
    {
        _connectionString = connectionString;
        _databaseHelper = databaseHelper;
    }

    public void Insert(Admin admin)
    {
        // Use IDbConnection instead of SqlConnection for better testability
        using IDbConnection sqlConnection = new SqlConnection(_connectionString);
        sqlConnection.Open();
        
        // Use the injected IDatabaseHelper instead of static calls
        using IDbCommand sqlCommand = _databaseHelper.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", sqlConnection);
        
        // Your parameter-adding logic stays the same
        sqlCommand.Parameters.AddWithValue("@PIN", admin.PIN);
        sqlCommand.Parameters.AddWithValue("@AdminType", admin.AdminTypeCode);
        sqlCommand.Parameters.AddWithValue("@FirstName", admin.FirstName);
        sqlCommand.Parameters.AddWithValue("@LastName", admin.LastName);
        sqlCommand.Parameters.AddWithValue("@EmailAddress", admin.EmailAddress);
        sqlCommand.Parameters.AddWithValue("@Password", admin.Password);
        sqlCommand.Parameters.AddWithValue("@AssessmentScore", admin.AssessmentScore);

        var userAddedSuccessfully = sqlCommand.ExecuteNonQuery();
        sqlConnection.Close();

        if (userAddedSuccessfully < 0)
        {
            throw new AdminNotAddedToDatabaseException("User was unsuccessful at being uploaded to the database for an unknown reason.");
        }
    }
}

1.4 Update Your Business Logic to Accept Injected Dependencies #

If you’re using static methods, consider switching to an instance class for better testability. Here’s how to refactor your console UI code:

public class AdminService
{
    private readonly IAdminDatabaseWriter _adminDatabaseWriter;

    // Inject the writer via constructor
    public AdminService(IAdminDatabaseWriter adminDatabaseWriter)
    {
        _adminDatabaseWriter = adminDatabaseWriter;
    }

    public void InsertNewAdmin(Admin admin)
    {
        _adminDatabaseWriter.Insert(admin);
    }
}

Step 2: Write Unit Tests with Mocks (Using Moq) #

Moq is a popular .NET mocking framework that lets you simulate dependencies and verify interactions. Below are two key tests: one for successful insertion, and one for failure.

First, add NuGet packages: xunit, xunit.runner.visualstudio, and Moq.

Test 1: Verify Correct Parameters and Stored Procedure Execution #

using Xunit;
using Moq;
using System.Data;

public class AdminDatabaseWriterTests
{
    [Fact]
    public void Insert_ValidAdmin_ExecutesStoredProcedureWithCorrectParameters()
    {
        // Arrange
        // Mock the database helper, connection, command, and parameters
        var mockDbHelper = new Mock<IDatabaseHelper>();
        var mockConnection = new Mock<IDbConnection>();
        var mockCommand = new Mock<IDbCommand>();
        var mockParameters = new Mock<IDataParameterCollection>();

        // Set up mock command to return our parameter collection
        mockCommand.Setup(c => c.Parameters).Returns(mockParameters.Object);
        // Simulate successful insertion (ExecuteNonQuery returns 1)
        mockCommand.Setup(c => c.ExecuteNonQuery()).Returns(1);

        // Make the helper return our mock command
        mockDbHelper.Setup(h => h.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", mockConnection.Object))
                    .Returns(mockCommand.Object);

        // Create a test Admin object
        var testAdmin = new Admin
        {
            PIN = "1234",
            AdminTypeCode = "ADMIN",
            FirstName = "John",
            LastName = "Doe",
            EmailAddress = "john.doe@example.com",
            Password = "password123",
            AssessmentScore = 95
        };

        var writer = new AdminDatabaseWriter("fake_connection_string", mockDbHelper.Object);

        // Act
        writer.Insert(testAdmin);

        // Assert
        // Verify the correct stored procedure was called
        mockDbHelper.Verify(h => h.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", It.IsAny<IDbConnection>()), Times.Once);

        // Verify all parameters were added with the correct values
        mockParameters.Verify(p => p.AddWithValue("@PIN", testAdmin.PIN), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@AdminType", testAdmin.AdminTypeCode), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@FirstName", testAdmin.FirstName), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@LastName", testAdmin.LastName), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@EmailAddress", testAdmin.EmailAddress), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@Password", testAdmin.Password), Times.Once);
        mockParameters.Verify(p => p.AddWithValue("@AssessmentScore", testAdmin.AssessmentScore), Times.Once);

        // Verify the command was executed once
        mockCommand.Verify(c => c.ExecuteNonQuery(), Times.Once);
    }

Test 2: Verify Exception is Thrown on Insert Failure #

[Fact]
    public void Insert_ExecuteNonQueryReturnsNegative_ThrowsAdminNotAddedException()
    {
        // Arrange
        var mockDbHelper = new Mock<IDatabaseHelper>();
        var mockConnection = new Mock<IDbConnection>();
        var mockCommand = new Mock<IDbCommand>();
        var mockParameters = new Mock<IDataParameterCollection>();

        mockCommand.Setup(c => c.Parameters).Returns(mockParameters.Object);
        // Simulate insertion failure (return -1)
        mockCommand.Setup(c => c.ExecuteNonQuery()).Returns(-1);

        mockDbHelper.Setup(h => h.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", mockConnection.Object))
                    .Returns(mockCommand.Object);

        var testAdmin = new Admin();
        var writer = new AdminDatabaseWriter("fake_connection_string", mockDbHelper.Object);

        // Act & Assert
        var exception = Assert.Throws<AdminNotAddedToDatabaseException>(() => writer.Insert(testAdmin));
        Assert.Equal("User was unsuccessful at being uploaded to the database for an unknown reason.", exception.Message);
    }
}

Step 3: Alternative: Use Fake Implementations (No Mock Framework) #

If you prefer not to use a mocking framework, you can write "fake" classes that implement your interfaces and track interactions manually.

Example Fake Classes #

public class FakeDatabaseHelper : IDatabaseHelper
{
    public string LastProcedureName { get; private set; }
    public IDbCommand CreatedCommand { get; private set; }

    public IDbCommand CreateNewSqlCommandWithStoredProcedure(string procedureName, IDbConnection connection)
    {
        LastProcedureName = procedureName;
        CreatedCommand = new FakeDbCommand();
        return CreatedCommand;
    }
}

public class FakeDbCommand : IDbCommand
{
    public int ExecuteNonQueryResult { get; set; } = 1;
    public FakeParameterCollection Parameters { get; } = new();

    public int ExecuteNonQuery() => ExecuteNonQueryResult;

    // Implement other IDbCommand methods with stubs (throw NotImplementedException if unused)
    public void Cancel() => throw new NotImplementedException();
    public IDbDataParameter CreateParameter() => throw new NotImplementedException();
    public int ExecuteScalar() => throw new NotImplementedException();
    public IDataReader ExecuteReader() => throw new NotImplementedException();
    public IDataReader ExecuteReader(CommandBehavior behavior) => throw new NotImplementedException();
    public void Prepare() => throw new NotImplementedException();
    public string CommandText { get; set; }
    public int CommandTimeout { get; set; }
    public CommandType CommandType { get; set; }
    public IDbConnection Connection { get; set; }
    public IDbTransaction Transaction { get; set; }
    public UpdateRowSource UpdatedRowSource { get; set; }
    public void Dispose() {}
}

public class FakeParameterCollection : IDataParameterCollection
{
    public Dictionary<string, object> ParameterValues { get; } = new();

    public void AddWithValue(string parameterName, object value)
    {
        ParameterValues[parameterName] = value;
    }

    // Implement other IDataParameterCollection methods (stubs for unused ones)
    public int Count => ParameterValues.Count;
    public bool IsFixedSize => false;
    public bool IsReadOnly => false;
    public bool IsSynchronized => false;
    public object SyncRoot => this;
    public object this[string parameterName] => ParameterValues[parameterName];
    public object this[int index] => ParameterValues.ElementAt(index).Value;
    public int Add(object value) => throw new NotImplementedException();
    public void Clear() => ParameterValues.Clear();
    public bool Contains(object value) => throw new NotImplementedException();
    public bool Contains(string parameterName) => ParameterValues.ContainsKey(parameterName);
    public void CopyTo(Array array, int index) => throw new NotImplementedException();
    public IEnumerator GetEnumerator() => ParameterValues.GetEnumerator();
    public int IndexOf(object value) => throw new NotImplementedException();
    public int IndexOf(string parameterName) => ParameterValues.Keys.ToList().IndexOf(parameterName);
    public void Insert(int index, object value) => throw new NotImplementedException();
    public void Remove(object value) => throw new NotImplementedException();
    public void RemoveAt(int index) => throw new NotImplementedException();
    public void RemoveAt(string parameterName) => ParameterValues.Remove(parameterName);
}

Test with Fakes #

[Fact]
public void Insert_ValidAdmin_AddsCorrectParameters()
{
    // Arrange
    var fakeDbHelper = new FakeDatabaseHelper();
    var fakeCommand = (FakeDbCommand)fakeDbHelper.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", null);
    fakeCommand.ExecuteNonQueryResult = 1;

    var testAdmin = new Admin
    {
        PIN = "1234",
        AdminTypeCode = "ADMIN",
        FirstName = "John",
        LastName = "Doe",
        EmailAddress = "john.doe@example.com",
        Password = "password123",
        AssessmentScore = 95
    };

    var writer = new AdminDatabaseWriter("fake_conn", fakeDbHelper);

    // Act
    writer.Insert(testAdmin);

    // Assert
    Assert.Equal("InsertNewAdmin", fakeDbHelper.LastProcedureName);
    Assert.Equal(testAdmin.PIN, fakeCommand.Parameters.ParameterValues["@PIN"]);
    Assert.Equal(testAdmin.AdminTypeCode, fakeCommand.Parameters.ParameterValues["@AdminType"]);
    // Verify other parameters similarly
}

Key Takeaways #

• Decouple dependencies using interfaces so you can swap real implementations for test doubles.
• Mock frameworks like Moq are great for verifying interactions (e.g., "was this method called with the right parameters?").
• Fake classes are useful if you want more control over test behavior or avoid adding third-party libraries.
• Always test both success and failure scenarios to ensure your code handles edge cases correctly.

内容的提问来源于stack exchange，提问作者James McKinney