ADO.NET with Visual Basic .NET

Posted December 3, 2003

Chat Date: November 4, 2003

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 Studio team here to answer your questions today. I will ask the hosts to introduce themselves.

Host: DavidSc (Microsoft)
My name is David Sceppa. I work on the data access team at Microsoft and am also the author of "Microsoft ADO.NET" available from Microsoft Press.

Host: Hussein (Microsoft)
Hi, this is Hussein Abuthuraya with the PSS - WebData Team, supporting VB.NET, C# and ADO.NET.

Host: Scott (Microsoft)
I'm Scott Berry, a tester with the .Net Client team. I work on a variety of things, including data-related controls.

Host: Kawarjit (Microsoft)
Hi, I'm Kawarjit Bedi, working as a Program Manager on ADO.NET

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

Start of Chat

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

Host: Hussein (Microsoft)
Lewis: Sure you can. Use ODBC.NET and the Dbase ODBC driver to connect to a DB3 database.

Host: Hussein (Microsoft)
Lewis: Sample: Dim cnDBF As OdbcConnection = New OdbcConnection("Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:\AccessDB\")

Host: DavidSc (Microsoft)
Q: If you have a sql database set up and you are using a data reader to pull small date time values and place them in a selection list how do you make it not show the time when it populates?
A: The simplest solution is to work with the value as a System.DateTime and then use the date formatting options when calling ToString(). For example:

Dim dt As DateTime = DateTime.Now

Console.WriteLine(dt.ToString("d"))

Host: Scott (Microsoft)
Q: I have problems with databinding, specifically in how it syncronizes the controls updating the datasource in strange times i.e. entering the control's focus, leaving focus, validating. It seems like a difficult thing to get right. Is there a good reference you give people for these problems?
A: It updates the data when you have a PropertyChanged event fire (like TextChanged). And the bound properties get updated when you move to a control with CausesValidation = true (which is the default). It can get a little odd when some of the controls

Host: Scott (Microsoft)
A: false: it won't update until you move to a control with CausesValidation = true. We're working to make this more user-controlled in the next version.

Host: Kawarjit (Microsoft)
Q: Hi..is possible to pass a null value when I use a dataset and XML???.
A: (HMD) Yes. Dataset understands xsi:nil. It reads xsi:nil and maps it to the null equivalent - DbNull.value. Dataset correctly interprets xsi:nil but does not write it out. If a column value is null, then the column is not written out.

Host: Kawarjit (Microsoft)
I see, you have a more generic concern of passing data with null values. Dataset persists to XML default values; if a column value is null, it is not persisted. One option is to use a reserved value say "NULL" for string values and in you application seman

Host: Hussein (Microsoft)
Q: Where can I find some good examples or patterns for programming powerful and easy to use data-entry user-interfaces ? (e.g. Master Detail with switching between datagrid / form, and choice between protected input with Edit/Save/Cancel OR full datagrid
A: Use the DataForm Wizard, it is your friend. Start new project, right-click and select new item then select DataForm Wizard. It is straight forward.

Host: Kawarjit (Microsoft)
contd: map it to null and process accordingly. By default the "default" value for a column is "DbNull.Value" (null equivalent), you can use your "NULL" as the column default (i.e. set Column.DefaultValue="NULL").

Host: DavidSc (Microsoft)
Q: Can you point me to, or give me, a code example of just how to do inserts to related tables having an identity pk on the parent? I can't find anything that actually shows what to do.
A: I couldn't find a knowledge base article with that information. Here's a "canned response" that has resolved this question on the newsgroups:

Host: DavidSc (Microsoft)
This is a fairly common scenario that ADO.NET handles much better than any of its predecessors. It may seem complex at first, but once you've handled the scenario once, it will hopefully feel more intuitive.

Host: DavidSc (Microsoft)
1.) How do I keep pending parent and children in synch? Set the ADO.NET DataColumn's AutoIncrement property to True and ADO.NET will generate placeholder values for new rows. The new values depend on the AutoIncrementStep, AutoIncrementSeed, and the last value used in the DataTable. I recommend setting AutoIncrementSeed and AutoIncrementStep to -1. These settings will generate placeholder values of -1, -2, -3, ...

Host: DavidSc (Microsoft)
There are two benefits to this approach. The values won't conflict with any that actually exist in the database. The user will not misinterpret the placeholder value as an actual value from the database.

Host: DavidSc (Microsoft)
As you add the parent rows and ADO.NET generates placeholder values, use those placeholder values for your pending child rows. The DataRelation object will make it easy to go from parent to child and back, either in code or in bound controls.

Host: DavidSc (Microsoft)
2.) How do I fetch the new key values for the parent rows as I submit them?

Host: DavidSc (Microsoft)
If you were writing your own queries, you would execute an "INSERT INTO ..." query to insert the new row and then execute a "SELECT SCOPE_IDENTITY()" query to retrieve the last identity value generated on that connection.

Host: DavidSc (Microsoft)
The DataAdapter submits changes via its InsertCommand property. You can append ";SELECT SCOPE_IDENTITY AS MyIDColumn" to the end of the "INSERT INTO..." query.

Host: DavidSc (Microsoft)
If you're writing your code by hand, make sure the InsertCommand's UpdatedRowSource property is set to Both (the default) or FirstReturnedRecord. This property controls whether the DataAdapter will fetch the row returned by the query and apply that data to the DataRow object.

Host: DavidSc (Microsoft)
3.) How do I cascade the new key values to the child rows before I submit them?

Host: DavidSc (Microsoft)
This is the simplest part of the process. When you create a DataRelation object, ADO.NET will add a ForeignKeyConstraint object to make sure that child rows match up to a parent row.

Host: DavidSc (Microsoft)
The ForeignKeyConstraint object exposes a UpdateRule property. If this property is set to Cascade (the default), ADO.NET will automatically cascade changes made to the parent down to the associated child rows.

Host: DavidSc (Microsoft)
So, if you have a DataRelation set up between the DataTables based on the auto-increment column, and you've set the parent DataAdapter's InsertCommand to fetch the new auto increment values from the database,

Host: DavidSc (Microsoft)
ADO.NET will cascade the new values down to the associated child rows automatically.

Host: Kawarjit (Microsoft)
Q: How can i use xsi:nil. (follow up Question by HMD).
A: The standard XML way; for instance <Order xsi:nil="true"/>.

Host: Scott (Microsoft)
Q: Any chance we'll see better standard control/binding support for dbnull in the future?
A: This is definitely something we're looking to address in a future version.

Host: Scott (Microsoft)
Q: I know the display member is with ToString? What is the Valuemember if I create a class object to load?
A: It's empty (SelectedValue = Nothing).

Host: Hussein (Microsoft)
Q: Hussein_MS : The results of the datawizard are not really what I'm looking for. Any samples on more advanced possibilities.
A: For starters, the DataForm Wizard is a good starting point. For more advanced samples, see ADO.ENT Core Reference by David Sceppa ISBN 0735614237

Host: Kawarjit (Microsoft)
Q: I have a question: in Datatable and Dataset there is a Method that clear all rows.. Is there Similar in the Dataview?
A: No. DataTable.Clear() removes all rows from DataTable. DataView shows a constrained set of rows based on the specified RowFilter. Clearing DataView implies that the filter be set to something that'll disqualify all rows.

Host: Kawarjit (Microsoft)
I'm sure you have something else in your mind. Would you like to remove rows from the underlying DataTable that exist in DataView. If yes, then the only way is to do foreach over all DataView rows and do delete on them like.

Host: DavidSc (Microsoft)
Q: Would you recommend using a guid or an autoincrement column as a record id?
A: That's a hotly contested debate. Developers often feel strongly one way or the other and debates on the topic sometimes end with pie throwing and/or name calling.

Host: DavidSc (Microsoft)
AutoIncrements are smaller values, so they take up less space and yield better performance in queries. However, since they're server-generated, an insert into the database requires that you query the database for the new key value.

Host: DavidSc (Microsoft)
This also complicates the process of submitting new parent and child data, as discussed earlier. Also, there is no standard for auto-increment functionality across databases.

Host: DavidSc (Microsoft)
Relying on Guids can simplify some of these issues, since you're generating your key values at the client. However, these values are much larger than integers and are less performant during searches as a result.

Host: DavidSc (Microsoft)
Not all databases natively support a guid type, but guids can be cast as strings.

Host: Kawarjit (Microsoft)
Q: Is there possible to Group Data in a Dataview or DataTable, Like using a Method Select .. or a filter.. but grouping.
A: Unfortunately no. DataTable provides very simple querying capabilities, you can do simple "Selects" with simple filters. The SELECT does not support Group By clause.

Host: DavidSc (Microsoft)
Q: I just tried using the dataform wiz against orders and order details in northwind...it doesn't allow adding a new order and details...have to add order first, then detail. Where do you fiddle with the autoincrementseed in the code?
A: You can set the AutoIncrementSeed and AutoIncrementStep properties for your strongly-typed DataSet at design-time in the DataSet Schema designer.

Host: Hussein (Microsoft)
Q: If I put the login in the connection string, where's the most secure place for that str?
A: Please see .NET Data Access Architecture Guide (Section Managing Database Connections) URL...

Host: Hussein (Microsoft)
A: https://msdn2.microsoft.com/en-us/library/ms978510.aspx

Host: Scott (Microsoft)
Q: I have had a rather difficult time finding out info on using a variable in the Table.select() method. I need to do this with a child table. A good resource for examples ? ' ListBox1.Items.Add(myRow("case_id") & " " & myRow("Document_nme"))
A: The only way I know that you could do this is with computed columns. That would mean you'd have to add a column to your table that had the concatenated string, then Select against that.

Host: DavidSc (Microsoft)
Q: currently I have to access a foxpro application for numerous queries. I am using the foxpro oledb driver, however I cannot automagically create insert update and delete queries throught the dataadapter wizard because it does not know the key column.
A: I believe the ability to query for this schema information was added to the more recent Visual FoxPro OLE DB Provider.

Host: DavidSc (Microsoft)
I built a fairly straightforward code generation tool to create that updating logic for generic .NET data providers and for scenarios where the provider cannot determine the key column(s).

Host: DavidSc (Microsoft)
The tool is on the CD for "Microsoft ADO.NET", source code included. If you're looking to write your own such tool, I relied on the DataReader's GetSchemaTable method to get the necessary schema information.

Host: Hussein (Microsoft)
Q: David, is the dataset schema designer what you get when you dblclick on the xsd file that the dfwiz creates?
A: I'm not David but the answer is Yes.

Host: DavidSc (Microsoft)
Q: ok, because I can't find where to set the autoincrementseed or step properties for the relationship.
A: The properties are on the column. It's the DataRelation that cascades the change to this value to the corresponding child rows.

Host: DavidSc (Microsoft)
Q: dfwiz must not create a strongly typed ds then.
A: The DataForm Wizard does create strongly-typed DataSets.

Host: Kawarjit (Microsoft)
Q: Since Dataset dont include the tag beacuse is null.. we're having problems reading it.. how can we use the Xsi:Nil.
A: Yes, that is a problem. We are aware of this issue and are working on it for a solution in a future release. A possible workaround is to use RESERVED tokens for NULL values. Dataset reads xsi:nil fine, but does not write it out.

Host: DavidSc (Microsoft)
Q: for the xsd, it gives me the orders and order details tables, the relationship on the orderid column, and orderid shows the pk icon and is an int. where do you set the seed and step?
A: Select the column and then go to the Properties Window.

Host: Hussein (Microsoft)
Q: Another Questions.. This is about the application datablock .. there are important changes between the 1.0 version to the 2.0? or is just the Framework 1.1 and Vs 2003?
A: The 2.0 release of the Data Access Application Block includes the following new features:

Host: Hussein (Microsoft)
A: 1) Support for strongly typed DataSets with the FillDataset method

Host: Hussein (Microsoft)
A: 2) Support for committing updates to a DataSet back to the database

Host: Hussein (Microsoft)
A: 3) Additional helper methods with support for DataRow type parameters

Host: Hussein (Microsoft)
A: 4) Minor bug fixes.

Host: DavidSc (Microsoft)
Q: David, Everytime I have to make a robust data-entry interfaces, it takes plenty of time and the result is hardly reusable. I'm looking for some pattern or component which can be used to make RE-USABLE, robust, powerful, flexible interfaces.
A: Good point and an excellent suggestion. We are looking at ways we can improve on the process in future versions of Visual Studio .NET.

Host: DavidSc (Microsoft)
In the meantime, I can talk to our development and user education team about patterns for re-use with the current components.

Host: Kawarjit (Microsoft)
Q: ok, because I can't find where to set the autoincrementseed or step properties for the relationship.
A: You can use the msdata annotations on the attribute / elements to specify the seed and steo values. try "msdata:AutoIncrementSeed='10' and "msdata:AutoIncrementStep='1'.

Moderator: Ed_H (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: Kawarjit (Microsoft)
Thanks !

Host: DavidSc (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.

Top of Page