ADO.NET with Visual Basic .NET (February 08, 2005)

Posted: February 16, 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.Net 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 Data Access team, and author of "Microsoft ADO.NET" from Microsoft Press.

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

Pablo Castro [MS] (Expert):
Hi, I'm Pablo Castro, Program Manager - ADO.NET team

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

Xiang_MS (Expert):
Hi all. I'm Xiang, a tester at Visual Studio Database Tools test team.

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: Struggling with updating a SIMPLE table using VB.NET and Access 2003... Won't update and I'm using the wizards.... I'm using the .update command.... I've two books on ADO.NET (Sceppa!) but still no luck, what worries me is that I'm not too newbie.
A: Terenas - What happens when you try to submit the pending changes? Do you get an exception?

Xiang_MS (Expert):
Q: the information that i have is the dataset data are stored in xml format internally. That is true ?
A: Yes. Data is transferred between apps and data source in XML format.

SteveS_MS (Expert):
Q: we loaded the asp.net framework and also have vbc.exe was also installed, why is that?
A: Since you can write VB code for handling your web pages the vb compiler gets installed.

DavidSc_MS (Expert):
Q: ok, here I go: when will MS address the arrays in ado.net - i mean a parameter of array type to pass an array :) or values (for example for use with IN clause)
A: Miha - The parameterized IN clause is more of a back-end limitation. SQL Server does not support that type of query. Is there another scenario where you want to pass in an array of values into a parameter?

Pablo Castro [MS] (Expert):
Q: ok, here I go: when will MS address the arrays in ado.net - i mean a parameter of array type to pass an array :) or values (for example for use with IN clause)
A: We are not planning to support parameter arrays in the Whidbey release. Note that for example in the case of SQL Server the server actually doesn't support such construct (pass a single array parameter to be used in a "IN" clause). If you're using SQL Server 2005 you can do some fancy things such as creating a User-Defined Type in the server that handles arrays, or packing the array in a blob, send it to the server and then unpack it using a CLR proc.

Pablo Castro [MS] (Expert):
Q: If any of you are from DataWorks team, perhaps you can answer this: Has the backing object for the datatable been changed from an arraylist to something faster and more efficient, like a red-black tree or hashtable even?
A: Sort of :) - the backing store itself is still an column-based array (efficient, boxing free store), however, we re-wrote the index engine of the DataSet to use a highly-efficient form of red-black tree

Eric_MS (Expert):
Q: I have a class dedicated to a Customer, for example. Every Customer object has a set of data (pulled from the database). Is it "proper" to create an untyped dataset at runtime for each object, or reuse a single dataset created at design time?
A: A lot depends on the specific scenario you are doing. If the data is always of the same format you can use a single typed dataset to make coding easier.

Pablo Castro [MS] (Expert):
Q: For the ado.net team - what are your opinions on object relational mapping and the extent that this should be supported by ADO.Net?
A: We think it's very important to have a consistent, forward-looking story around data-access APIs and models in general, including O/R mapping. That's we decided to drop O/R mapping from the Whidbey release, but we're certainly looking into the problem in a broader sense and actively working on future developments around the area of objects and database access.

DavidSc_MS (Expert):
Q: if i have an application in vb6 and Ado, if i use the save method with the adpersistXML parameter to save a file and then i try to open it in vb net with dataset readxml method, that going to work ?
A: PcBoy - ADO uses a very different schema to persist the data. I believe there are articles in the Knowledge Base that should help you transform the resulting XML data into a format that more closely matches ADO.NET schemas.

Xiang_MS (Expert):
Q: Trying to access data on a LAN computer. Having a problem with the MSDN example for even finding that computer. Imports System Imports System.Management ' This sample demonstrates perform a query using ' ManagementObjectSearcher object. Class Sam
A: Have you used WMI to access the computer?

Eric_MS (Expert):
Q: Ok, then how do I create a typed dataset at runtime?
A: A typed dataset has to be created at design time. At runtime, you just call an instance of it and then can take advantage of intellisense to access tables and columns by name.

Pablo Castro [MS] (Expert):
Q: I have come to learn that communication with the underlying database (in my case SQL server) is done in plain text. Should I choose to do this on a public domain, the risks are obvious. Is there a simple solution to encryption?
A: That actually depends on the database server and the configuration of both the server and the client. To use your scenario (SQL Server), the application-level protocol used to talk to the server is TDS, where data such as strings are sent/received in clear-text in the TDS layer itself. However, SQL Server and it's clients (ODBC, OLEDB and ADO.NET SqlClient) all support encryption. So if you have concerns about someone "listening" in your network, you can enable encryption (e.g. if you're using ADO.NET use "encrypt=true" in the connection string). Note that use of encryption requires a certificate to be properly installed on the server.

DavidSc_MS (Expert):
Q: Thinking about a multi-user environment, where everybody has the right to delete, are there better ways than refreshing that complete datatable?
A: If User A deletes a row, how vital is it that the row is not available to User B? In most applications, it's OK to have User B attempt to modify or delete the row, and have that operation fail because the row no longer exists. One solution would be to refresh the row when the user tries to go into "Edit" mode. That would save the user some failed update attempts while preventing you from having to repeatedly refresh all resultsets at every client.

Pablo Castro [MS] (Expert):
Q: For the ado.net team - THe current ADO.net implementation seems focused hevily on the Recordset design pattern (Fowler PEAA 2003) style of working as oposed to a OO domain model style, do you see this as something that will become more balance in future?
A: Probably, yes (my own opinon). I think there are still a lot of advantages in the relational model, even from the client perspective, so we'll have relational- centric data-access for a long-long time. However, we're certainly looking at how we'll evolve our current model so in addition to this relational-centric model we also support more OO-type constructs.

DavidSc_MS (Expert):
Cor and Miha are correct. The CurrencyManager does cache the changes. (See Step 7 in Chapter 13 for more info)

Xiang_MS (Expert):
Q: Sorry: Trying to access data on a LAN computer. Having a problem with the MSDN example for even finding that computer. “ManagementObjectSearcher” gives a ‘Type Expected’ compile error. What Reference do I need to add? Imports System
Imports System.Management
' This sample demonstrates perform a query using
' ManagementObjectSearcher object.
Class Sample_ManagementObjectSearcher
Overloads Public Shared Function Main(args() As String) As Integer Dim searcher As New ManagementObjectSearcher("SELECT * FROM Win32_Share").
A: You need to add project reference to System.Management.dll.

Pablo Castro [MS] (Expert):
Q: Has ADO MD been released yet? If not what are the plans for the release of this.
A: Do you mean "traditional" ADO MD or the .NET version of it?

Xiang_MS (Expert):
Q: answer to Xiang_MS (Expert): had to Add Reference: System.Management to make the “Imports System.Management” valid. How do I add WMI?
A: You don't need to. I originally thought you might not be able to manage the machine through WMI.

DavidSc_MS (Expert):
Q: is the object recordset ADO and the object recordset ADO.Net the same or there are some structural changes ?
A: There are a lot of differences.The ADO Recordset was designed to support a wide range of scenarios - forward-only data retrieval, live server-side cursor updates, off-line disconnected cache, etc. And if you asked it to handle a scenario it did not support, it would do its best. Those are great and lofty goals, but we got a lot of feedback telling us that they wanted to change the connection string, point the ADO code at another back-end and...it didn't quite work the way they expected, primarily because of features the provider didn't support.

Xiang_MS (Expert):
Q: answer to Xiang_MS (Expert): had to Add Reference: System.Management to make the “Imports System.Management” valid. How do I add WMI?
A: Answered.

DavidSc_MS (Expert):
Q: is the object recordset ado and the object recordset ado net the same or there are some structural changes ?
A: (Continued answer...) ADO.NET takes a different approach. Each .NET data provider exposes a DataReader as the lightweight way to get forward-only, read-only results. DataSets are designed for back-end agnostic resultset caching, and support scrolling, sorting, filtering, relations, cached updates, and integration with XML features.

Eric_MS (Expert):
Q: Using VS 2005 Beta1, I am trying to build a multitable query using the SQLDataSource Configuration wizard and cannot see how to do this. Comments? (Doing this building a web applcation)
A: What sort of multitable query are you trying to do? Some sort of join query or two separate queries? We've fixed up some issues with the join-type queries but we don't really support pulling back two resultsets using just one SQLDataSource.

Xiang_MS (Expert):
Q: I guess this need to go in 'question' not 'chat' answer to Xiang_MS (Expert): in my code fragment, “ManagementObjectSearcher” gives a ‘Type Expected’ compile error. It doesn't work
A: Did you check the System.Management.dll reference?

DavidSc_MS (Expert):
Q: Is there any plan to address the issue in a dataset where you cannot query across tables? For example, if you have a Customer Table and an Orders Table and you want the Customers who have more than 8 orders you are almost relegated using a calc field.
A: That's still the best solution in the upcoming release of ADO.NET.

DavidSc_MS (Expert):
Q: can to be filled a dataset asynchronous ?
A: There is no asynchronous overload to DataAdapter.Fill. However, you could call BeginExecuteReader asynchronously. Then, when the call completes, you can load the data via DataTable.Load (DataReader).

Eric_MS (Expert):
Q: Eric_MS: Just trying to create a select from two or more tables without having to enter the SQL manually
A: We looking into supporting that within the wizard but it made the UI a lot more complex for the simple cases using just one table. You can however choose to specify SQL and then launch the Query Builder which allows you to graphically construct your SQL statement going across multiple tables. I'm not sure if that was in the Beta1 build or not but you can do that in the current Beta2 bits.

Pablo Castro [MS] (Expert):
Q: Is there any plan to address the issue in a dataset where you cannot query across tables? For example, if you have a Customer Table and an Orders Table and you want the Customers who have more than 8 orders you are almost relegated using a calc field.
A: We are not currently planning support for sophisticated query capabilities on the DataSet, such as joins. We see DataSet as a great tools for data-transfer- objects, backing-store for data-binding, and in whidbey it's also great for mid-tier caching scenarios. We continue to evolve DataSet across releases, but we try to maintain the balance between DataSet and an actual database with SQL query capabilities.

Xiang_MS (Expert):
Q: answer to Xiang_MS (Expert): Yes, there is none in the PullDown. It goes from System.Management to System.Messaging.dll
A: The path of System.Management is to the dll. Select it and you should be able to compile.

DavidSc_MS (Expert):
Q: Suggestions on good ADO.NET and VB.NET Books? I have many from MS press, and The Complete Reference to ADO.NET. I'm interested to hear about books your guys have written or would suggest to a user of ADO.NET that never used ADO..
A: As the author of the MSPress book, I'm admittedly biased. I've heard very good things about Bob Beauchemin's book and Shawn Wildermuth's book. Take a look at some of the on-line reviews, go to your local bookstore and look through the books and find the one(s) that include the information, style, and sense of humor you're looking for.

Pablo Castro [MS] (Expert):
Q: will ado.net have an inherent understanding of nullable types (vb.net 2005) for more simply representing NULL values returned from a Stored Proc
A: ADO.NET supports database-specific null types (such as SqlInt32 or OracleNumber) - we're not planning to support CLR nullable types in the Whidbey release, although we're considering it for a future release, we'll see how it goes.

Eric_MS (Expert):
Q: when update a table with the update method, if another user changed the data after i open it, an exception will be raised ?
A: That depends on how your UpdateCommand of the DataAdapter is configured. By default, going through the configuration wizard will set it up to use optimistic concurrency checking which will throw an error if the same record has changed in the database. If you don't want this you can go and turn it off by going into the Advanced Options in the wizard. If the UpdateCommand.CommandText has a bunch of SQL which looks like this then its doing optimistic concurrency checking and will throw an error. Not the exact syntax but something like this:
UPDATE Table SET ... WHERE (ID=@ID AND (Column1=@Column1 or (Column1 IS NULL AND @Column1 IS NULL)) AND ...

Xiang_MS (Expert):
Q: whats the difference between dataset.reset and dataset.clear ? will dataset.reset clear all the dynamically created columns ?
A: Reset() does a Clear() of all data, plus removing tables and relations.

DavidSc_MS (Expert):
Q: Is any performance hit on using Compute on a dataset?I try to populate new table using Compute with Expression and filter. i have a table maybe 100 rows and u use Compute on this table maybe 120 times to build new table.Or better use SP???
A: Given the scenario you described, I think a stored procedure would handle that work much faster.

Eric_MS (Expert):
Q: and about the adapter, i must set some adapter property by that work ?
A: There isn't a property on the adapter itself to turn on or off concurrency checking. Our wizards do the generation for you based on your choices when clicking the Advanced Options... button in the wizards.

Pablo Castro [MS] (Expert):
Q: What would you recomend as the best method of storing connection infomation - and is it better to use SQL authentication or integrated security ?
A: Use integrated security whenever is possible. There are some scenarios that are somewhat more complicated (e.g. web apps that impersonate clients), but in general integrated security will do the work. As for how to store connection strings: first of all, PLEASE DON'T HARD-CODE THEM :). You can put them in the application configuration file. Executable and configuration files can be deployed together and properly secured (e.g. ACL'ed). If you're looking for more sophisticated configuration data management options, check out the configuration application block from the Pattern & Practices team (https://www.micros oft.com/practices)

Eric_MS (Expert):
Q: But if i want to check only the columns in the primary key by update, how can i do that ?
A: You can do that by unchecking the Use optimistic concurrency checkbox in the Advanced Options dialog of the wizard.

Pablo Castro [MS] (Expert):
Q: Do you know what the future of the SQL server data access application block is with .net 2005 coming out - i have managed to succesfuly recompile the source for the block in 2005 beta but woundered is there will be an update?
A: We've been talking with the Patterns & Practices folks about the DAAB. I think they'll do an update, but I have no official word from them.

Xiang_MS (Expert):
Q: further answer to Xiang_MS (Expert): Nope, I found the .dll in the framework folder, added it and System.Management as references and it still doesn't compile. This is copied right off the MSDN page. I'll add the whole code, just in case. Imports System
Imports System.Management
Class Sample_ManagementObjectSearcher
Public Overloads Shared Function Main(ByVal args() As String) As Integer
Dim searcher As New ManagementObjectSearcher ("SELECT * FROM Win32_Share")
'Dim searcher As New ManagementObjectSearcher ("win32_logicaldisk.deviceid=""c:""")
Dim share As ManagementObject For Each share In searcher.Get() Console.WriteLine ("Share = " & share("Name").ToString()) Next share
A: Your code works fine on my machine. It does print all shares on my C drive. What's the compile error msg?

Eric_MS (Expert):
Q: but when try to give the same code in button click event, it will not work
A: It sounds like an issue with when you are binding the form. Depending upon how your form is setup, its probably being reloaded on button click so the code you executed happened but then the form reloaded and your code didn't fire. I'm not too familiar with the eventing model in ASP.Net so you might want to check out some of their newsgroups as they could better help tell you exactly what's going on and how to properly fix it.

Eric_MS (Expert):
Q: But what happen i want to use a stored procedure, i must create one from the sql statement that the wizard going to create for me ?
A: It depends upon how your stored procedure is built. With stored procedures the concurrency checking happens inside the stored procedure. Our wizards using the same advanced options to determine how to create the stored procedure in the wizard.

Xiang_MS (Expert):
Q: the line "Dim searcher As New ManagementObjectSearcher("SELECT * FROM Win32_Share")" has "ManagementObjectSearcher" as "Type Expected" I am looking for info on the complete LAN. Am I on the right track?
A: You haven't gone that far yet if you have the compile error. Besides, for your code, you are accessing the local machine.

Eric_MS (Expert):
Q: When a data adapter is regenerated it doesnt properly overwrite the dataset it previously created. It works when I delete the already generated dataset.xsd file and generate a new dataset from the data adapter.. does any one know the reason ?
A: We do a merge when regenerating datasets since you might have made some changes you want to keep for client side logic like expression columns. What sorts of problems are you seeing?

Pablo Castro [MS] (Expert):
Q: are there any plans to release sources like winforms team is thinking?
A: We haven't discussed the topic around here. I would prefer not to comment on the topic (you know, I'm not a lawyer, don't want to get in trouble :) )

Eric_MS (Expert):
Q: Then i can use a stored procedure instead, because is faster than a SQL statement ?
A: Yes, you can use stored procedures instead of sending a SQL statement and still use concurrency checking. Sprocs are sometimes faster at runtime but not always depending on the query and other factors.

Xiang_MS (Expert):
Q: There's another error: The designer could not be shown for this file because none of the classes within it can be designed. Once we fix the compile error, can I get to the LAN? I set this up as a WindowsApplication. This is right?
A: Ahha that's your project problem. If you do not need UI to try this out, just use a console app. You can get to the LAN by creating a ManagementObject passing connecting information (computer, username, password).

Pablo Castro [MS] (Expert):
Q: vb net 2003 SqlClient will going to support SQL Server 2005 ?
A: Yes, we'll support connectivity and *some* of the new features. Other features that require client-specific extensions won't be available though.

Xiang_MS (Expert):
Q: vb net 2003 SqlClient will going to support SQL Server 2005 ?
A: Yes.

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 http: //www.msdn.microsoft.com/chats/recent.asp

Top of page