As a result of that I’m working extensively with ADO.NET I have found another useful (and simple) approach by using the ExecuteScala method to find out if a record exists or not.
From what I understand the ExecuteScala is faster than the ExecuteReader method as it among other things create less overhead then the later.
Depending on how we construct the SQL query we can either get a resultset that contains:
- The number of existing records that meet the criteria(s) or
- The first column of the first returned row (record) when ExecuteScala executes the query.
In this context, i e checking if individual records exist or not, the first approach is used as the following example shows. In the below example we should only allow one record to exist and therefore we can check if the returned value of the aggregated calculation of COUNT is one (1 = does exist) or not (0 = does not exist):
In the following case we check to see if the record exist or not by evaluating the resultset’s first column in the first retrieved record (row). In the following example the first column in the table _Code is of the type Autonumber which means that it can either be greater than 0 ( = does exist) or if not ( = does not exist):
The more I work with ADO.NET the more I realize its power and compared with classic ADO it’s in many ways far better. The only thing I regret is that Excel and VBA does not support to work with it (for obvious reasons).
Please let me know if You think that the above can be solved in a better way and if there exists alternative approaches (except for the ExecuteReader).