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
}
}
