ADO.NET with Visual Basic .NET

Posted August 25, 2004

Chat Date: August 3, 2004

Please note: Portions of this transcript have been edited for clarity

Introduction

Moderator: Ed_H (Microsoft)
Welcome to today’s chat on ADO.NET with Visual Basic .NET. We have members of the Visual Basic team here to answer your questions today. I will ask the hosts to introduce themselves.

Host: DavidSc (Microsoft)
My name is David Sceppa. I'm a Program Manager on the data access team and author of Microsoft ADO.NET from Microsoft Press.

Host: Steve (Microsoft)
Hi, I'm Steve Lasker. I'm a Program Manager responsible for Data Designtime features such as the Data Sources Window, Typed DataSet Designer and Data Configuration Wizards..

Host: SteveS (Microsoft)
Hi all - I'm Steve Stein, a programmer/writer on the Visual Basic team.

Moderator: Ed_H (Microsoft)
And I am Ed Hickey, Visual Basic MVP Lead. Glad you all could make it today!

Moderator: Ed_H (Microsoft)
Let's get started! Fire away with your questions for our hosts.

Start of Chat

Host: SteveS (Microsoft)
Q: What is ADO.NET and how does it work?
A: ADO.NET is the data access technology in the .NET Framework. Check out the Overview of ADO.NET topic on MSDN : https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconoverviewofadonet.asp

Host: Steve (Microsoft)
Q: NicholasPaldino_MVP I love the new data functionality in the IDE in VS.NET. However, when generating the data adapters, it is frustrating that the Connection property is internalized. I would prefer that you make it a public property, so that...
A: We've heard from customers that not all want the connection exposed publicly. Many want their connection information kept internal. However, using the partial class, you can expose the property as well as manipulate where the value is retrieved from.

Host: Steve (Microsoft)
Q: how can you data bind with My.Settings?
A: You can add the My.Settings class using the Data Sources Window and bind it up with the Data Connector using the Object Binding.

Host: DavidSc (Microsoft)
Q: (Cor) Simple question, how is it possible not to show an empty row in a datagrid with an empty dataset, while I can continue using dataview.newrow and/or currencymanager.newrow.
A: In the 1.x versions of the Framework, your best bet is to set DataView.AllowNew to False. In Visual Studio 2005, the DataGridView has an AllowUserToAddRows property, which you would set to False.

Host: DavidSc (Microsoft)
Q: (Cor) Other simple question is there a difference between da.update(ds) ; da.fill(ds) and da.update(ds); ds.clear ; da.fill(ds).
A: Yes, there is a difference. After the second example (with a call to Clear), your DataSet will be in the same state as the database.

Host: DavidSc (Microsoft)
Say another user has deleted data in your database since the last time you fetched the results of your query. Without the call to Clear, that row would still reside in your DataSet.

Host: DavidSc (Microsoft)
Q: (Cor) David, I thought that than the dataview.addnew was not working anymore (referring to my first question) am I wrong with that?
A: To the best of my knowledge, it's working just fine. Ed, however, may argue that I've achieved statelessness.

Host: Steve (Microsoft)
Q: NicholasPaldino_MVP : Why are you not dogfooding Generics when it comes to creating the CLR representations of stored procedures or inserts, etc, etc. If the parameter is nullable, why not use the Nullable type, instead of an overload that takes all object parameters?
A: Great question. In beta 2, or the next community technology preview, you will see exactly that. The parameters collection of the TableAdapter Command Objects will expose a AllowDBNull parameter. If set to true, we will generate Nullable(Of T) for all parameters. We will make best attempts at setting the default of AllowDBNull, but the developer can override the value with the designer.

Host: SteveS (Microsoft)
Q: BillBurrows : Several people in a seminar I taught asked "Why go to ADO .NET?" They said they were happy with ADO. Any pointers on what to tell them?
A: Bill, check out these topics on MSDN: Benefits of ADO.NET https://msdn.microsoft.com/library/en-us/vbcon/html/vbconBenefitsOfADO.asp?frame=true and ADO

Host: SteveS (Microsoft)
https://msdn.microsoft.com/library/en-us/vbcon/html/vbconADOPreviousVersionsOfADO.asp?frame=true

Host: Steve (Microsoft)
Q: scottw : The problems I have are related to navigation to a new record, after using the AddNew() method, when bound to individual controls.
A: Actually, this isn't limited to just C#. In Beta 1 the AddNew() of the DataConnector doesn't automatically move to the new record. This has been fixed since Beta 1. To work around this in B1, simply handle the AddingNew event and set the DataConnector.Position to the Count.

Host: DavidSc (Microsoft)
Q: (Cor) However had it performance disadvantages?
A: If I'm understanding the question correctly, you're referring to calling Clear prior to Fill possibly running more slowly than simply calling Fill. The DataAdapter still needs to pull the results of your query across the wire, regardless of whether or not the corresponding rows already exist in your DataSet. There may be some small difference in performance in pulling the results off the wire. I haven't run a perf test for that scenario.

Host: DavidSc (Microsoft)
My recommendation would be to do some perf checking of your own.

Host: Steve (Microsoft)
NicholasPaldino_MVP : Excellent. That's exactly what I want to see, Make sure the C# profile has those enabled by default

Host: Steve (Microsoft)
A: Thanks Nicholas. This came directly from the MVP summit. The defaults aren't based on language, but rather can we determine if the column supports null within the query or the database. When we're sure, we default to AllowDBNull=True. When we're not sure, we set AllowDBNull=false and don't generate Nullable(Of T) parameters.

Host: SteveS (Microsoft)
Q: scottw : I can't even get them to work using the dataform wizard... I won't use semicolons either
A: It could be related to a known bug where the default value of Null in one of the records cannot be handled by the control. Check out this KB article. The article is specific to a Checkbox but it also applies to other controls that cannot render Null values.

Host: SteveS (Microsoft)
BUG: AddNew Method of CurrencyManager Fails with Bound CheckBox

Host: SteveS (Microsoft)
https://support.microsoft.com/default.aspx?scid=kb;en-us;326440

Host: Steve (Microsoft)
Q: NicholasPaldino_MVP : Also, my previous question regarding connections was in reference to table adapters, not data adapters specifically.
A: Yeah, we've heard very clearly that users want to only expose the data, and not how the data is actually retrieved. For instance we no longer expose the CommandCollection or even the Transaction property. Again, it doesn't block a user to enable this, we just don't default to this exposure.

Host: Steve (Microsoft)
Q: NicholasPaldino_MVP : Steve: Is there an easy way to expose the Connection without having to modify machine-generated code?
A: Yes, sort of. The generated code lives in the equivalent of NorthwindDataSet.designer.vb/cs file. The developers code lives in NorthwindDataSet.vb/cs files. So, you can edit the code without the DataSetGenerator clobbering over your code. :) Hey, we do try to learn from the past.

Host: DavidSc (Microsoft)
Q: (Cor) Sorry, I find it very confusing in this chat that not is told in the answers are for 1.x or for 2.0 and as well not with the questions
A: (1.x) Very good point. (2.0) Whidbey is great. Tell your friends. Try it. Send feedback.

Host: Steve (Microsoft)
Q: NicholasPaldino_MVP : Steve: I was hoping there was a more elegant way to go about it.
A: Yeah, basically we have a certain level of support within the designers that we are really focused on. Then, there are a bunch of requests that we just can't get all them done, so rather then have the old glass ceiling, we have the partial class to "do with what you will". I suppose someone could write a plug-in to add more stuff to the generated code, but that's another story...

Host: Steve (Microsoft)
Q: Bill: You said to add the My.Settings to the Data Source window, but that does not actually work. Is this a scenario you have tested yet ?
A: I haven't tested on B1, but will try while we chat.

Host: SteveS (Microsoft)
Q: scottw : Next problem is with child datatables in a dataset. All controls appear to work fine in navigation, but combo boxes retain the value of the first record only
A: Bind the ComboBox as follows:

Host: SteveS (Microsoft)
Set the ComboBox.Datasource to your dataset Set the ComboBox.DisplayMember to ParentTable.FieldToDisplay Set the ComboBox.ValueMember to ParentTable.ForeignKeyField then bind the ComboBox.SelectedValue to the Child.ForeignKeyField

Host: SteveS (Microsoft)
Q: scottw : SteveS_MS - The data actually resides in the child table, the combo box is loaded with a lookup separately, will that still work, or do I need to add another child table?
A: the combobox needs to know what item to display for the current record so if it is not bound to a related table the databindings don't know what item in the list to display.

Host: Steve (Microsoft)
Q: Bill : You said to add the My.Settings to the Data Source window, but that does not actually work. Is this a scenario you have tested yet ?
A: Hmmm, I just checked B1 and it appears the My.Settings class isn't showing up a choice in the Data Sources Window Wizard. However, you can do this in code with the DataConnector.

Host: Steve (Microsoft)
Create a new DataConnector named SettingsDataConnector on your form along with a DataGridView.

Host: Steve (Microsoft)
In the Form.Load set the DataConnector.DataSource=My.Settings. Set the DataGridView.DataSource = SettingsDataConnector. Be sure to add a Settings value to the Settings designer.

Host: Steve (Microsoft)
Voila, settings are databound. We intend to either auto show this in the Data Sources Window or allow you to easily add it. It's just a bug in B1.

Host: DavidSc (Microsoft)
Q: (Bill) top 5 new cool features in ADO.NET?
A: I'm assuming we're talking about 2005/2.x/Whidbey. Here are five cool features, in somewhat-alphabetical order:

Host: DavidSc (Microsoft)
Asynchronous queries - You can issue true asynchronous queries using SqlClient.

Host: DavidSc (Microsoft)
Better reader integration with DataSets and DataTables - You can now fill DataSets with the contents of a reader, and you can read the contents using a reader.

Host: DavidSc (Microsoft)
Binary serialization of DataSets - You don't have to serialize your DataSet as XML

Host: DavidSc (Microsoft)
DataTables now have features that were previously only available in DataSets, such as serialization

Host: DavidSc (Microsoft)
New common provider model - This simplifies the process of finding installed providers and writing less provider-specific code.

Host: SteveS (Microsoft)
Q: scottw : STteveS_MS - So, could it be that I just haven bound the ValueMember then?
A: Yes, try binding the ValueMember to the UniqueID of the record you want to display.

Host: Steve (Microsoft)
Q: Cor: Multi-user can mean a lot of things. Are you referring to a user that brings data down to their client, yet data may be changing on the server? Steve, just refresh the dataset that is all what is an efficient method
A: I'm not sure this is what you’re asking, but the DataAdapter.Fill will merge rows that have the same Primary Key. So, you can issue it multiple times and get updates from the server.

Host: SteveS (Microsoft)
Q: Cor : David is there as well an datatable.haschangen in 2.0?
A: We're currently looking into this but no official decision has been made yet.

Host: DavidSc (Microsoft)
Q: (Bill) Can you give an example of Asynchronous queries?
A: Say you're running ASP.NET code to generate a web page, and your page needs to display data from three different databases. With synchronous queries, each time you query the database your code has to wait for the database to send results.

Host: DavidSc (Microsoft)
With asynchronous queries, you can send each query to each database and continue on with your code. Then, when the databases start to return data, you can process the results.

Host: DavidSc (Microsoft)
The main benefit here is that your ASP.NET code can do other things while it waits for the database to respond with results.

Host: Steve (Microsoft)
Q: scottw : last question, have a lot of issues updating tables with multiple field keys... is there any known issues that anyone can think of? This updates fine, but when I use the "merge" function, it creates a conflict and then the dataset contains two copies.
A: Yeah, this is a known issue that we are solving with a new property on the DataAdapter class called FillLoadOption. This should solve the issue.

Host: Steve (Microsoft)
Q: Cor : Yes however (I have never) when I would have a dataset of 10000 rows that means giving them all to the server, clear and fill them again
A: The DataAdapter will only send back rows that have been changed. You can see this by calling the GetChanges on the DataTable. But I see your point of knowing which rows to then bring back down to the client. You can do some stuff with a DataTimeStamp column to know what's changed since you first issued the query...

Host: Steve (Microsoft)
Q: scottw : Steve, was the multi field key problem all versions of ADO.NET?
A: I believe so.

Host: DavidSc (Microsoft)
Q: (Bill) Ah, okay so the async queries are just a wrapper over what we could do today by spawning our own thread ? Do you include automatic marshalling back to the UI thread in winforms ?
A: That interpretation makes sense, but this is a much richer model. You'll still need to work with the background UI thread. We are working on providing more thorough documentation for this feature.

Moderator: Ed_H (Microsoft)
This has been a great chat. Thank you to everyone for contributing. Unfortunately, it is time to go. Thanks for participating, and we'll see you next time!

Host: DavidSc (Microsoft)
Thanks, everyone. Happy programming!

For further information on this topic please visit the following:

Newsgroups: microsoft.public.dotnet.languages.vb

VB .NET Transcripts: Read the archive of past VB .NET chats.

Website: Visit the Microsoft Visual Basic .NET site.

Top of Page