CsharpGears Framework : Database Access

You have worked with databases, right ? Has it happened to you, to figure it out that it would be great if you could somehow magically map your SQL tables in neat C# classes ? Well, it's not that I am discovering new continents here, but I personally think that the Database Access sublibrary will effectively and easily allow you to map your database tables into C# classes. Among the other benefits, these classes can also come in handy if you wanted to treat some SQL queries as transactions and roll them back in case of error. You can also invoke stored procedures or simply pass ad-hoc queries to the database with just a few lines of code.

But what truely makes this library awesome, is it's ability to directly return lists of business objects. You don't even have to explicitely bind the class' properties to the SQL tables (as with Attributes). The Framework only requires that the name of the public property is the same as the name of the SQL table column. Have I mentioned that the library is absolutely generic ? It can accept any types of objects, as long as they have default constructor (which could be observed as limitation, but it is so simple to write it, that is no big deal). Well, enough introductory notes, let me show you what library can really do.

DatabaseEntity
This is the basic class in this library. It provides the basic functionality such as communicating with the database by executing queries. There are several constructors available which will allow you to choose your connection string to the database, the SQL provider, the type of the query (Stored procedure or ad-hoc query) etc. The DatabaseEntity class provides the four main query types in SQL: Select, Insert, Update, Delete. Please note that the Select query returns object which is actually DataTable (boxed). You need to cast it to DataTable for the sake of reusability of the code. So here is how it works.
Say you have the following table named Product  with the following columns in the database:
ID | Description | Price

Then suppose that I wanted to retrieve all Products from the SQL table Product. I write the following procedure:

CREATE PROCEDURE ProductsGetAll_Select
AS
BEGIN
SELECT
      ID,
      Description,
      Price
FROM
      Product
END

Then back in C#, I need to create DataBaseEntityobject:

string ConnectionString = "...";
string DbProvider = "System.Data.SqlClient";
DataBaseEntity dbEntity = new DataBaseEntity("ProductsGetAll_Select",ConnectionString, DbProvider);

DataTable dtResults = (DataTable)dbEntity.Select();

foreach(DataRow dr in dtResults)
{
Console.Write(dr["ID"] + " " + dr["Description] " " + dr["Price"]);
}

So basically, that is all you need to do in order to retrieve your data from the database. For other types of SQL queries you just need to invoke the appropriate method (namely Update, Insert, Delete).

Using the DataBaseEntity class for ad-hoc SQL queries

DataBaseEntity dbEntity = new DataBaseEntity("SELECT * FROM Product", CommandType.Text, ConnectionString);
DataTable dt = (DataTable)dbEntity.Select();

GenericDbEntity

Well, I have mentioned that this library is able to directly return lists of business objects instead of returning DataTable. This is good simply because you won't bother to map those nasty data rows into your own objects. So here is how it goes:

public class Product
{
  private int id;
  private double price;
  private string description;
 
  public Product(){}

  public string Description{ get{return description;} set{description = value;}}
  public double Price{get{return price;} set{price = value;}}
  public int Id {get {return id;} set {id = value;}}
}

...

Binding SQL results to C# Objects  

SELECT Query to C# objects example 

GenericDbEntity dbEntity = new GenericDbEntity("ProductsGetAll_Select",ConnectionString, DbProvider);
List<Product> listOfProducts = (List<Product>)dbEntity.Select();

INSERT Query 

GenericDbEntity dbEntity = new GenericDbEntity("Product_Insert",ConnectionString, DbProvider);
int newlyInsertedId = Convert.ToInt32(dbEntity.Insert());

UPDATE Query

GenericDbEntity dbEntity = new GenericDbEntity("Product_Update",ConnectionString, DbProvider);
dbEntity.Update();

DELETE Query

GenericDbEntity dbEntity = new GenericDbEntity("Product_Delete",ConnectionString, DbProvider);
dbEntity.Delete();

Adding Parameters to Database Entities

You can easily pass SQL parameters as well. Consider the following example:

dbEntity = new DataBaseEntity("GetProductByID");
            dbEntity.IntAddCommandParameter("@ProductID", 150);
            DataTable dt = (DataTable)dbEntity.Select();

Note the method IntAddCommandParameter() adds new DbParameter to the wrapped SQL command.

The first argument is the name of the parameter in the stored procedure, and the second is the actual value 

we want to pass. There is also more generic AddCommandParameter() method which takes

the first two parameters just as the IntAddCommandParameter(), but it also takes a third parameter of DbType 

to specify the type the database should treat it like.  

That is literally, It. Nothing more is needed, indeed.

Please note that I am aware of various other approaches to this problem, such as the SQL Helper classes, Entity Framework etc, but simplicity this library bears makes it my first choice when it comes to communication with the database.