*very* basic question about design of ado.net

[Steve]

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

 

[One Handed Man]

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

A.) Looks ok to me, but different people have different ideas about how to
design a structure.

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

A.) Use ( LostFocus )

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

A.) Trust me, this is not going to make any difference with one record.
Certainly
not one you could notice.

 

[Ivan Weiss]

That looks pretty good, I am also a newbie and know how frustrating
ADO.NET can be. By the way since you are using access you do not need
that third import statement for SQLclient.

Also general question for anyone who might know this. I saw that Steve
called the dispose method of his command object. Is this necessary to
remove the resources or does garbage collection eventually grab it on
its own (in my case I am using a database class not a module). I am
very confused about garbage collection compared to VB6. Also with forms
do we need to call the dispose method or is just close sufficient?

-Ivan

 

[One Handed Man]

It depends, as a matter of course, it is a good idea to call the dispose method rather than waiting for the system to realise that a form object needs to be marked/deleted.

OHM
That looks pretty good, I am also a newbie and know how frustrating
ADO.NET can be. By the way since you are using access you do not need
that third import statement for SQLclient.

Also general question for anyone who might know this. I saw that Steve
called the dispose method of his command object. Is this necessary to
remove the resources or does garbage collection eventually grab it on
its own (in my case I am using a database class not a module). I am
very confused about garbage collection compared to VB6. Also with forms
do we need to call the dispose method or is just close sufficient?

-Ivan

 

[Bob]

I'm no guru, but speaking from a few years of experience which began with Access
and VB5...

- Don't use SELECT *

The columns you're reading from the DB should be explicitly enumerated - even if
it's all of them. Cropping the returned columns down to just what you need will
not only save resources, but also tell you exactly what columns are used and
where so you don't have to go looking in your code and hope you got everything
when you have to change a table or a view definition.

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance,
have no discernable meaning. If you want to know what column 4 is you'll have to
look it up. This practice should be avoided to keep your code readable and
maintainable. Use the column names so you don't have to worry about what order
they came in or guess what number gives you what column.

- Just because you're a newbie doesn't mean you should start with Access instead
of SQL Server.

SQL Server is easier - not harder - to use, and far, far more stable and
scalable. If you ever want to increase the number of users for your app, you'll
be moving to it anyway so you should consider using it to begin with. If you
have the $$$, it's totally worth it.

- SQL does not belong in application code

It may not matter with a small app, but if it grows you're going to need a way
to analyze how your app accesses your DB when you need to make changes. This is
difficult if you have SQL in hard code, but simple if it's all in stored
procedures (a feature of SQL Server, these are kept server-side). Not only are
the contents of all stored procedures searchable like other data, but you can
also access table dependency information which will give you a list of
everything you need to check when you need to make changes. Put it another way -
say you need to make a change to a table that accessed from 8 different
locations in your application. Would you want to do a text search through your
code, hope you found all the references, and then recompile your app, or would
you rather just have a list of stored procedures given to you that you can edit
server-side, possibly while your app is live, without your users ever knowing?

Good luck to you and happy coding,
Bob

 

[Jay B. Harlow [MVP - Outlook]]

Bob,

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance,
maintainable. Use the column names so you don't have to worry about what order
they came in or guess what number gives you what column.

This is bad advice in that it will slow down your program! Good advice for
the reason you gave!

Seeing as I normally specify the fields in the select itself (your first
item) I normally setup an Enum with the fields in the correct order, then
use the enum to retrieve the values. Using the Enum helps avoid the "Magic
number" problem. Sure some may say I just shifted the magic number problem
;-) If I change the query, I change the Enum. Alternative I will lookup the
ordininal values (based on the names as you suggest) outside the loop, then
use these ordinals inside the loop.

However in .NET when using a DataAdapter & DataTables & DataSets this item
is moot. Of course if you are not using DataSets & DataTables, then this
item definitely makes a difference.

Hope this helps
Jay

 

[Steve Fay]

Oops. Weird. Let me try again. Thanks for all the help I've gotten so
far. This is fantastic!

As far as how to access my fields, I read somewhere that I can use the
dr.GetOrdinal("CustomerID") to get the position for the item. This
combines a naming convention that works, plus performance isn't lost.
Ideas?

On another note, I'm having a bit of trouble with form validation:

Private Sub txtCompNum_LostFocus(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum = "
& "'" & txtCompNum.Text & "'")
If txtCompNum.Text = "" Then
MessageBox.Show("Cannot be blank")
txtCompNum.Focus()
ElseIf dr Is Nothing Then
MessageBox.Show("Invalid Competition")
txtCompNum.Focus()
Else
While dr.Read
txtDance.Text = dr.GetString(1)
MessageBox.Show("true")
End While
End If
dr.Close()
End Sub

The problem with the above code is primarily with the "If
txtCompNum.Text = ""..." code. It seems to give me the messagebox ok,
but right after that, I don't know if the focus is being givenback to
that text box. I get an error saying the the datareader must be closed
(essentially). I can close it inside the "If" statment, but that's not
really the right way to do this, is it? Suggestions would guarantee a
christmas/hanukkah card to anyone who helps me out!

Thanks!
Steve

 

[Bernie Yaeger]

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read only
and forward only, so if you want to do anything with the data, you'll be
crippled. Use datasets/datatables instead. And when you refer to a column
you can refer to it as irow("fname") instead of irow(7). This is supposedly
slower (unless you have a typed dataset - I won't bore you with that) but
it's very effective and much easier to debug.

HTH,

Bernie Yaeger

 

[Mark Jerde]

As far as how to access my fields, I read somewhere that I can use the
dr.GetOrdinal("CustomerID") to get the position for the item. This
combines a naming convention that works, plus performance isn't lost.
Ideas?

Yeah -- let the computer do it. <g> What are those 3 users of the 3000
record DB going to do with the additonal 63 microseconds per week this
optimization MIGHT save them? ;-) Dogma from the 1970's still applies:
"Don't Optimize Abort Routines." Get the app working, use tools to see
where it's too slow if it is, and speed up the slow parts.

I have worked with people ultra concerned about micro efficiency -- ordinals
everywhere; fancy memory tricks, etc. They weren't terribly bothered with
macro items of interest to the customers -- on time delivery, reliability,
upgrades not breaking fielded systems, ease of use, etc. The overall result
is they had the fastest system that ISN'T a major force in the market today!

My advice is you worry about the ordinals after the online help is
integrated... ;-) I'm preaching to myself too. It's a classic codeslinger
occupational hazard: Being easily drawn into interesting but
off-the-critical-path items.

-- Mark

 

[Light Templar]

Am I correct in saying that the only use for a datareader would be to have
a fast lookup of data for displaying purposes? I'm new with VB .Net.

Just about the time I was getting really good with 6.0... :c)

 

[Giorgio Gentili]

Hello and for VB6 users like me and you, the changes to data access also
makes me not understand a bit of how this strange new thing work :-D , if
you wants the short answer it's yes, but you must think how now the things
are maded in dotnet (not needed to do something special, read if you want to
understand better how things are done). Every data access in dot net tries
to be as disconnected as possible, so looking this way, in a certain way,
every way to work with data is disconnected at some level. for example,
dataset object (the other approach to handle data in a more ado recordset
way) keeps "all the data" (i'm not certainly sure maybe just make an
automated look ahead) in the memory and then, when you modifies this info,
it keeps the changes in a datarow and the original in other, then you
finally reject or accept this changes sending the data changed (and that's
the fun part, just the changed data) to the real table minimizing the
overuse (or overusage, i don't know :-D) of opens connections and network
use. So, for example in web apps or other apps that many users will use,
it's often better work directly with datareaders and commands than datasets
because it uses a lot more of memory.

Hope this helps, and not get bored.

PS: Sorry for the English

GiorgioG

 

[Jay B. Harlow [MVP - Outlook]]

Light & Giorgio,
David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press,
explains DataReaders, DataAdapters & DataSets along with when to use which
when.

DataReaders are used to retrieve information from you database.

DataAdapters is used to adapt information from your database into a
DataTable

DataSets are collections of DataTables, used to store & manipulate data in a
disconnected fashion. DataSets can contain data from XML files instead of
from a database.

Hope this helps
Jay

 

[Light Templar]

Thanks for the explaination. I'm going to take a few classes in the summer
to bring me up to speed with .net, but I really didn't expect such a
drastic, or what seems like a drastic, departure from 6.0. Kinda has my
head spinning a bit. :c) My company just decided to upgrade everything
and move to .net about a month ago.

 

[Light Templar]

Yeah, I know the words... :c) Putting to use is proving to be more
difficult. We oldsters don't learn new languages like you youngsters. :c)