Contents tagged with Sqlite

  • CodeStore - Part 2 of n (SQLite, NHibernate)

     So, in the last post, I started creating a new application using TreeSurgeon and added Castle Windsor and log4net support. In this post, I'm going to describe how I added the Nhibernate support and the Castle Nhibernate facility.


    User Stories:

    Because CodeStore is about storing metrics about an Assembly therefore I'm going to start by storing the assembly information. Therefore the first user story is:


    1) As a customer, I want to query the Assembly data in the database so that I can get metrics about the assembly.


      After breaking the story into tasks, we have:

    a) Read Assembly Data

    b) Store Assembly Data in SQLite using Nhibernate.


    Although we can start with any task, but in this scenario I'll go with the second task which is about setting up Nhibernate to store data in the SQLite database.



    There are few things we need to download and setup before start coding.

  • Download SQLite ADO.NET Database Provider ( The good thing about this provider is that it embeds the sqlite database engine so you don't need any more dependencies.
  • Download SQLIte Database Browser to manage the database ( but you can also use the native sqlite engine to manage the database from console.
  • Download NHibernate.

    Once we have the required dependencies, we can start by creating a new datastore. SQLite Database Browser allows you to create a new database but you'll have to create a temporary table so that it can save it in the file.



    Then I added a new class AssemblyData in our Core.Domain namespace.



    Once all the fields are defined, I generated all the properties using Resharper (Alt+Ins)



    It seems that Resharper doesn't generate the auto properties so you'll have to fix it (Ctrl + Enter).



    Or you can just type the auto properties without any help which may be the quickest option :).


    The next step is to create the nhibernate mappings file. When editing the mapping file, it is much better to add the nhibernate mappings schema to get intellisense in the IDE.





    The complete mapping file looks like this. The build action should be set to embedded resource.


    <?xml version="1.0" encoding="utf-8" ?>

    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"

                       assembly="CodeStore.Core" namespace="CodeStore.Core.Domain">

      <class name="AssemblyData">

        <id name="Id">

          <generator class="guid"/>


        <property name="Name"/>

        <property name="FullName"/>

        <property name="Version"/>

        <property name="EntryPoint"/>




    As we have the domain class and the corresponding mapping file, we can configure the application to use Nhibernate. For that, I created a separate hibernate.cfg.xml file in the Core application with the following contents. The properties are self-explanatory and you can always learn more by looking into the Nhibernate documentation.


    <?xml version="1.0" encoding="utf-8" ?>

    <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">


        <property name="connection.driver_class">NHibernate.Driver.SQLite20Driver</property>

        <property name="connection.connection_string">Data Source=E:\Code\CodeStore\data\codestore.db;Version=3;New=False;Compress=True;</property>

        <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>

        <property name="query.substitutions">true=1;false=0</property>

        <property name="show_sql">true</property>




    To check whether the configuration is correct, I quickly wrote a test to generate the schema for the above domain model.



    public class HibernateTest



    public void CanExportSchema()


                var cfg = new Configuration();



                new SchemaExport(cfg).Execute(false, true, false, false);




    The above code will generate the AssemblyData table in the database with the defined fields.



    So this is one approach to design  (domain driven design) where we started with our domain model and then generate the database in the end. The other side of it is data driven design where you can start with the database and generate your domain from these entities. By using the database, you can also generate the plumbing code for domain model (i.e. Mapping files and Domain classes) using some generation tool like MyGeneration or CodeSmith.


    Now, using the Repository pattern, I'm going to create a repository to save assembly data into the database.



    Although not TDD, but writing a test now.



    If the code looks familiar to you then you must have been to the excellent tutorial on this website.


    Currently we are using plain vanilla Nhibernate through the NHibernateHelper which is not a bad thing but there is Castle NHibernate facility that we can utilise and abstract the Nhibernate plumbing. To do so, there are two different types of integration styles (defined here) I'm using the second approach which looks more straight forward.


    The first thing is the change in configuration file.  The hibernate configuration is currently defined in a separate hibernate.cfg.xml file which I'm going to move into the app.config file inside the existing castle/facilities section.



    The second thing is to change the repository class to inject ISessionManager (link) in the constructor.



    And then you can use the data repository as:



    Source Code: 



    In this blog post, we started with creating a database and the domain model for storing AssemblyData. In the next post, we are going to use mocks to test the repository.


    As always, comments and suggestions are most welcome to improve the project.