This project is read-only.

Table of Contents

Overview

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

The difference between Soma.Core.Db and Soma.Core.LocalDb is the way of connection handling. Soma.Core.Db class 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 classes. 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 class is suited for the case.

In this page, sample code are described in C#. In the Query and Execute methods of Soma.Core.Db and Soma.Core.LocalDb classes, 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 class mainly.

Soma.Core.Db

The API of Soma.Core.Db class is following:

API
public class Db : IDb
{
    // Constructors
    public Db(IDbConfig config);

    // Methods
    public override void Call<T>(T procedure) where T: class;
    public override void Delete<T>(T entity) where T: class;
    public override void Delete<T>(T entity, DeleteOpt opt) where T: class;
    public override int Execute(string sql);
    public override int Execute(string sql, object condition);
    public override T ExecuteReader<T>(Func<DbDataReader, T> handler, string  sql);
    public override T ExecuteReader<T>(Func<DbDataReader, T> handler, string  sql, object condition);
    public override T Find<T>(object id) where T: class, new();
    public override T FindWithVersion<T>(object id, object version) where T: class, new();
    public override void Insert<T>(T entity) where T: class;
    public override void Insert<T>(T entity, InsertOpt opt) where T: class;
    public override IList<T> Paginate<T>(string sql, long offset, long limit);
    public override IList<T> Paginate<T>(string sql, long offset, long limit, object condition);
    public override Tuple<IList<T>, long> PaginateAndCount<T>(string sql, long offset, long limit);
    public override Tuple<IList<T>, long> PaginateAndCount<T>(string sql, long offset, long limit, object condition);
    public override IEnumerable<T> PaginateOnDemand<T>(string sql, long offset, long limit);
    public override IEnumerable<T> PaginateOnDemand<T>(string sql, long offset, long limit, object condition);
    public override IList<T> Query<T>(string sql);
    public override IList<T> Query<T>(string sql, object condition);
    public override IEnumerable<T> QueryOnDemand<T>(string sql);
    public override IEnumerable<T> QueryOnDemand<T>(string sql, object condition);
    public override T TryFind<T>(object id) where T: class, new();
    public override T TryFindWithVersion<T>(object id, object version) where T: class, new();
    public override void Update<T>(T entity) where T: class;
    public override void Update<T>(T entity, UpdateOpt opt) where T: class;

    // Properties
    public override IDbConfig DbConfig { get; }
}

The typical usage is following:

The typical usage
var db = new Db(new Config());

using (var tx = new TransactionScope())
{
    var emp = db.Find<Employee>(1);
    emp.EmployeeName = "Hoge";
    db.Update(emp);
    db.Delete(emp);

    tx.Complete();
}

The points of this example are followings:
  • The instance of Soma.Core.Db is instantiated specifing with the Configuration instance.
  • The instance of Soma.Core.Db is thread-safe. Multiple threads can access it at same time.
  • The transaction are controllable with System.Transactions.TransactionScope.
  • The connection handling is managed automatically.

Soma.Core.LocalDb

The API of Soma.Core.LocalDb class is following:

API
public class LocalDb : ILocalDb
{
    // Constructors
    public LocalDb(IDbConfig config);

    // Methods
    public override void Call<T>(DbConnection connection, T procedure) where T: class;
    public override DbConnection CreateConnection();
    public override void Delete<T>(DbConnection connection, T entity) where T: class;
    public override void Delete<T>(DbConnection connection, T entity, DeleteOpt opt) where T: class;
    public override int Execute(DbConnection connection, string sql);
    public override int Execute(DbConnection connection, string sql, object condition);
    public override T ExecuteReader<T>(DbConnection connection, Func<DbDataReader, T> handler, string  sql);
    public override T ExecuteReader<T>(DbConnection connection, Func<DbDataReader, T> handler, string  sql, object condition);
    public override T Find<T>(DbConnection connection, object id) where T: class, new();
    public override T FindWithVersion<T>(DbConnection connection, object id, object version) where T: class, new();
    public override void Insert<T>(DbConnection connection, T entity) where T: class;
    public override void Insert<T>(DbConnection connection, T entity, InsertOpt opt) where T: class;
    public override IList<T> Paginate<T>(DbConnection connection, string sql, long offset, long limit);
    public override IList<T> Paginate<T>(DbConnection connection, string sql, long offset, long limit, object condition);
    public override Tuple<IList<T>, long> PaginateAndCount<T>(DbConnection connection, string sql, long offset, long limit);
    public override Tuple<IList<T>, long> PaginateAndCount<T>(DbConnection connection, string sql, long offset, long limit, object condition);
    public override IEnumerable<T> PaginateOnDemand<T>(DbConnection connection, string sql, long offset, long limit);
    public override IEnumerable<T> PaginateOnDemand<T>(DbConnection connection, string sql, long offset, long limit, object condition);
    public override IList<T> Query<T>(DbConnection connection, string sql);
    public override IList<T> Query<T>(DbConnection connection, string sql, object condition);
    public override IEnumerable<T> QueryOnDemand<T>(DbConnection connection, string sql);
    public override IEnumerable<T> QueryOnDemand<T>(DbConnection connection, string sql, object condition);
    public override T TryFind<T>(DbConnection connection, object id) where T: class, new();
    public override T TryFindWithVersion<T>(DbConnection connection, object id, object version) where T: class, new();
    public override void Update<T>(DbConnection connection, T entity) where T: class;
    public override void Update<T>(DbConnection connection, T entity, UpdateOpt opt) where T: class;

    // Properties
    public override IDbConfig DbConfig { get; }
}

By comparison with Soma.Core.Db class, Soma.Core.LocalDb class has following differences.
  • Has the CreateConnection method.
  • All methods except the CreateConnection method have first parameter and its type is System.Data.Common.DbConnection.

The typical usage is following:

The typical usage
var db = new LocalDb(new Config());

using (var tx = new TransactionScope())
using (var con = db.CreateConnection())
{
    var emp = db.Find<Employee>(con, 1);
    emp.EmployeeName = "Hoge";
    db.Update(con, emp);
    db.Delete(con, emp);

    tx.Complete();
}

The points of this example are followings:
  • The instance of Soma.Core.LocalDbis instantiated specifing with the Configuration instance.
  • The instance of Soma.Core.LocalDb is thread-safe. Multiple threads can access it at same time.
  • The transaction are controllable with System.Transactions.TransactionScope.
  • The connection is created by the CreateConnection method and closed by the client program.
  • The created connection is passed to other methods as parameter.
  • The client program has to manage that only one connection is enlisted to one transaction.

Db Constructor

The constructor accepts the parameter whose type is Soma.Core.IDbConfig.

Example : The typical usage
internal class MyConfig : MsSqlConfig
{
    public override string ConnectionString { get { return "Data Source=.;Initial Catalog=Soma.Tutorial;Integrated Security=True"; } }
}

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

DbConfig Property

The DbConfig property provides the instance passed with constructor.

Example : The typical usage
var dbConfig = db.DbConfig;

Query Method

The Query method queries rows as IList<'T> with the specfied SELECT SQL.
The generic parameter 'T may be the primitive type, the POCO type, the tuple type or the dynamic type.

Example : The generic parameter 'T is the string type
var empList = 
  db.Query<string>(
    "select EmployeeName from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empList = 
  db.Query<Employee>(
    "select * from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
var empList = 
  db.Query<Tuple<string, decimal>>(
    "select EmployeeName, Salary from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of Department and Employee POCO types
var deptEmpList = 
  db.Query<Tuple<Department, Employee>>(
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and Employee POCO types
var empList = 
  db.Query<Tuple<string, Employee>>(
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the dynamic type
var empList = 
  db.Query<dynamic>(
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0",
    new { salary = 1000M} );
foreach (emp in empList) {
  Console.WriteLine("{0}, {1}", emp.EmployeeId, emp.EmployeeName);
}

QueryOnDemand Method

The QueryOnDemand method queries rows as IEnumerable<'T> with the specfied SELECT SQL. The DB access is differed until IEnumerable<'T> is evaluated.
The generic parameter 'T may be the primitive type, the POCO type, the tuple type or the dynamic type.

Example : The generic parameter 'T is the string type
var empSeq = 
  db.QueryOnDemand<string>(
    "select EmployeeName from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empSeq = 
  db.QueryOnDemand<Employee>(
    "select * from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
var empSeq = 
  db.QueryOnDemand<Tuple<string, decimal>>(
    "select EmployeeName, Salary from Employee where Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of Department and Employee POCO types
var deptEmpSeq = 
  db.QueryOnDemand<Tuple<Department, Employee>>(
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and Employee POCO types
var empSeq = 
  db.QueryOnDemand<Tuple<string, Employee>>(
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0",
    new { salary = 1000M} );

Example : The generic parameter 'T is the dynamic type
var empSeq = 
  db.QueryOnDemand<dynamic>(
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0",
    new { salary = 1000M} );
foreach (emp in empSeq) {
  Console.WriteLine("{0}, {1}", emp.EmployeeId, emp.EmployeeName);
}

Pagenate Method

The Pagenate method queries rows as IList<'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 POCO type, the tuple type or the dynamic type.

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

C# code
var empList = 
  db.Pagenate<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );
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
var empList = 
  db.Pagenate<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empList = 
  db.Pagenate<Employee>(
    "select * from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
var empList = 
  db.Pagenate<Tuple<string, decimal>>(
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of Department and Employee POCO types
var deptEmpList = 
  db.Pagenate<Tuple<Department, Employee>>(
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and Employee POCO types
var empList = 
  db.Pagenate<Tuple<string, Employee>>(
    "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",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the dynamic type
var empList = 
  db.Pagenate<dynamic>(
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );
foreach (emp in empList ) {
  Console.WriteLine("{0}, {1}", emp.EmployeeId, emp.EmployeeName);
}

PagenateOnDemand Method

The PagenateOnDemand method queries rows as IEnumerable<'T> with the specfied SELECT SQL. The specified SQL is transformed for pagination and then issued. The DB access is differed until IEnumerable<'T> is evaluated.
The generic parameter 'T may be the primitive type, the POCO type, the tuple type or the dynamic type.

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

C# code
var empSeq = 
  db.PagenateOnDemand<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );
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
var empSeq = 
  db.PagenateOnDemand<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empSeq = 
  db.PagenateOnDemand<Employee>(
    "select * from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and decimal types
var empSeq = 
  db.PagenateOnDemand<Tuple<string, decimal>>(
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of Department and Employee POCO types
var deptEmpSeq = 
  db.PagenateOnDemand<Tuple<Department, Employee>>(
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and Employee POCO types
var empSeq = 
  db.PagenateOnDemand<Tuple<string, Employee>>(
    "select d.DepartmentName, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by Salary"
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the dynamic type
var empSeq = 
  db.PagenateOnDemand<dynamic>(
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0",
    10,
    50,
    new { salary = 1000M} );
foreach (emp in empSeq) {
  Console.WriteLine("{0}, {1}", emp.EmployeeId, emp.EmployeeName);
}

PaginateAndCount Method

The PaginateAndCount method 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 POCO type, the tuple type or the dynamic type.

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

C# code
var empListAndCount = 
  db.PaginateAndCount<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );
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
var empListAndCount = 
  db.PaginateAndCount<string>(
    "select EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empListAndCount = 
  db.PaginateAndCount<Employee>(
    "select * from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the Employee POCO type
var empListAndCount = 
  db.PaginateAndCount<Tuple<string, decimal>>(
    "select EmployeeName, Salary from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of Department and Employee POCO types
var deptEmpListAndcount = 
  db.PaginateAndCount<Tuple<Department, Employee>>(
    "select d.*, e.* from Employee e inner join Department d on (e.DepartmentId = d.DepartmentId) where e.Salary > /* salary */0 order by e.Salary",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the tuple type which consists of string and Employee POCO types
var empListAndCount = 
  db.PaginateAndCount<Tuple<string, Employee>>(
    "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",
    10,
    50,
    new { salary = 1000M} );

Example : The generic parameter 'T is the dynamic type
var empListAndCount = 
  db.PaginateAndCount<dynamic>(
    "select EmployeeId, EmployeeName from Employee where Salary > /* salary */0 order by Salary",
    10,
    50,
    new { salary = 1000M} );
foreach (emp in empListAndCount.Item1) {
  Console.WriteLine("{0}, {1}", emp.EmployeeId, emp.EmployeeName);
}

Execute Method

The Execute method executes the specified SQL.

Example : Deleting multiple rows
var rows = 
  db.Execute(
    "delete from Employee where Salary > /* salary */0",
    new { salary = 1000M } )

ExecuteReader Method

The ExecuteReader method executes the SQL and handles the reader.

Example : Loading to the DataTable
var table = 
  db.ExecuteReader(
    reader =>
    {
      var t = new DataTable();
      t.Load(reader);
      return t;
    },
    "select * from Employee where Salary > /* salary */0",
    new { salary = 1000M } );

Find Method

The Find method gets the entity with primary keys.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute.

Example : The typical usage
var emp = db.Find<Employee>(1);

When multiple primary keys are defined, pass primary key values as IEnumerable (ex. array).

Example : Multiple primary keys
var emp = db.Find<Employee>(new []{1, "abc"});

When the entity is not found, EntityNotFoundException is thrown.

TryFind Method

The TryFind method trys to get the entity with the primary keys.
If the entity found, the entity is returned, otherwise null is returned.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute.

Example : The typical usage
var emp = db.TryFind<Employee>(1);

When multiple primary keys are defined, pass primary key values as IEnumerable (ex. array).

Example : Multiple primary keys
var emp = db.TryFind<Employee>(new []{ 1, "abc" });

FindWithVersion Method

The FindWithVersion method gets the entity with primary keys and checks the version.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute and property annotated with Soma.Core.VersionAttribute.

Example : The typical usage
var emp = db.FindWithVersion<Employee>(1, 0);

When multiple primary keys are defined, pass primary key values as IEnumerable (ex. array).

Example : Multiple primary keys
var emp = db.FindWithVersion<Employee>(new []{ 1, "abc" }, 0);

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

When the versions are different, OptimisticLockException is thrown.

TryFindWithVersion Method

The TryFindWithVersion method trys to get the entity with primary keys and checks the version.
If the entity found, the entity is returned, otherwise null is returned.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute and property annotated with Soma.Core.VersionAttribute.

Example : The typical usage
var emp = db.TryFindWithVersion<Employee>(1, 0);

When multiple primary keys are defined, pass primary key values as IEnumerable (ex. array).

Example : Multiple primary keys
var emp = db.TryFindWithVersion<Employee>(new []{ 1, "abc" }, 0);

When the versions are different, OptimisticLockException is thrown.

Insert Method

The Insert method inserts the row mapped with the entity.
The generic parameter 'T must be the POCO type.
When a unique constraint violation is occurred, UniqueConstraintException is thrown.

Example : The typical usage
var emp = new Employee { EmployeeId = 0, EmployeeName = "Hoge", VersionNo = 0 };
db.Insert(emp);

With Soma.Core.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
var emp = new Employee { EmployeeId = 0, EmployeeName = "Hoge", Age = 21, Salary = 200000M, VersionNo = 0 };
db.Insert(emp, new InsertOpt { Exclude = new [] {"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
var emp = new Employee { EmployeeId = 0, EmployeeName = "Hoge", Age = 21, Salary = 200000M, VersionNo = 0 };
db.Insert(emp, new InsertOpt { Include = new [] {"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
var emp = new Employee { EmployeeId = 0, EmployeeName = "Hoge", Age = null, Salary = null, VersionNo = 0 };
db.Insert(emp, new InsertOpt { ExcludeNull = true } );

Update Method

The Update method updates the row mapped with the entity.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute.
When a unique constraint violation is occurred, UniqueConstraintException is thrown.
When the updated row count is 0 and the POCO type has a property annotated with Soma.Core.VersionAttribute, OptimisticLockException is thrown.
When the updated row count is 0 and the POCO type has not a property annotated with Soma.Core.VersionAttribute, NoAffectedRowException is thrown.

Example : The typical usage
var emp = db.Find<Employee>(1);
emp.EmployeeName = "Hoge";
db.Update(emp);

With Soma.Core.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
var emp = new Employee { EmployeeId = 1, EmployeeName = "Hoge", Age = 21, Salary = 200000M, VersionNo = 1 };
db.Update(emp, new UpdateOpt { Exclude = new [] {"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
var emp = new Employee { EmployeeId = 1, EmployeeName = "Hoge", Age = 21, Salary = 200000M, VersionNo = 1 };
db.Update(emp, new UpdateOpt { Include = new [] {"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
var emp = new Employee { EmployeeId = 1, EmployeeName = "Hoge", Age = null, Salary = null, VersionNo = 1 };
db.Update(emp, new UpdateOpt { ExcludeNull = true } );

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

Example : Ignoring version
var emp = new Employee { EmployeeId = 1, EmployeeName = "Hoge", Age = null, Salary = null, VersionNo = 1 };
db.Update(emp, new UpdateOpt { IgnoreVersion = true } );

Delete Method

The Delete method deletes the row mapped with entity.
The generic parameter 'T must be the POCO type which has properties annotated with Soma.Core.IdAttribute.
When the deleted row count is 0 and the POCO type has a property annotated with Soma.Core.VersionAttribute, OptimisticLockException is thrown.
When the deleted row count is 0 and the POCO type has not a property annotated with Soma.Core.VersionAttribute, NoAffectedRowException is thrown.

Example : The typical usage
var emp = db.Find<Employee>(1);
db.Delete(emp);

With Soma.Core.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
var emp = db.Find<Employee>(1);
db.Delete(emp, new DeleteOpt { IgnoreVersion = true } );

Call Method

The Call method calls the stored procedure.
The generic parameter 'T must be the POCO type which represents the stored procedure. About the mapping between the stored procedure and the POCO type, see Mapping.

Example : The typical usage
var procedure = new ProcMultiParams { Param1 = 1, Param2= 2, Param3 = 0 };
db.Call(procedure);

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

Last edited Aug 21, 2011 at 3:01 AM by toshihiro, version 15

Comments

No comments yet.