Is SqlDataReader Thread Safe?

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I am using SQLDataReader to do a rather simple select statement, and then
running an update in the while loop for the Read method.

When I place the SQLDataReader in a thread delegate or not, it always pulls
duplicates for example my database contains the following:

TestID
1
2
3

And I output my values

string strTestID = "";

while(Connection1.Read())
{
strTest = Connection1[0].ToString();
}

It will output

1
1
2
2
3
3

If I take this out of a thread it will output like it should
1
2
3

Any ideas on what might be going on?
 
Matt,

While the data reader is not thread safe (it says as much in the
documentation), I think that this stems from the connection, and not the
reader itself. You should be marshaling the call across threads, or
establishing the connection on the thread that will access it.

Hope this helps.
 
Nicholas,

Thanks, ya actually I do establish the connection on the thread that will
access it.

Below is a general idea of what I am doing, Console.WriteLine actually will
duplicate each selected record.

private void button3_Click(object sender, System.EventArgs e)
{
ThreadStart DBThread = new ThreadStart(StartDBDelegate) ;
Thread DB = new Thread(DBThread) ;
DB.Start();
}

public void StartDBDelegate)()
{

Invoke(new UpdateLoadTableDelegate(RunUpdateNow));

}

public void RunUpdateNow()
{


string source =
"server=testserver;uid=testuid;pwd=testpassword;database=TestDB";

string selectconn1 = "SELECT TestID as dbTestID FROM TestTable"

SqlConnection conn1 = new SqlConnection(source);
conn1.Open();
SqlCommand executeconn1 = new SqlCommand(selectconn1,conn1);
SqlDataReader Connection1 = executeconn1.ExecuteReader();


while(Connection1.Read())
{
Console.WriteLine (Connection1[0].ToString())
}
}





Nicholas Paldino said:
Matt,

While the data reader is not thread safe (it says as much in the
documentation), I think that this stems from the connection, and not the
reader itself. You should be marshaling the call across threads, or
establishing the connection on the thread that will access it.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Matt said:
I am using SQLDataReader to do a rather simple select statement, and then
running an update in the while loop for the Read method.

When I place the SQLDataReader in a thread delegate or not, it always pulls
duplicates for example my database contains the following:

TestID
1
2
3

And I output my values

string strTestID = "";

while(Connection1.Read())
{
strTest = Connection1[0].ToString();
}

It will output

1
1
2
2
3
3

If I take this out of a thread it will output like it should
1
2
3

Any ideas on what might be going on?
Click to expand...
Click to expand...
 
nicholas,

I actually I have to do this in my Connection1.Read() while loop to get it
to work properly..

string strTestID = "";

while(Connection1.Read())
{
if(strTestID != Connection1[0].ToString()))
{
Console.WriteLine (Connection1[0].ToString())

strTestID = Connection1[0].ToString());
}

}



Nicholas Paldino said:
Matt,

While the data reader is not thread safe (it says as much in the
documentation), I think that this stems from the connection, and not the
reader itself. You should be marshaling the call across threads, or
establishing the connection on the thread that will access it.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Matt said:
I am using SQLDataReader to do a rather simple select statement, and then
running an update in the while loop for the Read method.

When I place the SQLDataReader in a thread delegate or not, it always pulls
duplicates for example my database contains the following:

TestID
1
2
3

And I output my values

string strTestID = "";

while(Connection1.Read())
{
strTest = Connection1[0].ToString();
}

It will output

1
1
2
2
3
3

If I take this out of a thread it will output like it should
1
2
3

Any ideas on what might be going on?
Click to expand...
Click to expand...
 
Back
Top