The best overview over ADO.NET provides the
documentation MSDN Library, .NET Development, Building Distributed
Applications, .Net Data Access Architecture Guide.
??? Should the data types from the
System.Data.SqlTypes namespace be used or native .NET datatype ?
??? Handling of varchars (limited string length)
??? Use of ASCII versus UNICODE
Write a method in ADO.NET for each stored procedure
providing access to the database. Use the stored procedure抯 name also for the
method. List the method抯 parameters in the same sequence like the parameters
of the stored procedure.
Catching of errors reported by stored procedure,
handling some of them, rethrowing the other ones.
It抯 important to differentiate between database
connection problems (database is not accessible) and errors encountered when a
stored procedure runs (run time error). The first one is a temporary problem,
the second one is a task specific problem and the cause might need to be investigated.
What should be done if there is no database
connection ? Proposal: Send one alert to administrator and start an additional
thread polling to see when database is up again. While this task is running,
every other thread returns immediately a run time error. This avoids many
threads waiting for seconds. Problem: How to differentiate between just a slow
database and no connection at all ?
There should be a test stored procedure for each
database access stored procedure. Write a test method following the structure
of the test stored procedure.
The test stored procedure cannot test properly
raised errors in the database access stored procedure. For each raise error
statement in the database access stored procedure, a test needs to be written.
Database connections are expensive resources,
because it might take seconds until a connection is established with a database
access rights settled. ADO.NET pools connections per default, but can only do
so, if the connection is closed as soon when query is done. DataAdapter open
and closes connection automatically, if the connection is closed before a fill
or update.
Properties of Windows Client:
•
Often only one thread, few multitasking problems
•
Response times of 100 milliseconds acceptable
•
Plenty of processor cycles and memory available
•
Tasks executed every few seconds only once
Goals:
•
Minimize number of database accesses
•
Move processor intensive tasks from database server to client. It
might make sense to move big chunks of data to the client and do filtering,
sorting and paging there.
•
Keep data during several user actions in datasets =>
programming with state
Properties of ASP.NET:
•
Multiple threads running in parallel. It抯 very likely that one
thread starts a database query, waits for the answer while another thread
starts the same query. It must be guaranteed that each threat owns its own
connection, reader, etc.
•
Only few milliseconds of processor cycles can be used to create
an HTML response, even from the browser point of view it might look like
several 100 milliseconds because of waiting for database results
•
Memory is scarce
Goals:
•
Minimize number of database accesses
•
Minimize sorting, better store data sorted
•
Do paging and filtering on database server
•
Reuse objects instead creating new objects for each task to avoid
excessive memory collection. However, be careful about multithreading.
•
Don抰 keep data between to user requests => state less
programming, use DataReader
If possible use DataReader, because it is faster and
provides all the functionality needed to display data (typical tables).
DataSets are only needed if the user can change several records before the
database is accessed again.
Mostly used in Windows programming, not ASP.NET
???
DA牋牋牋 DataAdapter
DS牋牋牋 Dataset
•
Are the method抯 parameters in the same sequence like the
parameters of the stored procedure ?
•
Is each stored procedure tested ? Are all error conditions
detected by the stored procedure (RAISE ERROR) tested ?
•
Often, stored procedures need to be called in a certain sequence
like Create, Update, Delete. Is out of sequence calling tested like Delete,
Update, Insert ?
•
If several tables are changed by a stored procedure, are transaction
handled and tested ?
Chapters Overview