ADO.NET with Visual Basic .NET (March 29, 2005)

Posted: Tuesday, April 4, 2005

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

Introduction

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

DavidSc_MS (Expert):
My name is David Sceppa. I'm a Program Manager on the team that owns ADO.NET and am also the author of Microsoft ADO.NET, available from Microsoft Press.

EddieG_MS (Expert):
Hi,
I am Eddie Goziker.
I am Dev at Visual Studio Data team.
We own Database Explorer, Database Project, Query Designer and TSQL debugging.

Eric_MS (Expert):
Hi, I'm Eric Gruber and I work on the Visual Basic QA team focusing on the data design features of Visual Studio.

John_MS (Expert):
Hi, I am John! I am a developer in the VB.Net data team working on the ADO.Net design time features.

Kris_MS (Expert):
My name is Kris langohr. I am a tester on the Visual Basic QA team focusing on Data design features.

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

As you can probably tell, this is a new chat client. To submit a question for our hosts to answer, please use the "Submit a Question" radio button in the bottom room, then click "Send", this way we can get them in the queue to answer. If you just want to add a comment to the chat window, use the "Guest Chat" radio button.

Let's get started! Fire away with your questions for our hosts.

Start of Chat

DavidSc_MS (Expert):
Q: Hi People! Hi David! I have your book and it is good but lacks some information on using DataSets on multi-form Windows Forms applications. Or maybee I just didn't find it...
A: Thanks for the kind words, Alfred. Could you provide a little more information on the multi-form scenario you described?

Steve_MS (Expert):
Hi all - I'm Steve Stein, a programmer/writer on the VB team.

DavidSc_MS (Expert):
Q: If you have your form bound to a dataset that is sorted by name, how do you determine the position of a record if you only know the value of a different column, like the ID number?
A: Great question, Mike. Your best bet is to perform a look-up using the given value, then find the value of the column(s) used in the sort. Then use that value to find the position of the row based on the sort criteria.

SteveLas_MS (Expert):
Q: I've been developing MDI appilcations and haven't found the best way to lay out my typed DataSets. One per form vs. One per application/module.
A: This is a routine question. The simplest model is 1 DataSet for your Lookup Data, such as the list of states, and codes that don’t need to change during your app. Basically you load a LookupDataSet with data and share will all forms. Then you create a 2nd DataSet for the data you will edit within the application. For an app that has 10 editable Tables, you’d create a Typed DataSet with these 10 tables. For a particular form you may only load a few of those tables. You can create multiple datasets, one per logical unit of work. However, it becomes a little difficult to manage similar tables across multiple DataSets. So, you can do both. It’s up to you which model you’d like to use.

Eric_MS (Expert):
Q: why the fill method of the sqldataadapter only accept dataset or datatable like argument and not an custom object ?
A: Datasets and DataTables are the ADO.Net structure which DataAdapters are built around. Datasets/DataTables keep track of rowstate and allow the DataAdapter to send back changes to the database for just the rows that were changed. Other custom types don't have this functionality so the Dataset and DataTable solve this problem well.

EddieG_MS (Expert):
Jucat2005: “Integrated Security” means you will be authorized at SQL server as the NT USER currently logged in at your client machine

DavidSc_MS (Expert):
Q: Will there be any way to hide/encrypt the connection string on an ADO.NET connection object? IE. Create the connection remotely, pass it back to the client, I don't want the client to see the connection string.
A: Interesting question, Mark. Your best bet would be to hide such data access internals and the actual connection object inside of a data access layer. You could move that layer to a middle-tier in the form of a Web Service to make sure that information is not available to client applications.

John_MS (Expert):
Q: Back in the day of working with VB6, ADO and Office applications we had hooks that would let us efficently transport data (such as the filling of a worksheet). Will we ever get that again other than via code?
A: Yes, in the coming version of Visual Studio 2005, you should have a much better experience to use the office application.

EddieG_MS (Expert):
Q: How is the Connection string with "Integrated Security" ???
A: Jucat2005: “Integrated Security” means you will be authorized at SQL server as the NT USER currently logged in at your client machine

EddieG_MS (Expert):
Q: There are no BEFORE triggers in SQL Server, why? A "BEFORE TRIGGER" can make modifications in the database before any validation (check constraint, referential constraint, ...) rule take effects.
A: I know SQL server guys were considering this for next release.
It a question of prioritization.

DavidSc_MS (Expert):
Q: Is there any chance that Microsoft releases System.Data source code as was done with Rotor/SSCLI?
A: There are people investigating this possibility and I'll definitely forward your interest along to those people. Thanks!

Eric_MS (Expert):
Q: what happend with the business object, can create it or i forced to use the dataset always ?
A: Yes, you can bind to any custom object instead of datasets and we've made the experience much better in Whidbey. You can define what objects you want to bind to in the designer and the property editors show the You will still need to get the data from your database into your custom object though somehow.

EddieG_MS (Expert):
Kazi: Yukon, i guess, but I am not sure it make it, let me check

Ed_H_MS (Moderator):
Q: John_MS: sorry, had a comma at the end. The link works. Thx.
A: Excellent - glad it works for you now.

EddieG_MS (Expert):
Kazi: Yukon, i guess, but I am not sure it make it, let me check

DavidSc_MS (Expert):
Q: Is there a specific reason why SqlDataAdapter is sealed (NotInheritable)? I was trying to create some customized components to put on the toolbox and could derive SqlDataAdapter, SqlConnection, etc.
A: Many of the .NET Framework classes are marked sealed to avoid possible breaking changes in future releases. While wrapping classes can cause some frustration, it's still preferable to running into unexpected behavior when upgrading to a new release.

Eric_MS (Expert):
Q: what happend with the business object, can create it or i forced to use the dataset always ?
A: (hit enter too soon) Yes, you can bind to any custom object instead of datasets and we've made the experience much better in Whidbey. You can define what objects you want to bind to in the designer and the objects will show up in the property grid pickers and in our new DataSources Window which allows you to do drag/drop to quickly create bound UI. After creating your UI which gets bound to the custom object, you just have to write the code to load your objects up at runtime.

EddieG_MS (Expert):
Q: Eddie: Thanks for the answer, but what is next release, did you mean Yukon or after Yukon?
A: Kazi: Yukon, i guess, but I am not sure it make it, let me check

DavidSc_MS (Expert):
Q: David: There are similar studies in the Windows Forms group. As I told them, I woud be great to have the source code and debug symbols for our own debugging purposes.
A: Thanks, Alfred. We've received similar feedback from a number of developers. I can't promise anything, but I'll be sure to pass the request along.

DavidSc_MS (Expert):
Q: Yesterday I received a copy of "SQL Server 2005 Beta 2 Resource Kit" wich contains an e-book: "A First Look at SQL Server 2005 for Developers" from AW... Inside it mentions ObjectSpaces.. Are ObjectSpaces going to be released in Whidbey?
A: ObjectSpaces will not be part of the next release of the .NET Framework.

Eric_MS (Expert):
Q: what happend with the business object, can create it or i forced to use the dataset always ?
A: The DataAdapter knows what the DataSet/DataTable looks like and how it works so its clear on how to fill it. With custom objects, it would be really difficult to be able to know how to construct any object and then fill it with data in the appropriate places. I agree that the scenario you suggest would be really nice to have but its really challenging to do in a generic way.

EddieG_MS (Expert):
Q: Eddie: Thanks for the answer, but what is next release, did you mean Yukon or after Yukon?
A: Yukon will not support "Before" triggers

Eric_MS (Expert):
Q: but why the sqldataadapter can not load the data in my custom object directly ? ( without create a dataset )
A: The DataAdapter knows what the DataSet/DataTable looks like and how it works so its clear on how to fill it. With custom objects, it would be really difficult to be able to know how to construct any object and then fill it with data in the appropriate places. I agree that the scenario you suggest would be really nice to have but its really challenging to do in a generic way.

Steve_MS (Expert):
Q: Will we be able to inherit/extend DataSets & DataTables easier?
A: Inheriting datasets and datatables will be the same for VS2005 as in previous versions.

DavidSc_MS (Expert):
Q: Is DataSet going to better support a GUID type for auto generated PKs in 2.0?
A: You won't be able to achieve this functionality by setting a property, like with auto-increment values. However, the DataTable does support a new event that fires when a new row is added. You could use this event to assign new Guid values.

DavidSc_MS (Expert):
Q: David: Is there going to be another edition of your book upon the release of whidbey?
A: A second edition is in the works. Thank you for the interest.

Steve_MS (Expert):
Q: Will we be able to inherit/extend DataSets & DataTables easier?
A: Extending datsets and datatables has been greatly improved through the use of Partial classes. Placing user code into the partial classes eliminates the potential loss of code when the classes are regenerated.

EddieG_MS (Expert):
Q: Have you done any benchmarking from a performance perspective to know when the number of concurrent user's grows so large that it's more appropriate to use a dedicated account for the connection rather than use windows authentication?
A: There should be no difference that to use for the connection - SQL authentication or NT authentication

DavidSc_MS (Expert):
Q: In ADO.NET, is the binding mechanism under the good all XML mapping?
A: You're asking about data binding for Windows forms, correct? This functionality is based on some basic interfaces rather than XML mapping.

Eric_MS (Expert):
Q: you only talking about *binding* custom object but what happend if my custom object is a middler tier object. how can another users see the properties values because the object doesn't contain values ?
A: They won't be able to see the values at runtime, just the properties. Its just like in VS2003 how you can see the columns in a data table, with Whidbey you can see the properties of your object at design time. At runtime you will need to fill the values but the controls bound to that object will automatically update to show those values and commit them back to the object.

DavidSc_MS (Expert):
Q: What is an easy way to detach a DataTable From one DataSet and attach it to another?
A: If the end goal is to copy the data and schema to another DataSet, your best bet is likely DataSet.Merge or (DataSet.Tables.Add(MyTable.Copy())

Steve_MS (Expert):
Q: Steve, I miss something because I find it very easy to inherit a dataset, what will be changed?
A: There are no changes to how you inherit a dataset. The improvements are on extending the dataset (through partial classes)

Eric_MS (Expert):
Q: maybe if the custom object looks like a datarow where each property is like a datatable field !!!!
A: You can bind to any custom object with properties in Whidbey. For example, you could bind to an collection of strings, integers, etc.

Steve_MS (Expert):
Q: Steve_MS: are there any plans to make it easier to inherit/extend even a DataRow?
A: You can extend functionality on a datarow using Partial classes just like datasets. As far as inheriting the behavior will be the same as previous versions.

DavidSc_MS (Expert):
Q: Is there a recommended way of sharing DataSets between different Windows Forms? I've been passing them on during the form's construction but had a lot of troubles binding controls to the DataSource since the design time DataSet wasn't the runtime DataSet..
A: That's the main drawback to binding the same DataSet to multiple Windows Forms, having to re-wire the bound controls to the DataSet supplied at run-time. That's currently your best option. We are addressing this scenario in the next version of the .NET Framework through the BindingSource, which essentially sits between the bound controls and the DataSet. In Visual Studio .NET 2005, you would simply assign the run-time DataSet to the BindingSource and ... violà, you're done!

Kris_MS (Expert):
Q: this question may be best suited for eddieg. I'm considering an application that needs to read data from another applications simultaneously running window and use it in my own application. any hooks in vb6, or technologies such as API that i can use ?
A: Jerry thanks for the question. From my understanding of your question the best way to share the data between two applications is to use some sort of common mechanism that both applications can read. A temporary file would be a good place to start.

DavidSc_MS (Expert):
Q: DavidSc_MS: I was hoping there was a way to move the DataTables without having to create a new copy of the DataTable. (EX. if the DataTable has 100,000 rows, the perf is bad).-
A: You're correct. Creating a separate copy of a hundred thousand rows or so in this fashion will not be as performant as copying that area in memory. I'm not sure if Tables.Remove / Tables.Add is a viable option. If I remember correctly, that won't work.

DavidSc_MS (Expert):
Q: David: Do you blog?
A: I currently suffer from "Blog Envy" but hope to find a remedy soon.

Steve_MS (Expert):
Q: The binding support has been greatly enhanced in .Net 2.0. Are there any good code/doc examples on how to implement the new functionality?
A: Check out the topics on the BindingSource and BindingNavigator objects. You can also check out the 'Displaying Data on Windows Forms' section of help for the new way to quickly design and databind controls on Windows Forms.

Eric_MS (Expert):
Q: could edit a listarray of custom object an datagrid control ?
A: Yes - you can bind to a DataGrid to a listarray though if data changes you'll need to add code to refresh the UI. We have introduced a BindingList in Whidbey to take care of that for you and make some of the scenarios easier.

DavidSc_MS (Expert):
Q: i would like to suggest that sqldataadapter must fill custom object directly in whidbey because the performance is low when an dataset is created first and the must be created an object for each datarow ( make the task two times )
A: We appreciate the input, but at this time I don't believe we could sufficiently address this scenario in the next version. Your best bet would be to have your class expose a method that returns a collection of objects based on the contents of a DataReader. That would give you the best mix of flexibility, control and performance.

Eric_MS (Expert):
Q: Who is the owner of Windows Forms Data Binding? ADO.NET or Windows Forms? Can I ask questions about DataBinding here?
A: Go ahead and ask and we'll do our best. :)

EddieG_MS (Expert):
Q: to eddieg followup point is that both apps are by different users and have no knowledge of the field names in each window
A: By different users, do you mena they have connction to database under different cridentials ?

DavidSc_MS (Expert):
Q: Typical best practices for database access is to use views for select & stored procedures/functions for deletion,update or insert. Any chance that future versions of .NET will create these database objects by default as opposed to writing in-line SQL?
A: If I remember correctly, much of this functionality is available through the DataAdapter Configuration Wizard in Visual Studio .NET 2002 / 2003. The wizard does not create views, but it does create Insert, Update, and Delete stored procedures given a SELECT query.

DavidSc_MS (Expert):
Q: Is DataView going to support finding by a criteria other than the fields used for sorting?
A: Not in the upcoming release, no. You can still start from the DataView, access the DataTable and then use the Select method to locate rows based on more generic search criteria.

Steve_MS (Expert):
Q: Are there any examples of how to use the BindingSource.Find() method? The help file says to use a PropertyDescriptor??? How would I use a PropertyDescription to find a record in my BindingSource based on a customer ID#?
A: The way to use BindingSource.Find is changing. You will be able to pass in a string ("PropertyName"). There are currently no examples for this new functionality...

SteveLas_MS (Expert):
Q: With Avalon right around the corner, what will be the impact that on binding and current bind code?
A: We're working to provide a consistent experience between our DataBinding infrastructure we have today, and what is comming in Avalon. We are still using the same underlying interfaces, such as IBindingList, IListSource and such.

SteveLas_MS (Expert):
Q: Are we going to have something like a JOIN between DataTables sometime in the future?
A: Not in Whidbey, but this is something we're looking at post whidbey.

Ed_H_MS (Moderator):
Q: thanks for response eddieg but i meant data objects contained on the windows themselves while both apps running not external data
A: Jerry - you may want to ask this question in the microsoft.public.vb.general.discussion newsgroup. There are many ways to do what you are asking, it really depends on the context of the app, and they will be able to answer it there.

SteveLas_MS (Expert):
Q: Let's take Alfred's question a bit further, will we get SQL capabilities to operation on data already contained in a DataSet (IE. between tables)?
A: Also, post Whidbey, we're looking at doing more integration of data into the languages.

EddieG_MS (Expert):
Q: to eddieg if you can point me to correct technologies or people who have worked on this email jerrysavagee@yahoo.com thanks
A: Jerry ,

Kris_MS (Expert):
Q: thanks for response eddieg but i meant data objects contained on the windows themselves while both apps running not external data
A: Thanks for the question John. You can use the new My.Net features available in Whidbey. You can simply call Dim PhysicalMem As ULong = My.Computer.Info.TotalPhysicalMemory()
Dim AvailableMem As ULong = My.Computer.Info.AvailablePhysicalMemory()

EddieG_MS (Expert):
Jerry:I'll send you link to appropriate news group

DavidSc_MS (Expert):
Q: What is an easy way to detach a DataTable From one DataSet and attach it to another?
A: Mark, Cor just (thankfully) corrected me on this issue. It looks like Tables.Remove / Tables.Add achieves the desired result.

John_MS (Expert):
Q: to transmit an middler tier object that contains database data to a remote machine, i must convert to xml file and then reconvert in the remote machine ?
A: Using xml file is a way but I think there must be some other ways. .

Kris_MS (Expert):
Q: How do I check the availible RAM on my computer?
A: Thanks for the question John. You can use the new My.Net features available in Whidbey. You can simply call
Dim PhysicalMem As ULong = My.Computer.Info.TotalPhysicalMemory()
Dim AvailableMem As ULong = My.Computer.Info.AvailablePhysicalMemory()

DavidSc_MS (Expert):
Q: David: I need it to be from the DataView to get it's position and set the CurrencyManager.Position
A: Alfred: I understand the scenario you're describing. At the moment, there's no simpler solution.

SteveLas_MS (Expert):
Thanks for attending

John_MS (Expert):
Q: Back in the day of working with VB6, ADO and Office applications we had hooks that would let us efficently transport data (such as the filling of a worksheet). Will we ever get that again other than via code?
A: I have a more specific answer from office dev:

DavidSc_MS (Expert):
Q: In Whidbey, if you're sending a dataset across the wire via a web service call, does setting DataSet1.RemotingFormat = SerializationFormat.Binary; really improve the transmission speed? I was told it was a 10-fold improvement in that area.
A: Passing DataSets across the wire in the binary format does increase performance.

Ed_H_MS (Moderator):
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!

The transcript from today's chat will be posted on https://www.msdn.microsoft.com/chats/recent.asp

DavidSc_MS (Expert):
Thanks for participating in the chat, everyone!

John_MS (Expert):
Q: Back in the day of working with VB6, ADO and Office applications we had hooks that would let us efficently transport data (such as the filling of a worksheet). Will we ever get that again other than via code?
A: This is a answer Stepen in office team: hope this is more closer: I don't really understand the question. This user wants to import data from a datasource into an Excel worksheet without any code? We don't have anything like that, but we certainly make it a lot easier to write the code to get data into Excel worksheets in VSTO 2005. Our projects support the Data Sources window, which allows you to add data sources to a project and then create data bindings via drag/drop. We also provide two special controls in Excel, the NamedRange and the ListObject, that support data binding. These are essentially smart ranges that can be bound to any data source using the same type of binding as Winform controls. A user would still need to write code to load the dataset, etc, but we make it easier by auto-generating this code at design-time when the databindings are created through gestures in the IDE (like drag/drop from the Data Sources window, or adding bindings through the Properties window).

Top of page