How to test C# method that uses SQL Connection and stored procedure

Question

I am currently facing a big issue where I need to test a method that uses a SQL Server connection and a stored procedure in nUnit. For some reason, when I try to mock the repository and I initialize the dependencies the method always crashed.

This is my method:

public int RunStoredProcedure()
{
        List<string> myList = new List<string>();
        string connetionString = null;

        SqlConnection connection;
        SqlDataAdapter adapter;

        SqlCommand command = new SqlCommand();
        SqlParameter param;

        DataSet ds = new DataSet();

        int i = 0;

        connetionString = _myConnectionRepo.ConnectionString;
        connection = new SqlConnection(connetionString);

        connection.Open();
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "SPCOUNTRY";

        param = new SqlParameter("@COUNTRY", "Germany");
        param.Direction = ParameterDirection.Input;
        param.DbType = DbType.String;
        command.Parameters.Add(param);

        adapter = new SqlDataAdapter(command);
        adapter.Fill(ds);

        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            myList.Add(ds.Tables[0].Rows[i][0].ToString());
        }

        connection.Close();
}

And the unit test is:

[Test]
public void Should_Return_String_list()
{
        _repository.Setup(r => r.RunStoredProcedure()).Returns(new List<string>
        {
            "string1",
            "string2",
            "string3"
        });
        _cnxStringService
             .SetUp(x => x.GetConnectionString)
             .Returns("MockConnectionString");
        var result = _repository.RunStoredProcedure();

        Assert.That(result, It.IsNotNull);
}

When I run the project it works perfect, it returns the values correctly but the unit test says that connection string should be null, and I tried to pass it a simple string but it says the format is not correct, also I tried with a random connection string and it says there is no connection but if I pass the local connection string it works, however when I push my changes the in repo it crashes.

Can somebody help me out with this problem?

Thanks in advance.

Answer 1

I solved the problem by my own, the solution is the following, to avoid null reference in Unit Test (nUnit) what we must do is create a two separate methods, one to retrieve a connection string from the reference project and other to split the values of that connection string.

private static string ReturnConnectionString(IEnumerable<JToken> jTokenValues)
    {
        var cnxString = string.Empty;
        for (int i = 0; i < jTokenValues.Count(); i++)
        {
            var str1 = jTokenValues.ElementAt(i);

            foreach (JProperty attributeProperty in str1)
            {
                if (attributeProperty.Name == "SqlConnectionString")
                {
                    var attribute = str1[attributeProperty.Name];
                    cnxString = attribute.ToString();
                }
            }
        }

        return cnxString;
    }

    private static IEnumerable<JToken> ConfigureEnvironmentVariablesFromLocalSettings()
    {
        var path = Path.GetDirectoryName(typeof(DimAccountRepository)
            .Assembly.Location); var json =
                File.ReadAllText(Path.Join(path, "local.settings.json"));
        var parsed = Newtonsoft.Json.Linq.JObject.Parse(json).Values();

        return parsed;
    }

thanks to this we can initialize the service and pass a connection string and avoid null references.