Table of Contents

Overview

Soma.Core.Db and Soma.Core.Local modules provide API optimized for the F# programming.
For the C# and VB.NET programming, see Db and LocalDb classes.

The difference between Soma.Core.Db and Soma.Core.LocalDb is the way of connection handling. Soma.Core.Db module opens and closes connections automatically. The client program has not to manage connections. On the countrary, Soma.Core.LocalDb does not open and colse connections. The client program has to manage connections.

Transactions are controllable with System.Transactions.TransactionScope class for both modules. But, Some ADO.NET data providers such as Microsoft SQL Server Compact 4.0 and SQLite 3 do not support to enlist multiple connections to one transaction. In the case, the client program has to manage a connection in one transaction. Soma.Core.LocalDb module is suited for the case.

In this page, sample code are described in F#. In the query and execute functions of Soma.Core.Db and Soma.Core.LocalDb modules, SQL is available. To know how to write SQL, see SQL Description and Expression Language.
The API Soma.Core.Db and Soma.Core.LocalDb provide are very similar, so this page describes Soma.Core.Db module mainly.

Soma.Core.Db

The signature of Soma.Core.Db module is following:

Signature
[<RequireQualifiedAccess>]
[<CompilationRepresentation(CompilationRepresentationFlags.ModuleSuffix)>]
module Db =
  val query<'T> : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> 'T list
  val queryOnDemand<'T> : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> 'T seq
  val paginate<'T> : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> offset:int64 * limit:int64 -> 'T list
  val paginateOnDemand<'T> : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> offset:int64 * limit:int64-> 'T seq
  val paginateAndCount<'T> : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> offset:int64 * limit:int64 -> 'T list * int64
  val execute : config:IDbConfig -> sql:string -> condition:(string * obj * Type) list -> int  
  val executeReader<'T> : config:IDbConfig -> handler:(DbDataReader -> 'T) -> sql:string -> condition:(string * obj * Type) list -> 'T
  val find<'T when 'T : not struct> : config:IDbConfig -> idList:obj list -> 'T
  val tryFind<'T when 'T : not struct> : config:IDbConfig -> idList:obj list -> 'T option
  val findWithVersion<'T when 'T : not struct> : config:IDbConfig -> idList:obj list -> version:obj -> 'T
  val tryFindWithVersion<'T when 'T : not struct> : config:IDbConfig -> idList:obj list -> version:obj -> 'T option
  val insert<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> 'T
  val insertWithOpt<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> opt:InsertOpt -> 'T
  val update<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> 'T
  val updateWithOpt<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> opt:UpdateOpt -> 'T
  val delete<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> unit
  val deleteWithOpt<'T when 'T : not struct> : config:IDbConfig -> entity:'T -> opt:DeleteOpt -> unit
  val call<'T when 'T : not struct> : config:IDbConfig -> procedure:'T -> 'T


The typical usage is following:

The typical usage
  use tx = new TransactionScope()

  let emp = Db.find<Employee> config [1]
  let emp = Db.update config { emp with EmployeeName = "Hoge" }
  Db.delete config emp

  tx.Complete()

The points of this example are followings:
  • The transaction are controllable with System.Transactions.TransactionScope.
  • The first parameter of all functions of Soma.Core.Db module is the Configuration instance. It is recommended to use partial function application for convenience.
  • The connection handling is managed automatically.

Soma.Core.LocalDb

The signature of Soma.Core.LocalDb module is following:

Signature
[<RequireQualifiedAccess>]
[<CompilationRepresentation(CompilationRepresentationFlags.ModuleSuffix)>]
module LocalDb =
  val query<'T> : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list  -> 'T list
  val queryOnDemand<'T> : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list  -> 'T seq
  val paginate<'T> : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list -> offset:int64 * limit:int64 -> 'T list
  val paginateOnDemand<'T> : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list  -> offset:int64 * limit:int64-> 'T seq
  val paginateAndCount<'T> : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list  -> offset:int64 * limit:int64 -> 'T list * int64
  val execute : config:IDbConfig -> connection:DbConnection -> sql:string -> condition:(string * obj * Type) list ->  int
  val executeReader<'T> : config:IDbConfig -> connection:DbConnection -> handler:(DbDataReader -> 'T) -> sql:string -> condition:(string * obj * Type) list -> 'T
  val find<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> idList:obj list -> 'T
  val tryFind<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> idList:obj list -> 'T option
  val findWithVersion<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> idList:obj list -> version:obj -> 'T
  val tryFindWithVersion<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> idList:obj list -> version:obj -> 'T option
  val insert<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> entity:'T -> 'T
  val update<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> entity:'T -> 'T
  val updateWithoutVersion<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> entity:'T -> 'T
  val delete<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> entity:'T -> unit
  val deleteWithoutVersion<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> entity:'T -> unit
  val call<'T when 'T : not struct> : config:IDbConfig -> connection:DbConnection -> procedure:'T -> 'T
  val createConnection : config:IDbConfig -> DbConnection


By comparison with Soma.Core.Db module, Soma.Core.LocalDb module has following differences.
  • Has the createConnection function.
  • All functions except the createConnection function have second parameter and its type is System.Data.Common.DbConnection.

The typical usage is following:

The typical usage
  use tx = new TransactionScope()
  use con = LocalDb.createConnection config

  let emp = LocalDb.find<Employee> config con [1]
  let emp = LocalDb.update config con { emp with EmployeeName = "Hoge" }
  LocalDb.delete config con emp

  tx.Complete()


The points of this example are followings:
  • The transaction are controllable with System.Transactions.TransactionScope.
  • The first parameter of all functions of Soma.Core.Db module is the Configuration instance. It is recommended to use partial function application for convenience.
  • The connection is created by the createConnection function and closed by the client program.
  • The created connection is passed to other functions as parameter.
  • The client program has to manage that only one connection is enlisted to one transaction.

Db.query

The query function queries rows as list<'T> with the specfied SELECT SQL.
The generic parameter 'T may be the primitive type, the record type, the tuple type or the Soma.Core.dynamic type.

Example : The generic parameter 'T is the string type
let empList = 
  Db.query<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the Employee record type
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
let empList = 
  Db.query<string, decimal>
    config
    "select EmployeeName, Salary from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of Department and Employee record types
let deptEmpList = 
  Db.query<Department, Employee>
    config
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of string and Employee record types
let empList = 
  Db.query<string, Employee>
    config
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the dynamic type
let empList = 
  Db.query<dynamic>
    config
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 
empList |> Seq.iter (fun emp -> printfn "%O, %O" emp?EmployeeId emp?EmployeeName)

Db.queryOnDemand

The queryOnDemand function queries rows as seq<'T> with the specfied SELECT SQL. The DB access is differed until seq<'T> is evaluated.
The generic parameter 'T may be the primitive type, the record type, the tuple type or the Soma.Core.dynamic type.

Example : The generic parameter 'T is the string type
let empSeq = 
  Db.queryOnDemand<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the Employee record type
let empSeq = 
  Db.queryOnDemand<Employee>
    config
    "select * from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
let empSeq = 
  Db.queryOnDemand<string, decimal>
    config
    "select EmployeeName, Salary from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of Department and Employee record types
let deptEmpSeq = 
  Db.queryOnDemand<Department, Employee>
    config
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the tuple type which consists of string and Employee record types
let empSeq = 
  Db.queryOnDemand<string, Employee>
    config
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0"
    ["salary" @= 1000M] 

Example : The generic parameter 'T is the dynamic type
let empSeq = 
  Db.queryOnDemand<dynamic>
    config
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 
empSeq |> Seq.iter (fun emp -> printfn "%O, %O" emp?EmployeeId emp?EmployeeName)

Db.pagenate

The pagenate function queries rows as list<'T> with the specfied SELECT SQL. The specified SQL is transformed for pagination and then issued.
The generic parameter 'T may be the primitive type, the record type, the tuple type or the Soma.Core.dynamic type.

The below example shows the typical usage and the transformed SQL.

F# code
let empList = 
  Db.pagenate<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)
transformed SQL
select 
  * 
from 
  ( select 
      temp_.*, 
      row_number() over( order by Salary) as soma_rownumber_ 
    from 
      ( select EmployeeName from Employee where Salary > @p0 ) as temp_ 
  ) as temp2_ 
where 
  soma_rownumber_ > @p1
  and 
  soma_rownumber_ <= @p2
For the parameters @p0, @p1 and @p2, the values 1000M, 10 and 60 are bound.

Example : The generic parameter 'T is the string type
let empList = 
  Db.pagenate<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the Employee record type
let empList = 
  Db.pagenate<Employee>
    config
    "select * from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
let empList = 
  Db.pagenate<string, decimal>
    config
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of Department and Employee record types
let deptEmpList = 
  Db.pagenate<Department, Employee>
    config
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and Employee record types
let empList = 
  Db.pagenate<string, Employee>
    config
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the dynamic type
let empList = 
  Db.pagenate<dynamic>
    config
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 
    (10L, 50L)
empList |> Seq.iter (fun emp -> printfn "%O, %O" emp?EmployeeId emp?EmployeeName)

Db.pagenateOnDemand

The pagenateOnDemand function queries rows as seq<'T> with the specfied SELECT SQL. The specified SQL is transformed for pagination and then issued. The DB access is differed until seq<'T> is evaluated.
The generic parameter 'T may be the primitive type, the record type, the tuple type or the Soma.Core.dynamic type.

The below example shows the typical usage and the transformed SQL.

F# code
let empSeq = 
  Db.pagenateOnDemand<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)
transformed SQL
select 
  * 
from 
  ( select 
      temp_.*, 
      row_number() over( order by Salary) as soma_rownumber_ 
    from 
      ( select EmployeeName from Employee where Salary > @p0 ) as temp_ 
  ) as temp2_ 
where 
  soma_rownumber_ > @p1
  and 
  soma_rownumber_ <= @p2
For the parameters @p0, @p1 and @p2, the values 1000M, 10 and 60 are bound.

Example : The generic parameter 'T is the string type
let empSeq = 
  Db.pagenateOnDemand<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the Employee record type
let empSeq = 
  Db.pagenateOnDemand<Employee>
    config
    "select * from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
let empSeq = 
  Db.pagenateOnDemand<string, decimal>
    config
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of Department and Employee record types
let deptEmpSeq = 
  Db.pagenateOnDemand<Department, Employee>
    config
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and Employee record types
let empSeq = 
  Db.pagenateOnDemand<string, Employee>
    config
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the dynamic type
let empSeq = 
  Db.pagenateOnDemand<dynamic>
    config
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 
    (10L, 50L)
empSeq |> Seq.iter (fun emp -> printfn "%O, %O" emp?EmployeeId emp?EmployeeName)

Db.paginateAndCount

The paginateAndCount function queries rows and count at once with the specfied SELECT SQL. The specified SQL is transformed for pagination and then issued. And the specified SQL is transformed for counting and then issued.
The generic parameter 'T may be the primitive type, the record type, the tuple type or the Soma.Core.dynamic type.

The below example shows the typical usage and the transformed SQL.

F# code
let empList, count = 
  Db.paginateAndCount<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)
transformed SQL : for pagination
select 
  * 
from 
  ( select 
      temp_.*, 
      row_number() over( order by Salary) as soma_rownumber_ 
    from 
      ( select EmployeeName from Employee where Salary > @p0 ) as temp_ 
  ) as temp2_ 
where 
  soma_rownumber_ > @p1
  and 
  soma_rownumber_ <= @p2
For the parameters @p0, @p1 and @p2, the values 1000M, 10 and 60 are bound.

transformed SQL : for counting
select 
  count(*) 
from 
  ( select EmployeeName from Employee where Salary > @p0 ) as t_
For the parameter @p0, the values 1000M is bound.

Example : The generic parameter 'T is the string type
let empList, count = 
  Db.paginateAndCount<string>
    config
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the Employee record type
let empList, count = 
  Db.paginateAndCount<Employee>
    config
    "select * from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
let empList, count = 
  Db.paginateAndCount<string, decimal>
    config
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of Department and Employee record types
let deptEmpList, count = 
  Db.paginateAndCount<Department, Employee>
    config
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the tuple type which consists of string and Employee record types
let empList, count = 
  Db.paginateAndCount<string, Employee>
    config
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary"
    ["salary" @= 1000M] 
    (10L, 50L)

Example : The generic parameter 'T is the dynamic type
let empList, count = 
  Db.paginateAndCount<dynamic>
    config
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 
    (10L, 50L)
empList |> Seq.iter (fun emp -> printfn "%O, %O" emp?EmployeeId emp?EmployeeName)

Db.execute

The execute function executes the specified SQL.

Example : Deleting multiple rows
let rows = 
  Db.execute
    config
    "delete from Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Db.executeReader

The executeReader function executes the SQL and handles the reader.

Example : Loading to the DataTable
let table = 
  Db.executeReader<DataTable>
    config
    (fun reader ->
      let table = new DataTable()
      table.Load reader
      table )
    "select from * Employee where Salary > /* salary */0"
    ["salary" @= 1000M] 

Db.find

The find function gets the entity with primary keys.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute.

Example : The typical usage
let emp = Db.find<Employee> config [1]

When the entity is not found, EntityNotFoundException is thrown.

Example : Handling EntityNotFoundException
try
  let emp = Db.find<Employee> config [1]
  ...
with
| EntityNotFoundException  _ -> 
  ...

Db.tryFind

The tryFind function trys to get the entity with the primary keys.
If the entity found, Some value is returned, otherwise None is returned.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute.

Example : The typical usage
let emp = Db.tryFind<Employee> config [1]

Db.findWithVersion

The findWithVersion function gets the entity with primary keys and checks the version.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute and field annotated with Soma.Core.VersionAttribute.

Example : The typical usage
let emp = Db.findWithVersion<Employee> config [1] 0

When the entity is not found by primary keys, EntityNotFoundException is thrown.

Example : Handling EntityNotFoundException
try
  let emp = Db.findWithVersion<Employee> config [1] 0
  ...
with
| EntityNotFoundException  _ -> 
  ...

When the versions are different, OptimisticLockException is thrown.

Example : Handling OptimisticLockException
try
  let emp = Db.findWithVersion<Employee> config [1] 99
  ...
with
| OptimisticLockException _ -> 
  ...

Db.tryFindWithVersion

The tryFindWithVersion function trys to get the entity with primary keys and checks the version.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute and field annotated with Soma.Core.VersionAttribute.

Example : The typical usage
let emp = Db.tryFindWithVersion<Employee> config [1] 0

When the versions are different, OptimisticLockException is thrown.

Example : Handling OptimisticLockException
try
  let emp = Db.tryFindWithVersion<Employee> config [1] 99
  ...
with
| OptimisticLockException _ -> 
  ...

Db.insert

The insert function inserts the row mapped with the entity and gets the new entity.
The generic parameter 'T must be the record type.

Example : The typical usage
let emp = { EmployeeId = 0; EmployeeName = "Hoge"; VersionNo = 0 }
let emp = Db.insert<Employee> config emp

When a unique constraint violation is occurred, UniqueConstraintException is thrown.

Example : Handling UniqueConstraintException
let emp = { EmployeeId = 0; EmployeeName = "Hoge"; VersionNo = 0 }
try
  let emp = Db.insert<Employee> config emp
  ...
with 
| UniqueConstraintException _ ->
  ...

Db.insertWithOpt

The insertWithOpt function is similar with the insert function except this function accepts the Soma.Core.InsertOpt instance as parameter. With InsertOpt, it is possible to customize the INSERT SQL.

To exclude paticular columns from INSERT INTO clause, specify fields mapped to the columns to the Exclude property of InsertOpt.

Example : Excluding paticular columns from INSERT INTO clause
let emp = { EmployeeId = 0; EmployeeName = "Hoge"; Age = Some 21; Salary = Some 200000M; VersionNo = 0 }
let emp = Db.insertWithOpt<Employee> config emp (InsertOpt(Exclude = ["Age"; "Salary"]))

To include only paticular columns into INSERT INTO clause, specify fields mapped to the columns to the Include property of InsertOpt.

Example : Including only paticular columns into INSERT INTO clause
let emp = { EmployeeId = 0; EmployeeName = "Hoge"; Age = Some 21; Salary = Some 200000M; VersionNo = 0 }
let emp = Db.insertWithOpt<Employee> config emp (InsertOpt(Include = ["EmployeeName"]))

To exclude columns whose value is null from INSERT INTO clause, specify true to the ExcludeNull property of InsertOpt.

Example : Excluding columns whose value is nulls from INSERT INTO clause
let emp = { EmployeeId = 0; EmployeeName = "Hoge"; Age = None; Salary = None; VersionNo = 0 }
let emp = Db.insertWithOpt<Employee> config emp (InsertOpt(ExcludeNull= true))

Db.update

The update function updates the row mapped with the entity and gets the new entity.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute.

Example : The typical usage
let emp = Db.find<Employee> config [1]
let emp = Db.update<Employee> config { emp with EmployeeName = "Hoge" }

When a unique constraint violation is occurred, UniqueConstraintException is thrown.

Example : Handling UniqueConstraintException
let emp = Db.find<Employee> config [1]
try
  let emp = Db.update<Employee> config { emp with EmployeeName = "Hoge" }
  ...
with 
| UniqueConstraintException _ ->
  ...

When the updated row count is 0 and the record type has a field annotated with Soma.Core.VersionAttribute, OptimisticLockException is thrown.

Example : Handling OptimisticLockException
let emp = Db.find<Employee> config [1]
try
  let emp = Db.update<Employee> config { emp with EmployeeName = "Hoge" }
  ...
with 
| OptimisticLockException _ ->
  ...

When the updated row count is 0 and the record type has not a field annotated with Soma.Core.VersionAttribute, NoAffectedRowException is thrown.

Example : Handling NoAffectedRowException
let emp = Db.find<Employee> config [1]
try
  let emp = Db.update<Employee> config { emp with EmployeeName = "Hoge" }
  ...
with 
| NoAffectedRowException _ ->
  ...

Db.updateWithOpt

The updateWithOpt function is similar with the update function except this function accepts the Soma.Core.UpdateOpt instance as parameter. With UpdateOpt, it is possible to customize the UPDATE SQL and the update behavior.

To exclude paticular columns from SET clause, specify fields mapped to the columns to the Exclude property of UpdateOpt.

Example : Excluding paticular columns from SET clause
let emp = { EmployeeId = 1; EmployeeName = "Hoge"; Age = Some 21; Salary = Some 200000M; VersionNo = 1 }
let emp = Db.updateWithOpt<Employee> config emp (UpdateOpt(Exclude = ["Age"; "Salary"]))

To include only paticular columns into SET clause, specify fields mapped to the columnsto the Include property of UpdateOpt.

Example : Including only paticular columns into SET clause
let emp = { EmployeeId = 1; EmployeeName = "Hoge"; Age = Some 21; Salary = Some 200000M; VersionNo = 1 }
let emp = Db.updateWithOpt<Employee> config emp (UpdateOpt(Include = ["EmployeeName"]))

To exclude columns whose value is null from SET clause, specify true to the ExcludeNull of UpdateOpt.

Example : Excluding columns whose value is nulls from SET clause
let emp = { EmployeeId = 1; EmployeeName = "Hoge"; Age = None; Salary = None; VersionNo = 1 }
let emp = Db.updateWithOpt<Employee> config emp (UpdateOpt(ExcludeNull= true))

To ignore version and suppress OptimisticLockException, specify true to the IgnoreVersion of UpdateOpt.

Example : Ignoring version
let emp = { EmployeeId = 1; EmployeeName = "Hoge"; Age = None; Salary = None; VersionNo = 1 }
let emp = Db.updateWithOpt<Employee> config emp (UpdateOpt(IgnoreVersion= true))

Db.delete

The delete function deletes the row mapped with entity.
The generic parameter 'T must be the record type which has fields annotated with Soma.Core.IdAttribute.

Example : The typical usage
let emp = Db.find<Employee> config [1]
Db.delete<Employee> config emp

When the deleted row count is 0 and the record type has a field annotated with Soma.Core.VersionAttribute, OptimisticLockException is thrown.

Example : Handling OptimisticLockException
let emp = Db.find<Employee> config [1]
try
  Db.delete<Employee> config emp
  ...
with 
| OptimisticLockException _ ->
  ...

When the deleted row count is 0 and the record type has not a field annotated with Soma.Core.VersionAttribute, NoAffectedRowException is thrown.

Example : Handling NoAffectedRowException
let emp = Db.find<Employee> config [1]
try
  Db.delete<Employee> config emp
  ...
with 
| NoAffectedRowException _ ->
  ...

Db.deleteWithOpt

The deleteWithOpt function is similar with the update function except this function accepts the Soma.Core.DeleteOpt instance as parameter. With DeleteOpt, it is possible to customize the delete behavior.

To ignore version and suppress OptimisticLockException, specify true to the IgnoreVersion of DeleteOpt.

Example : Ignoring version
let emp = Db.find<Employee> config [1]
Db.deleteWithOpt<Employee> config emp (DeleteOpt(IgnoreVersion= true))

Db.call

The call function calls the stored procedure.
The generic parameter 'T must be the record type which represents the stored procedure. About the mapping between the stored procedure and the record type, see Mapping.

Example : The typical usage
let procedure = { Param1 = 1; Param2= 2; Param3 = 0 }
let procedure = Db.call<ProcMultiParams> config procedure

When a unique constraint violation is occurred, UniqueConstraintException is thrown.

Example : Handling UniqueConstraintException
let procedure = { Param1 = 1; Param2= 2; Param3 = 0 }
try
  let procedure = Db.call<ProcMultiParams> config procedure
  ...
with 
| UniqueConstraintException _ ->
  ...

Last edited Aug 21, 2011 at 1:59 AM by toshihiro, version 35

Comments

No comments yet.