Triple Whammy Newbie Question - abstracting connections/commands

  • Thread starter Thread starter J L
  • Start date Start date
J

J L

I am new to .Net, ADO.Net and OOP programming. Have plenty of
experience with VB6 and DAO but now need to move on. Here is my simple
question:

I want to create a procedure or class that will handle the creation of
connections and commands at runtime so that the rest of my code will
not have to know if I am using OleDB, SQLDb or something else.

I hope that is stated clearily. If not, please prod me along the right
path...I need to be enlightened :>)

John
 
Let me clarify...in VB6 since there was only one flavor of DAO, I
could use a general purpose routine that created the database
connection via Opendatabase() to either Access or SQL based on a user
defined configuration. Now with ADO.Net there are at least two
versions of every thing in the set of Provider Objects (Connection,
Command, DataAdapter and DataReader). How can I create a generic
method to use either the SQLdb. or the OleDb. objects so the rest of
my application can be oblivious to the underlying data source? Or does
this question even make sense?

TIA
John
 
As far as I know, there is no real way to generalize everything except
to create a separate data access layer that contains all the code that
accesses your database (one for each flavor of database you want to
access). Then I've seen some people have that pass back generic
datasets to another layer or you can have the data access layer
populate an object by itself.

Here is a helpful article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

Keanen
 
You may want to look at the Data Access Application Block from Microsoft.
The newest version is part of their Enterprise blocks. It try's to
generalize the access to the database for you and best of all it's already
written.


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

--
Thanks
Wayne Sepega
Jacksonville, Fl


"When a man sits with a pretty girl for an hour, it seems like a minute. But
let him sit on a hot stove for a minute and it's longer than any hour.
That's relativity." - Albert Einstein
 
Thanks Wayne for pointing me to the DAAB...it may be what I need but I
will have to study it futher. Have you used it? If so, please share
your experience with it and recommendations.

TIA,
John
 
Thank you Keanen for your response. The MS site says to program
against the IDBConnection, etc. I have no idea how to do that.

What I was hoping was to be able to be able to create a generic object
(e.g. Connection) and then have a routine that would cast it as the
OleDB or SQLDb version based on an application's configuration
parameters. But it seems that System.Data only implements the DataSet
objects and not the provider type...too bad.

Is the following code legal (i.e. putting different Dim statements
within an If-Then-Else clause? (I hope this is not a really stupid
question...but hey, if I dont ask I will never know)

If USE_SQL then
dim conn as New SQLDb.Connection( <some connection string stuff>)
dim cmd as New SQLCommand
dim da as New SQLDataAdapter()
Else
dim conn as New OleDB.Connection(<some connection string stuff>)
dim cmd as New OleDBCommand
dim da as New OleDBDataAdapter()
End If

< work with the above ojects here in a general way>

TIA,
John
 
No I have not used as of yet. I will however, be looking at it soon for my
use in the future.
 
Then let's keep in touch and share our knowledge with the NG. I will
be looking into it also. On the MS site, they did have a Power Point
presentation that discusses the general idea behind these App Blocks.
Sounds good as long as it is not too complicated. I dont want to bloat
my projects.

Thanks,
John
..
 
You can only write generic code that goes against multiple data
providers up to a point -- and that point is Command objects. The
problem is that each provider has its own syntax for handling
parameters, so it ends up being impossible to provide an elegant
solution without writing branching code that is provider-specific. The
reason your old VB6 code worked with DAO/SQL Server is because when
you used DAO with SQL Server, Jet was loaded under the covers and
provided the translation into ODBC-T-SQL for you. So you were going
through multiple layers--DAO-Jet-ODBC-SQL Server, which might have
been fine for a small database with few users, but was inherently
limited and not at all scalable.

--Mary
 
Hi Mary,
Thank you for clarifying the issue for me.

I had hoped there was a "minimum level" of compatibility between the
providers above the Connection level, that I could program against and
over-ride if need be to use more specific features. For example, I
dont use stored procedures or parameters. So I would think a gneric
Command object and hence a generic DataAdapter, DataReader would be
possible.

But we have to work with what we are given. And I do thank you for
clarifying.

John
 
Yes, that is one way around it -- don't use parameterized statements
or stored procedures and create your SQL statements on the fly. But
that means that you are potentially leaving yourself wide open to SQL
injection attacks and are unable to leverage the security features
that stored procedures and parameterized commands provide. Not a good
tradeoff, I'm afraid.

--Mary
 
Hi Mary,
Since my last post, I have been reading a lot about DataFactories and
using interfaces to create a generic data access layer. This sounds
like the solution. I also thought I read that ADO.Net 2.0 has a
DataFactory? (Since I am so new to .Net and ADO.net, I do not even
know what version is the current one). It seems so obvious to put that
layer between the physical and the UI or BLL that I do not understand
why MS has not done this as part of the ADO.Net product. Although
Silvano Coriani wrote a good article on MSDN about how to do it.

As for security, my apps are very specific to data collection in the
food industry. I am not exposed to people making ad hoc requests of my
data and I do not use bound data controls. I handle and validate all
input before accessing the database. So an SQL injection would never
happen since the damaging strings would not pass my validation
requirements.

Really all I need is the good ole DAO recordset! LOL...old habits die
hard I guess.

Thanks,
John
 
Unfortunately, ADO 2.0, although much improved, still won't get you
where you want to go. My opinion is, and it isn't politically correct
and is not what is considered best practice, is that dynamic strings
are the way to go for your particular application since you are
performing all of the necessary validation up front. You'll just have
to make sure that your servers are locked down tight and strictly
monitored, because this path requires that permissions be granted on
the base tables. So anyone getting in can have a field day on the data
(not necessarily breaking in via your app, but by other means).

--Mary

Hi Mary,
Since my last post, I have been reading a lot about DataFactories and
using interfaces to create a generic data access layer. This sounds
like the solution. I also thought I read that ADO.Net 2.0 has a
DataFactory? (Since I am so new to .Net and ADO.net, I do not even
know what version is the current one). It seems so obvious to put that
layer between the physical and the UI or BLL that I do not understand
why MS has not done this as part of the ADO.Net product. Although
Silvano Coriani wrote a good article on MSDN about how to do it.

As for security, my apps are very specific to data collection in the
food industry. I am not exposed to people making ad hoc requests of my
data and I do not use bound data controls. I handle and validate all
input before accessing the database. So an SQL injection would never
happen since the damaging strings would not pass my validation
requirements.

Really all I need is the good ole DAO recordset! LOL...old habits die
hard I guess.

Thanks,
John

Yes, that is one way around it -- don't use parameterized statements
or stored procedures and create your SQL statements on the fly. But
that means that you are potentially leaving yourself wide open to SQL
injection attacks and are unable to leverage the security features
that stored procedures and parameterized commands provide. Not a good
tradeoff, I'm afraid.

--Mary

Hi Mary,
Thank you for clarifying the issue for me.

I had hoped there was a "minimum level" of compatibility between the
providers above the Connection level, that I could program against and
over-ride if need be to use more specific features. For example, I
dont use stored procedures or parameters. So I would think a gneric
Command object and hence a generic DataAdapter, DataReader would be
possible.

But we have to work with what we are given. And I do thank you for
clarifying.

John

On Tue, 08 Feb 2005 14:43:16 -0500, "Mary Chipman [MSFT]"

You can only write generic code that goes against multiple data
providers up to a point -- and that point is Command objects. The
problem is that each provider has its own syntax for handling
parameters, so it ends up being impossible to provide an elegant
solution without writing branching code that is provider-specific. The
reason your old VB6 code worked with DAO/SQL Server is because when
you used DAO with SQL Server, Jet was loaded under the covers and
provided the translation into ODBC-T-SQL for you. So you were going
through multiple layers--DAO-Jet-ODBC-SQL Server, which might have
been fine for a small database with few users, but was inherently
limited and not at all scalable.

--Mary


Thank you Keanen for your response. The MS site says to program
against the IDBConnection, etc. I have no idea how to do that.

What I was hoping was to be able to be able to create a generic object
(e.g. Connection) and then have a routine that would cast it as the
OleDB or SQLDb version based on an application's configuration
parameters. But it seems that System.Data only implements the DataSet
objects and not the provider type...too bad.

Is the following code legal (i.e. putting different Dim statements
within an If-Then-Else clause? (I hope this is not a really stupid
question...but hey, if I dont ask I will never know)

If USE_SQL then
dim conn as New SQLDb.Connection( <some connection string stuff>)
dim cmd as New SQLCommand
dim da as New SQLDataAdapter()
Else
dim conn as New OleDB.Connection(<some connection string stuff>)
dim cmd as New OleDBCommand
dim da as New OleDBDataAdapter()
End If

< work with the above ojects here in a general way>

TIA,
John

On 7 Feb 2005 08:15:17 -0800, "Keanen"

As far as I know, there is no real way to generalize everything except
to create a separate data access layer that contains all the code that
accesses your database (one for each flavor of database you want to
access). Then I've seen some people have that pass back generic
datasets to another layer or you can have the data access layer
populate an object by itself.

Here is a helpful article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

Keanen

J L wrote:
Let me clarify...in VB6 since there was only one flavor of DAO, I
could use a general purpose routine that created the database
connection via Opendatabase() to either Access or SQL based on a user
defined configuration. Now with ADO.Net there are at least two
versions of every thing in the set of Provider Objects (Connection,
Command, DataAdapter and DataReader). How can I create a generic
method to use either the SQLdb. or the OleDb. objects so the rest of
my application can be oblivious to the underlying data source? Or
does
this question even make sense?

TIA
John


I am new to .Net, ADO.Net and OOP programming. Have plenty of
experience with VB6 and DAO but now need to move on. Here is my
simple
question:

I want to create a procedure or class that will handle the creation
of
connections and commands at runtime so that the rest of my code will
not have to know if I am using OleDB, SQLDb or something else.

I hope that is stated clearily. If not, please prod me along the
right
path...I need to be enlightened :>)

John
Click to expand...
Click to expand...
Click to expand...
 
Back
Top