Multiple Declarations?

[BadgerByte]

Hi,

I have created an application which currently uses an access mdb as a datastore, however the access mdb becomes unpractical when used in a multi user environment. I want the application to be able to use two different types of datasource, access and msde/SQL Server. The Data access layer is encapsulated in a component 'dbaccess' which holds update/insert/delete functions as well as shared events etc. Now (not thinking ahead too much ) I created a second DAL for SQL server 'dbsql' mimicing the exact same functions/subs/events as dbaccess with a view to switching the component used by my classes for data access. For example, a form ViewEmployees would contain:

Private Withevents mydb as New dbaccess

and all calls to the database will be made through mydb.

The question is, can anyone think of a way I can switch the type of 'mydb' at runtime? Or must I have one DAL component which can cope with both situations?

I hope I haven't lost you here, difficult one to explain!

Many thanks if you've read this.

 

[kulrom]

If i understand well your question then this is an example how to change connection in runtime

http://www.vbdotnetforums.com/showthread.php?t=3595

Regards

 

[TechGnome]

I don't think it's so much changing the connection as it is the consumption data type - ie, SQLAdaptor vs ODBCAdaptor, that sort of thing.

What you should do is create a mid level class that sits between the form and the database itself. Then the code in the forms and the rest of the app only knows about this class. And it should return non-DB specific functions & data. Then as part of the constructor, you pass it the type of connection you want, or some kind of token that then determines if you use the Access version or the SQL Server or Oracle version or .....

I'm not sure that made sense....

-tg

 

[BadgerByte]

Thank you

Thank you both for your input here, Kulrom, although your code was not exactly what I was after (mainly due to my rather confused description) it is extremely handy for another part of my project so many thanks there TechGnome I have decided to attempt to create a mid-level class as you suggest between the form and the data access layer.

If I could bug you both for a little more advice, my data access layer or layer(s) now, is basically a componenent onto which I have placed all the DataAdapters, subs and functions necessary for accessing the database.

When I call fill or update for a table I call the necessary function say: fillappointments with a reference to the dataset I wish to fill, same for updates ie.

private function fillappointments(ByRef Ds as MyDatasetType) as DbStatus
.
.
End function

I'd simply like to know if passing a reference like this is a costly procedure, is it bad coding practise? As I'm obviously going to have to pass that reference twice if I implement a mid-level class.

Thank you both again

 

[TechGnome]

Actualy you don't pass the DS in.... you pass it out:

Private Function FillAppointments() As DataSet or it could be a DataTable

.
.
.
End Function

FillAppointments would then make the necessary calls to the right data object(s) and return a DataSet (or DataTable) with the info requested.

And you could call it like this:

cboAppointment.DataSource = MyDBClass.FillAppointments()

Where MyDBClass is your mid-tier.

-tg

 

[BadgerByte]

Is this awful coding?

Thanks again TechGnome,

I'm going to come clean here, I have a horrible feeling that the way I've created this product is generally, well, ever so slightly pants, and am starting to get that familiar urge to redesign for the sake of my sanity. The current structure is as follows:

Each form/class (which requires database access) has:

- A typed dataset for ALL the tables regardless of whether they need access to all the tables. This is always called DsAllTables. (I know shocking!)

- A dbaccess component as previously discussed. (defined here as mydb)

When the class needs to fill say, employees it calls the function -

mydb.fillemployees(DsAllTables, "<Optional SQL SELECT String>")

mydb (the dbaccess component) then builds an OleDBCommand using the SQLString (if it's specified) and performs the necessary fill on the employees table in DsAllTables.

It then returns a value indicating whether or not a concurrency or dberror has occurred which can be dealt with in the class.

So the form now has it's employees DataTable filled by passing its own dataset by reference to mydb.

Is this an awful way of working? Feel free to slap me.

 

[TechGnome]

If it works, it works.... It's what I'm currently doing in a project of mine - but if I had the time to go back and do it all over again, I would.

PAssing DS and DTs around is less costly than passing Recordsets in ADO Classic. However, passing IN the DS to be filled will become costly as more tables are filled in it.

That's why I was thinking that an object in between might be more optimal.

Or move DsAllTables into myDB.....
I guess it depends on what you are doing with the data after you have it in the DsAllTables.

Given what I do know, I'd move DsAllTables into myDB. Then to fill it, you call myDB.FillEmployees ("optional sql")
To access it: myDB.Employees where Employees is a property that returns a DT type

To get a specific field from a specific row: myDB.Employees.Rows(1).Cols(1)....

Make sense?

-tg

 

[BadgerByte]

If only time stood still!!

That's definitely a more elegant solution and I shall attempt to use it in the future, completely encapsulates the data.

Thanks again for your time.

 

[BadgerByte]

Oh dear I fear I'm going to bug you again!,

My current situation is that I have created:

a dbaccess component containing all functions for accessing an access (OleDB) database.

a dbSQL component containing all functions for accessing an SQL Server (SQL) database.

And now:

A mid-level class dbMidLevel which mimics the exact functions of the other two.. non-dbspecific.

I obviously have to define, depending on whether the constructor specifies SQL or Access, the data access component either access or SQL.

Does this mean I have to declare it as object then use late binding to access the correct functions in dbaccess or dbsql.

ie
declarations:

private mydb as object

in the constructor:

Select Case dbType
Case access
mydb = New dbaccess
Case sql
mydb = New dbSQL
End Select

An example function:

private function fillappointments() as dataset
 
Select Case dbType
Case access
return DirectCast(mydb, dbaccess).fillappointments
Case sql
return DirectCast(mydb, dbsql).fillappointments
End Select
 
end function

Sorry about the code it may be rubbish, off the top of my head, but would this be the only way to do it?

 

[TechGnome]

No, and that's not quite how I meant it.... Let me see if I can whip up a quick example....

Private myDB as MyDataClass

Then instanciate the class like this:

myDB = New MyDataClass("Access",MyConnectionString)

The constructor for MyDataClass would look like this: (the default New sub would get removed as it won't be needed.)

Public Sub New (DbType as String, ConnectionString as string)
'Store the dbType and ConnectionString in module variables.
End Sub

PublicFunction FillAppointments () As DataSet
Dim retDataSet as DataSet

  Select Case DbType
    Case "Access"
      retDataSet = CallMyObject_ThatUses_Access
    Case "SQL Server"
      retDataSet = CallMyObject_ThatUses_SQLServer
  End Select

  Return retDataSet

End Function

From your code, call it like this:

myDB = New MyDataClass("Access",MyConnectionString)
myDSTables = myDB.FillAppointments

Nothing is dimed as an object, so everything is strongly typed. By creating new instances of MyDataClass, and setting different parameters to it, you can connect to multiple datasources. Handy if you need to move data from one DB to another. And, by setting it up like this, your forms know nothing about the datasource. Which is as it should be.

Does that help clear it up some?

-tg

 

[BadgerByte]

That's nothing short of beautiful tg, thank you!

So if I declare as follows:

private mydbaccess as dbaccess
private mydbsql as dbsql

Then in New also add

Public Sub New (DbType as String, ConnectionString as string)
'Store the dbType and ConnectionString in module variables.
 
Select Case dbType
Case "access"
	 mydbaccess = New dbaccess
Case "dbSql"
	 mydbSql = New dbSql
End Select
 
End Sub

This would reduce the overhead, ie I wouldn't have to create new instances of both objects, is this correct?

 

[TechGnome]

BINGO! You've got it!

-tg