Introduction
Seven years ago, I wrote a wiki for managing requirements for multiple software development projects. I used C# in a simplistic ASP.NET web forms application with SQL Server as my data store. I published it under the name OpenCollective on Code Project and since then it has been viewed on just over 196,000 occasions and downloaded a little more than 1,600 times.

I still get an occasional question about OpenCollective, so a month or two ago I decided to rewrite it in order to explore using SQLite and Fluent NHibernate in a “code first” approach set in an ASP.NET MVC 3 application written in C#. The original OpenCollective was in some ways an experiment with technologies and techniques I was interested in at that time, so a rewrite to learn and explore something new seemed like a good idea.

The plan is simple. As time allows, I’ll rewrite OpenCollective and blog about my progress whenever it seems I have something of value to share. With some of these posts, I’ll share the entire code base for download. Others may just illustrate something I’ve learned or something I want to preserve for my own future reference. I tackled the first chore over a month ago but work and other activities pushed this project aside for a while, so I’m just getting around to blogging about the code now. I suspect this will be a priority driven pattern for this project.

Code First with Fluent NHibernate and SQLite
The first thing I wanted to accomplish in the OpenCollective rewrite was a move to SQLite and Fluent NHibernate in a “code first” approach to replace the SQL Server schema and the old ADO.NET code in the original. Some quick searching led me to the Fluent NHibernate Getting Started page. (If you haven’t read it, do so now.) Combining that with what I have already learned in exploring SQLite in this blog, I soon had a code first database being generated.

I’ll attach the entire code in 7zip (saves me 25% in bandwidth over standard zip), so I’m not going to go through all of the code in text here, but I do want to share with you some of the key steps in the my code first with Fluent NHibernate and SQLite adventure.

First, create your POCO entity class:

public class Project
{
	public virtual long Id { get; private set; }
	public virtual string Name { get; set; }
	public virtual DateTime Updated { get; set; }

	public virtual Author Author { get; set; }
	public virtual IList<Topic> Topics { get; set; }
	public virtual IList<Attachment> Attachments { get; set; }

	public Project()
	{
		Topics = new List<Topic>();
		Attachments = new List<Attachment>();
	}

	public virtual void AddTopic(Topic topic)
	{
		topic.Project = this;
		Topics.Add(topic);
	}

	public virtual void AddAttachment(Attachment attachment)
	{
		attachment.Project = this;
		Attachments.Add(attachment);
	}
}

Second, create a Fluent NHibernate mapping class:

public class ProjectMap : ClassMap<Project>
{
	public ProjectMap()
	{
		Id(x => x.Id);
		Map(x => x.Name);
		Map(x => x.Updated);
		References(x => x.Author);

		HasMany(x => x.Attachments)
			.Cascade.All();

		HasMany(x => x.Topics)
			.Cascade.All();
	}
}

Third, create a factory class that will return the NHibernate ISessionFactory and generate the SQLite schema and database file:

internal class DataFactory
{
	string _dbFile = string.Empty;
	bool _overwriteExisting = false;

	public DataFactory(string dbFile, bool overwriteExisting)
	{
		_dbFile = dbFile;
		_overwriteExisting = overwriteExisting;
	}

	public ISessionFactory CreateSessionFactory()
	{
		return Fluently.Configure()
			.Database(
				SQLiteConfiguration.Standard
					.UsingFile(_dbFile)
			)
			.Mappings(m => m.FluentMappings.AddFromAssemblyOf<DataFactory>())
			.ExposeConfiguration(BuildSchema)
			.BuildSessionFactory();
	}

	private void BuildSchema(Configuration config)
	{
		if (_overwriteExisting)
		{
			if (File.Exists(_dbFile)) File.Delete(_dbFile);

			var se = new SchemaExport(config);
			se.Create(false, true);
		}
	}
}

Fourth, create a repository class that will let you create the database and perform CRUD operations using your entity classes:

public static class OcDataRepository
{
	public static void Create(string dbFile)
	{
		DataFactory df = new DataFactory(dbFile, true);
		using (var sf = df.CreateSessionFactory())
		{
			sf.Close();
		}
	}

	public static void AddProject(string dbFile, Project project)
	{
		DataFactory df = new DataFactory(dbFile, false);
		using (var sf = df.CreateSessionFactory())
		using (var session = sf.OpenSession())
		using (var trans = session.BeginTransaction())
		{
			session.SaveOrUpdate(project.Author);
			session.SaveOrUpdate(project);
			trans.Commit();
		}
	}
}

Finally, write a little test console app to see if it all works:

class Program
{
	static void Main(string[] args)
	{
		string dbFile = @"C:\temp\test4.db";
		OcDataRepository.Create(dbFile);

		Project project = new Project
		{
			Name = "My First Project",
			Updated = DateTime.Now,
			Author = new Author { Name = "John Smith", Username = "smithj", Email = "jsmith@gmial.com" }
		};
		OcDataRepository.AddProject(dbFile, project);

		Console.WriteLine("done");
		Console.ReadLine();
	}
}

Examine the SQLite db file with your favorite SQLite tool and see the SQL:

CREATE TABLE "Project" (
	Id  integer, 
	Name TEXT, 
	Updated DATETIME, 
	Author_id INTEGER, 
	primary key (Id)
)

If you don’t have 7-zip, download and install it first. Download the code OcWiki.7z (695.8KB)