0

I am following few answers on stack/others from here and here but having a hard time writing tests for SqlConnection. I dont want to use any repository as it is a small one time activity that will run everyday and will not change anytime. I although have below GetData method in an interface

So far my code as:

public DataSet GetData()
    {
        DataSet dataSet = new DataSet("dataset"); 
        string connectionString = "my connection string";        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand("myStoredProc", connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            adapter.SelectCommand = command;
            adapter.Fill(dataSet);
            return dataSet;
        }
    }

and the unit test is (new here in writing tests (: )

    [TestMethod]
    public void GetDevices_That_ReturnsDataSet()
    {
        string connectionString = "Server=tcp:localserver,1433;Initial Catalog=mydb;Persist Security Info=False;User ID=userid;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (ShimsContext.Create())
        {

   System.Data.Common.Fakes.ShimDbConnection.AllInstances.CreateCommand = (c) => {
                c.ConnectionString = connectionString;
                return new ShimSqlCommand();
            };
            ShimSqlConnection.AllInstances.ValidateConnectionForExecuteStringSqlCommand = (co, s, cmd) => {  };
            ShimSqlConnection.AllInstances.Open = (c) =>
            {
                c.ConnectionString = connectionString;
            };
            ShimSqlConnection.AllInstances.Close = (c) => { };
            ShimSqlConnection.AllInstances.StateGet = (c) => ConnectionState.Open;
            ShimSqlConnection.AllInstances.ServerVersionGet = (c) => "14.0";
            ShimSqlConnection.AllInstances.ClientConnectionIdGet = (c) => It.IsAny<Guid>();
            ShimSqlConnection.AllInstances.CredentialGet = (c) => new ShimSqlCredential();
            ShimSqlDataReader.AllInstances.Close = (c) => { };
            // Faking Adapter, from command for current instance of command
            ShimSqlDataAdapter.ConstructorSqlCommand = (@this, value) => new ShimSqlDataAdapter(@this);
            System.Data.Fakes.ShimDataSet.Constructor = (@this) => new System.Data.Fakes.ShimDataSet(@this)
            {
                // fake DataTableCollection of data set
                TablesGet = () => new System.Data.Fakes.ShimDataTableCollection()
                {
                    ItemGetInt32 = (S) =>
                    {
                        var dt = new DataTable("Devices");
                        dt.Columns.Add("Id");
                        dt.Columns.Add("Column2");
                        dt.Columns.Add("Column3");
                        dt.Rows.Add(It.IsAny<Guid>(), It.IsAny<string>(), It.IsAny<string>());
                        return dt;
                    }
                },
                // fake culture set for data set
                LocaleSetCultureInfo = (c) => { }
            };
            //string commandText;
            //ShimSqlCommand.AllInstances.ExecuteReader = command =>
            //{
            //    commandText = command.CommandText;
            //    return new ShimSqlDataReader();
            //};
            // Faking Fill
            System.Data.Common.Fakes.ShimDbDataAdapter.AllInstances.FillDataSetString = (a, b, c) => 0;


            //Act
            var dataService = new DataService();
            var result = dataService.GetData();

            //Assert
            Assert.IsNotNull(result); 
        }
    }

Appreciate all your help

narcs
  • 213
  • 1
  • 2
  • 13
  • Tight coupling to implementation concerns make unit testing them isolation difficult. abstract out the connection and you should be able to mock as needed for isolated testing. – Nkosi Dec 30 '18 at 19:55
  • i do agree to your point but don't you think its an overkill for such a small implementation? – narcs Dec 30 '18 at 19:58
  • Yet such a small implementation is not testable in isolation? Good design is good design no matter the size of the project. By your same logic then such a small implementation should have not need to be unit tested? (sarcasm) – Nkosi Dec 30 '18 at 20:41
  • Another option would be to forgo unit tests and instead do an integration test with an actual connection, but it looks like the code has a hard-coded connection string – Nkosi Dec 30 '18 at 20:45
  • Connection will be thru MSI as hosted in azure. I am more looking to shim sql objects to solve my scenario like its demoed on the links i have shared in my post. – narcs Dec 30 '18 at 21:20

0 Answers0