MS Test DataSource Connection String not working

EricO 26

Utilizing an MS SQL DB to provide data for my unit tests, I have several TestMethods using the following connection string:

WORKS - [DataSource("Persist Security Info=False;User ID=MyUserId; Password=MyPassword;Initial Catalog=UnitTestData;Server=database-server;Provider=SQLOLEDB", "ValidData")]

The above works but requires my user name/password. Windows authentication should be working, as well although I cannot get the connection string below to work:

DOES NOT WORK - [DataSource("Data Source=database-server; Initial Catalog=UnitTestData; Integrated Security=True; Provider=SQLOLEDB", "ValidData")]

I don't want to use SQLOLEDB as the Provider, I want to System.Data.SqlClient but when I try, I get an error that it's not registered.

I know Windows Authentication should work. In the ctor of this TestClass, I created a new SqlConnection instance, using the non-working connection string and I'm abloe to open a connection to the server.

I think an answer may be to provide a correct value in the Provider= section but I'm unsure what it may be.

Karen Payne MVP 35,116 Reputation points

2022-06-03T23:24:48.253+00:00

Is this targeting .NET Framework or .NET Core Framework?

And I get an error that it's not registered. what is not registered?
EricO 26 Reputation points

2022-06-03T23:27:33.557+00:00

.net framework 4.62
EricO 26 Reputation points

2022-06-03T23:36:35.237+00:00

if I specify Provider=System.Data.SqlClient, I receive an error that SqlClient is not registered on this machine.

Karen Payne MVP 35,116

A typical connection outside of a unit test

public static bool ConnectionTest()
{
    var connectionString = "Data Source=database-server;Initial Catalog=UnitTestData;Integrated Security=True";
    using (var cn = new SqlConnection(connectionString))
    {
        try
        {
            cn.Open();
            return true;
        }
        catch (Exception localException)
        {
            Console.WriteLine(localException.Message);
            return false;
        }
    }
}

EricO 26 Reputation points

2022-06-04T00:31:28.763+00:00

This is exactly my point. I have almost the exact same thing, as your example, in the ctor of the TestClass which contain my unit tests. And it works. However, if I use the same connection string in a

[TestMethod]
[DataSource("Data Source=database-server;Initial Catalog=UnitTestData;Integrated Security=True;:")]

The first error I get is that it wants a Provider section, they give the example in the error message "Provider=SQLOLEDB"
If I change it and use:
[DataSource("Data Source=database-server;Initial Catalog=UnitTestData;Integrated Security=True; Provider=SQLOLEDB")]

I get an Authentication Error. Trying to figure out how to correctly use Provider=System.Data.SqlClient
Karen Payne MVP 35,116 Reputation points

2022-06-04T08:18:03.437+00:00

Unsure why this is happening. I would not use this method for unit testing in the first place, instead mockup data and in method decolorated with[TestInitialize] setup data for a test metod. If there are many test you can check TestContext.TestName == "SomeTest" or current .NET Framework, TestContext.TestName == nameof(SomeTest). To ensure data is setup once you can check say if a list is null etc.