Reading a Scalar Value from a Row

These scenarios demonstrate how to read simple scalar values. For example, reading a single field from a row.

For an example of reading a scalar value from a stored procedure, see Basic Stored Procedures.

For an example of reading a row count, see Row Counts.

Scenario Prototype

public interface IScalarValueScenario
{
    int? GetDivisionKey(string divisionName);

    string GetDivisionName(int divisionKey);

    string? GetDivisionNameOrNull(int divisionKey);

    DateTimeOffset? GetLastReviewCycle(int divisionKey);

    int? GetMaxEmployees(int divisionKey);

    DateTime GetModifiedDate(int divisionKey);

    decimal? GetSalaryBudget(int divisionKey);

    TimeSpan? GetStartTime(int divisionKey);
}

ADO.NET

In ADO.NET, ExecuteScalar returns the first column of the first row in the resultset. Everything else is discarded.

public class ScalarValueScenario : SqlServerScenarioBase, IScalarValueScenario
{
    public ScalarValueScenario(string connectionString) : base(connectionString)
    { }

    public int? GetDivisionKey(string divisionName)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionName = @DivisionName;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionName", divisionName);

            var result = cmd.ExecuteScalar();
            //No results will return a `null`. A result containing a null will return a `DBNull.Value`
            if (result == null || result == DBNull.Value)
                return null;
            return (int)result;
        }
    }

    public string GetDivisionName(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);
            return (string)cmd.ExecuteScalar();
        }
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return null;
            return (string)result;
        }
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return null;
            return (DateTimeOffset)result;
        }
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return null;
            return (int)result;
        }
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            return (DateTime)cmd.ExecuteScalar();
        }
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return null;
            return (decimal)result;
        }
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@DivisionKey", divisionKey);

            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return null;
            return (TimeSpan)result;
        }
    }
}

Chain

public class ScalarValueScenario : IScalarValueScenario
{
    const string TableName = "HR.Division";
    readonly SqlServerDataSource m_DataSource;

    public ScalarValueScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int? GetDivisionKey(string divisionName)
    {
        return m_DataSource.From(TableName, new { divisionName }).ToInt32OrNull("DivisionKey").Execute();
    }

    public string GetDivisionName(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToString("DivisionName").Execute();
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToStringOrNull("DivisionName").Execute();
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToDateTimeOffsetOrNull("LastReviewCycle").Execute();
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToInt32OrNull("MaxEmployees").Execute();
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToDateTime("ModifiedDate").Execute();
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToDecimalOrNull("SalaryBudget").Execute();
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        return m_DataSource.From(TableName, new { divisionKey }).ToTimeSpanOrNull("StartTime").Execute();
    }
}

Dapper

public class ScalarValueScenario : ScenarioBase, IScalarValueScenario
{
    public ScalarValueScenario(string connectionString) : base(connectionString)
    { }

    public int? GetDivisionKey(string divisionName)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionName = @DivisionName;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<int?>(sql, new { divisionName });
    }

    public string GetDivisionName(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<string>(sql, new { divisionKey });
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<string?>(sql, new { divisionKey });
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<DateTimeOffset?>(sql, new { divisionKey });
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<int?>(sql, new { divisionKey });
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<DateTime>(sql, new { divisionKey });
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<decimal?>(sql, new { divisionKey });
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey = @DivisionKey;";
        using (var con = OpenConnection())
            return con.ExecuteScalar<TimeSpan?>(sql, new { divisionKey });
    }
}

DbConnector

public class ScalarValueScenario : ScenarioBase, IScalarValueScenario
{
    public ScalarValueScenario(string connectionString) : base(connectionString)
    { }

    public int? GetDivisionKey(string divisionName)
    {
        var sql = "SELECT DivisionKey FROM HR.Division WHERE DivisionName = @divisionName;";

        return DbConnector.Scalar<int?>(sql, new { divisionName }).Execute();
    }

    public string GetDivisionName(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<string>(sql, new { divisionKey }).Execute();
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<string?>(sql, new { divisionKey }).Execute();
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<DateTimeOffset?>(sql, new { divisionKey }).Execute();
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        var sql = "SELECT MaxEmployees FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<int?>(sql, new { divisionKey }).Execute();
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        var sql = "SELECT ModifiedDate FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<DateTime>(sql, new { divisionKey }).Execute();
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        var sql = "SELECT SalaryBudget FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<decimal?>(sql, new { divisionKey }).Execute();
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        var sql = "SELECT StartTime FROM HR.Division WHERE DivisionKey = @divisionKey;";

        return DbConnector.Scalar<TimeSpan?>(sql, new { divisionKey }).Execute();
    }
}

Entity Framework 6

public class ScalarValueScenario : IScalarValueScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public ScalarValueScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int? GetDivisionKey(string divisionName)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionName == divisionName).Select(d => (int?)d.DivisionKey).SingleOrDefault();
    }

    public string GetDivisionName(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.DivisionName!).Single();
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.DivisionName!).SingleOrDefault();
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.LastReviewCycle).SingleOrDefault();
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.MaxEmployees).SingleOrDefault();
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.ModifiedDate).Single();
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.SalaryBudget).SingleOrDefault();
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.StartTime).SingleOrDefault();
    }
}

Entity Framework Core

public class ScalarValueScenario : IScalarValueScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public ScalarValueScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int? GetDivisionKey(string divisionName)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionName == divisionName).Select(d => (int?)d.DivisionKey).SingleOrDefault();
    }

    public string GetDivisionName(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.DivisionName!).Single();
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.DivisionName!).SingleOrDefault();
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.LastReviewCycle).SingleOrDefault();
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.MaxEmployees).SingleOrDefault();
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.ModifiedDate).Single();
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.SalaryBudget).SingleOrDefault();
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        using (var context = CreateDbContext())
            return context.Division.Where(d => d.DivisionKey == divisionKey).Select(d => d.StartTime).SingleOrDefault();
    }
}

LINQ to DB

public class ScalarValueScenario : IScalarValueScenario
{
    public int? GetDivisionKey(string divisionName)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionName == divisionName).SingleOrDefault()?.DivisionKey;
    }

    public string GetDivisionName(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.DivisionName!;
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.DivisionName;
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.LastReviewCycle;
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.MaxEmployees;
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).Single().ModifiedDate;
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.SalaryBudget;
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        using (var db = new OrmCookbook())
            return db.Division.Where(d => d.DivisionKey == divisionKey).SingleOrDefault()?.StartTime;
    }
}

LLBLGen Pro

public class ScalarValueScenario : IScalarValueScenario
{
    public int? GetDivisionKey(string divisionName)
    {
        using(var adapter = new DataAccessAdapter())
        {
            // As the field in the projection isn't nullable, LLBLGen Pro will normally return the default value
            // for when the field is null. Here, however this gives a problem, so we have to
            // cast the field to int? to make FirstOrDefault return a nullable type.  
            // A workaround could have been to project to the type first and use that as a check, however that's 
            // less efficient. 
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionName == divisionName)
                                            .Select(d => d.DivisionKey as int?).FirstOrDefault();
        }
    }


    public string GetDivisionName(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.DivisionName).FirstOrDefault();
        }
    }


    public string? GetDivisionNameOrNull(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.DivisionName).FirstOrDefault();
        }
    }


    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.LastReviewCycle).FirstOrDefault();
        }
    }


    public int? GetMaxEmployees(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.MaxEmployees).FirstOrDefault();
        }
    }


    public DateTime GetModifiedDate(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.ModifiedDate).FirstOrDefault();
        }
    }


    public decimal? GetSalaryBudget(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.SalaryBudget).FirstOrDefault();
        }
    }


    public TimeSpan? GetStartTime(int divisionKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Division.Where(d => d.DivisionKey == divisionKey)
                                            .Select(d => d.StartTime).FirstOrDefault();
        }
    }
}

NHibernate

TODO

RepoDb

public class ScalarValueScenario : DbRepository<SqlConnection>,
    IScalarValueScenario
{
    public ScalarValueScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int? GetDivisionKey(string divisionName)
    {
        var sql = "SELECT DivisionKey FROM [HR].[Division] WHERE (DivisionName = @DivisionName);";

        return ExecuteScalar<int?>(sql, new { divisionName });
    }

    public string GetDivisionName(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<string>(sql, new { divisionKey });
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        var sql = "SELECT DivisionName FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<string>(sql, new { divisionKey });
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        var sql = "SELECT LastReviewCycle FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<DateTimeOffset?>(sql, new { divisionKey });
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        var sql = "SELECT MaxEmployees FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<int?>(sql, new { divisionKey });
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        var sql = "SELECT ModifiedDate FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<DateTime>(sql, new { divisionKey });
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        var sql = "SELECT SalaryBudget FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<decimal?>(sql, new { divisionKey });
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        var sql = "SELECT StartTime FROM [HR].[Division] WHERE (DivisionKey = @DivisionKey);";

        return ExecuteScalar<TimeSpan?>(sql, new { divisionKey });
    }
}

ServiceStack

public class ScalarValueScenario : IScalarValueScenario
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    
    public ScalarValueScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int? GetDivisionKey(string divisionName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, int?>(d => d.Id, d => d.DivisionName == divisionName);
    }

    public string GetDivisionName(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, string>(d => d.DivisionName, d => d.Id == divisionKey);
    }

    public string? GetDivisionNameOrNull(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, string>(d => d.DivisionName, d => d.Id == divisionKey);
    }

    public DateTimeOffset? GetLastReviewCycle(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, DateTimeOffset?>(d => d.LastReviewCycle, d => d.Id == divisionKey);
    }

    public int? GetMaxEmployees(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, int?>(d => d.MaxEmployees, d => d.Id == divisionKey);
    }

    public DateTime GetModifiedDate(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, DateTime>(d => d.ModifiedDate, d => d.Id == divisionKey);
    }

    public decimal? GetSalaryBudget(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, decimal?>(d => d.SalaryBudget, d => d.Id == divisionKey);
    }

    public TimeSpan? GetStartTime(int divisionKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.Scalar<Division, TimeSpan?>(d => d.StartTime, d => d.Id == divisionKey);
    }
}