I have several portions of code in different classes that use this or very similar code to pull back some data from an SQL Server database. Most of them work, but I'm running into a problem on a few and really don't understand why. The code above will execute for 60-64 times, retrieving rows but then it blows up with a invalid read error. ive traced down the records for anything hinkey, but it appears to just hit anywhere between those records and stops. There are no calls out from this class to any other class functions, essentially, this is the onyl thing running.
My connection string has a time out of 600 seconds and I have mars set as well. another note; this is being called from a Parallel.ForEach with the parallelism set to 2.
As i said, I use this structure of code in multiple places through out the program, most of it working great, what worries me is not just the error, but the inconsistancy of its occurance.
when I get rid of the Parallel.ForEach and just run it as foreach, it works. I need those threads though, or this takes far too long. And I'm also curious where the reader is closing.
Exception: System.InvalidOperationException--Invalid attempt to call Read when reader is closed.
Called Code:
public string GetUnderwritingType(string id)
{
UnderwritingType uwt = new UnderwritingType();
GetRequiredPercent(id, ref uwt);
GetParticipation(id, ref uwt);
return CalculateUnderwritingType(ref uwt);
}
private void GetRequiredPercent(string id, ref UnderwritingType uwt)
{
DataCommon common = new DataCommon(Settings.Default.ReqPercentSP, id);
try
{
/******* Error on this reader statement ********/
using (SqlDataReader reader = common.Command.ExecuteReader(CommandBehavior.CloseConnection)) {
/* no data */
if (reader.HasRows == false) return;
while (reader.Read())
{
uwt.RequiredPercent.Add(Double.Parse(reader[REQ_PERCENT_FIELD].ToString()));
uwt.MinimumCount.Add(Int32.Parse(reader[MIN_COUNT_FIELD].ToString()));
uwt.UWType.Add(Int32.Parse(reader[UW_LEVEL_FIELD].ToString()));
}
}
}
catch (InvalidOperationException)
{
if (common.Connection.State == ConnectionState.Open) common.Connection.Close();
throw;
}
catch (SqlException)
{
if (common.Connection.State == ConnectionState.Open) common.Connection.Close();
throw;
}
}
Code that sets up connection:
public class DataCommon
{
private readonly SqlCommand _command;
private SqlConnection _connection;
public DataCommon(string storedProcedure, string id)
{
SetConnection();
_command = new SqlCommand(storedProcedure, Connection);
if (_command != null) _command.CommandType = CommandType.StoredProcedure;
_command.Parameters.Add("@Id", SqlDbType.NVarChar).Value = id;
}
/// <summary>
/// Gets the database connection.
/// </summary>
public SqlConnection Connection
{
get { return _connection; }
}
/// <summary>
/// Gets the database command object.
/// </summary>
public SqlCommand Command
{
get { return _command; }
}
/// <summary>
/// Sets the database connection.
/// </summary>
private void SetConnection()
{
_connection = new SqlConnection {ConnectionString = Settings.Default.TestConnection};
_connection.Open();
if (_connection.State != ConnectionState.Open)
throw new DataException(Settings.Default.ConnectionErrorMessage);
}
}
Connection string: Data Source=xxxxx;Initial Catalog=yyyyy;Trusted_Connection=True;MultipleActiveResultSets=True;Connection Timeout=600;
Stack Trace: .Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at GroupDataAccess.EligibilityClassData.GetEligibilityClass(String id) in C:\Subversion\branches\Fimmas\FimmasToInfoHub\GroupHubFeed\GroupData\EligibilityClassData.cs:line 95 at GroupLogic.GroupExtract.BuildEligibilityClass(String id, Group& group) in C:\Subversion\branches\Fimmas\FimmasToInfoHub\GroupHubFeed\GroupLogic\GroupExtract.cs:line 188 at GroupLogic.GroupExtract.<>c_DisplayClass2.b_1() in C:\Subversion\branches\Fimmas\FimmasToInfoHub\GroupHubFeed\GroupLogic\GroupExtract.cs:line 113 at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.Execute()
Initial Call to go through loop (when converted to normal foreach, everythings fine):
ParallelOptions options = new ParallelOptions();
options.MaxDegreeOfParallelism = 2;
Parallel.ForEach(ids, options, id => BuildGroup(id));
calls
private void BuildGroup(string id)
{
Group[] group = { new Group() };
Task eligibilityBlock = Task.Factory.StartNew(() =>
BuildEligibilityClass(id, ref @group[0]));
Task.WaitAny(eligibilityBlock);
GroupList.Add(@group[0]);
}
Which calls
private static void BuildEligibilityClass(string id, ref Group group)
{
EligibilityClassData eligClassData = new EligibilityClassData();
group.EligibilityClassList = eligClassData.GetEligibilityClass(id);
}
which hits into one of those reads.
