MS SQL Server and MS Access

MS SQL Server and MS Access

Post by Stefan Hellber » Thu, 12 Jun 2003 21:37:14



Hello everyone,

Im going to start building a ASP .NET application and I
want to support both Access and SQL Server if possible.

Anyone know if there is a good aproach for this using
ADO .NET? (performance is important for SQL Server version)

Appreciate all input!

Regards
Stefan Hellberg

 
 
 

MS SQL Server and MS Access

Post by Pete Wrigh » Thu, 12 Jun 2003 23:04:06


There are many good approaches to this, none of them trivial (either in
terms of effort, or sheer volume of coding).

The simplest (from a 'dumb' angle) would be to separate all data access away
from the business entity objects into their own data layer with its own
namespace. you could then have a library to fill that namespace that
provides business entity driven data access through the SQL Server data
provider, and another that does the same with Access. Depending on the
version you are building, just slot in the appropriate library.

A more complex, but elegant solution follows on from this, using the Factory
Method, or Abstract Factory patterns from the Gang Of Four book.Both these
patterns provide an interface that the business entities can use to grab a
data access object, but with the factory itself responsible for providing
the most appropriate data access object based on the back end in use.

An even better alternative would be to use MSDE, and NOT Access. MSDE is
free, provides much of the power and flexibility of Access and is shippable
with your application. I am assuming from your question that you are not
using Access Forms or Reports, so this would really appear to be the best
option. You then just have all your data access based around the SQL Server
data provider and change the connection string in the application's
configuration file.

Hope that helps,

Peter Wright
Author of ADO.NET Novice To Pro, From Apress Inc.


Quote:> Hello everyone,

> Im going to start building a ASP .NET application and I
> want to support both Access and SQL Server if possible.

> Anyone know if there is a good aproach for this using
> ADO .NET? (performance is important for SQL Server version)

> Appreciate all input!

> Regards
> Stefan Hellberg

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

 
 
 

MS SQL Server and MS Access

Post by Stefan Hellber » Thu, 12 Jun 2003 23:43:11


Thanks alot Peter,

Great information.

Do you have any links to pages explaining one or more of
these scenarios further?

Regards
Stefan Hellberg

Quote:>-----Original Message-----
>There are many good approaches to this, none of them
trivial (either in
>terms of effort, or sheer volume of coding).

>The simplest (from a 'dumb' angle) would be to separate

all data access away
Quote:>from the business entity objects into their own data
layer with its own
>namespace. you could then have a library to fill that
namespace that
>provides business entity driven data access through the
SQL Server data
>provider, and another that does the same with Access.
Depending on the
>version you are building, just slot in the appropriate
library.

>A more complex, but elegant solution follows on from

this, using the Factory
Quote:>Method, or Abstract Factory patterns from the Gang Of

Four book.Both these
Quote:>patterns provide an interface that the business entities
can use to grab a
>data access object, but with the factory itself

responsible for providing
Quote:>the most appropriate data access object based on the back
end in use.

>An even better alternative would be to use MSDE, and NOT
Access. MSDE is
>free, provides much of the power and flexibility of

Access and is shippable
Quote:>with your application. I am assuming from your question
that you are not
>using Access Forms or Reports, so this would really

appear to be the best
Quote:>option. You then just have all your data access based

around the SQL Server
>data provider and change the connection string in the
application's
>configuration file.

>Hope that helps,

>Peter Wright
>Author of ADO.NET Novice To Pro, From Apress Inc.


message

>> Hello everyone,

>> Im going to start building a ASP .NET application and I
>> want to support both Access and SQL Server if possible.

>> Anyone know if there is a good aproach for this using
>> ADO .NET? (performance is important for SQL Server
version)

>> Appreciate all input!

>> Regards
>> Stefan Hellberg

>----== Posted via Newsfeed.Com - Unlimited-Uncensored-

Secure Usenet News==----
Quote:>http://www.newsfeed.com The #1 Newsgroup Service in the

World! >100,000 Newsgroups
Quote:>---= 19 East/West-Coast Specialized Servers - Total

Privacy via Encryption =---

- Show quoted text -

Quote:>.

 
 
 

MS SQL Server and MS Access

Post by Pete Wrigh » Fri, 13 Jun 2003 00:26:07


Well for MSDE, just take a look in MSDN that came with Visual Studio, or
wander over to msdn.microsoft.com and search on MSDE.

For the Factory Patterns, do a google search, or have a peek in "Design
Patterns", by Gamma, Helm, Johnson and Vlissides.

For the first approach, one namespace, two libraries filling it, you don't
really need an example, just look through the documentation on class
libraries and namespaces. All you are doing is creating two sets of data
access classes, one for SQL Server, one for Access, but with classes in both
libraries having exactly the same names, methods properties etc. Assign them
both to the same namespace and then include the appropriate project
reference based on the database you want to hit.

Really though the best approach is to drop Access and go MSDE.


> Thanks alot Peter,

> Great information.

> Do you have any links to pages explaining one or more of
> these scenarios further?

> Regards
> Stefan Hellberg

> >-----Original Message-----
> >There are many good approaches to this, none of them
> trivial (either in
> >terms of effort, or sheer volume of coding).

> >The simplest (from a 'dumb' angle) would be to separate
> all data access away
> >from the business entity objects into their own data
> layer with its own
> >namespace. you could then have a library to fill that
> namespace that
> >provides business entity driven data access through the
> SQL Server data
> >provider, and another that does the same with Access.
> Depending on the
> >version you are building, just slot in the appropriate
> library.

> >A more complex, but elegant solution follows on from
> this, using the Factory
> >Method, or Abstract Factory patterns from the Gang Of
> Four book.Both these
> >patterns provide an interface that the business entities
> can use to grab a
> >data access object, but with the factory itself
> responsible for providing
> >the most appropriate data access object based on the back
> end in use.

> >An even better alternative would be to use MSDE, and NOT
> Access. MSDE is
> >free, provides much of the power and flexibility of
> Access and is shippable
> >with your application. I am assuming from your question
> that you are not
> >using Access Forms or Reports, so this would really
> appear to be the best
> >option. You then just have all your data access based
> around the SQL Server
> >data provider and change the connection string in the
> application's
> >configuration file.

> >Hope that helps,

> >Peter Wright
> >Author of ADO.NET Novice To Pro, From Apress Inc.


> message

> >> Hello everyone,

> >> Im going to start building a ASP .NET application and I
> >> want to support both Access and SQL Server if possible.

> >> Anyone know if there is a good aproach for this using
> >> ADO .NET? (performance is important for SQL Server
> version)

> >> Appreciate all input!

> >> Regards
> >> Stefan Hellberg

> >----== Posted via Newsfeed.Com - Unlimited-Uncensored-
> Secure Usenet News==----
> >http://www.newsfeed.com The #1 Newsgroup Service in the
> World! >100,000 Newsgroups
> >---= 19 East/West-Coast Specialized Servers - Total
> Privacy via Encryption =---
> >.

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
 
 
 

MS SQL Server and MS Access

Post by Alex » Fri, 13 Jun 2003 04:02:45


You may want to take a look at this tool
http://www.2lkit.com/Default.aspx?path=products/2LKitnTier . It generates
well documented DB tier code for MS SQL and Access and a test WinUI
application. It will help you start and later save a lot of development and
testing time.


Quote:> Hello everyone,

> Im going to start building a ASP .NET application and I
> want to support both Access and SQL Server if possible.

> Anyone know if there is a good aproach for this using
> ADO .NET? (performance is important for SQL Server version)

> Appreciate all input!

> Regards
> Stefan Hellberg

 
 
 

MS SQL Server and MS Access

Post by Pete Wrigh » Sat, 14 Jun 2003 14:25:53


Hi Stefan,

You are a little off base.

First up, I have to labour the point (sorry), MSDE is completely free, you
can download it from Microsoft and it will let you do your entire
application using only the SQL Server data provider. Microsoft also provide
information on how you can install MSDE with your application onto your
end-user's machines. There really is no reason to use Access any more for
this kind of thing ;)

That said, your problem is a nice early morning problem to solve, so here
goes ;)

Simply stated, your problem is that you want to have two data access layers
in your application. One would work with an optimized OleDB data provider
(for Access), the other would work with an optimized SQL Server data
provider. You want the code, at runtime, to determine which to use, and you
want to seperate the functionality of these data access layers out from the
rest of the application to the extent that the rest of the app has no idea
which concrete data layer it's really using (because it doesn't really need
to know).

In the previous email, I pointed you in the general direction of two design
patterns - "Abstract Factory" and "Factory Method". Without going into too
much detail, let me explain what each does. The "Factory Method" pattern
simply defines an interface for creating an object. There is a lot more to
it than that, but that's really all you need to know at this point.

Abstract Factory on the other hand "provides an interface for creating
families of related or dependent objects". What this means is that there
would be a common interface shared by a number of classes (in your case, an
AccessDataLayer, and a SqlDataLayer that both implement the same "DataLayer"
interface). You would be passed an instance of either AccessDataLayer or
SqlDataLayer by the FactoryMethod, and would treat that object instance as
just a simple "DataLayer". Since both objects implement the DataLayer
interface you can work with them the same way. Since the objects themselves
are created by a FactoryMethod, your calling code won't ever really care
which data layer it's using. All it cares about is that the data layer
provided implements that "DataLayer" interface. The interface in question
would provide methods for doing all the usual things, like getting a
DataSet, running a query, etc etc.

At a higher level, here's what the code that calls the factory method looks
like.
Sub Main()

    Dim dataAccessLayer As IDAL

    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_SQLServer)

    ' dataAccessLayer now points to a SQL Server data access layer

    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_Access)

    ' dataAccessLayer now points to an Access data layer

End Sub

Notice tha the 'dataAccessLayer' object is declared as 'IDAL'. IDAL is an
interface declared elsewhere. Notice also that the code calls a 'shared
method' on a class called 'DAL' to get at an actual data access layer
object. By passing a parameter to DAL.GetDAL, the GetDAL factory method is
able to return the right type of lower level data access layer object to
you.

The code for that DAL class looks like this.
Public Enum DALTYPE

    DAL_Access = 1

    DAL_SQLServer = 2

End Enum

Public Class DAL

        Shared Function GetDAL(ByVal dbType As DALTYPE) As IDAL

            If dbType = DALTYPE.DAL_Access Then

            Return New AccessDataAccessLayer

        Else

            Return New SQLDataAccessLayer

        End If

    End Function

End Class

Simple enough - we declare an Enum to specify the data access layer type and
use that enum in the call to GetDaL to determine exactly which object to
return. Notice that GetDal returns an 'IDAL', but the code creates an
instance of either AccessDataAccessLayer or SqlDataAccessLayer. These are
two classes that implement the 'IDAL' interface. So the method here creates
the appropriate data access layer object, but the calling code treats that
object as a nice common IDAL implementation.

IDAL looks like this
Public Interface IDAL

   Function GetDataSet(ByVal query As String, ByVal ParamArray queryParams
As Object()) As DataSet

    Function GetReader(ByVal query As String, ByVal ParamArray queryParams
As Object()) As IDataReader

    Function ExecuteNonQuery(ByVal query As String, ByVal ParamArray
queryParams As Object()) As Object

End Interface

What we are saying here is that IDAL is an interface that contains three
functions (yours will probably have a lot more). Any object that implements
the IDAL interface will need to put some meat onto these three functions.
Also notice the GetReader() function. ADO.NET uses interfaces and
inheritance the same way we are. The OleDBDatareader and the SqlDataReader
objects both implement the IDAtaReader interface, so for those times when
you need to work with a reader in your code, we can have the Data Access
layer return an IDataReader type (which applies to both SqlDataReader and
OleDbDataReader).

Time now to look at code for the two 'concrete' data access layers.
Public Class SQLDataAccessLayer

    Implements IDAL

    Function GetDataSet(ByVal query As String, ByVal ParamArray queryParams
As Object()) As DataSet _

        Implements IDAL.GetDataSet

            ' Run code against SQL Server to build a dataset and return it

    End Function

    Function GetReader(ByVal query As String, ByVal ParamArray queryParams
As Object()) As IDataReader _

        Implements IDAL.GetReader

            ' Run code against SQL Server to build reader and return it
(notice that the return type

            ' is IDataReader! This allows us to return a SqlDataReader from
here, and an OleDbDataReader

            ' from AccessDataAccessLayer

    End Function

    Function ExecuteNonQuery(ByVal query As String, ByVal ParamArray
queryParams As Object()) As Object _

        Implements IDAL.ExecuteNonQuery

            ' Run code against SQL Server to run a non-query and return the
result

    End Function

End Class

Public Class AccessDataAccessLayer

        Implements IDAL

    Function GetDataSet(ByVal query As String, ByVal ParamArray queryParams
As Object()) As DataSet _

        Implements IDAL.GetDataSet

            ' Run code against Access to build a dataset and return it

    End Function

    Function GetReader(ByVal query As String, ByVal ParamArray queryParams
As Object()) As IDataReader _

        Implements IDAL.GetReader

            ' Run code against Access to build reader and return it (notice
that the return type

            ' is IDataReader! This allows us to return an OleDbDataReader
from here, and a SqlDataReader

            ' from SqlDataAccessLayer

    End Function

    Function ExecuteNonQuery(ByVal query As String, ByVal ParamArray
queryParams As Object()) As Object _

        Implements IDAL.ExecuteNonQuery

            ' Run code against access to run a non-query and return the
result

    End Function

End Class

That's all there is to it. Obviously there's no real code in the two data
access layers here, and obviously the methods are over simplified.

I still think you should take a look at MSDE ;)

So, some reading for you

1. Take a look at MSDN (msdn.microsoft.com) for the Microsoft Data Access
Application Block  - this will give you an idea of the kind of interface
that you need to be implementing in your data access layers (if you choose
not to use MSDE and go down this route).
2. Take a look at MSDN again for tutorials on Interfaces, Inheritance and
just general VB.NET OO principles.

Hope that helps,

Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.

"Stefan Hellberg" <stefanhellb...@hotmail.com> wrote in message

news:02cb01c33105$b6446900$a101280a@phx.gbl...

> Hi again Pete,

> Im going to build a content management system for ASP .NET
> I should have told you that before(sorry), thats why im
> still interested in MS Access. That way it can be used
> for smaller sites with low budget.

> I have been reading about Factory Patterns that you
> informed me about. Great to have some pointers what to
> look into :)

> Object orientation is new for me so Im not sure that I
> understand all things correct but I think this is how I
> can do it.

> I will in my scenario always use the class "DataAccess"
> to get data access objects, like the DataReader, DataSet
> and command objects.

> Can you(or anyone else) tell me if its a good approach or
> if im way out off track if I build using what I think is
> a Factory class below?

> (I have to buy your book :)

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
 
 
 

MS SQL Server and MS Access

Post by Stefan Hellber » Sun, 15 Jun 2003 07:22:11


Hi Pete,

Almost felt like christmas morning when I read your
post :) Your example code and good explanation was
exactly what I needed to start get a grip on things.

I very much appreciate the time you spent on giving me
this information.

Thanks
Stefan Hellberg

>-----Original Message-----
>Hi Stefan,

>You are a little off base.

>First up, I have to labour the point (sorry), MSDE is

completely free, you
>can download it from Microsoft and it will let you do
your entire
>application using only the SQL Server data provider.

Microsoft also provide
>information on how you can install MSDE with your

application onto your
>end-user's machines. There really is no reason to use
Access any more for
>this kind of thing ;)

>That said, your problem is a nice early morning problem
to solve, so here
>goes ;)

>Simply stated, your problem is that you want to have two
data access layers
>in your application. One would work with an optimized
OleDB data provider
>(for Access), the other would work with an optimized SQL
Server data
>provider. You want the code, at runtime, to determine

which to use, and you
>want to seperate the functionality of these data access
layers out from the
>rest of the application to the extent that the rest of
the app has no idea
>which concrete data layer it's really using (because it
doesn't really need
>to know).

>In the previous email, I pointed you in the general

direction of two design
>patterns - "Abstract Factory" and "Factory Method".

Without going into too
>much detail, let me explain what each does. The "Factory
Method" pattern
>simply defines an interface for creating an object.

There is a lot more to
>it than that, but that's really all you need to know at
this point.

>Abstract Factory on the other hand "provides an

interface for creating
>families of related or dependent objects". What this
means is that there
>would be a common interface shared by a number of

classes (in your case, an
>AccessDataLayer, and a SqlDataLayer that both implement

the same "DataLayer"
>interface). You would be passed an instance of either
AccessDataLayer or
>SqlDataLayer by the FactoryMethod, and would treat that
object instance as
>just a simple "DataLayer". Since both objects implement
the DataLayer
>interface you can work with them the same way. Since the
objects themselves
>are created by a FactoryMethod, your calling code won't
ever really care
>which data layer it's using. All it cares about is that
the data layer
>provided implements that "DataLayer" interface. The

interface in question
>would provide methods for doing all the usual things,
like getting a
>DataSet, running a query, etc etc.

>At a higher level, here's what the code that calls the

factory method looks
>like.
>Sub Main()

>    Dim dataAccessLayer As IDAL

>    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_SQLServer)

>    ' dataAccessLayer now points to a SQL Server data
access layer

>    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_Access)

>    ' dataAccessLayer now points to an Access data layer

>End Sub

>Notice tha the 'dataAccessLayer' object is declared

as 'IDAL'. IDAL is an

- Show quoted text -

>interface declared elsewhere. Notice also that the code
calls a 'shared
>method' on a class called 'DAL' to get at an actual data
access layer
>object. By passing a parameter to DAL.GetDAL, the GetDAL
factory method is
>able to return the right type of lower level data access
layer object to
>you.

>The code for that DAL class looks like this.
>Public Enum DALTYPE

>    DAL_Access = 1

>    DAL_SQLServer = 2

>End Enum

>Public Class DAL

>        Shared Function GetDAL(ByVal dbType As DALTYPE)
As IDAL

>            If dbType = DALTYPE.DAL_Access Then

>            Return New AccessDataAccessLayer

>        Else

>            Return New SQLDataAccessLayer

>        End If

>    End Function

>End Class

>Simple enough - we declare an Enum to specify the data

access layer type and
>use that enum in the call to GetDaL to determine exactly
which object to
>return. Notice that GetDal returns an 'IDAL', but the
code creates an
>instance of either AccessDataAccessLayer or

SqlDataAccessLayer. These are
>two classes that implement the 'IDAL' interface. So the
method here creates
>the appropriate data access layer object, but the

calling code treats that
>object as a nice common IDAL implementation.

>IDAL looks like this
>Public Interface IDAL

>   Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object

>End Interface

>What we are saying here is that IDAL is an interface
that contains three
>functions (yours will probably have a lot more). Any

object that implements

- Show quoted text -

>the IDAL interface will need to put some meat onto these
three functions.
>Also notice the GetReader() function. ADO.NET uses
interfaces and
>inheritance the same way we are. The OleDBDatareader and
the SqlDataReader
>objects both implement the IDAtaReader interface, so for
those times when
>you need to work with a reader in your code, we can have
the Data Access
>layer return an IDataReader type (which applies to both
SqlDataReader and
>OleDbDataReader).

>Time now to look at code for the two 'concrete' data
access layers.
>Public Class SQLDataAccessLayer

>    Implements IDAL

>    Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet _

>        Implements IDAL.GetDataSet

>            ' Run code against SQL Server to build a

dataset and return it

>    End Function

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader _

>        Implements IDAL.GetReader

>            ' Run code against SQL Server to build

reader and return it

- Show quoted text -

>(notice that the return type

>            ' is IDataReader! This allows us to return a
SqlDataReader from
>here, and an OleDbDataReader

>            ' from AccessDataAccessLayer

>    End Function

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object _

>        Implements IDAL.ExecuteNonQuery

>            ' Run code against SQL Server to run a non-

query and return the
>result

>    End Function

>End Class

>Public Class AccessDataAccessLayer

>        Implements IDAL

>    Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet _

>        Implements IDAL.GetDataSet

>            ' Run code against Access to build a dataset
and return it

>    End Function

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader _

>        Implements IDAL.GetReader

>            ' Run code against Access to build reader

and return it (notice

- Show quoted text -

>that the return type

>            ' is IDataReader! This allows us to return
an OleDbDataReader
>from here, and a SqlDataReader

>            ' from SqlDataAccessLayer

>    End Function

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object _

>        Implements IDAL.ExecuteNonQuery

>            ' Run code against access to run a non-query
and return the
>result

>    End Function

>End Class

>That's all there is to it. Obviously there's no real

code in the two data
>access layers here, and obviously the methods are over
simplified.

>I still think you should take a look at MSDE ;)

>So, some reading for you

>1. Take a look at MSDN (msdn.microsoft.com) for the

Microsoft Data Access
>Application Block  - this will give you an idea of the
kind of interface
>that you need to be implementing in your data access

layers (if you choose
>not to use MSDE and go down this route).
>2. Take a look at MSDN again for tutorials on

Interfaces, Inheritance and

- Show quoted text -

>just general VB.NET OO principles.

>Hope that helps,

>Peter Wright
>Author of ADO.NET Novice To Pro, from Apress Inc.

>"Stefan Hellberg" <stefanhellb...@hotmail.com> wrote in
message
>news:02cb01c33105$b6446900$a101280a@phx.gbl...
>> Hi again Pete,

>> Im going to build a content management system for
ASP .NET
>> I should have told you that before(sorry), thats why im
>> still interested in MS Access. That way it can be used
>> for smaller sites with low budget.

>> I have been reading about Factory Patterns that you
>> informed me about. Great to have some pointers what to
>> look into :)

>> Object orientation is new for me so Im not sure that I
>> understand all things correct but I think this is how I
>> can do it.

>> I will in my scenario always use the class "DataAccess"
>> to get data access objects, like the DataReader,
DataSet
>> and command objects.

>> Can you(or anyone else) tell me if its a good approach
or
>> if im way out off track if I build using what I think
is
>> a Factory class below?

>> (I have to buy your book :)

>----== Posted via Newsfeed.Com - Unlimited-Uncensored-

Secure Usenet News==----
>http://www.newsfeed.com The #1 Newsgroup Service in the

World! >100,000 Newsgroups
>---= 19 East/West-Coast Specialized Servers - Total

Privacy via Encryption =---

- Show quoted text -

>.

 
 
 

MS SQL Server and MS Access

Post by Stefan Hellber » Thu, 19 Jun 2003 08:01:14


Hi again Pete,

After some reading and thinking, I have decided to take
your advice and support MS SQL-Server and MSDE. But I
still want to have the option in the future to support
other databases, if the need should arise.

I now think about if I should use Microsoft Data
Application Block or go for the Factory method aproach
and implement my own data access functionality.

What is the benefits of using the Factory method compared
to the first aproach you named?

Its also one thing about the factory method that I didnt
understand completely.

Where should I call the code to create the Data Access
Object?

In my business layer classes, should I call this code and
create a DAL-object inside every function or sub routine
that uses data access?

And if so, should I use an IF-statement lite this?

Dim dataAccessLayer As IDAL

If ConfigurationSettings.AppSettings("DB_Type") = "SQL"
Then
   dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_SQLServer)
ElseIf ConfigurationSettings.AppSettings("DB_Type")
= "Access" Then
   dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_Access)
End If
---
I hope I dont bug you too much :)

Regards
Stefan Hellberg

>-----Original Message-----
>Hi Stefan,

>You are a little off base.

>First up, I have to labour the point (sorry), MSDE is

completely free, you
>can download it from Microsoft and it will let you do
your entire
>application using only the SQL Server data provider.

Microsoft also provide
>information on how you can install MSDE with your

application onto your
>end-user's machines. There really is no reason to use
Access any more for
>this kind of thing ;)

>That said, your problem is a nice early morning problem
to solve, so here
>goes ;)

>Simply stated, your problem is that you want to have two
data access layers
>in your application. One would work with an optimized
OleDB data provider
>(for Access), the other would work with an optimized SQL
Server data
>provider. You want the code, at runtime, to determine

which to use, and you
>want to seperate the functionality of these data access
layers out from the
>rest of the application to the extent that the rest of
the app has no idea
>which concrete data layer it's really using (because it
doesn't really need
>to know).

>In the previous email, I pointed you in the general

direction of two design
>patterns - "Abstract Factory" and "Factory Method".

Without going into too
>much detail, let me explain what each does. The "Factory
Method" pattern
>simply defines an interface for creating an object.

There is a lot more to
>it than that, but that's really all you need to know at
this point.

>Abstract Factory on the other hand "provides an

interface for creating
>families of related or dependent objects". What this
means is that there
>would be a common interface shared by a number of

classes (in your case, an
>AccessDataLayer, and a SqlDataLayer that both implement

the same "DataLayer"
>interface). You would be passed an instance of either
AccessDataLayer or
>SqlDataLayer by the FactoryMethod, and would treat that
object instance as
>just a simple "DataLayer". Since both objects implement
the DataLayer
>interface you can work with them the same way. Since the
objects themselves
>are created by a FactoryMethod, your calling code won't
ever really care
>which data layer it's using. All it cares about is that
the data layer
>provided implements that "DataLayer" interface. The

interface in question
>would provide methods for doing all the usual things,
like getting a
>DataSet, running a query, etc etc.

>At a higher level, here's what the code that calls the

factory method looks
>like.
>Sub Main()

>    Dim dataAccessLayer As IDAL

>    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_SQLServer)

>    ' dataAccessLayer now points to a SQL Server data
access layer

>    dataAccessLayer = DAL.GetDAL(DALTYPE.DAL_Access)

>    ' dataAccessLayer now points to an Access data layer

>End Sub

>Notice tha the 'dataAccessLayer' object is declared

as 'IDAL'. IDAL is an

- Show quoted text -

>interface declared elsewhere. Notice also that the code
calls a 'shared
>method' on a class called 'DAL' to get at an actual data
access layer
>object. By passing a parameter to DAL.GetDAL, the GetDAL
factory method is
>able to return the right type of lower level data access
layer object to
>you.

>The code for that DAL class looks like this.
>Public Enum DALTYPE

>    DAL_Access = 1

>    DAL_SQLServer = 2

>End Enum

>Public Class DAL

>        Shared Function GetDAL(ByVal dbType As DALTYPE)
As IDAL

>            If dbType = DALTYPE.DAL_Access Then

>            Return New AccessDataAccessLayer

>        Else

>            Return New SQLDataAccessLayer

>        End If

>    End Function

>End Class

>Simple enough - we declare an Enum to specify the data

access layer type and
>use that enum in the call to GetDaL to determine exactly
which object to
>return. Notice that GetDal returns an 'IDAL', but the
code creates an
>instance of either AccessDataAccessLayer or

SqlDataAccessLayer. These are
>two classes that implement the 'IDAL' interface. So the
method here creates
>the appropriate data access layer object, but the

calling code treats that
>object as a nice common IDAL implementation.

>IDAL looks like this
>Public Interface IDAL

>   Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object

>End Interface

>What we are saying here is that IDAL is an interface
that contains three
>functions (yours will probably have a lot more). Any

object that implements

- Show quoted text -

>the IDAL interface will need to put some meat onto these
three functions.
>Also notice the GetReader() function. ADO.NET uses
interfaces and
>inheritance the same way we are. The OleDBDatareader and
the SqlDataReader
>objects both implement the IDAtaReader interface, so for
those times when
>you need to work with a reader in your code, we can have
the Data Access
>layer return an IDataReader type (which applies to both
SqlDataReader and
>OleDbDataReader).

>Time now to look at code for the two 'concrete' data
access layers.
>Public Class SQLDataAccessLayer

>    Implements IDAL

>    Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet _

>        Implements IDAL.GetDataSet

>            ' Run code against SQL Server to build a

dataset and return it

>    End Function

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader _

>        Implements IDAL.GetReader

>            ' Run code against SQL Server to build

reader and return it

- Show quoted text -

>(notice that the return type

>            ' is IDataReader! This allows us to return a
SqlDataReader from
>here, and an OleDbDataReader

>            ' from AccessDataAccessLayer

>    End Function

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object _

>        Implements IDAL.ExecuteNonQuery

>            ' Run code against SQL Server to run a non-

query and return the
>result

>    End Function

>End Class

>Public Class AccessDataAccessLayer

>        Implements IDAL

>    Function GetDataSet(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As DataSet _

>        Implements IDAL.GetDataSet

>            ' Run code against Access to build a dataset
and return it

>    End Function

>    Function GetReader(ByVal query As String, ByVal

ParamArray queryParams
>As Object()) As IDataReader _

>        Implements IDAL.GetReader

>            ' Run code against Access to build reader

and return it (notice

- Show quoted text -

>that the return type

>            ' is IDataReader! This allows us to return
an OleDbDataReader
>from here, and a SqlDataReader

>            ' from SqlDataAccessLayer

>    End Function

>    Function ExecuteNonQuery(ByVal query As String,
ByVal ParamArray
>queryParams As Object()) As Object _

>        Implements IDAL.ExecuteNonQuery

>            ' Run code against access to run a non-query
and return the
>result

>    End Function

>End Class

>That's all there is to it. Obviously there's no real

code in the two data
>access layers here, and obviously the methods are over
simplified.

>I still think you should take a look at MSDE ;)

>So, some reading for you

>1. Take a look at MSDN (msdn.microsoft.com) for the

Microsoft Data Access
>Application Block  - this will give you an idea of the
kind of interface
>that you need to be implementing in your data access

layers (if you choose
>not to use MSDE and go down this route).
>2. Take a look at MSDN again for tutorials on

Interfaces, Inheritance and

- Show quoted text -

>just general VB.NET OO principles.

>Hope that helps,

>Peter Wright
>Author of ADO.NET Novice To Pro, from Apress Inc.

>"Stefan Hellberg" <stefanhellb...@hotmail.com> wrote in
message
>news:02cb01c33105$b6446900$a101280a@phx.gbl...
>> Hi again Pete,

>> Im going to build a content management system for
ASP .NET
>> I should have told you that before(sorry), thats why im
>> still interested in MS Access. That way it can be used
>> for smaller sites with low budget.

>> I have been reading about Factory Patterns that you
>> informed me about. Great to have some pointers what to
>> look into :)

>> Object orientation is new for me so Im not sure that I
>> understand all things correct but I think this is how I
>> can do it.

>> I will in my scenario always use the class "DataAccess"
>> to get data access objects, like the DataReader,
DataSet
>> and command objects.

>> Can you(or anyone else) tell me if its a good approach
or
>> if im way out off track if I build using what I think
is
>> a Factory class below?

>> (I have

...

read more »