Northwind Database with NoSQL DBreeze

• Download NWSTS.zip - 6.5 MB

Introduction

This is a small article (also my first article) on code project, regarding using NoSQL with ASP.NET. NoSQL is around from long back, while many are using NoSQL successfully. I found very less codes and complete example of using NoSQL with ASP.NET. Every NoSQL database provides illustrations and examples but to just at starter level. Hence I am putting my entire tested project as an article for whom NoSQL is creating interest and have ASP.NET background.

One can download entire source code for study, before beginning one must have experience of ASP.NET, C#, Lambda equations, N-Tier, Generics, JQuery, JavaScript, KnockoutJs and CSS. And off course, the one must be working with SQL Server and understand basic Northwind Database which is around us from last 15 years. To run the complete code the only requirement is Visual Studio 2010 or above.

Background

A little bit about NoSQL. If you are very new to NoSQL, then NoSQL will be little different concept from traditional RDBMS relational databases. So to have start just think about a database which does not have relationships (means no RDBMS) and hence no constraint over joins. Just entities and set of inner (or dependent) entities, directly saved to a manageable file either on disk or in memory. If database is on disk that can be on same working project directly, hosting environment or also on cloud. Simple, for details I would request to have wild search on net regarding NoSQL. :-)

For this article I am using DBreeze NoSQL embedded database. If you downloaded the source code, you can find DBreeze binaries already included. If you want some more details you can directly search DBreeze on net and go to their home page for further details.

I guess, enough background, now let we start detailing on the same with the help of source code.

Using the Code

Simply download the source code and unzip the code on your system at your favorite location. Open the project on Visual Studio 2010 or above.

Before running we need to do little settings, hence first open web.config of website named "Site". Search following lines at the start of config file.

<configSections> 
    <section name="logger" type="Logger.Configuration.LoggerConfiguration, Logger">
    <section name="dbreezedbconfig" type="DataLayer.DataBaseConfig.DBreezeConfiguration, DataLayer">
  <configSections>

  <logger name="mylogger">
    <logconfig folder="sitelog" file="log.txt" filesize="5" rollday="true"><logconfig>
  <logger>

  <dbreezedbconfig name="databaseconfig">
    <dbreezeconfig databasefolder="App_Data\northwind_db" usebackup="true" backupfolder="I:\NoSql\NorthWind" backupinterval="60"><dbreezeconfig>
  <dbreezedbconfig>

Let I explain in short, the first section is of logger, this is custom logger I had created, its details are under logger tag below configSections.

1. The property "folder" will be the log folder directly created at root of website.
2. The property "file" is the name of log file.
3. The property "filesize" is the size of log file, here it is 5 in GB. Once this is reached another file is created automatically.
4. The property "rollday" is the one which changes file on day basis.

The entire Logger code is included in source code under "Logger" Class Library. Now the second config section which is important for us to use for DBreeze NoSQL database. The details are as under:

1. The property "databasefolder" specifies where our database will be stored. Basically we keep it in App_Data of our website application, hence the same pattern is used here.
2. The property "usebackup" specifies the auto back of DBreeze database is to be taken our not. Here we are using backup.
3. The property "backupfolder" specifies where back needs to be kept. Please note this should be physical location either on your system or shared location and specified rights to IIS must be given. If we are using back up as specified in step 2, then this property is required or else there would be off course error. Also if specified folder is not found there is error.
4. The property "backupinterval" specifies when next back up should be taken. The number is in minutes, here it is 60 means every hour back up needs to be done. (Please note DBreeze automatically backs up the database every interval we set up.) That’s it if above all config settings are set correctly now is the time to compile the solution, setting the start project to our website application and start page to index.html, hit F5 in visual studio and bingo.

One would wonder that, when you run the application for the first time, what happens?. Let I put some light on the working.

Open Global.asax file:

1. On Application Start event there are two lines which do the initial set up.
2. The first line invokes the DBreeze database engine, and off course disposes the engine when application ends.
3. The second line creates the northwind database with DBreeze, which is created in App_Data folder. Simple go to the Business.BaseDataBase class library the entire script is coded there. This script runs once only if the most required base table is not found, means we are running for the first time.

Once the database is created the entire application hooks up and the interaction starts with our database. Please note as we are using the embedded database all the files of the database are now locked. Hence we create a backup of the same every 1 hour so that if we need we can copy the live database and start investigating if required.

Points of Interest

The entire solution contains the working project. Before detailing the NoSQL, let I clear the basic structure of the project for easiness to understand. Open the solution, it will look like following.

1. Business.Entities class library holds all the entities. This will be used further as tables for NoSQL. NoSQL does not have tables as we have in SQL Server.
2. DataLayer class library contains the repository class for our DBreeze database and other configuration elements need to make Dbreeze run. The repository is used to query on the database to fetch relevant data. As we have done database configuration in web.config, the base class for the configuration is in DataLayer class library.
3. Business.Components class library contents all the business logic we need to implement.
4. NWSTS class library is having simple Factory class so that we encapsulate all the data which is not required at other end.
5. Service class library contains the webservices, used by off course our web application.
6. Logger class library contains code for logging errors or any other information.
7. Business.BaseDataBase class library contains the NoSQL script which is used once if northwind database is not created.

   In all class library we have to take DBreeze DLL reference.

Now, let see whats DBreeze offers. Following are the some of the points needs to be understand:

1. DBreeze is NoSQL embedded database, which uses a class as a table. Whatever property we define in class are treated as columns of the table.
2. DBreeze easily maintain database engine for transaction to commit and rollback, hence operation are ACID like and completely managed.
3. DBreeze needs a unique key for every row which needs to be kept in database for a table. Hence traditionally in SQL Server we have primary key, the same is concept with DBreeze NoSQL. The only difference is we need System.Collections.Generics.Dicitionary rather than System.Collections.Generices.List. Hence primary key can be kept in class as well as in key of System.Collections.Generics.Dicitionary.
4. DBreeze NoSQL engine needs to be start once and then dispose in entire life cycle of application. Hence we can either starts it from global.asax file or we can write our custom windows service for the same and then expose the engine. (I haven’t tried windows service, but as we can write custom windows service in .NET, this can be easily achieved.)
5. DBreeze offers transaction, hence traditional commit and rollback makes this much familiar for SQL Server people. There is simple thumb rule, if in transaction the data is not committed the data is simply kept in memory.
6. DBreeze transaction offers Synchronize Tables, hence we can explicitly synchronize the table. This makes other thread interacting with same table to wait until the first is not complete.
7. DBreeze transaction also offers entire table Exclusive and Shared locks.
8. The most appealing characteristics (which I like most) of DBreeze is there is zero installation and plug and start the database, further every things are completely manageable and managed code with .NET. We simply need to make reference to DBreeze DLL, that’s it.

Wait, there are some down side also, let we take them also in consideration.

1. As stated in point 3 above, we need a key for each row, while key can be any valid datatype of .Net except GUID. This makes GUID to override with ToString() if we need to use GUID.
2. We have to take precaution for DBreeze engine as it can be initialize and dispose once. Hence either we have to open engine again and again and dispose simultaneously with using keyword, or we have to globally open it or close. (The latter is used in our source code project)

Please note I found above points, may be there would be new release or some of the points I may missed. But they are all as per my experience with DBreeze.

Let see basic usage of DBreeze NoSQL to insert and retrieve the data:

    using DBreeze;
    public class myClass
    {
	      public string id {get;set;}
	      public string name{get;set;}
    }
    myClass _myClass = new myClass(){id="1",name="name"};
    using (var engine =  new DBreezeEngine(@"D:\temp\DBR1"))
    {
          using (var tran = engine.GetTransaction()) 
          { 
            try 
            { 
                  tran.Insert<int, DbMJSON<myClass>>("t1", 1, _myClass);
                  tran.commit();
            } 
            catch (Exception ex) 
            { 
                  tran.rollback();
            }
    }
    using (var engine =  new DBreezeEngine(@"D:\temp\DBR1"))
    {
            using (var tran = engine.GetTransaction()) 
            { 
              try 
              { 
                myClass _myClass = Tran.SelectForward<int, DbMJSON<myClass>>("t1").Select(qr=>qr.Value.Get).Where(qr=>qr.name == "name").FirstOrDefault();
              }
              catch(Exception ex)
              {
	            //log error
              }
            }
}

As we can see above, it is quite simple to insert and then retrieve the data in and from table. But what about update? DBreeze does not have update! Don’t be surprise, just come out from the SQL Server, this is NoSQL. For update same insert is use, if primary key is already present in database the same is updated and if not then inserted. Hence minimizing the error.

Above code is implemented in our project in the form of repository so every time we don’t need to remember the structure of the table. We need little experience in generics (already stated in beginning), if so the DataLayer class library does not needs any further explanation. The generics are implemented to support above code only, off course in generics way. Additional to it is the transactional types for Shared and Exclusive sceneries. We can use as per our business logic demands.

The last point I need to mention is the order search functionality. If you run the application we can find the order search from home screen, click on it and try to search any orders. You can see that the searching is little slow! Why? Because I had implemented SQL Server in NoSQL, this makes difference, the structure for NoSQL should not be same as structure and architecture for SQL Server. I had made this project to understand between architecture difference between SQL Server and NoSQL. So let we have little point on this.

First, in SQL Server there are relationships. If one is aware of NorthWind database we know that in it

1. Orders are depended on OrderDetails, Customers, Employees and Shippers
2. Further OrderDetails are depended on Products.
3. Products on Suppliers and Categories.

Just verify this in NorthWind Database. Hence before creating Orders and OrderDetails we need Customers, Employees, Shippers, Suppliers and Products. Off Course dependencies are on master tables. Hence only references are enough to keep.

Now what will be NoSQL scenario? Same again we will be needing the masters, but it is not a constrained. Why? That is because each and every details will be added to Orders entity only. We can directly map employee, customer, product on which order is created, shippers and suppliers! Simple. We do not need to complete the master first then operate on depend, simply we can create master on the spot and attach to make, means order entity.

This is the reason why order search is slow in our application we have up holded the architecture of SQL Server, and this is done for our understanding. Further in OrderSearch (in Buisness.Components.Components, namespace) there being heavy use of Linq Expression to create dynamic lambda expressions. Have a look on that class, yes off course one should be aware of generics, Linq Expressions and Lambda. But the same will not be the case in NoSQL architecture. That will be very simple.

One additional library on client side is KnockoutJs. This is used to bind list of entities easily with HTML and CSS. If you are new to knockoutJs, just have small tutorial on the same on official website of Knockout. The tutorials are straight forward and simple to implement.

I guess enough for this article. Talked much. I would request your comments on the same and if missed or implemented in other way, your suggestion would be welcome. Further I apologies for the bad web design which is implemented in web application.

Thanks.