moving ado to ado.net and option strict

[Jeff Jarrell]

I am sure this has been discussed many times before, so I apologize up
front. I did google the topic but I didn't seem to hit on the issue at hand.

I am migrating a significant codebase from vb6 to vb.net. I have been
working on appropriate abstractions of the data layers (nifty stuff behind
the scenes) but when I ultimately get to how it looks to the application
programmer (as in how much change is necessary in the old code). I am stuck
with how to keep option strict on without having to cast everything in
sight. (Or use type-safe getters.)

I don't need to debate option strict. Seems to be a strong consensus that
it should be on. Now to keep my port fast I need to make something that
looks like a ado recordset. presently the .field property returns an
object. But is there no way to code around this specific issue. The fewer
changes at the application level the better. I do have complete freedom in
the data access layers.

Now correct me if I am wrong, but now that I am all about option strict on,
I have created a dependency in my code to the schema of the database.
schema changes (a data type changes) my code breaks.

In our vb6 app we had a wrapper around an ADO record set. It had some
programmer usability features and enhancements in terms of error handling.

Here is a typical coding pattern.
------------------------------------------------------------
sSQL = "select * from oewmssec where user_name = '%s'"
sSQL = wsprintf(sSQL, sUserName)

Call rs.openFile(sSQL, adoCon, sSQL, adOpenForwardOnly, adLockReadOnly)

If Not rs.EOF Then
sWMSUser = rs.Field("user_name")
bShippingAuthorized = rs.Field("shipping")
bReceivingAuthorized = rs.Field("receiving")
endif

rs.close
set rs = nothing
-------------------------------------------------------------
The rs.field property returns a variant which in turn is implictly cast.

Now if i start do it the vb.net\ado.net way with option strict on, i have to
include casts or create type-safe getters. (the .field property returns an
object).

sSQL = "select * from oewmssec where user_name = '%s'"
sSQL = wsprintf(sSQL, sUserName)

Call rs.openFile(sSQL, adoCon, sSQL, adOpenForwardOnly, adLockReadOnly)

If Not rs.EOF Then
sWMSUser = ctype(rs.Field("user_name"),string)
' cast
bShippingAuthorized = ctype(rs.Field("shipping"),boolean)
bReceivingAuthorized = rs.getBoolean("receiving")
'typesafe getter
endif


==========
so is this it? my destiny? inserting "ctype(" accross thousands of lines
of code.

thanks
jeff
==========

 

[Earl]

Your code really hasn't been converted to ado.net at all. You are using the
namespace for classic ado, using the same syntax as you would for a classic
ado app, and simply casting the value. I doubt if that is really where you
want to head with an upgrade.

While there is more than one way to skin this, a more typical ADO.Net
example might look as shown below. With ADO.Net, you generally use either
the datareader or the dataset to get to your values (you could also the
ExecuteScalar method on a Command object to pull single values). The first
example shows how you might do it to bind to a combo, the second example,
how to use a datareader just to pull some values into variables.

Imports System
Imports System.data
Imports System.Data.SqlClient

Public Sub PopulateEmployees()
'populating the in-memory dataset and data table
Dim strEmp As String = "SELECT * FROM Employees"
Dim strSQLServer As New SqlConnection(strConnString)
Dim da As New SqlDataAdapter(strEmp, strSQLServer)
strSQLServer.Open()
da.Fill(ds, "dtEmp")
strSQLServer.Close()
End Sub

Private Sub LoadEmployeeCombo()
'binding the in-memory table to a combo
cmbEmp.DataSource = ds
cmbEmp.DisplayMember = "dtEmp.Employee"
cmbEmp.ValueMember = "dtEmp.EmpID"
End Sub

-OR- to pop some values out discretely, as you did with your recordset, in
this case using a datareader

Dim strFirstName As String
Dim strLastName As String

Dim strSelect As String = "SELECT FirstName,LastName FROM Individuals WHERE
CustomerID=" & CustID

Dim cmd As New SqlCommand(strSelect, cnSQLServer)
Dim dr As SqlDataReader
cnSQLServer.Open()
dr = cmd.ExecuteReader()
Dim strFirstName As String
Dim strLastName As String

While (dr.Read())
If Not dr.IsDBNull(1) Then strFirstName = dr.GetString(1)
If Not dr.IsDBNull(2) Then strLastName = dr.GetString(2)
End While

dr.Close()
cnSQLServer.Close()

 

[Jeff Jarrell]

thank you for taking the time to actually read the example.

I am not using classic ADO. What wasn't evident was that the "rs" is a
class that owns\contains a DataSet, then the properties like eof, bof, and
field are just emulated and the current record pointer is a thing of my
creation..

here is an example the field property (it's not finished).
Public Property field(ByVal sFldName As String) As Object

Get

Dim r As DataRow

r = m.ds.Tables(0).Rows(m.iRecPtr)

Return r.Item(sFldName)

End Get

I know the ado.net syntax but my problem at hand is that i have about
150,000 lines of code to port. It might be nice to have ado.net syntax
sprinkled through the code base but does that "really" add value? I have
looked at this from both sides and came to the conclusion for this project
and circumstances it is most efficient to bury the .net syntax into a RS
emulator type thing and try and keep the basic interface the same. I am not
advocating that this is the right thing to do ALL of the time.

thanks for taking the time to look.

 

[Cor Ligthert]

Jeff,

As long as your return type is object you are sticked to that CType or
better in this case DirectCast.

You can of course as well create strongly typed classes of all your
datarows.

In fact the same as you do now, however than you return the right type for
every item. In general try to avoid the Object as return type, you will see
that you almost never need it.

Problem can be in that, that you than have than to select forever the full
row from your database.

(The route in the middle is in this case probably the best. For datatables
used frequently in your program you can do this, while you use for seldom
used datatables the DirectCast way)

And please don't use misleading datanames in questions. Even a word as
"m.iRecPtr" is needing more time to understand that you mean "index" or
"columname" or "datacolumn".

Just my thoughts,

Cor

 

[Earl]

That is indeed a lot of work to emulate a recordset approach. Don't take
offense, but that really bastardizes the concept of .Net, where the need to
manage eof, bof, and record pointer have become passe'. When I was moving
from VB6 to .Net, I tried to look at things through the same spectacles,
that is, with the idea of classic ADO in my mind's eye, and it lead to
nothing but frustration, a lot of additional work, and in the long run, I
pitched it ALL out. I've come full circle and now I do not use ADO classic
at all. Fundamentally, this is why the so-called "upgrade wizard" is an
abysmal failure when it comes to database code.