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.

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

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

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

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

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.

No comments:

Post a Comment