The Old Joel on Software Forum: Part 3 (of 5)

Data Access Functions

I have two central functions which I use to access data from a database. (FYI - I am speaking in the context of ADO/DAO and ADO.NET on an IBM PC.) They are - OpenRS and ExecuteSQL. These functions also trap and display errors that occur while accessing the database.

OpenRS takes an SQL string as an argument and returns a read only - forward only recordset, directly from the connection. This function returns success or failure.

ExecuteSQL also takes an SQL string as an argument and returns (via parameter), depending on the SQL statement, the number of records affected, a singular value representing the ID of a new record or the value of a single aggregate function (i.e. SELECT Count(*) FROM tblSoAndSo WHERE ....). All statements executed in this function are executed inside a transaction. This function returns success or failure.

Anything more complicated and I have to use a recordset.

At any rate, I'd be interested in hearing what centralized "data access" functions you guys/gals use and maybe a short description of how they work or are structured.

If you structure your data access differently, how do you it?

Does anyone have a function that updates recordsets? (I did but found it was too unwieldy.)

Maybe some of you just use the recordset outright (i.e. in each individual function) each time you need data access, is there a reason for this?

Dave B.
Thursday, November 6, 2003

I likewise use GetRS and ExecuteSQL, although my GetRS returns a connected recordset, and I use it a lot more than ExecuteSQL. Basically, for changes to individual records I just use the recordset because I found it easier to call .Update than to write a pair of update/insert statements for every table. For mass changes, I use a SQL statement.

Of course, I mostly do single-user desktop apps, so haven't had to deal with the concurrency management stuff that might prompt a more SQL-oriented approach.

Sam Livingston-Gray
Thursday, November 6, 2003

I have a data access component with methods like this...

ReadRS(DBident, query, ...) [non-transactional]
- returns disconnected, static, read-only client-cursor recordset

GetRS(DBident, query, ...) [transactional]
- returns disconnected, static, Batch-Optimistic client-cursor recordset

GetNewRS(DBident, table-name, ...) [transactional]
- returns empty, disconnected, static, Batch-Optimistic client-cursor recordset

UpdateRS(DBident, recordset) [transactional]
[passing in recordset previously obtained from either GetRS or GetNewRS method and modified/added-to]
- connects to database, sets recordset ActiveConnection, and calls UpdateBatch

The return values from the methods are recordsets (not true/false or success/failure) and status is indicated by raising errors for anything other than "success".

I also have an ExecuteSQL method, but rarely use it - I have found it better in many ways to use ADO recordsets rather than directly executing SQL queries, and this also makes it easier to support multiple database vendors with the same code.

Philip Dickerson
Thursday, November 6, 2003

I wrap the results in an object. For instance, for a person, I might call getPersonList( args ). That would return a PersonList object, which is (of course) a list of Person objects. Person objects are mostly just accessors, and PersonList objects include such handy functions as sortBy(), sortByTwoValues( value1, value2 ), sortByThreeValues( value1, value2, value3 ), getCount, getCountByValue, getCountByTwoValues, etc.

Everything I know about VBA I learned from the first few chapters of a "VBA for Excel 2000" book, and frequent searches on google.

My setup here works fine because everything I do with this data is reporting, so I don't have to worry about updating the database after changes. Which is also why I have all the sort* and getCount* functions, handy for summarizing results that Excel's built-in sheet summarizing functions can't handle well.

Andrew Hurst
Thursday, November 6, 2003

Like some of you guys, I have QueryRS for disconnected and ExecuteSQL. ADO stays at my data-layer only. I pass data up to my applications as variant arrays, or more recently XML!

I never use ADO for updating recordsets. When saving data I always translate to INSERT/UPDATE statements.

For complex multi-table stuff in MSSQL, I consider using stored-procs using prepared ADO Commands.

Which is nice.

Tim H
Friday, November 7, 2003

Thanks for the comments guys. I now have some idea as to how I can improve my "Data Access Functions".

Dave B.
Friday, November 7, 2003