Multi-threaded apps and data readers

[Smokey Grindle]

Say I have the code provided below

Dim connection As DbConnection = New SqlClient.SqlConnection("Data
Source=DEVELOPMENT;Database=BENE_Development;Trusted_Connection=True;")
Dim dt As New DataTable
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandText = "SELECT AccountName, accountid from accounts"
cmd.CommandType = CommandType.Text
connection.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try

connection.Close()

Me.lstAccounts.DataSource = dt
Me.lstAccounts.ValueMember = "accountid"
Me.lstAccounts.DisplayMember = "accountname"

which of course uses a data reader, now say I have this application in an
MDI window and this form ran its code but then the user opened up another
form that ran similar code. would the two readers clash and cause an
exception? I'm assumeing yes since they are on the same database connection
(I am using the same connection for all objects, and this is an always open
connection, the connection string above is just for the code example) so,
now I have two data readers trying to excute on a single connection. How do
I prevent them from clashing with each other? I know it is possible because
the data adapter class already does something simliar to this (please dont
tell me to just use the data adapter instead, I'm trying to learn here). How
would you go about handleing this? (I dont want to use MARS either, just
single connection with out multiple active result sets)

thanks a lot!

 

[Miha Markic [MVP C#]]

Hi Smokey,

Since you are creating the conneciton instance just in time (which is a good
behaviour) they won't clash. Because the second reader will get new
*physicall* connection if there are no free ones.
I would refactor your code a bit though, like this (so you guarantee that
connection is closed and it is closed before (if) displaying messagebox
which could remain open for hours):

try
connection.Open()
try
dt.Load...
finally
connection.Close();
end
Catch ex As DbException

MessageBox.Show(ex.ToString)
End Try

 

[Smokey Grindle]

however, my question is what if you used the same connection over and over
without opening and closeing it. Say the application kept the same
connection through out its life, and did this, what would happen? I ask this
because connections can take time to open if its a remote site, we're
talking seconds of time wasted now. So if I kept the connection open all the
time and somehow ended up executing two Load methods with the commands
reader, how would I prevent the readers from clashing or do they already do
this when the load event is executed?

Hi Smokey,

Since you are creating the conneciton instance just in time (which is a
good behaviour) they won't clash. Because the second reader will get new
*physicall* connection if there are no free ones.
I would refactor your code a bit though, like this (so you guarantee that
connection is closed and it is closed before (if) displaying messagebox
which could remain open for hours):

try
connection.Open()
try
dt.Load...
finally
connection.Close();
end
Catch ex As DbException

MessageBox.Show(ex.ToString)
End Try

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Say I have the code provided below

Dim connection As DbConnection = New SqlClient.SqlConnection("Data
Source=DEVELOPMENT;Database=BENE_Development;Trusted_Connection=True;")
Dim dt As New DataTable
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandText = "SELECT AccountName, accountid from accounts"
cmd.CommandType = CommandType.Text
connection.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try

connection.Close()

Me.lstAccounts.DataSource = dt
Me.lstAccounts.ValueMember = "accountid"
Me.lstAccounts.DisplayMember = "accountname"

which of course uses a data reader, now say I have this application in an
MDI window and this form ran its code but then the user opened up another
form that ran similar code. would the two readers clash and cause an
exception? I'm assumeing yes since they are on the same database
connection (I am using the same connection for all objects, and this is
an always open connection, the connection string above is just for the
code example) so, now I have two data readers trying to excute on a
single connection. How do I prevent them from clashing with each other? I
know it is possible because the data adapter class already does something
simliar to this (please dont tell me to just use the data adapter
instead, I'm trying to learn here). How would you go about handleing
this? (I dont want to use MARS either, just single connection with out
multiple active result sets)

thanks a lot!

 

[Miha Markic [MVP C#]]

however, my question is what if you used the same connection over and over
without opening and closeing it. Say the application kept the same
connection through out its life, and did this, what would happen?

Exception would be rised.

I ask this

because connections can take time to open if its a remote site, we're
talking seconds of time wasted now. So if I kept the connection open all
the time and somehow ended up executing two Load methods with the commands
reader, how would I prevent the readers from clashing or do they already
do this when the load event is executed?

Just set connection pool parameters to a good value for you (how many
connection are present in the pool) and do the open/close everytime.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi Smokey,

Since you are creating the conneciton instance just in time (which is a
good behaviour) they won't clash. Because the second reader will get new
*physicall* connection if there are no free ones.
I would refactor your code a bit though, like this (so you guarantee that
connection is closed and it is closed before (if) displaying messagebox
which could remain open for hours):

try
connection.Open()
try
dt.Load...
finally
connection.Close();
end
Catch ex As DbException

MessageBox.Show(ex.ToString)
End Try

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Say I have the code provided below

Dim connection As DbConnection = New SqlClient.SqlConnection("Data
Source=DEVELOPMENT;Database=BENE_Development;Trusted_Connection=True;")
Dim dt As New DataTable
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandText = "SELECT AccountName, accountid from accounts"
cmd.CommandType = CommandType.Text
connection.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try

connection.Close()

Me.lstAccounts.DataSource = dt
Me.lstAccounts.ValueMember = "accountid"
Me.lstAccounts.DisplayMember = "accountname"

which of course uses a data reader, now say I have this application in
an MDI window and this form ran its code but then the user opened up
another form that ran similar code. would the two readers clash and
cause an exception? I'm assumeing yes since they are on the same
database connection (I am using the same connection for all objects, and
this is an always open connection, the connection string above is just
for the code example) so, now I have two data readers trying to excute
on a single connection. How do I prevent them from clashing with each
other? I know it is possible because the data adapter class already does
something simliar to this (please dont tell me to just use the data
adapter instead, I'm trying to learn here). How would you go about
handleing this? (I dont want to use MARS either, just single connection
with out multiple active result sets)

thanks a lot!

 

[Smokey Grindle]

how does the data adapter handle it? becuase it internally uses the data
reader to return tabular results

however, my question is what if you used the same connection over and
over without opening and closeing it. Say the application kept the same
connection through out its life, and did this, what would happen?

Exception would be rised.

I ask this

because connections can take time to open if its a remote site, we're
talking seconds of time wasted now. So if I kept the connection open all
the time and somehow ended up executing two Load methods with the
commands reader, how would I prevent the readers from clashing or do they
already do this when the load event is executed?

Just set connection pool parameters to a good value for you (how many
connection are present in the pool) and do the open/close everytime.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi Smokey,

Since you are creating the conneciton instance just in time (which is a
good behaviour) they won't clash. Because the second reader will get new
*physicall* connection if there are no free ones.
I would refactor your code a bit though, like this (so you guarantee
that connection is closed and it is closed before (if) displaying
messagebox which could remain open for hours):

try
connection.Open()
try
dt.Load...
finally
connection.Close();
end
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try


--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Say I have the code provided below

Dim connection As DbConnection = New SqlClient.SqlConnection("Data
Source=DEVELOPMENT;Database=BENE_Development;Trusted_Connection=True;")
Dim dt As New DataTable
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandText = "SELECT AccountName, accountid from accounts"
cmd.CommandType = CommandType.Text
connection.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try

connection.Close()

Me.lstAccounts.DataSource = dt
Me.lstAccounts.ValueMember = "accountid"
Me.lstAccounts.DisplayMember = "accountname"

which of course uses a data reader, now say I have this application in
an MDI window and this form ran its code but then the user opened up
another form that ran similar code. would the two readers clash and
cause an exception? I'm assumeing yes since they are on the same
database connection (I am using the same connection for all objects,
and this is an always open connection, the connection string above is
just for the code example) so, now I have two data readers trying to
excute on a single connection. How do I prevent them from clashing with
each other? I know it is possible because the data adapter class
already does something simliar to this (please dont tell me to just use
the data adapter instead, I'm trying to learn here). How would you go
about handleing this? (I dont want to use MARS either, just single
connection with out multiple active result sets)

thanks a lot!

 

[Miha Markic [MVP C#]]

It doesn't handle anything.
If an exception occur it will be propagated.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

how does the data adapter handle it? becuase it internally uses the data
reader to return tabular results

however, my question is what if you used the same connection over and
over without opening and closeing it. Say the application kept the same
connection through out its life, and did this, what would happen?

Exception would be rised.

I ask this

because connections can take time to open if its a remote site, we're
talking seconds of time wasted now. So if I kept the connection open all
the time and somehow ended up executing two Load methods with the
commands reader, how would I prevent the readers from clashing or do
they already do this when the load event is executed?

Just set connection pool parameters to a good value for you (how many
connection are present in the pool) and do the open/close everytime.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi Smokey,

Since you are creating the conneciton instance just in time (which is a
good behaviour) they won't clash. Because the second reader will get
new *physicall* connection if there are no free ones.
I would refactor your code a bit though, like this (so you guarantee
that connection is closed and it is closed before (if) displaying
messagebox which could remain open for hours):

try
connection.Open()
try
dt.Load...
finally
connection.Close();
end
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try


--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Say I have the code provided below

Dim connection As DbConnection = New SqlClient.SqlConnection("Data
Source=DEVELOPMENT;Database=BENE_Development;Trusted_Connection=True;")
Dim dt As New DataTable
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandText = "SELECT AccountName, accountid from accounts"
cmd.CommandType = CommandType.Text
connection.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As DbException
MessageBox.Show(ex.ToString)
End Try

connection.Close()

Me.lstAccounts.DataSource = dt
Me.lstAccounts.ValueMember = "accountid"
Me.lstAccounts.DisplayMember = "accountname"

which of course uses a data reader, now say I have this application in
an MDI window and this form ran its code but then the user opened up
another form that ran similar code. would the two readers clash and
cause an exception? I'm assumeing yes since they are on the same
database connection (I am using the same connection for all objects,
and this is an always open connection, the connection string above is
just for the code example) so, now I have two data readers trying to
excute on a single connection. How do I prevent them from clashing
with each other? I know it is possible because the data adapter class
already does something simliar to this (please dont tell me to just
use the data adapter instead, I'm trying to learn here). How would you
go about handleing this? (I dont want to use MARS either, just single
connection with out multiple active result sets)

thanks a lot!