This project is read-only.

Table of Contents

Overview

Database tables and stored procedures are mapped to F# record types or reference types which have a no-arg constructor.

Mapping between Table and Record

A database table is mapped to a F# record type. The columns of the table are mapped to the fields of the record type.

DDL : Department Table
create table Department (
    DepartmentId int identity primary key,
    DepartmentName varchar(50) unique,
    VersionNo int not null
);

F# : Department Record
open Soma.Core

type Department = 
  { [<Id(IdKind.Identity)>]
    DepartmentId : int 
    DepartmentName : string
    [<Version>]
    VersionNo : int }


A database table is mapped to a reference type, too. The columns of the table are mapped to the properties of the reference type which has a no-arg constructor. The propeties must have both getter and setter.

C# : Department Class
using Soma.Core;

public class Department
{
    [Id(IdKind.Identity)]
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    [Version]
    public int VersionNo { get; set; }
}

Table Definition

A table name is mapped to a record name implicitly.
You can specify the catalog name, the schema name and the table name with the TableAttribute type.

example : The TableAttribute
open Soma.Core

[<Table(Catalog = "CATALOG", Schema = "SCHEMA", Name = "DEPT")>]
type Department = 
  { ... }

When the IsEnclosed property of the TableAttribute type is set to true, the catalog name, the schema name and the table name are enclosed with special delimiters RDBMS supports.
example : The IsEnclosed property of the TableAttribute
open Soma.Core

[<Table(IsEnclosed = true)>]
type Department = 
  { ... }
example : The generated SQL when the IsEnclosed property of the TableAttribute type is set to true
select ... from [Department]

Column Definition

A colum name is mapped to a field name implicitly.
You can specify the column name with the ColumnAttribute type.

example : The ColumnAttribute
open Soma.Core

type Department = 
  { ...
    [<Column(Name = "DEPTNAME")>]
    DepartmentName : string }

The ColumnAttribute has the Insertable property and the Updatable property。These properties are set to false, the fields marked with the ColumnAttribute are excluded from INSERT and UPDATE。

example : The Updatable property of the ColumnAttribute
open Soma.Core

type Department = 
  { ...
    [<Column(Updatable= false)>]
    DepartmentName : string }

When the IsEnclosed property of the ColumnAttribute type is set to true, the column name is enclosed with special delimiters RDBMS supports.

example : The IsEnclosed property of the ColumnAttribute
open Soma.Core

type Department = 
  { ...
    [<Column(IsEnclosed= true)>]
    DepartmentName : string }
example : The generated SQL when the IsEnclosed property of the ColumnAttribute type is set to true
select ... [DepartmentName] ... from Department

Id

You must specify the IdAttribute type to the field mapped to the primary key column.

The IdKind.Assigned means that the primary key is assigned by the application.
The no-arg IdAttribute is same as the IdAttribute(IdKind.Assigned).
Following two examples have same semantic.

example : The IdAttribute with the implicit IdKind.Assigned
open Soma.Core

type Department = 
  { [<Id>]
    DepartmentId : int 
    ... }

example : The IdAttribute with the explicit IdKind.Assigned
open Soma.Core

type Department = 
  { [<Id(IdKind.Assigned)>]
    DepartmentId : int 
    ... }

The IdKind.Identity means that the primary key is assigned automatically by the database.
The primary key must be specified as identity.
Microsoft SQL Server and MySQL support the identity feature.

example : The IdAttribute with the IdKind.Identity
open Soma.Core

type Department = 
  { [<Id(IdKind.Identity)>]
    DepartmentId : int 
    ... }

The IdKind.Sequence means that the primary key is assigned automatically using the database sequence.
When the IdKind.Sequence is specifed to the field, the SequenceAttribute type must be specified, too.
The implicit sequence name is table name_SEQ. To explict, you can specify the sequence name to the Name property of the SequenceAttribute type.
The IncrementBy property of the SequenceAttribute type means the interval between sequence numbers. The value must be the database sequence definition.

example : The IdAttribute with the IdKind.Sequence
open Soma.Core

type Department = 
  { [<Id(IdKind.Sequence)>]
    [<Sequence>]
    DepartmentId : int 
    ... }

Version

You can specify the VersionAttribute type to the field mapped to the version column which is used for the optimistic lock.

The VersionKind.Incremented means that the version value is incremented automatically. To use the VersionKind.Incremented, the colum data type and the corresponding field type must be a numerical.
The no-arg VersionAttribute is same as the VersionAttribute(VersionKind.Incremented).
Following two examples have same semantic.

example : The VersionAttribute with the implicit VersionKind.Incremented
open Soma.Core

type Department = 
  { [<Version>]
    VersionNo : int 
    ... }

example : The VersionAttribute with the explicit VersionKind.Incremented
open Soma.Core

type Department = 
  { [<Version(VersionKind.Incremented)>]
    VersionNo : int 
    ... }

The VersionKind.Computed means that the version value is computed automatically by the database.
To use the VersionKind.Computed, following conditions are required.
  • The RDBMS must be Microsoft SQL Server. and the corresponding field type must be a numerical.
  • The colum data type must be rowversion or timestamp.
  • The corresponding field type must be a byte array.

example :The VersionAttribute with the VersionKind.Computed
open Soma.Core

type Department = 
  { [<Version(VersionKind.Computed)>]
    VersionNo : byte[] 
    ... }

Mapping between Stored Procedure and Record

A database store procedure(function) is mapped to a F# record type. The parameters of the store procedure are mapped to the fields of the record type.

DDL : ProcMultiParams Stored Procedure
CREATE PROCEDURE [dbo].[ProcMultiParams]
    @Param1 int,
    @Param2 int OUTPUT,
    @Param3 int OUTPUT
AS
BEGIN
    SET @Param2 = @Param2 + @Param1;
    SET @Param3 = @Param1;
END

F# : ProcMultiParams Record
open Soma.Core

type ProcMultiParams =
  { Param1 : int
    [<ProcedureParam(Direction = Direction.InputOutput)>]
    Param2 : int
    [<ProcedureParam(Direction = Direction.Output)>]
    Param3 : int }

A database store procedure is mapped to a reference type, too. The parameters of the stored procedure are mapped to the properties of the reference type which has a no-arg constructor. The propeties must have both getter and setter.

C# : ProcMultiParams Class
using Soma.Core;

public class ProcMultiParams
{
    public int Param1 { get; set; }
    [ProcedureParam(Direction = Direction.InputOutput)]
    public int Param2 { get; set; }
    [<ProcedureParam(Direction = Direction.Output)>]
    public int Param3 { get; set; }
}

Stored Procedure Definition

A stored procedure name is mapped to a record name implicitly.
You can specify the catalog name, the schema name and the stored procedure name with the ProcedureAttribute type.

example : The ProcedureAttribute
open Soma.Core

[<Procedure(Catalog = "CATALOG", Schema = "SCHEMA", Name = "PROC")>]
type ProcMultiParams = 
  { ... }

When the IsEnclosed property of the ProcedureAttribute type is set to true, the catalog name, the schema name and the stored procedure name are enclosed with special delimiters RDBMS supports.

example : The IsEnclosed property of the ProcedureAttribute
open Soma.Core

[<Procedure(IsEnclosed = true)>]
type ProcMultiParams = 
  { ... }

Stored Procedure Parameter Definition

A parameter name is mapped to a field name implicitly.
You can specify the parameter name with the ProcedureParamAttribute type.

example : The Name property of the ProcedureParamAttribute
open Soma.Core

type ProcMultiParams = 
  { [<ProcedureParam(Name = "P1")>]
    Param1: int 
    ... }

Parameter Direction

The Direction property of the ProcedureParamAttribute type accepts the Direction enum.
The Directoin enum indicates parameter direction. It has five members.

member name description
Input The parameter is an input parameter. This is default value.
InputOutput The parameter is capable of both input and output.
Output The parameter is an output parameter.
ReturnValue The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.
Result The parameter is a result set from an operation such as a stored procedure, built-in function, or user-defined function. The field type must be F# list or System.Collections.Generics.IList. The element type of F# list or System.Collections.Generics.IList must be a type such as a basic type, a record type, a reference type or a tuple type.


example : The Direction property of the ProcedureParamAttribute
open Soma.Core

type Employee =
  { EmployeeId : int
    EmployeeName : string }

type ProcDirections = 
  { [<ProcedureParam(Direction = Direction.Input)>]
    Param1: int 
    [<ProcedureParam(Direction = Direction.InputOutput)>]
    Param2: int 
    [<ProcedureParam(Direction = Direction.Output)>]
    Param3: int 
    [<ProcedureParam(Direction = Direction.ReturnValue)>]
    Param4: int 
    [<ProcedureParam(Direction = Direction.Result)>]
    Param5: int list
    [<ProcedureParam(Direction = Direction.Result)>]
    Param6: Employee list }

Parameter Order

The parameter order represented by the record field order .
To use a Direction.Result parameter, be careful the followings depending on RDBMS.

Microsoft SQL Server

The Direction.Result parameter order must be defined depending on the stored procedure SELECT order.

DDL : ProcResults Stored Procedure
CREATE PROCEDURE [dbo].[ProcResults]
    @EmployeeId int,
    @DepartmentId int
AS
BEGIN
    SELECT * FROM Employee WHERE EmployeeId > @EmployeeId ORDER BY EmployeeId;
    SELECT * FROM Department WHERE DepartmentId > @DepartmentId ORDER BY DepartmentId;
END

F# : ProcResults Record
type ProcResults =
  { EmployeeId : int
    DepartmentId : int
    [<ProcedureParam(Direction = Direction.Result)>]
    EmpList : Employee list
    [<ProcedureParam(Direction = Direction.Result)>]
    DeptList : Department list }

MySQL

The Direction.Result parameter order must be defined depending on the stored procedure SELECT order.

DDL : ProcResults Stored Procedure
CREATE PROCEDURE ProcResults(
  IN ParamEmployeeId INTEGER,
  IN ParamDepartmentId INTEGER)
BEGIN
    SELECT * FROM Employee WHERE EmployeeId > ParamEmployeeId ORDER BY EmployeeId;  
    SELECT * FROM Department WHERE DepartmentId > ParamDepartmentId ORDER BY DepartmentId;
END

F# : ProcResults Record
type ProcResults =
  { EmployeeId : int
    DepartmentId : int
    [<ProcedureParam(Direction = Direction.Result)>]
    EmpList : Employee list
    [<ProcedureParam(Direction = Direction.Result)>]
    DeptList : Department list }

Oracle Database

The Direction.Result parameter order must be defined depending on the stored procedure SYS_REFCURSOR parameter order.

DDL : ProcResults Stored Procedure
CREATE PROCEDURE ProcResults
(  ParamEmployeeId IN NUMERIC, 
  ParamDepartmentId IN NUMERIC,
  empCur OUT SYS_REFCURSOR, 
  deptCur OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN empCur FOR SELECT * FROM Employee WHERE EmployeeId > ParamEmployeeId ORDER BY EmployeeId;
  OPEN deptCur FOR SELECT * FROM Department WHERE DepartmentId > ParamDepartmentId ORDER BY DepartmentId;
END ProcResults;

F# : ProcResults Record
type ProcResults =
  { EmployeeId : int
    DepartmentId : int
    [<ProcedureParam(Direction = Direction.Result)>]
    EmpList : Employee list
    [<ProcedureParam(Direction = Direction.Result)>]
    DeptList : Department list }

When Input/InputOutput/Output Parameters are Undefined

You can define a Unit type field.

example : Defining a Unit type field
type ProcNoneParam = 
  { Unit : unit }

Db.call config { Unit = () }

Alternatively, you can define a field specified as Direction.ReturnValue parameter.

example : Defining a field specified as Direction.ReturnValue parameter
type ProcNoneParam = 
  { [<ProcedureParam(Direction = Direction.ReturnValue)>]
    Ret : int }

Db.call config { Ret = 0 }

Oracle UDT Types

Oracle UDT types are supported.

example : Mapping to an UDT type
type UdtParam = 
  { [<ProcedureParam(UdtTypeName = "MyUdt")>]
    Values : string array }

Type Mappings

Mappings between RDBMS Data Type and .NET Framework Type

Microsoft SQL Server 2008

SQL Server data type .NET Framework type description
tinyint System.Byte
smallint System.Int16
int System.Int32
bigint System.Int64
binary System.Byte[]
image System.Byte[]
varbinary System.Byte[]
rowversion System.Byte[]
timestamp System.Byte[]
bit System.Boolean
date System.DateTime
datetime System.DateTime
datetime2 System.DateTime
smalldatetime System.DateTime
datetimeoffset System.DateTimeOffset
time System.TimeSpan
decimal System.Decimal
numeric System.Decimal
money System.Decimal
smallmoney System.Decimal
float System.Double
real System.Single
varchar System.String
nvarchar System.String
ntext System.String
uniqueidentifier System.Guid

Microsoft SQL Server Compact 4.0

SQL Server Compact data type .NET Framework type description
tinyint System.Byte
smallint System.Int16
int System.Int32
bigint System.Int64
binary System.Byte[]
image System.Byte[]
varbinary System.Byte[]
rowversion System.Byte[]
timestamp System.Byte[]
bit System.Boolean
datetime System.DateTime
decimal System.Decimal
numeric System.Decimal
money System.Decimal
float System.Double
real System.Single
nvarchar System.String
ntext System.String
uniqueidentifier System.Guid

Oracle Database 11g

Oracle Database data type .NET Framework type description
raw System.Byte[]
timestamp System.DateTime
number System.Byte
number System.Int16
number System.Int32
number System.Int64
number System.Decimal
number System.Boolean The number "1" is mapped System.Boolean "true", others which doesn't contain NULL are mapped to "false".
float System.Double
float System.Single
varchar2 System.String
nvarchar2 System.String
interval day to second System.TimeSpan

SQLite 3

SQLite data type .NET Framework type description
integer System.Byte
integer System.Int16
integer System.Int32
integer System.Int64
numeric System.Decimal
numeric System.Boolean The numeric "1" is mapped System.Boolean "true", others which doesn't contain NULL are mapped to "false".
real System.Double
real System.Single
text System.String
text System.DateTime
blob System.Byte[]
blob System.Guid

Indirect Mappable .NET Framework Types

Enumeration Types, F# option Types and nullable Types are mapped to database data types indirectly.

Enumeration Types

When a enumeration type is used, the underlying type is mapped to the database data type.
In the following example, the underlying type of the JobKind enumeration type is the System.Int32. So the JobKind is mapped as if it is the System.Int32.

DDL
create table Person (
  PersonId int identity primary key,
  PersonName varchar(50) not null,
  JobKind int not null,
  VersionNo int not null
)
F#
type JobKind =
  | Salesman = 0
  | Manager = 1

type Person =
  { [<Id(IdKind.Identity)>]
    PersonId : int
    PersonName : string
    JobKind : JobKind
    [<Version>]
    VersionNo : int }

F# Option Types

When a option type is used, the underlying type is mapped to the database data type.
The value Some represents a not-null database value and the value None represents a database null.

DDL
create table Person (
  PersonId int identity primary key,
  PersonName varchar(50),
  JobKind int,
  VersionNo int
)
F#
type JobKind =
  | Salesman = 0
  | Manager = 1

type Person =
  { [<Id(IdKind.Identity)>]
    PersonId : int option
    PersonName : string option
    JobKind : JobKind option
    [<Version>]
    VersionNo : int option }

Nullable Types

When a nullable type is used, the underlying type is mapped to the database data type.
With nullable types, a field can represent a database null value.

DDL
create table Person (
  PersonId int identity primary key,
  PersonName varchar(50),
  JobKind int,
  VersionNo int
)
F#
type JobKind =
  | Salesman = 0
  | Manager = 1

type Person =
  { [<Id(IdKind.Identity)>]
    PersonId : int Nullable
    PersonName : string
    JobKind : JobKind Nullable
    [<Version>]
    VersionNo : int Nullable }

Last edited Mar 20, 2012 at 4:10 AM by toshihiro, version 11

Comments

No comments yet.