Forward Only Read Access with TableAdapter

[mbrande]

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?

 

[jmcilhinney]

A TableAdapter is just wrapper for a DataAdapter. A DataAdapter is just a wrapper for four Commands. What do you need to do to create a DataReader? Execute a Command.

Dim reader = myTableAdapter.SelectCommand.ExecuteReader()

 

[mbrande]

Interesting.

With Intellisense when I do TableAdapter.'' I see fill's & get's for DataTable's.

I do not see .ExecuteReader anywhere in the TableAdapter. To date I've only seen DataTableReader's work, not just the DataReader.

 

[jmcilhinney]

I suggest that you take a closer look at my code.

 

[mbrande]

You're code is not the issue. The issue is that the .SelectCommand does not exist in the TableAdapter. The default 'Select' Command seems to only be available as the default entry in the 'Fill' Property. In the attached image you can see I have 100% used your code with a TableAdapter in my Project. Again, there is no .SelectCommand.

It looks like the TableAdapter can only be used with DataTables (using .Fill to fill an existing DataTable and .Get to retrieve a DataTable). My screenshot seems to also show this.

Believe me, I wish I could push all Data Access through the TableAdapter... but I have some queries that return large amounts of data that do overload the DataTable. Due to the DataTable's disconnected Data Model, it has to load the entire query into the DataTable before you can use it. This is great for some things, but not when you need millions and millions of rows to write to a text file. In that case it seems impossible to use a TableAdapter.

Please take a look at the screenshot.

 

[JohnH]

A TableAdapter is just wrapper for a DataAdapter.

...that is exposed by the Adapter property. ta.Adapter.SelectCommand

 

[mbrande]

Yes, but you can visually see from my screenshot that there is no Adapter Property of the TableAdapter that you can directly access.

I see nothing that indicates that the TableAdapter can do anything but deal with DataTables. If there is a way that the TableAdapter can do it, please explain.

Peter Bromberg's UnBlog: YEESH! FORGET DATAREADERS!

Technically though, TableAdapters Connect & Release the Connection when they return data. This process is not possible in a db connected state that's required by the DataReader.

 

[jmcilhinney]

JohnH is quite right. You must access the Adapter property to get the DataAdapter and then get the SelectCommand from that. I've never actually tried before and the fact that there's a SelectCommand in the DataSet designer led me to believe that it was a direct property of the TableAdapter, which it's not. The Adapter property is listed on the All tab of the Intellisense menu, or you can simply type it.

 

[JohnH]

Technically though, TableAdapters Connect & Release the Connection when they return data.

True

This process is not possible in a db connected state that's required by the DataReader.

That is not what is meant by the ADO.Net disconnected architecture. Using a datareader you have to open and close the local connection yourself, and do so timely when done using it. It is exactly the same the TA is doing (open, transfer, close) when you call one of it's commands. The associated connection object is exposed by the TA.Connection property.

 

[mbrande]

JohnH is quite right. You must access the Adapter property to get the DataAdapter and then get the SelectCommand from that. I've never actually tried before and the fact that there's a SelectCommand in the DataSet designer led me to believe that it was a direct property of the TableAdapter, which it's not. The Adapter property is listed on the All tab of the Intellisense menu, or you can simply type it.

Selecting the 'All' tab does not expose the 'adapter' property, sorry.

 

[mbrande]

True

That is not what is meant by the ADO.Net disconnected architecture. Using a datareader you have to open and close the local connection yourself, and do so timely when done using it. It is exactly the same the TA is doing (open, transfer, close) when you call one of it's commands. The associated connection object is exposed by the TA.Connection property.

Yes I know I need to manually open/close the db connection when using the datareader. That's exactly my point. It's a 'connected architecture'.

With DataTables/DataSet's and TableAdapters: all data immediately loaded so the connection to the db is 'disconnected' while you use the object. To my knowledge that is exactly what is meant by the 'disconnected architecture'. Back before .NET with RecordSet's, it maintained a db connection while you used the object itself (just like the DataReader). Now feel free to enlighten me if I am wrong here, but I think I'm grasping exactly what a 'disconnected architecture' is. So why even try to reverse engineer the functionality to accessing the 'connection object' or 'adapter object' to make it operate in a more db connected fashion when this is obviously not what it was designed to do?

That is the question I am ending at after doing all of this research. I just wish the TableAdapter had have been built robust enough to natively handle either the connected or disconnected architectures because different application requirements can require a mixture of both.

 

[JohnH]

When you call TA.Fill it open the connection, uses the datareader to transfer the data from database to the local datatable, then closes the connection. Give that a thought in regards to disconnected architecture. How is that any different from you doing the same thing? (open, read, close) Using a datareader is not a connected architecture, and can't be compared to RecordSet. Opening a connection to a database does not mean 'connected architecture', ADO.Net is a disconnected architecture.
ADO.NET Architecture

Although the DataAdapter uses the DataReader to fill the contents of a DataSet, by using the DataReader, you can boost performance because you will save memory that would be consumed by the DataSet, and avoid the processing that is required to create and fill the contents of the DataSet.

This whole discussion is about you asking if you can use the generated TA to open a datareader. Yes, in most cases this is a walk in the park with a cup of tea. The reponses so far is basically explaining 'what is a TA'. TA is a generated object that contains Connection, Adapter and any strongly typed queries added. The Adapter keeps four command objects (select, insert, delete, update). The datareader can be executed from a command. TA.Connection property has Friend access by default, and can be changed with ConnectionModifier property. Adapter property has Protected Friend access and this can't be changed, so it will only be accessible within the assembly or inheritors. If (as it seems) you're operating from a different assembly than the one defining the generated TA you could expose the DataAdapter object by defining a Partial class and adding a Public readonly property for it. However, this is over-complicating things, forgetting the TA and creating a connection and a command object yourself is just two lines of code. Copy the connectionstring and select query from TA if you need to. This is what this whole discussion really boils down to, much ado (ha ha) about nothing:

dim conn as new sqlconnection("connstr")
dim cmd as new sqlcommand("select query", conn)

 

[jmcilhinney]

Selecting the 'All' tab does not expose the 'adapter' property, sorry.

I don't know what to tell you other than that it does for me and apparanelt for JohnH too. Did you try just typing it, as I said in my previous post?

 

[mbrande]

When you call TA.Fill it open the connection, uses the datareader to transfer the data from database to the local datatable, then closes the connection. Give that a thought in regards to disconnected architecture. How is that any different from you doing the same thing? (open, read, close)

John, it is alot different. The DataSet does have a disconnected Architecture, the DataReader itself does not. The difference is that I can access the DataReader data (forward-only) 'before' all the data is loaded (which is what makes this a connected architecture). Again the difference is whether or not you have 'access to the data' before the entire result set is loaded.

In a 'Disconnected' Architecture I have to wait for all the data to load before I have access to it. It is not just forward-only. I can move through the data in reverse. I can sort the data/etc. This John, is a 'disconnected' architecture because you can move through the data without a 'connection' to the database. And this is fine for some things. But not for large amounts of data.

I hope that explains to you how different the data access is, because yes... it is different. Alot different.

Using a datareader is not a connected architecture, and can't be compared to RecordSet.

This is just flat out not true.
Difference between connected and disconnected mode in ado.net - ASP.NET Forums

This whole discussion is about you asking if you can use the generated TA to open a datareader. Yes, in most cases this is a walk in the park with a cup of tea. The reponses so far is basically explaining 'what is a TA'. TA is a generated object that contains Connection, Adapter and any strongly typed queries added. The Adapter keeps four command objects (select, insert, delete, update). The datareader can be executed from a command. TA.Connection property has Friend access by default, and can be changed with ConnectionModifier property. Adapter property has Protected Friend access and this can't be changed, so it will only be accessible within the assembly or inheritors. If (as it seems) you're operating from a different assembly than the one defining the generated TA you could expose the DataAdapter object by defining a Partial class and adding a Public readonly property for it. However, this is over-complicating things, forgetting the TA and creating a connection and a command object yourself is just two lines of code. Copy the connectionstring and select query from TA if you need to. This is what this whole discussion really boils down to, much ado (ha ha) about nothing:

After educating myself regarding the 'limitations' of the 'disconnected ADO.NET architecture', I don't find it usable for my data need. I've already tested the direct database 'connected' method of using the datareader. The PC running the code can execute the command faster and it doesn't max out the memory of .NET and crash the app.

This was my first trip to this forum and I was very disappointed to see how many wrong answers were thrown out there.

 

[JohnH]

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"? 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