Forward Only Read Access with TableAdapter

mbrande

Member
Joined
Apr 15, 2010
Messages
12
Programming Experience
10+
Does the TableAdapter have a Forward Only Read Access Object (like the DataReader)?

It seems the TableAdapter just works the best with the DataTable, which is great but it's disconnected. So if I need to run a huge query in SQL Server that will return huge amounts of data I could max out the DataTable because it has to load the entire result into the object before I can use it. I am just loading the data and outputting it to a text file (no data manipulation required) Is the TableAdapter not suited for this type of data?

Should I just forget about the TableAdapter for this use the DataReader?
 
So, in your opinion, when TA does it (open connection, uses the datareader, close connection) it is "disconnected", but when you do it (open connection, uses the datareader, close connection) you are "connected"?

Oh it isn't just my opinion.
DataSet Vs. DataReader

The TA is designed to: open the db connection, execute the reader, 'load the entire result set into a datatable', close the db connection. Why tweak it to do something in a 'connected' fashion (bypassing the datatable step) when it was never designed to work that way? I guess that's the big question I arrived at.

When I use the DataReader and manually just open a connection, I am processing data before the entire result set is loaded (connected architecture).

There is a difference in with the terms "architecture" and "state" you know. The disconnected ADO.Net architecture of course has the connection in open state while it transfers data between client and database ;)

Yes I did my best to point that out and the article I linked also points that out.
 
When I use the DataReader and manually just open a connection, I am processing data before the entire result set is loaded (connected architecture).
Nope, you are in a connected state while transferring data, same as TableAdapter is in a connected state while transferring data. ADO.Net is and remains a disconnected architecture whichever of its tools you are using.
 
Nope, you are in a connected state while transferring data, same as TableAdapter is in a connected state while transferring data. ADO.Net is and remains a disconnected architecture whichever of its tools you are using.

The 'connection' state an object can function in is what defines whether something is using a 'connected' or 'disconnected' architecture. Your attempt to disassociate connection state's within a database used in objects is misleading and confusing.

Furthermore, ADO.NET supports both a connected and disconnected architecture.
ADONET FAQ - Connected Data Access Architecture of ADO.Net - Programmer's Heaven

In case you don't believe that forum you can find the same thing I had been trying to tell you here:
All You Need to Know about ADO.NET: Part 2/2: ASP Alliance
They also make the connection between the ADO Recordset (so again, yes... this is a valid distinction) and the ADO.NET DataReader.
 
I don't find it confusing, nor have I attempted misleading you in any way. According to MS TA.Fill uses datareader to read the data, so there is conceptually no difference in you using the datareader directly. The main thing you should take notice of using the ADO.Net architecture is the imperative disconnected design to connect, perform you db transfers, then disconnect. Don't leave the connection open longer than necessary. ADO.Nets connection pooling ensures the fastest performance when making several transfers, while freeing up such resources when they are not needed. How you use the data locally is really beside the point, but it is a point that it is locally/detached you handle the data either way.
 
I don't find it confusing, nor have I attempted misleading you in any way. According to MS TA.Fill uses datareader to read the data, so there is conceptually no difference in you using the datareader directly. The main thing you should take notice of using the ADO.Net architecture is the imperative disconnected design to connect, perform you db transfers, then disconnect. Don't leave the connection open longer than necessary. ADO.Nets connection pooling ensures the fastest performance when making several transfers, while freeing up such resources when they are not needed. How you use the data locally is really beside the point.

The difference is with the TA.Fill, the TA is attempting to load all the data into an object and close the connection. When you connect directly (w/o the TA) you can access the data as it comes across, you don't need to wait for the entire set of data to be loaded.

And yes I close the connection after I'm done.

But seriously when you mention:
ADO.Net is and remains a disconnected architecture whichever of its tools you are using.

That is confusing because it is not true. ADO.Net supports BOTH Connected and Disconnected Architecture. It's up to the Developer to decide (based on the project needs) which way to go.

The main thing you should take notice of using the ADO.Net architecture is the imperative disconnected design to connect, perform you db transfers, then disconnect. Don't leave the connection open longer than necessary.

What you need to realize is what you are describing above is a 'connected' architecture. You are processing the data while connected to the db, unlike a disconnected architecture (where you process the data AFTER the db read).
 
What you need to realize
I realize that we will never agree. There will be no further attempts at explaining this to you from me.
 
ADO uses a connected architecture. With ADO, you can create a Recordset and editing that Recordset automatically edits the database because the two are connected. Can you do that using ADO.NET? No you can't. The data you edit using ADO.NET is disconnected from the database, whether you use a DataReader directly or a DataAdapter. The fact that you process the data from a DataReader while connected to the database doesn't mean that the data is connected. You can change the data in any way you want while reading it and the database won't change, because of the disconnected architecture of ADO.NET.
 
Couple of things to point out:

ADO.NET is disconnected because the data that is held locally is held separately from the database data, and changes to one do not instantly cause the other to update. The fact that, at some point in the life cycle of a system, a connection to a database must be formed is NOT a defining characteristic of whether the system uses a connected/disconnected DATA architecture.

The .Adapter property of a TableAdapter is PROTECTED INTERNAL. This means its accessibility is protected (only subclasses can see it) OR internal (only classes in the same assembly can see it)
If youre attempting to use a TableAdapter that you have created in a DLL that you have then referenced in your project, you won't see the .Adapter property because your assembly (my.exe) is referencing a different assembly (the.dll)

As the name of a TableAdapter suggests, it is designed for shunting data between DataTables and databases (or other datasources). If you don't need to use a datatable, you probably wouldnt use a TableAdapter. It is a COINCIDENTAL CONVENIENCE that you can access the DataReader of a table adapter. You might choose to add a query to a a TableAdapter, and thus generate a load of code in your app that you won't use but it would be rather like needing a battery for your tv remote, so you buy a brand new flashlight, because it comes with batteries, then discard the flashlight and just use the batteries..
..I'd advocate just making a DataReader for this. However, if you have a tableadapter that you will genuinely use to load 1 account at a time, edit and send back to the db, but once a month you will dump ALL accounts from the system and write them to a TCP socket, you may choose to reuse the existing commands within your tableadapter:

You'd have to make your query SELECT * FROM accs WHERE accid LIKE @accid
pass a unique ID in daily download/upload of the db
and then pass a % symbol for the accid instead of a unique id when you ExecuteReader at the end of the month
Get your datareader and read from it as you write to the socket.. minimal resource consumption
 
Back
Top