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()

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();
        }
    }
}