ADO.NET

Posted May 17, 2003

Chat Date: April 29, 2003

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

Chat Participants:

• David Sceppa, Data access team & author of the Microsoft Press book ADO.NET
• Eric Gruber, Tester on the Visual Basic Data team
• Karol Zadora-Przylecki, Developer on the Visual Basic .NET team
• Paul Yuknewicz, Program Manager for VB.NET
• Ed Hickey, VB Communities PM

Moderator: Ed (Microsoft)
Welcome to today’s general chat on ADO.Net. We have members of the VB Data team here to answer your questions today. I will ask the hosts to introduce themselves.

Host: David (Microsoft)
My name is David Sceppa. I work with the data access team at Microsoft and am also the author of the Microsoft Press book ADO.NET.

Host: Eric (Microsoft)
Hi, my name is Eric Gruber and I am a tester on the Visual Basic Data team.

Host: Karol (Microsoft)
I am Karol, a developer in the Visual Basic .NET team.

Host: Paul (Microsoft)
Hi everybody! - this is Paul Yuknewicz, Program Manager for VB.NET.

Moderator: Ed (Microsoft)
And I am Ed Hickey, VB Communities PM. Glad you all could make it today!

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

Moderator: Ed (Microsoft)
I have an announcement to make. Our buddy Nicholas Paldino reached his goal of 1000 technical answers in the newsgroups this month!

Moderator: Ed (Microsoft)
We will have more MS guys pop in as we proceed, I will have them introduce themselves as they come in.

Host: David (Microsoft)
Q: I have a question about ADOMD - how different are things in it with .Net?

A: There is no .NET equivalent of ADOMD in versions 1.0 or 1.1 of the .NET Framework. The current solution is to use ADOMD through COM interop. The Microsoft development team is looking to add similar functionality in a future release of the .NET Framework.

Host: David (Microsoft)
Q: How do you setup ado.net object to ignore warnings sent via a stored procedure and keep on running

A: You can control this behavior by setting the severity of the error you're throwing from the stored procedure via RAISERROR.

Host: Karol (Microsoft)
Q: How do I use DataSets to cut down on SQL Activity

A: The first thing you want do do to improve performance is to cut down database round-trips. You have a couple of options. One is to use DataSets for caching data. Another is to do more processing on the server (by using stored procedures).

Host: David (Microsoft)
Q: How do I update the changes made to the child adapter data back to the Database?

A: If you're only changing data in the child table, you could simply call ChildAdapter.Update(ChildTable). If you're changing data in different DataTables within the DataSet, you may need to be more explicit to submit inserts in a top-down approach against your hierarchy and deletes from the bottom up. If that doesn't answer your question, could you please provide more specifics about where you're having problems.

Host: David (Microsoft)
Q: After each single insert/update/delete to a row in a DataTable I want to update the underlying database table (Sql Server) in a transaction. Is there a way to also rollback the changes made to the DataSet in case the action on Sql Server failed?

A: The DataSet does not persist state when you call BeginTransaction. If you want to be able to revert the DataSet to a previous state, your best option is to store that state yourself by calling DataSet.Copy or DataSet.WriteXml. Then, if you roll back the transaction, you'll also need to jump back to the DataSet you stored in your code.

Host: David (Microsoft)
Q: Is there anyway i can e-mail you my scenario, so that we can understand better

A: Your best bet would be to post the question to the ADO.NET newsgroup at microsoft.public.dotnet.framework.adonet

Host: Paul (Microsoft)
Q: When will we be able to change the RowState ?

A: This is a really common request that we're looking in to for the next version. Let me know if there is some task in particular you'd like help with now.

Host: Karol (Microsoft)
Q: I would like to find out how to Build a perm. dataset on the project that was the Web Service can perform most of the processing and then let the SQL Server know of the changes

A: Roger, the DataSet basically acts as a relational cache of data. To synchronize DataSet content with your database you usually use an adapter object. Please look at description of SqlDataAdapter and DataRow.RowState on MSDN. David Sceppa's book ("Microsoft ADO.NET Core Reference) is a great resource too.

Host: Kawarjit (Microsoft)
Q: I need a SqlDataReader like reader for DataSet

A: There is no built-in support, however, its possible to build a customer reader. You may want to a implement class that implements IDataReader

Host: Eric (Microsoft)
Q: I use a function called GetProductDetails which creates and fills an instance of the class ProductDetails to get all of the product details from the database. I have situations where I will call the function GetProductDetails just to get the name of a product, but the function itself gets much more information from the database, because of it's generic nature. Does anyone else have this problem with a Data Access Layer? Any suggestions of a better way to do this?

A: One option is to make some specific helper functions in your Data Access Layer for common methods you are looking for. You can create a function that would use a simple data command that will return just the name of the product. It can get to be a lot of code to write but we're looking at ways of making this easier in the next version of Visual Studio.

Host: Karol (Microsoft)
Q: Do you have any other places I can read more about Perm. DataSets in Visual Studies.NET

A: Also look at typed datasets (search for "Generate Dataset Dialog Box" in Visual Studio documentation). Typed dataset are nice extensions that lets you work with your data in a type-safe way. They are particularly useful if your schema doesn't change very often.

Host: Eric (Microsoft)
Q: Does one need to open a connection to the SQL server or an OLEDB connection when using a local XML file as a database.

A: No. You can simply use a dataset to read/write from an XML file without needing to use a databse connection or adapter. Something like this with do the read/write:

Dim ds As New DataSet

ds.ReadXml("filename")

ds.WriteXml("filename")

Host: David (Microsoft)
Q: But isn't using DataSet.Copy a bit of a memory hog?

A: You could track just the pending changes you want to remember (i.e. DataSet.GetChanges().Copy). And if you want to conserve memory, you could write data to a temp file instead.

Host: Karol (Microsoft)
Q: Is there any advice you can give regarding the use the ExecuteNonQuery method of a command object for doing updates vs. doing updates using databound controls (i.e.: Performance, Standards) ?

A: I think the tradeoffs are rather simple. ExecuteNonQuery gives you absolute control over what gets sent to the database, so you will usually get the best performance with it. On the other hand (assuming that your controls are bound to a DataTable) using a data adapter requires less code, especially if you use Visual Studio features to configure them. Note that an adapter will update the database row by row (i.e. one UPDATE command will be sent for each modified row), this might be slow if there are many rows to be updated.

Host: Eric (Microsoft)
Q: When I make manual changes to the XSD of a DataSet directly in XML but have made an error all my Databinding definitions to this DataSet are removed from code upon "save". Is this by design?

A: The problem here is that the data binding picker on the form doesn't have anything to show in the picker since the dataset instance on the form is invalid since the Dataset class couldn't be generated from the bad XML. We're looking at ways of making this experience better in the next version of VS.

Host: Karol (Microsoft)
Q: I am an asp developer and have a hard time trusting data binding yet

A: Data binding in ASP.NET is essentially read-only. I agree with BetaV that it is usually better (more secure and faster) to use command object and not pass hand-crafted SQL to ExecuteNonQuery.

Host: David (Microsoft)
Q: We have an app where multiple people might be trying to edit the same records

A: You can employ optimistic concurrency where the data is not locked and updates succeed or fail based on concurrency checks within your updating logic. You can also employ pessimistic concurrency by locking data prior to editing data. Coding with pessimistic locking looks easier because you do not have to handle failed update attempts, but this approach does not scale well.

Host: David (Microsoft)
Q: How do you do pessimistic locking in ADO.net I heard it was no longer supported

A: Pessimistic locking is not natively supported in ADO.NET. However, you can achieve this type of locking through transactions. Again, pessimistic locking can help you create a simple application that scales poorly.

Host: David (Microsoft)
Q: Any advice how to profit from Sql Server TimeStamp fields in ADO.NET. They seem not to be supported natively.

A: I'm not sure what you mean by not "supported natively". Use the primary key and timestamp columns in the updating logic for your DataAdapter's UpdateCommand and DeleteCommand. Just be sure to re-query for the contents of the row after successful inserts and updates so your client has "fresh" timestamp values.

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

Host: David (Microsoft)
Thanks, everyone!

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.

au

Top of Page