DataSet.Fill Times out in Transaction

[Joe Cool]

I have been tasked to convert the methods in a data access class from
returning an open OdbcDataReader to a DataTable. I use the standard
type of code, like:

adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
dataSet = new DataSet();
adapter.Fill(dataSet, "Data");
adapter.Dispose();
dataTable = dataSet.Tables["Data"];

Where command is a command with an appropriate SQL SELECT statement.

This works fine UNLESS the connection that the command is associated
with is a connection that has an open Transaction. The adapter.Fill
method times out.

Since the row being read is the same row just inserted using the same
transaction, I tried adding IsolationLevel.ReadUncommitted to the
BeginTransaction method, but that did not help. Really didn't expect
it to since a OdbcDataReader read the row just fine with the default
isolation level.

Any help would be appreciated!!

 

[William Vaughn \(MVP\)]

I would run sp_lock to see if a lock contention is blocking the
operation--of course this assumes you're using SQL Server, but now that I
see you're using ODBC, it probably isn't and you're basically on your own
AFA diagnostic tools.
I would also tend to lean toward simpler code: (pseudo code follows)

Dim tb as New DataTable
Dim dr as xxDataReader
dim cmd as new xxCommand(MySelectStatement, myConnection)
cn.open
dr=cmd.ExecuteReader
tb.load (dr)
cn.close

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Joe Cool]

I would run sp_lock to see if a lock contention is blocking the
operation--of course this assumes you're using SQL Server, but now that I
see you're using ODBC, it probably isn't and you're basically on your own
AFA diagnostic tools.
I would also tend to lean toward simpler code: (pseudo code follows)

Actually we do use a SQL Server. We use ODBC to connect for various
reasons.

But the issue was not a deadlock causing the problem. I am using a
local database set up just for testing code changes and no one else
has any access to it.

Dim tb as New DataTable
Dim dr as xxDataReader
dim cmd as new xxCommand(MySelectStatement, myConnection)
cn.open
dr=cmd.ExecuteReader
tb.load (dr)
cn.close

hth

Actually, your pseudo-code helped. The way I was loading the data
table was the way I learned it from sample code from Microsoft. I had
no idea it could be done so much simpler!!

I have been tasked to convert the methods in a data access class from
returning an open OdbcDataReader to a DataTable. I use the standard
type of code, like:

adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
dataSet = new DataSet();
adapter.Fill(dataSet, "Data");
adapter.Dispose();
dataTable = dataSet.Tables["Data"];

Where command is a command with an appropriate SQL SELECT statement.

This works fine UNLESS the connection that the command is associated
with is a connection that has an open Transaction. The adapter.Fill
method times out.

Since the row being read is the same row just inserted using the same
transaction, I tried adding IsolationLevel.ReadUncommitted to the
BeginTransaction method, but that did not help. Really didn't expect
it to since a OdbcDataReader read the row just fine with the default
isolation level.

Any help would be appreciated!!

 

[William Vaughn \(MVP\)]

In my experience, it's perfectly reasonable to expect that an application
can easily lock itself out from other operations.
With SQL Server you can run the profiler to see what's getting executed and
check sp_lock for contention.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

I would run sp_lock to see if a lock contention is blocking the
operation--of course this assumes you're using SQL Server, but now that I
see you're using ODBC, it probably isn't and you're basically on your own
AFA diagnostic tools.
I would also tend to lean toward simpler code: (pseudo code follows)

Actually we do use a SQL Server. We use ODBC to connect for various
reasons.

But the issue was not a deadlock causing the problem. I am using a
local database set up just for testing code changes and no one else
has any access to it.

Dim tb as New DataTable
Dim dr as xxDataReader
dim cmd as new xxCommand(MySelectStatement, myConnection)
cn.open
dr=cmd.ExecuteReader
tb.load (dr)
cn.close

hth

Actually, your pseudo-code helped. The way I was loading the data
table was the way I learned it from sample code from Microsoft. I had
no idea it could be done so much simpler!!

I have been tasked to convert the methods in a data access class from
returning an open OdbcDataReader to a DataTable. I use the standard
type of code, like:

adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
dataSet = new DataSet();
adapter.Fill(dataSet, "Data");
adapter.Dispose();
dataTable = dataSet.Tables["Data"];

Where command is a command with an appropriate SQL SELECT statement.

This works fine UNLESS the connection that the command is associated
with is a connection that has an open Transaction. The adapter.Fill
method times out.

Since the row being read is the same row just inserted using the same
transaction, I tried adding IsolationLevel.ReadUncommitted to the
BeginTransaction method, but that did not help. Really didn't expect
it to since a OdbcDataReader read the row just fine with the default
isolation level.

Any help would be appreciated!!

 

[Joe Cool]

In my experience, it's perfectly reasonable to expect that an application
can easily lock itself out from other operations.
With SQL Server you can run the profiler to see what's getting executed and
check sp_lock for contention.

I had run the profiler. An Insert had started and completed before the
select started and later timed ot.

--

Actually we do use a SQL Server. We use ODBC to connect for various
reasons.

But the issue was not a deadlock causing the problem. I am using a
local database set up just for testing code changes and no one else
has any access to it.

Actually, your pseudo-code helped. The way I was loading the data
table was the way I learned it from sample code from Microsoft. I had
no idea it could be done so much simpler!!

I have been tasked to convert the methods in a data access class from
returning an open OdbcDataReader to a DataTable. I use the standard
type of code, like:
adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
dataSet = new DataSet();
adapter.Fill(dataSet, "Data");
adapter.Dispose();
dataTable = dataSet.Tables["Data"];
Where command is a command with an appropriate SQL SELECT statement.
This works fine UNLESS the connection that the command is associated
with is a connection that has an open Transaction. The adapter.Fill
method times out.
Since the row being read is the same row just inserted using the same
transaction, I tried adding IsolationLevel.ReadUncommitted to the
BeginTransaction method, but that did not help. Really didn't expect
it to since a OdbcDataReader read the row just fine with the default
isolation level.
Any help would be appreciated!!