SqliteOutput.cs

using System;
using System.Data.SQLite;
using System.Linq;
using System.Transactions;
using Dapper;
using LinkCrawler.Models;

namespace LinkCrawler.Utils.Outputs
{
    public class SqliteOutput : IOutput
    {
        private readonly SQLiteConnection _dbConnection;

        public SqliteOutput(SQLiteConnection dbConnection)
        {
            _dbConnection = dbConnection;
            CreateSchemaIfNecessary();
        }

        public void WriteError(IResponseModel responseModel)
        {
            WriteRecord(responseModel);
        }

        public void WriteInfo(IResponseModel responseModel)
        {
            WriteRecord(responseModel);
        }

        private void WriteRecord(IResponseModel responseModel)
        {
            try
            {
                using (var tx = new TransactionScope())
                {
                    _dbConnection.Execute(@"
                    INSERT INTO LinkCrawlerLog (Markup, RequestedUrl, ReferrerUrl, StatusCodeNumber, IsSuccess) VALUES (
                    @Markup,
                    @RequestedUrl,
                    @ReferrerUrl,
                    @StatusCodeNumber,
                    @IsSuccess);", responseModel);
                    tx.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error writing to SQLite DB", ex);
            }
        }

        private void CreateSchemaIfNecessary()
        {
            try
            {
                var result = _dbConnection.Query<int>(@"SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='LinkCrawlerLog'")
                        .FirstOrDefault();
                if (result == 1)
                    return;

                using (var tx = new TransactionScope())
                {
                    _dbConnection.Execute(@"
                    CREATE TABLE LinkCrawlerLog (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Markup TEXT NULL,
                        RequestedUrl TEXT NULL,
                        ReferrerUrl TEXT NULL,
                        StatusCodeNumber TEXT NULL,
                        IsSuccess INTEGER NULL
                    );");
                    tx.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error validating and setting up SQLite schema", ex);
            }
        }
    }
}

StructureMapRegistry.cs

using System.Configuration;
using System.Data.SQLite;
using LinkCrawler.Utils.Outputs;
using StructureMap.Configuration.DSL;
using StructureMap.Graph;

namespace LinkCrawler.Utils
{
    public class StructureMapRegistry : Registry
    {
        public StructureMapRegistry()
        {
            Scan(scan =>
            {
                scan.TheCallingAssembly();
                scan.WithDefaultConventions();
                scan.AddAllTypesOf<IOutput>();
            });
            var conn = new SQLiteConnection(ConfigurationManager.AppSettings["SQLiteConnectionString"]);
            For<SQLiteConnection>().Singleton().Use(conn);
        }
    }
}