Project Summary

Soma is an O/R mapping framework developed in F#.
Supported programming languages and RDBMS are followings:

languages
  • F# 2.0
  • C# 4.0
  • Visual Basic 2010
  • PowerShell
RDBMS
  • Microsoft SQL Server 2008
  • Microsoft SQL Server Compact 4.0
  • Oracle Database 11g
  • MySQL 5.x.
  • SQLite 3

Main Features

Main features are followings:
  • 2 way SQL - SQL is executable in programs and in sql tools out of the box
  • SQL log handling
  • automatic primary key generation
  • optimistic lock
  • pagination
  • support for F# immutable record type
  • support for muttable POCO
  • arbitrary SQL execution and result mapping
  • stored procedure call
  • no configuration file
  • stateless architecture

Downlowds

Download new versions from Download page or install using NuGet.

Source Code

Soma source code has moved to GitHub.

Feedback

Soma is a free open source project that I have developed in my personal time.
I really appreciate your feedback and support for Soma. Especially any help for improving English documents is very welcomed.

You are also welcome to contact me (toshihiro) directly with questions, comments or suggestions.

Samples

Following sample programs(F# and C# editions) are included in the distribution zip file.

F#
open System
open System.Transactions
open Soma.Core

// define a module wraps Soma.Core.Db module
module MyDb = 
  let config = 
    { new MsSqlConfig() with
      member this.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=Soma.Tutorial;Integrated Security=True" }
  let query<'T> = Db.query<'T> config
  let queryOnDemand<'T> = Db.queryOnDemand<'T> config
  let execute sql expr = Db.execute config sql expr
  let find<'T when 'T : not struct> = Db.find<'T> config
  let tryFind<'T when 'T : not struct> = Db.tryFind<'T> config
  let insert<'T when 'T : not struct> = Db.insert<'T> config
  let update<'T when 'T : not struct> = Db.update<'T> config
  let delete<'T when 'T : not struct> = Db.delete<'T> config
  let call<'T when 'T : not struct> = Db.call<'T> config

// define a record mapped to a table 
type Employee = 
  { [<Id(IdKind.Identity)>]
    EmployeeId : int 
    EmployeeName : string
    DepartmentId : int
    [<Version>]
    VersionNo : int }

// define a record mapped to a procedure
type ProcResultAndOut = 
  { EmployeeId : int
    [<ProcedureParam(Direction = Direction.Output)>]
    EmployeeCount : int
    [<ProcedureParam(Direction = Direction.Result)>]
    Employees : Employee list }

let main =
  // execute following code in a transaction, but don't commit
  use tx = new TransactionScope()
  
  // find by id
  let emp = MyDb.find<Employee> [1]
  printfn "FOUND RECORD : \n%A\n" emp

  // update
  let emp = MyDb.update { emp with EmployeeName = "Hoge" }
  printfn "UPDATED RECORD : \n%A\n" emp

  // delete
  MyDb.delete emp
  printfn "DELETED RECORD : \n%A\n" emp

  // insert
  let emp = MyDb.insert { EmployeeId = 0; EmployeeName = "Allen"; DepartmentId = 2; VersionNo = 0}
  printfn "INSERTED RECORD : \n%A\n" emp

  // query and map results to records. parameters are bindable with the list of key/value pairs. 
  let empList = 
    MyDb.query<Employee> @"
    select 
      e.EmployeeId, e.EmployeeName, e.DepartmentId, e.VersionNo   
    from 
      Employee e 
    where 
      e.DepartmentId = /* emp.DepartmentId */0 
    " ["emp" @= emp]
  printfn "QUERY RESULTS AS RECORDS :"
  empList |> List.iter (printfn "%A")
  printfn ""

  // query and map results to dynamic objects. parameters are bindable with the list of key/value pairs. 
  let empList = 
    MyDb.query<dynamic> @"
    select 
      e.EmployeeId, e.EmployeeName, e.DepartmentId, e.VersionNo   
    from 
      Employee e 
    where 
      e.DepartmentId = /* emp.DepartmentId */0 
    " ["emp" @= emp]
  printfn "QUERY RESULTS AS DYNAMIC OBJECTS :"
  empList |> List.iter (fun emp -> printfn "EmployeeId=%O, EmployeeName=%O" emp?EmployeeId emp?EmployeeName)
  printfn ""

  // call procedure
  let result = MyDb.call<ProcResultAndOut> { EmployeeId = 1; EmployeeCount = 0; Employees = [] }
  printfn "PROCEDURE OUTPUT : \n%A\n" result

  // execute arbitrary SQL
  let rows = 
    MyDb.execute @"
    delete from Employee 
    " []
  printfn "AFFECTED ROWS : \n%A\n" rows

  Console.ReadKey()

C#
using System;
using System.Collections.Generic;
using System.Transactions;

using Soma.Core;

namespace Tutorial.CSharp
{
    // define a config class
    internal class MyConfig : MsSqlConfig
    {
        public override string ConnectionString { get { return @"Data Source=.\SQLEXPRESS;Initial Catalog=Soma.Tutorial;Integrated Security=True"; } }
    }

    // define a class mapped to a table
    internal class Employee
    {
        [Id(IdKind.Identity)]
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public int DepartmentId { get; set; }
        [Version]
        public int VersionNo { get; set; }

        public override string ToString()
        {
            const string fmt = "EmployeeId: {0}, EmployeeName: {1}, DepartmentId: {2}, VersionNo: {3}";
            return string.Format(fmt, EmployeeId, EmployeeName, DepartmentId, VersionNo);
        }
    }

    // define a class mapped to a procedure
    internal class ProcResultAndOut
    {
        public int EmployeeId { get; set; }
        [ProcedureParam(Direction = Direction.Output)]
        public int EmployeeCount { get; set; }
        [ProcedureParam(Direction = Direction.Result)]
        public IList<Employee> Employees { get; set; }
    }

    internal class Program
    {
        private static void Main()
        {
            var db = new Db(new MyConfig());

            // execute following code in a transaction, but don't commit
            using (new TransactionScope())
            {
                // find by id
                var emp = db.Find<Employee>(1);
                Console.WriteLine("FOUND ENTITY : \n{0}\n", emp);

                // update
                emp.EmployeeName = "Hoge";
                db.Update(emp);
                Console.WriteLine("UPDATED ENTITY : \n{0}\n", emp);

                // delete
                db.Delete(emp);
                Console.WriteLine("DELETED ENTITY : \n{0}\n", emp);

                // insert
                emp = new Employee { EmployeeName = "Allen", DepartmentId = 2 };
                db.Insert(emp);
                Console.WriteLine("INSERTED ENTITY : \n{0}\n", emp);

                // query and map results to entities. parameters are bindable with "Anonymous Types".
                var empList = db.Query<Employee>(@"
                    select 
                        e.EmployeeId,
                        e.EmployeeName,
                        e.DepartmentId,
                        e.VersionNo
                    from
                        Employee e
                    where
                        e.DepartmentId = /* emp.DepartmentId */0
                    ", new { emp });
                Console.WriteLine("QUERRY RESULTS AS ENTITIES :");
                foreach (var e in empList)
                {
                    Console.WriteLine(e);
                }
                Console.WriteLine();

                // query and map results to dynamic objects. parameters are bindable with "Anonymous Types".
                var empList2 = db.Query<dynamic>(@"
                    select 
                        e.EmployeeId,
                        e.EmployeeName,
                        e.DepartmentId,
                        e.VersionNo
                    from
                        Employee e
                    where
                        e.DepartmentId = /* emp.DepartmentId */0
                    ", new { emp });
                Console.WriteLine("QUERY RESULTS AS DYNAMIC OBJECTS :");
                foreach (var e in empList2)
                {
                    Console.WriteLine("EmployeeId={0}, EmployeeName={1}", e.EmployeeId, e.EmployeeName);
                }
                Console.WriteLine();

                // call procedure
                var procedure = new ProcResultAndOut { EmployeeId = 1 };
                db.Call(procedure);
                Console.WriteLine("PROCEDURE OUTPUT VALUE : \n{0}\n", procedure.EmployeeCount);
                Console.WriteLine("PROCEDURE RESULT ENTITIES :");
                foreach (var e in procedure.Employees)
                {
                    Console.WriteLine(e);
                }
                Console.WriteLine();

                // execute arbitrary SQL
                var rows = db.Execute("delete from Employee");
                Console.WriteLine("AFFECTED ROWS : \n{0}\n", rows);
            }
            Console.ReadKey();
        }
    }
}

Last edited Dec 13, 2012 at 12:50 AM by toshihiro, version 43