SimpleQuery – quick and easy database access via ADO.NET

• Download source file - 1.90 Kb

Introduction

This is my first CodeProject post so be gentle with me :).

SimpleQuery is a class designed to run simple T-SQL statements against a .NET Connection. To this end, SimpleQuery contains three static methods to execute T-SQL statements and either return a count of records affected (ExecuteNonQuery), a dataset of the result set (ExecuteSelectQuery), or the scalar value from the first cell of the first row of the result set (ExecuteScalar).

The three public function headers look like this:

public static int ExecuteNonQuery(string SQL, System.Data.IDbConnection connection)
public static SD.DataSet ExecuteSelectQuery(string SQL, 
                         System.Data.IDbConnection connection)
public static object ExecuteScalar(string SQL, System.Data.IDbConnection connection)

Each of these methods takes a string containing the T-SQL code to run and an IDBConnection object to run the SQL against.

By default, no modifications are made by SimpleQuery to the connection objects that it receives and it is up to the client of the class to ensure that the connection is opened before calling. A property does exist named AutoConnect which when set to true (defaults to false) will check to see if the connection is open or not. If it is not, the connection will be opened before running the SQL code. If SimpleQuery does open the database connection then it will always close it when it finishes. If you wish to control the state of the connection outside of SimpleQuery then leave AutoConnect set to false.

Internally there are several overloaded private static functions of similar names, one for each actual type of connection (OdbcConnection, OleDbConnection and SQLClientConnection are the three currently supported), and the relevant one is called depending on the type of the connection object passed to the public method.

Background

The reason for developing this class was a suite of tools I had to develop recently for a client. The tools were originally written in VB6 and used DAO and ADO 2.7. Additionally, certain tools were destined for certain preconfigured machines which meant some tools had to use either ODBC or OLEDB connections depending on the machine they were to be deployed on. So I wanted a way to quickly and easily run SQL code against any database.

Using the code

The code is very simple to use, all the members are static so one only need include the SimpleQuery.cs file and reference the desired method directly.

// Assumes myConnection has already been created
SimpleQuery.AutoConnect = true;

DataSet dsTest = SimpleQuery.ExecuteSelectQuery("SELECT * FROM AUTHORS", 
                                                          myConnection);
int count = SimpleQuery.ExecuteScalar("SELECT COUNT(*) FROM AUTHORS", 
                                                       myConnection);
int deletedCount = SimpleQuery.ExecuteNonQuery("DELETE * FROM SomeTable", 
                                                           myConnection);

Any exceptions are trapped and wrapped in a SimpleQueryException object, which contains two additional properties, QueryCode and ConnectionString which contain the SQL string and the connection string from the connection object; original exceptions are available through innerException as normal.

Points of Interest

As the name suggests, this class has been designed to offer very quick and simple access to databases, and although finer control may be required in many applications, I’ve found this class very useful for little apps, DB conversion tools and prototyping.

The code is tested using NUnit, but the test classes are tied up with some other helper classes I need to sort out before I can post them here.

There are also XML comments which I hope to get converted to a proper help file of some sort as soon as I get NDoc installed ;)

History

• V1.0 - Cleaned up for CodeProject.
• V1.1 - Modified the internal handling for ExecuteScalar and ExecuteNonQuery based on Richard Deeming's comments below. Added the AutoConnect property.