G
Guest
Can someone help me upgrade a concept / thinking which I currently use when I
develop Access databases – to how you would do it in ASP.NET / ADO.NET - SQL
Server ?- if the concept or method is not correct, please suggest how it
can be modified.
Particularly for parameterized searches I like to write a multipurpose
querydef from code that can be used for all sorts of things. (Erland
Sommarskog) has a great stored procedure for performing this. But you can’t
do what I’m about to describe (at least my limited scope of knowledge thus
far – doesn’t see how)
Please read all the way through before drawing conclusions – remember you
may have advanced thinking about these concepts. –Mine so far is limited to
MS Access (10 years)
For example, I create a query called qActorSelect and what that is, is a
query that looks like this: ‘Select * from Actors Where LastName =
‘Eastwood’. (I’m over simplifying, the table is actually more complex with
many columns and criteria) if a different parameter or parameters were
selected, I could modify this in code and qActorSelect stays ‘dynamically
stagnant’ inside other queries.
It’s a lightweight select that if it were used in place of the actual Actor
Table, it isn’t so heavy so I can base other queries on it like unions, joins
with other tables, etc. All I have to worry about is changing the
querydef.SQL for this multi-purpose qActorSelect query and any query built
around this will be updated.
For example, creating a union query based on qActorSelect as opposed to
trying that with a the actual Actor table containing millions works out
rather nice, particularly because I know qActorSelect will always be a small
subset.
My stumbling block with ADO.NET is I can grab that nice – seemingly
multi-purpose select in a DataAdapter and use it with a view?… And maybe I
just don’t know all of the magic yet, but I haven’t seen being able to
perform dynamic SQL on dataset tables like joining it with other dataset
tables or in particular getting a UNION out of one of those dataset tables.
I saw something in the newsgroup about a product that can perform SQL
operations on a dataset, but please don’t point me in that direction. I know
there are many of you who don’t use that – AND ADO.NET is supposed to be the
‘ideal highly evolved’ new way to do things, and I’d like to take advantage
of this power and new thinking.
So how do I rethink this - and shift? I know MS Access isn’t better, but I
challenge you to match this capability:
A split database in MS Access can, in the front-end, refer to a local query
written from code that interacts with tables in the back-end as if they were
in the same database, all without having to worry about attaching some sort
of an ID to that query for that a specific user (because the front –end is
unique to that user – it sits on the users local drive) .
I like being able to build things this way, but I know there is a better way
and I’m ready to grow up from my old way.
Please don’t point me to some article. I’m utilizing the newsgroup to
hopefully find out a very specific answer to a very specific example which I
have provided here.
I can't type this sort of thing in google.
develop Access databases – to how you would do it in ASP.NET / ADO.NET - SQL
Server ?- if the concept or method is not correct, please suggest how it
can be modified.
Particularly for parameterized searches I like to write a multipurpose
querydef from code that can be used for all sorts of things. (Erland
Sommarskog) has a great stored procedure for performing this. But you can’t
do what I’m about to describe (at least my limited scope of knowledge thus
far – doesn’t see how)
Please read all the way through before drawing conclusions – remember you
may have advanced thinking about these concepts. –Mine so far is limited to
MS Access (10 years)
For example, I create a query called qActorSelect and what that is, is a
query that looks like this: ‘Select * from Actors Where LastName =
‘Eastwood’. (I’m over simplifying, the table is actually more complex with
many columns and criteria) if a different parameter or parameters were
selected, I could modify this in code and qActorSelect stays ‘dynamically
stagnant’ inside other queries.
It’s a lightweight select that if it were used in place of the actual Actor
Table, it isn’t so heavy so I can base other queries on it like unions, joins
with other tables, etc. All I have to worry about is changing the
querydef.SQL for this multi-purpose qActorSelect query and any query built
around this will be updated.
For example, creating a union query based on qActorSelect as opposed to
trying that with a the actual Actor table containing millions works out
rather nice, particularly because I know qActorSelect will always be a small
subset.
My stumbling block with ADO.NET is I can grab that nice – seemingly
multi-purpose select in a DataAdapter and use it with a view?… And maybe I
just don’t know all of the magic yet, but I haven’t seen being able to
perform dynamic SQL on dataset tables like joining it with other dataset
tables or in particular getting a UNION out of one of those dataset tables.
I saw something in the newsgroup about a product that can perform SQL
operations on a dataset, but please don’t point me in that direction. I know
there are many of you who don’t use that – AND ADO.NET is supposed to be the
‘ideal highly evolved’ new way to do things, and I’d like to take advantage
of this power and new thinking.
So how do I rethink this - and shift? I know MS Access isn’t better, but I
challenge you to match this capability:
A split database in MS Access can, in the front-end, refer to a local query
written from code that interacts with tables in the back-end as if they were
in the same database, all without having to worry about attaching some sort
of an ID to that query for that a specific user (because the front –end is
unique to that user – it sits on the users local drive) .
I like being able to build things this way, but I know there is a better way
and I’m ready to grow up from my old way.
Please don’t point me to some article. I’m utilizing the newsgroup to
hopefully find out a very specific answer to a very specific example which I
have provided here.
I can't type this sort of thing in google.