Row Counts

These scenarios demonstrate how to get a row count from a table.

Scenario Prototype

public interface IRowCountScenario<TEmployeeSimple>
   where TEmployeeSimple : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Count of the employees, filtered by last name.
    /// </summary>
    /// <param name="lastName">The last name.</param>
    int EmployeeCount(string lastName);

    /// <summary>
    /// Count of the employees, filtered by last name.
    /// </summary>
    int EmployeeCount();

    /// <summary>
    /// Insert a collection of Employee rows.
    /// </summary>
    void InsertBatch(IList<TEmployeeSimple> employees);
}

ADO.NET

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

    public class RowCountScenario : SqlServerScenarioBase, IRowCountScenario<EmployeeSimple>
    {
        public RowCountScenario(string connectionString) : base(connectionString)
        { }

        public int EmployeeCount(string lastName)
        {
            const string sql = "SELECT COUNT(*) FROM HR.Employee e WHERE e.LastName = @LastName";
            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@LastName", lastName);
                return (int)cmd.ExecuteScalar();
            }
        }

        public int EmployeeCount()
        {
            const string sql = "SELECT COUNT(*) FROM HR.Employee e";
            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
                return (int)cmd.ExecuteScalar();
        }

        public void InsertBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");

            for (var i = 0; i < employees.Count; i++)
            {
                if (i != 0)
                    sql.AppendLine(",");
                sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
            }
            sql.AppendLine(";");

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql.ToString(), con))
            {
                for (var i = 0; i < employees.Count; i++)
                {
                    cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
                    cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
                    cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
                    cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
                }
                cmd.ExecuteNonQuery();
            }
        }
    }

Chain

public class RowCountScenario : IRowCountScenario<EmployeeSimple>
{
    readonly SqlServerDataSource m_DataSource;

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

    public int EmployeeCount(string lastName)
    {
        return (int)m_DataSource.From<EmployeeSimple>(new { lastName }).AsCount().Execute();
    }

    public int EmployeeCount()
    {
        return (int)m_DataSource.From<EmployeeSimple>().AsCount().Execute();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).Execute();
    }
}

Dapper

    public class RowCountScenario : ScenarioBase, IRowCountScenario<EmployeeSimple>
    {
        public RowCountScenario(string connectionString) : base(connectionString)
        { }

        public int EmployeeCount(string lastName)
        {
            const string sql = "SELECT COUNT(*) FROM HR.Employee e WHERE e.LastName = @LastName";
            using (var con = OpenConnection())
                return con.ExecuteScalar<int>(sql, new { lastName });
        }

        public int EmployeeCount()
        {
            const string sql = "SELECT COUNT(*) FROM HR.Employee e";
            using (var con = OpenConnection())
                return con.ExecuteScalar<int>(sql);
        }

        public void InsertBatch(IList<EmployeeSimple> employees)
        {
            if (employees == null || employees.Count == 0)
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            const string sql = @"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES (@FirstName, @MiddleName, @LastName, @Title, @OfficePhone, @CellPhone, @EmployeeClassificationKey)";
            using (var con = OpenConnection())
                con.Execute(sql, employees);
        }
    }

DbConnector

public class RowCountScenario : ScenarioBase, IRowCountScenario<EmployeeSimple>
{
    public RowCountScenario(string connectionString) : base(connectionString)
    { }

    public int EmployeeCount(string lastName)
    {
        const string sql = "SELECT COUNT(*) FROM HR.Employee e WHERE e.LastName = @lastName";

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

    public int EmployeeCount()
    {
        const string sql = "SELECT COUNT(*) FROM HR.Employee e";

        return DbConnector.Scalar<int>(sql).Execute();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        //Best approach for unlimited inserts since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        DbConnector.Build<int?>(
                sql: @$"INSERT INTO {EmployeeSimple.TableName}
                (
                    CellPhone,
                    EmployeeClassificationKey,
                    FirstName,
                    LastName,
                    MiddleName,
                    OfficePhone,
                    Title
                ) 
                VALUES (
                    @{nameof(EmployeeSimple.CellPhone)},
                    @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                    @{nameof(EmployeeSimple.FirstName)},
                    @{nameof(EmployeeSimple.LastName)},
                    @{nameof(EmployeeSimple.MiddleName)},
                    @{nameof(EmployeeSimple.OfficePhone)},
                    @{nameof(EmployeeSimple.Title)}
                )",
                param: employees[0],
                onExecute: (int? result, IDbExecutionModel em) =>
                {
                    //Set the command
                    DbCommand command = em.Command;

                    //Execute first row.
                    em.NumberOfRowsAffected = command.ExecuteNonQuery();

                    //Set and execute remaining rows.
                    for (int i = 1; i < employees.Count; i++)
                    {
                        var emp = employees[i];

                        command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
                        command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
                        command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;

                        em.NumberOfRowsAffected += command.ExecuteNonQuery();
                    }

                    return em.NumberOfRowsAffected;
                }
            )
            .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
            .Execute();
    }
}

Entity Framework 6

public class RowCountScenario : IRowCountScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public int EmployeeCount(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee.Where(e => e.LastName == lastName).Count();
    }

    public int EmployeeCount()
    {
        using (var context = CreateDbContext())
            return context.Employee.Count();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        using (var context = CreateDbContext())
        {
            context.Employee.AddRange(employees);
            context.SaveChanges();
        }
    }
}

Entity Framework Core

public class RowCountScenario : IRowCountScenario<Employee>
{
    private Func<OrmCookbookContext> CreateDbContext;

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

    public int EmployeeCount(string lastName)
    {
        using (var context = CreateDbContext())
            return context.Employee.Where(e => e.LastName == lastName).Count();
    }

    public int EmployeeCount()
    {
        using (var context = CreateDbContext())
            return context.Employee.Count();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        using (var context = CreateDbContext())
        {
            context.Employee.AddRange(employees);
            context.SaveChanges();
        }
    }
}

LINQ to DB

public class RowCountScenario : IRowCountScenario<Employee>
{
    public int EmployeeCount(string lastName)
    {
        using (var db = new OrmCookbook())
            return db.Employee.Where(e => e.LastName == lastName).Count();
    }

    public int EmployeeCount()
    {
        using (var db = new OrmCookbook())
            return db.Employee.Count();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var db = new OrmCookbook())
        {
            foreach (var employee in employees)
                db.Insert(employee);
        }
    }
}

LLBLGen Pro

public class RowCountScenario : IRowCountScenario<EmployeeEntity>
{
    public int EmployeeCount(string lastName)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).Employee.Count(e => e.LastName == lastName);
        }
    }


    public int EmployeeCount()
    {
        using(var adapter = new DataAccessAdapter())
        {
            // Use queryspec for a change... 
            return adapter.FetchScalar<int>(new QueryFactory().Employee.Select(Functions.CountRow()));
        }
    }


    public void InsertBatch(IList<EmployeeEntity> employees)
    {
        using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
        {
            using(var adapter = new DataAccessAdapter())
            {
                // 0 switches off batching
                adapter.BatchSize = employees?.Count ?? 0;
                adapter.SaveEntityCollection(toInsert);
            }
        }
    }
}

NHibernate

public class RowCountScenario : IRowCountScenario<Employee>
{
    readonly ISessionFactory m_SessionFactory;

    public RowCountScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int EmployeeCount(string lastName)
    {
        using (var session = m_SessionFactory.OpenSession())
            return session.Query<Employee>().Where(e => e.LastName == lastName).Count();
    }

    public int EmployeeCount()
    {
        using (var session = m_SessionFactory.OpenSession())
            return session.Query<Employee>().Count();
    }

    public void InsertBatch(IList<Employee> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        using (var session = m_SessionFactory.OpenSession())
        {
            foreach (var employee in employees)
                session.Save(employee);
            session.Flush();
        }
    }
}

RepoDb

public class RowCountScenario : BaseRepository<EmployeeSimple, SqlConnection>,
    IRowCountScenario<EmployeeSimple>
{
    public RowCountScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int EmployeeCount(string lastName)
    {
        return (int)Count(e => e.LastName == lastName);
    }

    public int EmployeeCount()
    {
        return (int)CountAll();
    }

    public void InsertBatch(IList<EmployeeSimple> employees)
    {
        if (employees == null || employees.Count == 0)
            throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

        InsertAll(employees);
    }
}

ServiceStack

public class RowCountScenario : IRowCountScenario<Employee>
{
    private IDbConnectionFactory _dbConnectionFactory;

    public RowCountScenario(IDbConnectionFactory dbConnectionFactory)
    {
        this._dbConnectionFactory = dbConnectionFactory;
    }

    public int EmployeeCount(string lastName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int) db.Count<Employee>(e => e.LastName == lastName);
        }
    }

    public int EmployeeCount()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int) db.Count<Employee>();
        }
    }

    public void InsertBatch(IList<Employee> employees)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.InsertAll(employees);
        }
    }
}