Generic Database Accessor Library

Project description

The Generic Database Accessor Project (GDAlib) is a layer on top of ADO.NET, which aids in the development of applications that interact with a database engine. Currently, the only supported engine is Microsoft SQL Server 2005 and above, but in the future more will be supported. The main advantage of using GDAlib is that it simplifies the developer's work and speeds up writing the application's database block. This is because GDAlib hides a lot of common, repeating tasks that need to be solved for this objective. Such tasks include: establishing and managing database connections, command caching, asynchronous operations, exception handling and logging. Depending on the size and scope of the application, GDAlib can be used directly or incorporated into the database block.

Documentation

The DLL ships with the afferent XML documentation file, so Intellisense will show you hints on how to use GDALib's methods. Documentation will come out soon. Until then, check out the following examples:

Connecting to a database

The DatabaseAccessor object features many ways to connect to a database. The simplest, is to just specify the SQL server and the logging function. In this case authentication is done using the Windows user, the default database is set to Master and Console.Writeline() is used for logging.

DatabaseAccessor dbAccessor = new DatabaseAccessor("foobarserver", new LogExceptionFunction(Console.WriteLine));

Executing SQL queries

//single table reader
DataTable salesTable = dbAccessor.ExecuteReader("SELECT * FROM Sales;");

//multi table reader
DataTableCollection tables = dbAccessor.ExecuteMultiReader(@"SELECT * FROM Sales;
                                                               SELECT * FROM Customers;");

//non-query: INSERT, UPDATE, DELETE
int affectedRows = dbAccessor.ExecuteNonQuery("INSERT INTO Sales(SaleId, Value) VALUES(415634,5000);");

//asynchronous non-query
dbAccessor.ExecuteNonQueryAsync("INSERT INTO Sales(SaleId, SaleValue) VALUES(415634,5000);");

//read a single value from a table -> scalar
int saleValue = dbAccessor.ExecuteScalar<int>("SELECT SaleValue FROM Sales WHERE SaleId = 4135634").GetValueOrDefault(-1);

Cached SQL commands

The caching mechanism in GDAlib is designed to keep in memory those commands that you use most often in order to greatly improve execution speed.

//insert a new row into the Sales table.
//NB: in this case, the output parameter result will be an integer representing the number of affected rows
dbAccessor.CacheCommand("NewSale", "INSERT INTO Sales(SaleId, SaleValue) VALUES(@SaleId, @SaleValue);", SqlCommandType.NonQuery);
dbAccessor.ExecuteCachedCommand("NewSale", out result, 415634,5000);

//get all rows from the Sales table
//NB: in this case, the output parameter result will be a DataTable representing the content of the Sales table
dbAccessor.CacheCommand("GetAllSales", "SELECT * FROM Sales;", SqlCommandType.Reader);
dbAccessor.ExecuteCachedCommand("GetAllSales", out result);

Author

GDAlib is currently developed solely by Alex Gentea