Database Race Condition?

I have a Race condition where each time I run my code, the number of items returned changes and is slightly different for each run but if I step through the code, I see all records as expected.
My gut is that I was trying to make the db call thread same and was using IAsyncResult to accomplish that. I may be way off track here...

In my main program, I am instantiating a class that does some SQL DB work like this:
Database db = new Database();
db.BeginDatabaseCall();

Open in new window


my Database.cs
public void BeginDatabaseCall()
{
	SqlConnection conn = new SqlConnection(strMyConnectionString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	conn.Open();
	cmd.BeginExecuteReader(EndDatabaseCall, cmd);
}
	public void EndDatabaseCall(IAsyncResult result)
{
	SqlCommand cmd = result.AsyncState as SqlCommand;
		try
	{
		using (SqlDataReader reader = cmd.EndExecuteReader(result))
		{
			while (reader.Read())
			{
				aryLO.Add(reader["LO"]);
				aryPR.Add(reader["PR"]);
				aryBLN.Add(reader["BLN"]);
				aryMS.Add(reader["MS"]);
			}
		}
	}
	catch
	{
		throw;
	}
	finally
	{
		cmd.Connection.Close();
		cmd.Connection.Dispose();
		cmd.Dispose();
	}
}

Open in new window


Thanks in advance for any assistance or guidance you can provide on this topic.
LVL 12
Gregory MillerGeneral ManagerAsked:
Who is Participating?
 
Gregory MillerConnect With a Mentor General ManagerAuthor Commented:
I took the code above and rewrote it to solve the problem...

public void BeginDatabaseCall()
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
	conn.Open();
	SqlDataReader reader = cmd.ExecuteReader();

	try
	{
		while (reader.Read())
		{
			aryLO.Add(reader["LO"]);
			// NULL Prevention
			if (string.IsNullOrEmpty(reader["PName"].ToString())) { aryProc.Add("Unassigned"); } else { aryProc.Add(reader["PName"]); }
			aryBLast.Add(reader["BLast"]);
			aryMS.Add(reader["Status"]);
		}
	}
	catch(Exception ex)
	{
		System.Windows.Forms.MessageBox.Show(ex.ToString());
		throw;
	}
	finally
	{
		cmd.Connection.Close();
		cmd.Connection.Dispose();
		cmd.Dispose();
	}
}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.