LoginSignup
5
6

More than 5 years have passed since last update.

DapperでSQLServerのTime型フィールドを取得する方法

Posted at

こんなテーブルからデータ取得したい場合、

--始業と終業時刻マスタ
CREATE TABLE [work_time_range](
    [id] [nvarchar](50) NOT NULL,
    [start_time] [time](7) NULL,
    [end_time] [time](7) NULL
)

--従業員マスタ
CREATE TABLE [employee](
    [id] [nvarchar](50) NOT NULL,
    [name] [nvarchar](50) NOT NULL
)

time型フィールドはCONVERT関数つかってDateTime型に変換してやる必要がある。

public class WorkTimeRange
{
    public string Id { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

public class Employee
{
    public string Id { get; set; }
    public string Name { get; set; }
    public WorkTimeRange TimeRange { get; set; }
}

public interface IEmployeeService
{
    IList<Employee> GetAll();
}

public class EmployeeService : IEmployeeService
{
    private ConnectionStringSettings _conStr;

    public EmployeeService()
    {
        this._conStr = WebConfigurationManager.ConnectionStrings["DefaultConnection"];
    }

    public IList<Employee> GetAll()
    {
        using (var con = new SqlConnection(_conStr.ConnectionString))
        {
            con.Open();

            var sql = @"
select 
t1.*, 
CONVERT(DATETIME, t2.start_time) AS start_time,
CONVERT(DATETIME, t2.end_time) AS end_time
from 
employee t1 
left outer join 
work_time_range t2 
on 
t1.id = t2.id 
order by 
t1.id";
            var employees = con.Query<Employee, WorkTimeRange, Employee>(
                sql
                , (e, w) => {
                    e.TimeRange = w;
                    return e; 
                }
                , splitOn: "id,start_time");
            return employees.ToArray();
        }
    }
}

※DBはSQLServer2012を使用。試してないけどデータベース毎に個別対応が必要と思われます。

5
6
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
6