Mysql Data Access Layer

• Download source - 302.59 KB

Introduction

This is an implementation on how to access your database in an easy way and create some complex queries without previous knowledge about SQL queries and the join statement. There are many features supported in this library and we will figure it out together after a while.

Background  

There are too many ways to have interaction with your database especially in latest days as you can see in all ORM (Object Relation Mapping) applications and techniques. There is Linq (Language Integrated Query) which is provided by Microsoft and it works only with SQL Server. NHibernate gives you the flexibility to change your database at any time without changing your code. There are many others, but that was just an introduction about the benefits of this library.

So again this not the best way to use it in your project. There are too many ways that are more organized to deal with your database and give you more capabilities, but I submitted this article to share ideas and the code and how it's written.   

Pros and Cons

Pros

• Easy and fast way to deal with your database (Crud Operations)
• Transaction supported
• Caching supported
• Easy to modify
• Easy to debug and figure where exactly the problems are

Cons

• Not strongly typed
• Some issues in the main design

Prerequisites

• Mysql server (WAMP) - you can download it from here
• Visual Studio 2008 (any version, you can use the Express Edition)
• Mysql .NET Connector - you can download it here

Important Notes

• Download the attached folder, you will find two folders for two projects and a script file for the database. In order to see the sample, run your mysql and execute the script under database with name Sample and make a new user with username sa and the password sa (you can change it later, but this information is already defined in the sample project).
• Now open the sample project and check the application setting section in the web config and change the connection string as you want:
  XML

  </appsettings>
  	<add value="server=localhost;user id=sa;password=sa;
  		database=sample" key="DatabaseConnectionString"></add>
  	<add value="true" key="CacheEnabled"></add>
  </appsettings>

• You will find some comments beside each section to describe exactly what the function does and inside the library itself, you will find a full documentation for each function.

Using the Code

So as not to duplicate your code, first we will make a function inside the page and the return type is ColumnDetails which will provide us with the information we want about the table we are dealing with, like which column we want and we want to make any non select statement from which controls, we will take the value and here is a sample:

private ColumnsDetails CustomerClmns()
{
    ColumnsDetails clmns = new ColumnsDetails();

    //the columns inside your table 
    clmns.ColumnName.AddRange(new string[] { "CustomerId", "Name", "Description" });
    //the display name will appear when the datatable will be filled ( the alias in SQL) 
    clmns.ColumnDisplayName.AddRange(new string[] 
		{ "ID", "Customer Name", "Customer Description" });

    //use this part to make a join with other table 
    clmns.JoinDetails.Add(new JoinDetail(JoinType.LeftOutertJoinStatement, 
		"contacts", "Tel", "Telephone", "CustomerId", "CustomerId"));

    //tell the DLL I don't want to use this column in the insert statement 
    // ( it's autoincrement )
    clmns.EliminatedColumns.Add("CustomerId");

    //tell the DLL from where it will get the column values to use in 
    //all non select statements 
    clmns.ColumnValue.AddRange(new object[] { null, txtName.Text, txtDescription.Text });

    //tell the DLL when it will make any SQL statement 
    //how it will add the condition column and its value 
    clmns.ConditionColumns.Add(new ConditionColumn
	("CustomerId", ComparisonOperator.Equal, this.CustomerID, 
	TiTaN.DataAcessLayer.StatementType.Update, AndOrCondition.None));
    clmns.ConditionColumns.Add(new ConditionColumn
	("CustomerId", ComparisonOperator.Equal, this.CustomerID, 
	TiTaN.DataAcessLayer.StatementType.Delete, AndOrCondition.None));

    return clmns;
}	

Once you created this function inside your page you are almost done. The next steps will be to view lines to make any action on the table (Crud operation) and here is a sample.

DbManagement.SelectRecords("Customers", CustomerClmns(), 
			"CustomersCacheKey", ref app, ref cache);

Let's describe this line before we go to the next one.The DLL is a static class (one of the issues in the design), it's waiting for some information about the table it's going to deal with like the table name we already provided it as the first parameter and the column details returned from the function we just created, the caching key and a reference to the application state class and cache class (another issue in the design).

The caching key is required only if you turned on caching of the web config and made the value of the enable caching to true. All you need to do is add this key with a null value in the global.asax file as the next sample will show to let the DLL use it to cache the incoming data the first time only the server will run and every time if the data didn't change. That means the DLL will get the data from the cached object, not from the database.

Application.Lock();
 Application.Add("CustomersCacheKey", null);
 Application.UnLock();

Let's go to a non select statement like insert and see what's required to write it.

DbManagement.InsertNewRecord("customers", 
	CustomerClmns(), ref app, "CustomersCacheKey");

Almost the same like select statement but maybe someone will ask why we passed the cache key in this case? With any non select statement, you will pass the cache key to tell the DLL that I already changed the data to the next. Anyone who asks for the data will get it from the database and not from the caching and then redo the same work you did in the beginning with caching the incoming new data.

 try
{
DbManagement.BeginTransaction();
DbManagement.InsertNewRecord
	("customers", CustomerClmns(), ref app, "CustomersCacheKey");
//Be sure that the engine for mysql table is innodb 
throw new Exception();
DbManagement.CommitTransaction();
}
catch (Exception ex )
{
DbManagement.RollbackTransaction();
}

In this code, we used the transaction feature in the DLL but first be sure that the engine you are using with the table is InnoDB because this is the only engine that is supporting transaction and we get this information from mysql web site.  

The other features are related to debugging, for example put a break point after the insert statement and you will find in the DLL 2 properties for the last executed SQL statement and the parameters used in it with its value.

Points of Interest

As I mentioned in the beginning of this article, I published this article only for sharing the ideas and getting feedback to learn from other experienced developers. Actually I am now working on another library, but this time using the new interfaces that come with .NET 3.0 and 3.5 like IList IQuerable.

History

• 20^th April, 2009: Initial post