ADO.NET with Visual Basic .NET

Posted July 16, 2004

Chat Date: June 1, 2004

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

Introduction

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

Host: Hussein (Microsoft)
Hi, I'm Hussein and currently working with PSS (WebData team) supporting DataAccess technologies(ADO.NET & classic ADO).

Host: Kawarjit (Microsoft)
Hello, I'm Kawarjit Bedi, member of the ADO.NET team - specifically working with Datasets.

Host: MikeP (Microsoft)
I'm Michael Pizzo. I'm an Architect in the WebData team and one of the designers of ADO.NET version 1.0.

Moderator: Jason (Microsoft)
And I am Jason Cooke, the acting VB.NET chat coordinator. Glad you all could make it today! (Ed Hickey, our regular coordinator, is taking care of other community business.)

Start of Chat

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

Q: hey can i Bind datareader to datagrid in windows application
A: Unfortunately not today.

Moderator: Jason (Microsoft)
Joining us now is Pablo. I'll ask him to introduce himself.

Host: pablocas (Microsoft)
Hello, I'm Pablo Castro, Program Manager with the ADO.NET team

Host: Kawarjit (Microsoft)
Q: are JOINS pretty much out of the question for ADO.Net??
A: JOINS is an interesting scenario and has been raised a number of times. We are looking at it, would like to deliver it without compromising the simplicity of ADO.NET - Dataset. There are no current plans for supporting JOINS, but it's a feature that we'd like to do something about in the future you may find the KB article #325688 helpul, it descirbes how users can implement JOINS using Datasets. It's located at https://support.microsoft.com/default.aspx?scid=kb;EN-US;325688.

Host: MikeP (Microsoft)
Q: Another ? for MS, tied to data connection again.. Is there or will there be a data control similar to what we had in VB6 for VB.net? (and yes I know: yuck, real coders don't use them but there are some of us that do for specific things..)
A: Not the DataControl per-se, but the next version of Visual Basic is doing a great deal to make working with data a much simpler and more intuitive experience. There is also the "Application Block" from PAG which provides more of a single component for working with data.

Host: pablocas (Microsoft)
Q: The problem is, the DA can't do it properly. It uses the Command(Don'tUse)Builder behind the scenes. I think some work is being done there... Pablo?
Host: pablocas (Microsoft)
A: Just to clarify, the DataAdapter will only use CommandBuilder if explicitly asked for

Host: pablocas (Microsoft)
A: (by binding a CommandBuilder object to the adapter). Otherwise, the adapter

Host: pablocas (Microsoft)
A: will use exactly what's provided in the Select/Insert/Update/Delete command properies

Host: Kawarjit (Microsoft)
Q: I have a webservice that takes a single input and returns a dataset with the results. I'd like to hit the service several times and put all of the results into a single datatable. Any ideas?
A: In v1.0/v1.1 Webservice is supported by Dataset and not by Datatable. In the upcoming Beta, Datatable has been extended to support Webservice, so you should be able to use it, provided the results are schema compatible with the returned DataTable.

Host: MikeP (Microsoft)
Q: is the DataReader pointless?... for every DataReader, you have to open and close a connection ...so what is to be gained if you need it more than once?... my experience has been that it is dog slow for multiple DataReaders
A: You don't need to open/close a connection for each DataReader. You can open a connection once, execute a command, retrieve results any results from the DataReader, and then execute again. They key is that you can't (in the current version call execute when there are results pending.

Host: MikeP (Microsoft)
Note also that the DataAdapter uses DataReaders under the covers to populate the DataSet, so it incurs the same overhead as using a DataReader.

Host: MikeP (Microsoft)
Q: MikeP_MS I assume by next version of VB you mean VB.Net vs VB6 correct?
A: Yes.

Host: MikeP (Microsoft)
Q: Are there any tutorials or examples or walk-throughs for we newbies on how to do this?
A: You might check out MSDN.Microsoft.com/Data for some examples, or GotDotNet.

Host: Hussein (Microsoft)
Q: is the Connection object really advisable if your productionSQL Server is named differently than your development server?
A: You could setup the Conenction object to use Dynamic properties to read the connection string from file (App.Config in Windows App or Web.Config in Web Apps). See "Setting Dynamic Properties" topic in .NET Framework documentations.

Host: MikeP (Microsoft)
Q: Be careful, a lot of ADO.NET books leave you with a comfortable feeling but teaches you code that can be susceptible to lots of SQL Injections ...

Host: MikeP (Microsoft)
"Injections"?

Host: MikeP (Microsoft)
A: SqlInjection occurs when an application appends user-input to a query string sent to the server. Rather than concatenate unvalidated strings from the user, you should make it a practice to use parameterized queries. For example; instead of using: "Select * from customers where custid = " & customerIDuse "Select * from customers where custid = @custID"and call cmd.Parameters.AddWithValue("@custID",customerID)

Host: pablocas (Microsoft)
Q: I noticed that if a proc is configured to have a 'char ' input, you cannot use wildcards. Is this something that was planned or a minor bug? I can get around it by defining the parameter as a varchar in the proc
A: You can use '%' with LIKE in char values just like varchar. The only difference is that you need to take into account the fact that char(5) is always 5 chars in length, even if the value is shorter. So, if you

Host: pablocas (Microsoft)
A: want to use '%' in a patter with a char value, you'll have to RTRIM first to remove any trailing characers (i.e. RTRIM(column1) LIKE 'foo%')

Host: pablocas (Microsoft)
Q: SQL Security: How can I configure a webservice to access a SQL server? The webservice and SQL Server services both run as local accounts on different machines.
A: You have a number of options here:

Host: pablocas (Microsoft)
A: 1. Use SQLXML (downloadable from msnd.microsoft.com), which can help making the end-to-end thing easier

Host: pablocas (Microsoft)
A: 2. Use ASP.NET webservice support. In this case you'll have to code your webservices manually, but since it's your code you get all the flexibility

Host: pablocas (Microsoft)
A: (in the upcoming version of SQL Server 2005 we'll support webservices straight from the engine, which is useful for internal webservice consumption (not for

Host: pablocas (Microsoft)
A: the web though).

Host: Hussein (Microsoft)
Q: If I make a connection to an Access 2003 database that does NOT have a password, why do I continually get an error from VB.net about it when I try to drag and drop the connection to a window (form)?
A: This is new warning that Server Explorer in VS.NET 2003 just throws even if the database is not passowrd protected. You can ignore it if the DB is not password protected.

Host: pablocas (Microsoft)
Q: pablo: however, I don't want to do this on the production systems. Also, IIS and SQL Server are on different systems. Will the SQLXML address the security concerns in this case?
A: Regarding security: if you want to expose SQL Server data to the internet or any untrusted network, you should have the SQL Server properly firewalled and separated from the web

Host: pablocas (Microsoft)
A: server that's acting as a web-services front-end. By having the webserver in the DMZ and the SQL Server behind the DMZ you enhance the protection needed to make sure that only

Host: pablocas (Microsoft)
A: controlled access to SQL Server happens. Note that this is orthogonal to whether you use SQLXML, ASP.NET webservices or some other technologies. In general, you should have your

Host: pablocas (Microsoft)
A: database server protected and expose extry points through other servers.

Host: pablocas (Microsoft)
Q: pablo: I'd like to know how to configure the webservice to run as a specific user, then allow that user to have extremely limited access to the SQL database (certain SPs only).
A: One option is to create an NT account and setup your webservice to use that account. Then you can use integrated security to connect to SQL Server (add "integrated security=true"

Host: pablocas (Microsoft)
A: to the connection string). On the SQL Server side, you can restrict that particular account as much as possible so it can only do the things you want to enable.

Host: pablocas (Microsoft)
A: An alternate option is to use a SQL account...the rest would be the same. The NT account is a little bit more complicated to setup, but I'd recommend it as it is more secure.

Host: Hussein (Microsoft)
Q: Is is possible to update a table without a primary key or FK, in the database?

Host: MikeP (Microsoft)
A: Yes, as long as there is some combination of columns that uniquely identify a row (possibly all of the columns) then you can put those in the "Where" clause of the DataAdapter.UpdateCommand.

Host: Hussein (Microsoft)
A: NO, if you are using ADO.NET or such DA technologies.

Host: MikeP (Microsoft)
A: It is possible to update without *declared* PK (or unique) columns in the database. However, you do need something to uniquely identify the row, and many tools that attempt automatically generate insert/update/delete statements may fail if no PK or unique constraints are declared.

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

Top of Page