Check if records exist or not with ADO.NET

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 code for the first case can be downloaded from here.
The code for the second case above can be downloaded from here.

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).

Kind regards,
Dennis

Advertisement

Writing to Memo fields with ADO.NET

At present I’m working with the new version of my commercial add-in to Excel – SQL Tester. I took the decision to completely re-write it in VB.NET, i e it will be a managed COM Add-ins for Excel 2000 and later. Hopefully it will be available by the end of June.

Anyway, in some services it offer it requires to handling strings variables that exceed 255 characters. In general large text data is not an issue but it becomes an issue when we want to write (save) them to tables in Microsoft Databases (aka Access Databases).

To handle large text data we need to add fields of the type ‘Memo’ (some developers call it “Blob”) to the targeting tables. Compared with the Text field type, which can only store <=255 characters, the Memo field type can store up to about 60,000 characters.

Next we need to consider it when creating the code that writes the large text data to the database.  By using a name for the parameter in the SQL statement and by adding a Parameter Object it’s solved in an easily way.

The following example shows how it can be solved and here I use a class that contains the relevant function (one of many functions in the class).

The function requires 4 strings variables where the last one – ‘Code’ – contains the large textdata.

The SQL statement includes 4 fields where the ‘Code_Text’ represents the Memo field and the statement ends with the name ‘@Code_Text’.  In the final step the Parameter object is created and the command is executed.

 

Most controls that offer databinding can handle large text data well. In the present solution I use a control that is first populated with the large text data and it’s easy to get the large text data from that control. However, if it turns out that I need to retrieve the large text data directly from the table I will publish the solution here.

However, if someone already has a solution for it then please posts it here.

If You want a copy of the code it can be downloaded here.

Kind regards,
Dennis