Click here to Skip to main content
Click here to Skip to main content

SimpleQuery – quick and easy database access via ADO.NET

, 18 Aug 2005
Rate this:
Please Sign up or sign in to vote.
A class to allow easy running of T-SQL code against .NET data sources.

Introduction

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

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 Wink | ;)

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

ChrisLee1
Web Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralA much better way PinmemberRichard Deeming9-Aug-05 7:56 
GeneralRe: A much better way PinmemberChrisLee19-Aug-05 8:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 19 Aug 2005
Article Copyright 2005 by ChrisLee1
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid