Best way to work with ADO.net and MS Access

[Eniac]

Hello,

I'm working on a project using a MS Access 2002 database. I have to
build an ASP.net site and a vb.net web service to act as the database
tier to access the database.

On my ASP.net page, I do basic database operations, nothing fancy :
login, product search based on keyword/type/category with some paging
on the results (and if possible sorting...).

So far, everything is working nicely except that I just know that
Access aint the perfect database for the backend of a website (using
SQL Server or Oracle is what im more used to)

To fetch my data, I'm using MS Access 2002 Queries, the closest thing
to stored procedures as i understand.

But now I'm asking myself three questions that I believe people more
experienced with MS Acess would be able to answer.

1. Using parametrized queries in MS Access works in the WHERE clause
but does not seems to work for the ORDER BY clause. Is that a
limitations, feature or just something im not doing right ?

2. Do queries called from ADO.net really speed up the call compared to
a dynamic SQL string I'd build in the web service ? That would solve my
sorting problem but I dont want to kill the performance.

3. On my datagrid, I'm using basic paging, that is, fetch all the data
then go to appropriate page. As you probably know, thats much slower
than custom paging which retrieves only the required record for the
current page. Is there *any* way I can implement custom paging in the
MS Access queries ? or even with dynamic SQL ?

I've been thinking about caching the whole recordset to speed up things
when using the pager except that the client insist on the fact that the
data must be accurate at all times, meaning I have to go the database
everytime (to make sure product X is still in stock - in case another
client orders it)

Any thoughts ?

Thank you.

 

[Paul Clement]

¤ Hello,
¤
¤ I'm working on a project using a MS Access 2002 database. I have to
¤ build an ASP.net site and a vb.net web service to act as the database
¤ tier to access the database.
¤
¤ On my ASP.net page, I do basic database operations, nothing fancy :
¤ login, product search based on keyword/type/category with some paging
¤ on the results (and if possible sorting...).
¤
¤ So far, everything is working nicely except that I just know that
¤ Access aint the perfect database for the backend of a website (using
¤ SQL Server or Oracle is what im more used to)
¤
¤ To fetch my data, I'm using MS Access 2002 Queries, the closest thing
¤ to stored procedures as i understand.
¤
¤ But now I'm asking myself three questions that I believe people more
¤ experienced with MS Acess would be able to answer.
¤
¤ 1. Using parametrized queries in MS Access works in the WHERE clause
¤ but does not seems to work for the ORDER BY clause. Is that a
¤ limitations, feature or just something im not doing right ?
¤

ORDER BY operates off a known column name. You can't use a parameters value as a substitute for a
column name. I believe the same scenario in SQL Server or Oracle would require a dynamic query.

¤ 2. Do queries called from ADO.net really speed up the call compared to
¤ a dynamic SQL string I'd build in the web service ? That would solve my
¤ sorting problem but I dont want to kill the performance.
¤

With an Access/Jet database I don't believe there is any difference in performance.

¤ 3. On my datagrid, I'm using basic paging, that is, fetch all the data
¤ then go to appropriate page. As you probably know, thats much slower
¤ than custom paging which retrieves only the required record for the
¤ current page. Is there *any* way I can implement custom paging in the
¤ MS Access queries ? or even with dynamic SQL ?
¤

It's not really database dependent. You should be able to accomplish custom paging with any
database.

¤ I've been thinking about caching the whole recordset to speed up things
¤ when using the pager except that the client insist on the fact that the
¤ data must be accurate at all times, meaning I have to go the database
¤ everytime (to make sure product X is still in stock - in case another
¤ client orders it)

I wouldn't recommend using caching for exactly the reason you mention.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William \(Bill\) Vaughn]

One word of advice: "Don't"
Access/JET was never designed to be used to host a shared database. Use SQL
Server Express.

--
William (Bill) Vaughn
President and Founder Beta V Corporation
Redmond, WA
(425) 556-9205
Microsoft MVP, Author, Mentor
Microsoft MVP

 

[Eniac]

ORDER BY operates off a known column name. You can't use a parameters value as a substitute for a
column name. I believe the same scenario in SQL Server or Oracle would
require a dynamic query.

Yes, I was referring more the way its done in PL/SQL, Access is
offering just a little more than a regular SQL statement.
I suspected that, but now that you've confirmed it i won't hesitate to
make dyn. queries.

Hum, really ? (I'm still fairly new to .net) As I understood, custom
paging involved telling the database which page you wanted, otherwise
you still need to first receive all the data from the database rather
than receiving only the 15 records of page 4 (out of 8,000 for
instance). I know it can be done in Oracle because I've done it in the
past, I wasn't sure Access supported doing that (but im pretty sure the
real SQL server does). I'll need to read more on the topic.

Anyhow, thanks a lot for your answers Paul, it pretty much confirms
what i had in mind.

Merci!

 

[Eniac]

Yes, I know, I precisely said the same thing but the client doesn't
want to convert.

His business is running with an MS Access app connecting to an MS
Access database.

Converting to any version of decent database is out of the way because
he says its not worth it, the traffic won't be big enough to stress the
database, I have my reserves about that but in the end, if i wanted the
contract, I had to stop trying to convince to not use access
unfortunately, that is an all too common discussion in small companies.
As you can guess, Access is not my specialty either.

But thanks to good programming practices and good design, I'm sure I'll
be able to alleviate the stress on that poor Access backend that will
be hit by several sources now.

 

[Paul Clement]

¤ >> ORDER BY operates off a known column name. You can't use a parameters value as a substitute for a
¤ column name. I believe the same scenario in SQL Server or Oracle would
¤ require a dynamic query.
¤
¤ Yes, I was referring more the way its done in PL/SQL, Access is
¤ offering just a little more than a regular SQL statement.
¤

I seem to recall that you can do this in PL/SQL but I don't do it my stored procedures. In my
ASP.NET apps I used the DataGrid features and there isn't really a performance issue when selecting
different columns by which to sort the information. Of course I'm not working with a significant
amount of data at this point and I am using standard paging.

¤ >> With an Access/Jet database I don't believe there is any difference in performance.
¤ I suspected that, but now that you've confirmed it i won't hesitate to
¤ make dyn. queries.
¤
¤ >> It's not really database dependent. You should be able to accomplish custom paging with any database.
¤
¤ Hum, really ? (I'm still fairly new to .net) As I understood, custom
¤ paging involved telling the database which page you wanted, otherwise
¤ you still need to first receive all the data from the database rather
¤ than receiving only the 15 records of page 4 (out of 8,000 for
¤ instance). I know it can be done in Oracle because I've done it in the
¤ past, I wasn't sure Access supported doing that (but im pretty sure the
¤ real SQL server does). I'll need to read more on the topic.
¤

Custom paging simply restricts the results actually returned from the database. You can do this with
SQL using a primary key (e.g. greater than the value of the last row of your page) and keywords such
as TOP (for Access or SQL Server) and RowNum (for Oracle).


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Eniac]

oh... what an insightful post paul, thank you.

Up until now I kept focusing on getting rownum x to y. But instead, its
true, I could just sort on the primary key and tell access i want PKs
between x and y. What a great alternative, I'm very pleased because im
so used to oracle mostly that all my SPs with paging were using solely
rownums, I never thought of using the PK for paging, it sounds kind of
silly now that the solution is in front of me. {:-D

I'll be sure to investigate the TOP keyword as well, I'll try a few
things out to combine paging and sorting then I'll post a sample query
(for people who will be searching the groups later on with same
issue(s))

You've been of great help, thanks.

 

[Eniac]

Ok, for the records, here's the result for everyone who might try to do
what i tried to do.

I won't post any code because I ditched the whole idea, here's why.

Basically, it worked! I managed to implement "custom paging" using a
sort on the PK and the top 20 keyword.

if you're interested in the statement, its looks kinda like this :

select records.*, counter.*
from (
select top 20 ID, Description, Type, Categ, Price from inventory
where ID > [Lower Limit] and ID < [Upper Limit]
order by id ASC) as [Records],
(select count(id) as Recordcount from inventory) as [Counter]
;

By using the first displayed ID and last displayed ID you are able to
control pagination back & forth. Works like a charm in fact.

But I ditched the whole idea because I realized that by doing that, I'm
losing any possibility of sorting on the whole table. So i wouldnt be
able to sort by description and then paginate the results, because
thats screws up the PKs and i can't use the ">" and "<" anymore.

I can always re-sort the recordset on the client side but then im only
sorting on the 20 records I have on hands, making the sort more or less
irrelevant.

What I'll be doing instead is caching, since I absolutely want to
reduce traffic on the access database and that we'll be using flat
files to place orders.

Basically, I'll cache the whole inventory table on the server as an xml
file, I'll make the cache dependant on that file. Everytime an order
will be placed, I'll delete the cache file, forcing a refresh of the
cache. That will keep it up to date. So when people are browsing the
results, it will still be very fast (much faster anyway).

That's probably the best solution I can do for full sorting capability
and reduced traffic on the database.