Why is my SqlConnection entering a broken state during execution?

Question

I have a simple console app that is executing a series of stored procedures about 150,000 times. It's purpose is to import initial database data into a new instance of the database.

It runs fine at work, but at home I am trying to use Sql Server Express, and everything appears fine, but at exactly 25.76% complete the sql connection enters a broken state and generates an InvalidOperation Exception stating that the connection is broken and cannot be used to execute the next command.

I understand what that means, but I can't understand why it is becoming broken and why it happens at exactly the same time on each try of running the console app.

It enters the broken state at 25.76% complete every time, always at that spot, and always on the same line of SQL being executed.

The line that is being executed when the exception occurs is the following:

EXEC [geo].[addUpdateRegion] @countryCode = N'CG', @regionCode = N'08', @regionName = N'Plateaux', @initData = 1

It causes an InvalidOperationException: BeginExecuteNonQuery requires an open and available Connection. The connection's current state: Broken."

I am baffled, because I have timeouts disabled completely. So I'm wondering if SQLExpress maybe has a limit to how many commands you can execute on the same connection?

This is what the code looks like:

using log4net;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;

namespace XYZ.SetupData
{
    class Program
    {
        static ILog logger = null;
        static string connectionString = null;
        static SqlConnection conn = null;
        static double lineCount = 1;
        static double lineIndex = 1;
        static Program()
        {
            logger = LogManager.GetLogger(typeof(Program));
            log4net.Config.XmlConfigurator.Configure();
            var connSettings = ConfigurationManager.ConnectionStrings["theDb"];
            if (connSettings == null || string.IsNullOrEmpty(connSettings.ConnectionString))
                throw new ConfigurationErrorsException("connectionString theDbwas not found or is empty.");
            connectionString = connSettings.ConnectionString;
            conn = new SqlConnection(connectionString);
            conn.Open();
        }

        static void Main(string[] args)
        {
            var baseDir = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory + "sqlFiles");
            var files = baseDir.GetFiles("*.sql", SearchOption.TopDirectoryOnly).Select(file => file.FullName).ToArray();
            lineCount = (double)GetTotalFileLines(files);

            foreach (var file in files)
            {
                ExecuteFileLines(file);
            }
            Console.ReadKey(true);
        }

        #region Utility
        static void WriteProgress(string command)
        {
            double percent = (lineIndex / lineCount) * 100;
            Console.Write("\r Percent Complete: {0}%   ", Math.Round(percent, 2));
            ++lineIndex;
        }

        static int GetTotalFileLines(params string[] fileNames)
        {
            int total = 0;
            foreach (var fileName in fileNames)
                total += (File.ReadLines(fileName).Where(line => !string.IsNullOrEmpty(line) && !line.StartsWith("--")).Count());
            return total;
        }
        static void ExecuteFileLines(string fileName)
        {
            TryRun(() =>
            {
                if (string.IsNullOrEmpty(fileName))
                    throw new ArgumentNullException("fileName");
                if (!File.Exists(fileName))
                    throw new FileNotFoundException("file: " + fileName + " was not found!");
                IEnumerable<string> fileLines = File.ReadLines(fileName).Where(line => !string.IsNullOrEmpty(line) && !line.StartsWith("--"));


                LogInfo("--Staring Execution: " + fileName);
                foreach (var line in fileLines)
                {
                    RunSqlConnection(conn =>
                    {
                        LogInfo("RUNNING | " + line);
                        WriteProgress(line);
                        try
                        {
                            SqlCommand cmd = new SqlCommand(line, conn);
                            cmd.BeginExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            int i = 0;
                        }
                    });
                }
            });
        }
        static void RunSqlConnection(Action<SqlConnection> callBack)
        {
            try
            {
                callBack(conn);
            }
            catch (Exception ex)
            {
                LogError(ex);
                throw ex;
            }
        }
        static void TryRun(Action callBack)
        {
            try
            {
                callBack();
            }
            catch (Exception ex)
            {
                LogError(ex);
                throw ex;
            }
        }
        static void LogError(Exception ex)
        {
            logger.Error(ex.Message, ex);
            Console.WriteLine(ex.ToString());
        }

        static void LogInfo(string message, params object[] parameters)
        {
            logger.Info(string.Format(message, parameters));
        }

        static void LogDebug(string message, params object[] parameters)
        {
            logger.Debug(string.Format(message, parameters));
        }
        #endregion
    }
}

Answer 1

You are trying to process the work on multiple threads. There is a bug in that you are using the same connection concurrently. This is not allowed. Since this is a race condition anything could happen internally.

I'm not sure why you are using the APM pattern at all since it is obsolete. I think PLINQ plus synchronous IO plus one connection per work item actually fits your scenario nicely.