Query Executor

Date

May 2016

Category

Console Application

Address

Git Repo Link

Technology Used

C#

About this project

The Query Executor is a console application I developed to make it easier to use ADO.NET database connections. The programs underlying methods that execute queries are all templated to make it easier to use various different types of databases. This was done to make it easier to migrate to a different database provider if need be or use two or more databases of different types at the same time.

I developed this program because I needed an easy way to store/retrieve records from a database in a C# application and did not want to use the Entity Framework or any other ORM (Object-relational mapping) tools. This was because I felt that most ORM tools including the Entity Framework were too bloated for the small console application I was building at that time and the server I was using for testing was limited in resources. I was not really sure whether I wanted a MSSQL database or a MySQL database in the application but I wanted to be able to switch very quickly without too much modification of my source code. I also wanted maximum code reuse so that I would not have to create a new ADO.NET command for each query performed, instead this application would act as the engine to take care of that problem.

To use the application, first we need to have a relational database and then we need to have an object in our solution that maps exactly to the fields of the database. This is similar to the Entity Framework with the object and its properties reflecting the schema of the database table.

Once we have this relationship, we declare a new instance of the chosen database and pass it an instance of a connection string with all parameters needed. Please note that port is optional as we have an overloaded constructor for the connection string object.

DBMSSQL Db=new DBMSSQL(new DbConfig("databasename", "username", "password", "host"));

Once we have this connection object, we can then call the Query(), NonQuery() and Scaler() methods as needed.

The Query() methods takes a string as a parameter and this string would be a SELECT statement. This method returns a list of whatever object is passed into the templated ResultClass. Continuing with the previous example, the syntax to get a list of students would be:

List<Student> Students=new List<Student>(Db.Query<Student>("SELECT * FROM Student"));

In the background, once Query() is executed, XML is received with the results from the database, through serialization, we then obtain our list of records.

The NonQuery() method works a similar way except, no result is returned. A query string must be passed as a parameter. The NonQuery() method is to be used for INSERT, UPDATE and DELETE queries.

Db.NonQuery("INSERT INTO Student (ID, LastName, FirstName, StreetAddress) Values (1,'Smith','James','123 Sessame Street');");

The last Db method is the Scaler() method, as with the previous methods, a query string must be passed as a parameter. This method returns an object and if a numberic value is received, a simple "Convert.ToInt32()" would get the integar value.

object StudentCount=Db.Scalar("Select COUNT(*) FROM Student;");

The solution has been pushed to my GitHub account and available from here....