Questions on application design

[Matthew Hood]

My company has expressed a desire to convert an existing MS Access
application to a full VB.NET application. My experience is with VB6 so I
want to ask a few questions and get some input on the best way to handle the
following design:

Situation: We want to allow our customers to install with one of the
following options:
1. Use an Access MDB file as the data backend.
2. Use a SQL Server backend. (Either MS SQL, or MySQL)
3. Use an Oracle backend. (This option would be implemented at a later
date.)

Question: What would be the best way handle all data access within all the
forms?
My initial solution: (Don't know if it's the best way)
I have created a Database module structured like so:
**
Property DBType (Specifies the type of backend to use)
Property DBServer (Specifies the SQL Server)
Property DBUsername (Specifies the SQL Server account to use)
Property DBPassword (Specifies the SQL Server account's
password.)

Function ReturnDataset(ByVal SQLString) as Data.Dataset (Return
a dataset object with the specified SELECT command.)
Sub UpdateDatabase(ByVal SQLString) as Boolean (Update the
database using the specified UPDATE/INSERT command)
**

For each table I have created a "Record" class module structured
like so:
**
Property Field1
Property Field2
Property Field3, etc.
Property Dataset as Data.Dataset (Allow me to bind
controls/grids when needed)

Sub OpenData() (Opens a dataset and populates the properties)
Sub UpdateData() (Updates the database with new/changed data)
**

For the data forms I open the form, create a new "Record" class
module, and if appropriate, execute the OpenData routine.
Then I set the control values to the approprate property (through
code).
When appropriate, I then save any changes/updates using the UpdateDa
ta routine.

The idea I had by using the above methodology, is
1. It allows me to specify in 1 place the type of database to use in
the Database module.
2. The record class modules aren't aware of what backend they are
using. They all use the same routines to update and return data. (Using
T-SQL syntax hardcoded into the class module)
Am I on the right track with this type of design? Or is there an easier and
better way of accomplishing this.

Thanks for any info/feedback.
-Matt

[Cor]

Hi Matthew,

This kind of sollutions cannot be handled in detail I think, but when you
ask am I on the right track, I think yes. One of the things that triggered
me direct was that I would look for what the windows integrated security for
users and passwords could do for me.

Another thing was where to hold the user information, for your application,
have a look for that what an XMLdataset as file can do for you or the
registry.

I do not believe that a record class will help you, I think it will give you
only more work.

A dataset is a class that holds rows (records), which come from the datarow
class.

When you are used to it, it is much easier to use than the recordset.

I believe that by building those records classes you are loosing the very
usefull posibilities by instance from dataview, the datarowcollection class
and from the datatableclass.

I hope this helps a little bit?

Cor

[Jay B. Harlow [MVP - Outlook]]

Matthew,

Situation: We want to allow our customers to install with one of the
following options:
1. Use an Access MDB file as the data backend.
2. Use a SQL Server backend. (Either MS SQL, or MySQL)
3. Use an Oracle backend. (This option would be implemented at a later
date.) The easiest way is to use the OLE DB client in System.Data.OleDb namespace,
as all those databases support an OLE DB provider. Another option would be
the ODBC client in System.Data.Odbc. Both OLE DB & ODBC are designed to work
with "any" database in a general manner. However each database may still
have unique SQL syntax "features".

However to get better performance from Oracle, SQL Server, and MySql you
should use their respective client in their own namespaces (for example
System.Data.SqlClient for SQL Server). Using the database specific client
complicates things, but not too much. Read on...
I have created a Database module structured like so: Rather then create a Database Module, you may want to consider a Database
Class that has overrideable methods. Each database client (Access, SQL
Server, Oracle) would be a concrete class. As then you can use the Database
Class polymorphically.

The Database Class itself could have a shared method (A Factory Method) that
returns the current instance of the configured Database method. I would
consider implementing this by reading the type itself from the config file.

Something like:

Public MustInherit Class DataBase

Private Shared m_current As DataBase

Shared Sub New()
' Dynamically load an assembly, and create an object
Dim type As Type
type =
Type.GetType(ConfigurationSettings.AppSettings("da tabase")
' appsettings/database = "mynamespace.myclass, myassembly"
m_current = DirectCast(Activator.CreateInstance(type), Database)
End Sub

Public Shared Readonly Property Current() As DataBase
Get
Return m_current
End Get
End Property

Public Property DBUsername
Public MustOverride Function ReturnDataset(ByVal SQLString) as
Data.Dataset
Public MustOverride UpdateDatabase(ByVal SQLString) as Boolean
End Class
' Uses the OleDb client
Public Class OleDbDatabase
Inherits Database
Public Overrides Function ReturnDataset(ByVal SQLString) as
Data.Dataset
Public Overrides UpdateDatabase(ByVal SQLString) as Boolean
End Class

' Uses the SqlClient
Public Class SqlServerDatabase : Inherits Database

' Uses the OracleClient
Public Class OracleDatabase : Inherits Database

' Uses the MySqlClient
Public Class MySqlDatabase : Inherits Database

By creating new classes that inhert from Database, each possible in their
own DLL, you can add new database clients without requiring your app to be
recompiled. Note database client specific types (SqlCommand for example)
would not be exposed from the above classes.

NOTE: Rather then having "database" in the appSettings section of
app.config, I would consider creating a new section, that included both the
database, connection string, and other "database" specific settings that are
needed.
Function ReturnDataset(ByVal SQLString) as Data.Dataset (Return
Rather then returning a DataSet consider have the DataSet as a parameter, as
then you could put multiple DataTable, that are inter-related into a single
DataSet.
Function ReturnDataset(ByVal SQLString) as Data.Dataset
Sub UpdateDatabase(ByVal SQLString) as Boolean Rather then accepting SQL strings (that may vary depending on your access
method, I would consider implementing a DataGateway[3] for each table, then
use the Database Class as an Abstract Factory Pattern[4] to get the correct
Gateway for the correct access method.

Also if you are using DataSets, you need 3 parameterized commands to update
the database, an UpdateCommand, a DeleteCommand, and an InsertCommand, see
the four commands on DataAdapter[1].
For each table I have created a "Record" class module structured
like so: You could use a Domain Model[2] as you are describing or a Typed Dataset[1],
IMHO it really depends on how much "business" logic you want in your
objects. I've used Domain Models, Typed Datasets, and untyped Datasets,
depending on the requirements of the project.

Some useful references:

[1] David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS press
is
both a good tutorial on ADO.NET & a good desk reference for later. It covers
data access and DataSets.

[2] Rockford Lhotka's book "Expert One-On-One Visual Basic .NET Business
Objects" from A! Press, provides a framework for data binding to your own
domain (business) objects.
http://www.lhotka.net

[3] Martin Fowler's book "Patterns of Enterprise Application Architecture"
from Addison Wesley press covers both DataSets & Domain models at a higher
level plus a couple of other possibilities for managing your data. The
previous two books go into detail on two of the Martin's patterns...

[4] GOF's book "Design Patterns - Elements of Reusable Object-Oriented
Software" from Addison Wesley, covers a number of useful design patterns for
creating OO programs. GOF = Erich Gamma, Richard Helm, Ralph Johnson, and
John Vlissides. James W. Cooper's book "Visual Basic Design Patterns - VB6.0
and VB.NET" is a good companion book for the GOF book, as James explains the
patterns themselves in both VB6 & VB.NET syntax.

Hope this helps
Jay

"Matthew Hood" <No****@NoSpam.com> wrote in message
news:uc**************@TK2MSFTNGP11.phx.gbl... My company has expressed a desire to convert an existing MS Access
application to a full VB.NET application. My experience is with VB6 so I
want to ask a few questions and get some input on the best way to handle the following design:

Situation: We want to allow our customers to install with one of the
following options:
1. Use an Access MDB file as the data backend.
2. Use a SQL Server backend. (Either MS SQL, or MySQL)
3. Use an Oracle backend. (This option would be implemented at a later
date.)

Question: What would be the best way handle all data access within all the
forms?
My initial solution: (Don't know if it's the best way)
I have created a Database module structured like so:
**
Property DBType (Specifies the type of backend to use)
Property DBServer (Specifies the SQL Server)
Property DBUsername (Specifies the SQL Server account to use)
Property DBPassword (Specifies the SQL Server account's
password.)

Function ReturnDataset(ByVal SQLString) as Data.Dataset (Return a dataset object with the specified SELECT command.)
Sub UpdateDatabase(ByVal SQLString) as Boolean (Update the
database using the specified UPDATE/INSERT command)
**

For each table I have created a "Record" class module structured
like so:
**
Property Field1
Property Field2
Property Field3, etc.
Property Dataset as Data.Dataset (Allow me to bind
controls/grids when needed)

Sub OpenData() (Opens a dataset and populates the properties)
Sub UpdateData() (Updates the database with new/changed data)
**

For the data forms I open the form, create a new "Record" class
module, and if appropriate, execute the OpenData routine.
Then I set the control values to the approprate property (through
code).
When appropriate, I then save any changes/updates using the UpdateDa ta routine.

The idea I had by using the above methodology, is
1. It allows me to specify in 1 place the type of database to use in the Database module.
2. The record class modules aren't aware of what backend they are
using. They all use the same routines to update and return data. (Using
T-SQL syntax hardcoded into the class module)
Am I on the right track with this type of design? Or is there an easier and better way of accomplishing this.

Thanks for any info/feedback.
-Matt