Sony Arouje Blog

a programmer's log

Posts Tagged ‘C#

db4o A light weight Object Oriented Database

with 8 comments

Now a days I am spending my free time in rewriting one of my freeware for Flickr users called iTraveller. One of the design goals was the application should be scalable. My major concern was, I cannot freeze my db design before my first release because I am planning to have multiple version with added functionalities, so the db structure may change in the future. If I use conventional db’s then I have to write functionality to add or remove columns. iTraveller is not very data centric application, so managing db change scripts and writing a functionality to apply these changes when user upgrade to new version will be a big pain for me.

First I thought of using MongoDB, because I used Mongo db in a Silverlight application. But then finally dropped that idea. I want some thing more lighter than Mongo and NoSql sort of database. I evaluated several db’s but nothing suites what I am looking for. Finally my search ends at db4o. The usage of db4o is very straight forward and very easy to learn, db4o installer comes with a very good documentation to start off with the development.

db4o is a light weight Object Oriented database. Using db4o I can persist my entities very easily without any configuration. I can add or remove new properties to my entities without affecting the existing persisted entities.

I created a generic repository sort of class around db4o, the same way I did one for Entity Framework. This generic repository reduced lot of my work and act as the entry point to persist my entities to db4o data file. Below is the Generic repository I created for db4o.

using Db4objects.Db4o;
public class GenericRepository:IRepository,IDisposable
{
    private static IRepository _singleRepoInstance;
    public const string DBNAME = "Data.dat";

    public static IRepository GetRepositoryInstance()
    {
        if (_singleRepoInstance == null)
            _singleRepoInstance = new GenericRepository();

        return _singleRepoInstance;
    }

    IObjectContainer _context = null;
    private IObjectContainer Context
    {
        get 
        {
            if(_context==null)
                _context = Db4oFactory.OpenFile(DBNAME);
            
            return _context;
        }
    }

    private IList<TEntity> Query<TEntity>() where TEntity : class
    {
        return this.Context.Query<TEntity>();
    }

    public IList<TEntity> GetAll<TEntity>() where TEntity : class
    {
        return this.Query<TEntity>().ToList();
    }

    public IList<TEntity> GetAll<TEntity>(TEntity entity) where TEntity : class
    {
        return this.Context.QueryByExample(entity).Cast<TEntity>().ToList();
    }

    public IList<TEntity> Find<TEntity>(Func<TEntity, bool> criteria) where TEntity : class
    {
        return this.Query<TEntity>().Where<TEntity>(criteria).ToList<TEntity>();
    }

    public TEntity Single<TEntity>(Func<TEntity, bool> criteria) where TEntity : class
    {
        return this.Query<TEntity>().Single<TEntity>(criteria);
    }

    public TEntity First<TEntity>(Func<TEntity, bool> criteria) where TEntity : class
    {
        return this.Query<TEntity>().FirstOrDefault<TEntity>(criteria);
    }
    public TEntity First<TEntity>(TEntity criteria) where TEntity : class
    {
        return this.Context.QueryByExample(criteria).Cast<TEntity>().First<TEntity>();
    }
    public void Add<TEntity>(TEntity entity) where TEntity : class
    {
        this.Context.Store(entity);
    }

    public void Add<TEntity>(List<TEntity> entities) where TEntity : class
    {
        foreach (TEntity entity in entities)
        {
            this.Add<TEntity>(entity);
        }
    }

    public void Delete<TEntity>(TEntity entity) where TEntity : class
    {
        this.Context.Delete(entity);
    }

    public void Update<TEntity>(TEntity entity) where TEntity : class
    {
        this.Context.Store(entity);
    }

    public void Dispose()
    {
        this._context.Close();
        this._context.Dispose();
        this._context = null;
    }
}

The above repository is created in singletone mode. I did that way because I noticed that opening the database file is taking some delay and wanted to avoid that delay. So I wont close the data file until iTraveller get closed.

Let’s see how we can call the above repository to save an entity

GenericRepository repository = GenericRepository.GetRepositoryInstance();
repository.Add<Photo>(photos);

 

Below is the way we can retrieve some entities from the data file

IRepository repository = GenericRepository.GetRepositoryInstance();
List<LocalCategory> categories = repository.GetAll<LocalCategory>().ToList<LocalCategory>();
 

 

You can also retrieve data based on any condition as well. The below code will return a list of Photo object whose categoryId is 10.

IRepository repository = GenericRepository.GetRepositoryInstance();
return repository.Find<Photo>(p => p.Category.ID == 10).ToList<Photo>();

 

As you can see the db4o is very easy to use light weight database without the hurdles of mapping to tables and all sort of stuffs.

Written by Sony Arouje

January 5, 2011 at 4:26 pm

Posted in .NET

Tagged with , , ,

POCO Support in Entity Framework 4

with one comment

In this post I am going to explain a small experiment I did with Entity Framework CTP4. My intention was to create some Entity classes and use it with the code first approach provided with EF4.

Till now I haven’t touched EF because I am not a big fan of auto generated code and config files generated by EF. When I saw some article about the new features of EF that comes with v4 release, especially the code first approach and POCO support. I thought of wetting my hands in EF 4. POCO support in EF 4 was really good, no auto generated codes, no edmx files, you can write pretty clean codes.

Let’s jump into the code. My project is not structured just very plain. Entities, Context and UI,.. every thing in single project. As I said this is just a tracer to see how things works.

Table Structure

CREATE TABLE [dbo].[User](
   
[ID] [int] IDENTITY(1,1) NOT NULL,
   
[UserCode] [varchar](10) NOT NULL,
   
[UserName] [varchar](50) NOT NULL,
   
[Password] [varchar](50) NOT NULL,
   
[UserEmail] [varchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
   
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE[dbo].[ImageComment](
  
[CommentId] [int] IDENTITY(1,1) NOT NULL,
  
[CommentDescription] [varchar](max) NULL,
  
[UserName] [varchar](50) NULL,
  
[UserId] [int] NOT NULL,
  
[CommentDate] [datetime] NOT NULL,
CONSTRAINT[PK_ImageComments] PRIMARY KEY CLUSTERED
(
  
[CommentId] ASC
)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON[PRIMARY]
)ON[PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE[dbo].[ImageComments]  WITH CHECK ADD  CONSTRAINT[FK_ImageComments_Users] FOREIGN KEY([UserId])
REFERENCES[dbo].[Users] ([ID])
GO

User.UserId is a foreign key in ImageComment.

Entity Classes

public class User
{
    public User()
    {
        this.Comments = new List<UserComment>();
    }

    [Key,StoreGenerated(StoreGeneratedPattern.Identity)]
    public int UserID { get; set; }
    public string UserCode { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string UserEmail { get; set; }
    public ICollection<UserComment> Comments { get; set; }
}
public class UserComment
{
    public UserComment() { }
    [Key, StoreGenerated(StoreGeneratedPattern.Identity)]
    public int CommentId { get; set; }
    public string Comment { get; set; }
    public virtual int UserId { get; set; }
    public virtual User User1 { get; set; }
    public DateTime CommentDate { get; set; }
}

 

Nothing much to explain just plain Entity classes. To add Key Attribute (just above the identity column) I added the System.ComponentModel.DataAnnotations;name space to the above classes. You might think that why I added Key Attribute to the indentity, the reason is EF4 POCO supports on Conventions. In some scenarios it may not identify the key property and will throw error “Unable to infer a key for entity type”. We need to attach [Key] to mark the identity key for EF.

Let’s see the Configuration classes.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using Microsoft.Data.Entity.Ctp;
using System.Data.Entity.ModelConfiguration;
namespace EntityFrameWorkTracer_CodeFirstModel
{
    public class UserConfigurationen:EntityConfiguration<User>
    {
        public UserConfigurationen()
        {
            Property(c => c.UserID).IsIdentity();
            Property(c => c.UserName).HasMaxLength(50).IsRequired();
            Property(c => c.UserCode).HasMaxLength(10).IsRequired();
            Property(c => c.Password).HasMaxLength(50).IsRequired();
            Property(c => c.UserEmail).HasMaxLength(50);
            this.HasMany(c => c.Comments);
            this.MapHierarchy(c => new
            {
                UserName = c.UserName,
                UserCode = c.UserCode,
                Password = c.Password,
                UserEmail = c.UserEmail,
                ID = c.UserID
            }).ToTable("Users");
        }
    }

    public class CommentConfiguration : EntityConfiguration<UserComment>
    {
        public CommentConfiguration()
        {
            Property(c => c.CommentId).IsIdentity();
            Property(c => c.Comment).HasMaxLength(5000);
            this.HasRequired(c => c.User1).WithMany(a => a.Comments).WillCascadeOnDelete().HasConstraint((c, y) => c.UserId == y.UserID);
            this.MapHierarchy(c => new
            {
                CommentId=c.CommentId,
                CommentDescription = c.Comment,
                UserId = c.UserId,
            }).ToTable("ImageComments");
        }
    }
}
 
 

The configuration class is the important piece of code here. This configuration class maps the Entity to the respective tables. We can also specify the meta property of the column like Required property, size of the field, etc.

You can see one important line of code in CommentConfiguration (marked in bold italics). It specifies the relation between UserComment and User table.

Object Context class

The derived object context class is used of Saving and Retrieving data from the db.

using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.Objects;
usingSystem.Data.EntityClient;
namespaceEntityFrameWorkTracer_CodeFirstModel
{
    public classAWModel:ObjectContext
  
{
        publicAWModel(EntityConnection connection)
            : base(connection)
        {

        }
        publicIObjectSet<User> User
        {
            get{ return base.CreateObjectSet<User>(); }
        }
    }
}

UI Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Entity.ModelConfiguration;
namespace EntityFrameWorkTracer_CodeFirstModel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = new SqlConnection("Data Source=SQLserver;Initial Catalog=ImagePublisher;User ID=sony;PWD=sony;MultipleActiveResultSets=True;");

                var builder = new ModelBuilder();
                builder.Configurations.Add(new UserConfigurationen());
                builder.Configurations.Add(new CommentConfiguration());
                var model = builder.CreateModel();
               
                var context = model.CreateObjectContext<AWModel>(con);
                context.ContextOptions.LazyLoadingEnabled = false;
                var query = from c in context.User select c;
                foreach (var u in query)
                {
                    context.LoadProperty(u, "Comments"); //Explicity loads Comments property in User object.
                    MessageBox.Show(u.UserName); 
                    foreach (UserComment comment in u.Comments)
                    {
                        MessageBox.Show(comment.Comment); 
                    }
                }
                
                //Uncomment and see how data save will work
                //User usr = new User
                //{
                //    UserName = "Kevin",
                //    Password = "Kevin",
                //    UserEmail = "Kevin@gmail",
                //    UserCode = "gh"
                //};
                //context.User.AddObject(usr);
                //context.SaveChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    }
}

I struggled a lot to figure out how to load the related objects, like Comments in the User object. My architect forwared a MSDN link and solved my puzzle. From the link I got to know about the Context.LoadProperty and implemented, viola it worked.

CTP4 supports the above code only support of configuration, before that you have to provide the edmx file as the configuration. Download CTP4.

Let’s see how to achieve Many to Many relation ship in POCO model

Many to Many Relation ship

To experiment many to many relation ship I added a new table to my db, Role and UserRoles as shown below

image

I modified the User entity to add the below property.

public ICollection<Role> UserRoles { get; set; }

 

This property holds all the Roles associated to the User.

Role Entity is very straight forward as shown below

using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.ComponentModel.DataAnnotations;
namespaceEntityFrameWorkTracer_CodeFirstModel
{
    public classRole
  
{
        publicRole()
        {
            this.Users = newList<User>();
        }
        [Key]
        public int RoleId { get; set; }
        public stringRoleName { get; set; }
        publicICollection<User> Users { get; set; }
    }
}

The important piece of code is the RoleConfiguration class. Let see the configuration class

public classRoleConfiguration : EntityConfiguration<Role>
{
    publicRoleConfiguration()
    {
        Property(r => r.RoleId).IsIdentity();
        this.HasMany(r => r.Users).WithMany(u => u.UserRoles).Map("dbo.UserRoles", (role, user) =>new
       
{
            RoleId=role.RoleId,
            UserId=user.UserID
        });

       
        this.MapHierarchy(r => new
      
{
            RoleId=r.RoleId,
            RoleName=r.RoleName
        }).ToTable("Role");
    }
}

 

The code I marked in bold italics is our area of interest. Here I specified relationship between Role.Users and User.Roles. Also I specified the mapping table that holds the relation, here my mapping table is UserRoles. RoleId, RoleName, UserId I marked in big font is nothing but the columns in the respective tables in the db. It is a mapping between Table columns and entities property.

Written by Sony Arouje

September 24, 2010 at 3:41 pm

Follow

Get every new post delivered to your Inbox.

Join 161 other followers

%d bloggers like this: