VB Express/SQL Server/ADO.net Project Design Question?

  • Thread starter Thread starter bradmarcus
  • Start date Start date
B

bradmarcus

18 months ago I re-wrote an entire order entry system with an Access
2002 front end (original the system was all Access) and SQL Server
stored procedures for all data retrievals, updates, etct. This was to
prepare for a time when I could re-write the front-end in VB.net and
use all of the existing stored procedures. So, I started down the path
of moving the system to .NET a week ago. In my experimentation, I wrote
some code like the following to call a stored procedure, then read
through the results and parse the results for values in the form
controls, just to see if this will work:

Private Sub btnGetPersonID_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnGetPersonID.Click
Dim strConnectionString As String = "Data Source=Win2kserver;
Initial Catalog=BCORDERSYS;" & _
"User ID=XXXXXXX;PWD=XXXXXXXX"
Dim oSQLConn As SqlConnection = New SqlConnection()
Dim cmd As New SqlCommand
Dim custIdParam As New SqlParameter
Dim reader As SqlDataReader

'load parameter for the stored procedures
custIdParam.ParameterName = "@customerid"
custIdParam.SqlDbType = Int
custIdParam.Direction = ParameterDirection.Input
custIdParam.Value = CInt(Me.txtEmailAddress.Text)
cmd.Parameters.Add(custIdParam)

'set desired connection for the cmd object
cmd.Connection = oSQLConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "procGetCustomer"

oSQLConn.ConnectionString = strConnectionString
oSQLConn.Open()
reader = cmd.ExecuteReader()

' Call Read before accessing data.
'This should only return one row, but checking for more.
If reader.HasRows Then
Do While reader.HasRows
While reader.Read()
'Load fields into labels
Me.lblFirstName.Text = reader("FirstName").ToString
Me.lblLastName.Text = reader("LastName").ToString
End While
reader.NextResult()
Loop
Else
MsgBox("Sorry Monkey! no records")
End If

' Call Close when done reading.
reader.Close()
oSQLConn.Close()
End Sub

This appears to work fine (although a lot of handpoked code), and I
would be happy to re-rewrite all the code to be similar to this as most
of the database work is done with the existing stored procedures.

My questions are:
1. Is this a good approach to take for the entire project? Basically,
loading the parameters, calling the stored procedures and loading
controls with those results.
2. Will a similar approach work for UPDATE, and INSERT stored
procedures, not just SELECT?
3. Basically, I am using the SQLDATAREADER like the old ADO
recordsets....is that right?
4. I am guessing that VB Express has a limitation that won't let you
connect to a remote SQL SERVER and use the GUI database tools?
 
Brad,

ADO.Net's DataTable/DataSet is more like ADO's Recordset than the DataReader
is.

Also, the DataTable/DataSet gives you more options for displaing data. For
example, through data binding to controls on a form.

Using the datareader technique that you have shown, how do plan on handling
concurrency issues?
 
Do I need to worry about concurrency if there is a small number of
users that are EXTREMELY unlikely to be accessing the same data at the
same time?

The way the current system works is mostly like this:
- Collect text values from form to use as parameter(s) for stored
procedure. Like the value of a persons last name to find all orders
for that person.
- open the database
- run the stored procedure (GetOrdersBasedOnLastName)
- collect needed results (e.g. all order numbers for that person)
- close the database

So, in this style the database is being accessed VERY infrequently with
most of the front end processing being done with VB.

Does that make sense? Should I open the database at the start of the
app and close it when the app shuts down?

Anyway, the current system has been running rock-solid for 18 months,
but I'm not opposed to changing the general database approach if needed
to be more efficient.
 
Brad,

I suppose the "last in wins" approach to concurrency can sometimes make
sense. If that works for your app then I guess you don't need to worry about
concurrency issues.

For scalability most apps would open a connection to the database, perform
their database work and then close the connection. For apps that will always
have a small number of users I guess you could open a connection when the app
starts and hang on to it for the duration of the app. (This of course does
not affect concurrency issues).

Kerry Moorman
 
Back
Top