3
\$\begingroup\$

I have an .mdf DB file and I want to use this DB for integration testing.

In every test I should:

  1. Create a copy of an .mdf file.
  2. Attach DB to an .mdf copy file
  3. Do testing
  4. Detach DB copy
  5. Delete DB copy

Just to make all clear, integration testing should look something like this:

[TestFixture]    
    public class WebJobTests : IntegrationTest
    {
        [SetUp()]
        public void Init()
        {
            CreateTestDbCopy();
            AttachTestDbCopy();
            // Other init
        }
        [TearDown]
        public void TestCleanup()
        {
            DetachTestDbCopy();
            DeleteTestDbCopy();
         }
        [Test]
        public void SomeIntegrationTest()
        {
         //Do testing
        }
     }

The main thing that I want you to take a look is my IntegrationTest class. This is an abstract class that should be inherited in every integration test class, like in example above.

 /// <summary>
    /// Help functions for Integration tests.
    /// </summary>
    public abstract class IntegrationTest
    {
        private const string conectionStringName = "IntegrationTestDB";
        private string connectionString;
        private string dbPath;
        private string dbPathCopy;
        private string logFileCopy;
        private const string dbCopyName = "TestDbCopy";

        public IntegrationTest()
        {
            connectionString = ConfigurationManager.ConnectionStrings[conectionStringName].ConnectionString;
            //Location of mdf file:
            dbPath = System.Configuration.ConfigurationManager.AppSettings["DbPath"];
            var directory = Path.GetDirectoryName(dbPath);
            dbPathCopy = directory + "\\" + dbCopyName + ".mdf";
            logFileCopy = directory + "\\" + dbCopyName + "_log.ldf";
        }

        /// <summary>
        /// Detach DB after finish using.
        /// </summary>
        protected void DetachTestDbCopy()
        {
            KillProcesses();
            try
            {
                const string Sql = @"sys.sp_detach_db @DbName";
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand(Sql, connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Kill process should be use before detach becosue detach can't be done if process is live.
        /// </summary>
        private void KillProcesses()
        {
            const string Sql = @"SELECT SPId
                                     FROM master..SysProcesses
                                     WHERE DBId = DB_ID(@DbName)";

            const string kill = "exec('kill {0};')";
            try
            {
                List<int> AllSpIds = new List<int>();
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand(Sql, connection))
                using (var killCommand = new SqlCommand(Sql, connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AllSpIds.Add(reader.GetInt16(0));
                        }
                    }
                }

                if (AllSpIds.Count > 0)
                {
                    using (var connection = new SqlConnection(this.connectionString))
                    using (var command = new SqlCommand(kill, connection))
                    {
                        connection.Open();
                        foreach (var SpIds in AllSpIds)
                        {
                            command.CommandText = string.Format(command.CommandText, SpIds);
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Atach DB befor start using.
        /// </summary>
        protected void AttachTestDbCopy()
        {
            try
            {
                if (IsDbAttached()) {
                    DetachTestDbCopy();
                }
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand("exec sys.sp_attach_single_file_db  @DbName, @DbPath", connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
                    command.Parameters.Add("@DbPath", SqlDbType.VarChar).Value = this.dbPathCopy;
                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
        }

        private bool IsDbAttached()
        {
            const string isAttachedSqL = @"SELECT count(*)
                                        FROM sys.master_files
                                        WHERE  DB_NAME(database_id) = @DbName";

            bool isAttached = false;
            try
            {
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand(isAttachedSqL, connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
                    connection.Open();
                    var count = command.ExecuteScalar();
                    isAttached = (int)count > 0;
                }
            }
            catch
            {
                throw;
            }

            return isAttached;

        }

        /// <summary>
        /// Create temporary copy of orginal mdf file with new name.
        /// </summary>
        protected void CreateTestDbCopy()
        {
            DeleteTestDbCopy();
            System.IO.File.Copy(dbPath, dbPathCopy, true);
        }

        /// <summary>
        /// Delete temporary copy of mdf file.
        /// </summary>
        protected void DeleteTestDbCopy()
        {
            if (File.Exists(dbPathCopy))
            {
                File.Delete(dbPathCopy);
            }

            if (File.Exists(logFileCopy))
            {
                File.Delete(logFileCopy);
            }
        }
    }
\$\endgroup\$
1
\$\begingroup\$

This is not usually a good idea:

catch
{
    throw;
}

If you don't need to do anything, the code is more readable by omitting the try block altogether.

You should be able to simplify your code by putting more functionality into IntegrationTest. Can you move Init and TestCleanup into the base, so you don't have to redefine them for every subclass?

\$\endgroup\$
  • \$\begingroup\$ 'catch throw' thing is useful wile I am developing solution. Because if something break, than code will stope at catch line and I can quickly see what is exception. \$\endgroup\$ – Raskolnikov Aug 10 '15 at 6:15
  • \$\begingroup\$ I understand that it can be useful for debugging, but I don't consider it production quality code. \$\endgroup\$ – Pierre Menard Aug 10 '15 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.