Single Model CRUD
This scenario covers the basic Create-Read-Update-Delete operations on a model that represents a single row in the database.
Scenario Prototype
public interface ISingleModelCrudScenario<TEmployeeClassification>
where TEmployeeClassification : class, IEmployeeClassification, new()
{
/// <summary>
/// Create a new EmployeeClassification row, returning the new primary key.
/// </summary>
int Create(TEmployeeClassification classification);
/// <summary>
/// Delete a EmployeeClassification row using an object.
/// </summary>
/// <remarks>Behavior when row doesn't exist is not defined.</remarks>
void Delete(TEmployeeClassification classification);
/// <summary>
/// Delete a EmployeeClassification row using its primary key.
/// </summary>
/// <remarks>Behavior when row doesn't exist is not defined.</remarks>
void DeleteByKey(int employeeClassificationKey);
/// <summary>
/// Gets an EmployeeClassification row by its name. Assume the name is unique.
/// </summary>
TEmployeeClassification? FindByName(string employeeClassificationName);
/// <summary>
/// Gets all EmployeeClassification rows.
/// </summary>
IList<TEmployeeClassification> GetAll();
/// <summary>
/// Gets an EmployeeClassification row by its primary key.
/// </summary>
TEmployeeClassification? GetByKey(int employeeClassificationKey);
/// <summary>
/// Update a EmployeeClassification row.
/// </summary>
/// <remarks>Behavior when row doesn't exist is not defined.</remarks>
void Update(TEmployeeClassification classification);
}
ADO.NET
With ADO.NET, the model does not actually participate in database operations so it needs no adornment.
public class EmployeeClassification : IEmployeeClassification
{
public EmployeeClassification()
{
}
public EmployeeClassification(IDataReader reader)
{
if (reader == null)
throw new ArgumentNullException(nameof(reader), $"{nameof(reader)} is null.");
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey"));
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"));
IsExempt = reader.GetBoolean(reader.GetOrdinal("IsExempt"));
IsEmployee = reader.GetBoolean(reader.GetOrdinal("IsEmployee"));
}
public int EmployeeClassificationKey { get; set; }
public string? EmployeeClassificationName { get; set; }
public bool IsEmployee { get; set; }
public bool IsExempt { get; set; }
}
The repository methods use raw SQL strings. All other ORMs internally generate the same code.
SQL Server
To return a primary key from an INSERT statement, use OUTPUT Inserted.EmployeeClassificationKey.
public class SingleModelCrudScenario : SqlServerScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
public SingleModelCrudScenario(string connectionString) : base(connectionString)
{ }
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
OUTPUT Inserted.EmployeeClassificationKey
VALUES(@EmployeeClassificationName )";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
return (int)cmd.ExecuteScalar();
}
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
cmd.ExecuteNonQuery();
}
}
public void DeleteByKey(int employeeClassificationKey)
{
const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
cmd.ExecuteNonQuery();
}
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationName", employeeClassificationName);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
return null;
return new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
};
}
}
}
public IList<EmployeeClassification> GetAll()
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";
var result = new List<EmployeeClassification>();
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
});
}
return result;
}
}
public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
return null;
return new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
};
}
}
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"UPDATE HR.EmployeeClassification
SET EmployeeClassificationName = @EmployeeClassificationName
WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
cmd.ExecuteNonQuery();
}
}
}
PostgreSQL
To return a primary key from an INSERT statement, use RETURNING EmployeeClassificationKey.
public class SingleModelCrudPostgreSqlScenario : PostgreSqlScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
public SingleModelCrudPostgreSqlScenario(string connectionString) : base(connectionString)
{ }
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
VALUES(@EmployeeClassificationName )
RETURNING EmployeeClassificationKey";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
return (int)cmd.ExecuteScalar()!;
}
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"DELETE FROM HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
cmd.ExecuteNonQuery();
}
}
public void DeleteByKey(int employeeClassificationKey)
{
const string sql = @"DELETE FROM HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
cmd.ExecuteNonQuery();
}
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationName", employeeClassificationName);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
return null;
return new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
};
}
}
}
public IList<EmployeeClassification> GetAll()
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";
var result = new List<EmployeeClassification>();
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
});
}
return result;
}
}
public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
return null;
return new EmployeeClassification()
{
EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
};
}
}
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"UPDATE HR.EmployeeClassification
SET EmployeeClassificationName = @EmployeeClassificationName
WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
using (var cmd = new NpgsqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@EmployeeClassificationKey", classification.EmployeeClassificationKey);
cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
cmd.ExecuteNonQuery();
}
}
}
Chain
Strictly speaking, Chain can use the same models as ADO.NET and Dapper so long as the column and property names match. However, it is more convenient to tag the class with what table it refers to.
[Table("HR.EmployeeClassification")]
public class EmployeeClassification : IEmployeeClassification
{
public int EmployeeClassificationKey { get; set; }
public string? EmployeeClassificationName { get; set; }
public bool IsEmployee { get; set; }
public bool IsExempt { get; set; }
}
Without the Table attribute, the table name will have to be specified in every call in the repository.
Other information such as primary keys are read from the database's metadata.
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
readonly SqlServerDataSource m_DataSource;
public SingleModelCrudScenario(SqlServerDataSource dataSource)
{
m_DataSource = dataSource;
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
return m_DataSource.Insert(classification).ToInt32().Execute();
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
m_DataSource.Delete(classification).Execute();
}
public void DeleteByKey(int employeeClassificationKey)
{
m_DataSource.DeleteByKey<EmployeeClassification>(employeeClassificationKey).Execute();
}
public EmployeeClassification FindByName(string employeeClassificationName)
{
return m_DataSource.From<EmployeeClassification>(new { employeeClassificationName })
.ToObject().Execute();
}
public IList<EmployeeClassification> GetAll()
{
return m_DataSource.From<EmployeeClassification>().ToCollection().Execute();
}
public EmployeeClassification GetByKey(int employeeClassificationKey)
{
return m_DataSource.GetByKey<EmployeeClassification>(employeeClassificationKey)
.ToObject().Execute();
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
m_DataSource.Update(classification).Execute();
}
}
Dapper
Dapper is essentially just ADO.NET with some helper methods to reduce the amount of boilerplate code.
public class SingleModelCrudScenario : ScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
public SingleModelCrudScenario(string connectionString) : base(connectionString)
{
}
virtual public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
OUTPUT Inserted.EmployeeClassificationKey
VALUES(@EmployeeClassificationName )";
using (var con = OpenConnection())
return con.ExecuteScalar<int>(sql, classification);
}
virtual public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
con.Execute(sql, classification);
}
public void DeleteByKey(int employeeClassificationKey)
{
var sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
con.Execute(sql, new { employeeClassificationKey });
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;";
using (var con = OpenConnection())
return con.QuerySingle<EmployeeClassification>(sql, new { employeeClassificationName });
}
virtual public IList<EmployeeClassification> GetAll()
{
var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";
using (var con = OpenConnection())
return con.Query<EmployeeClassification>(sql).ToList();
}
virtual public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
return con.QuerySingle<EmployeeClassification>(sql, new { employeeClassificationKey });
}
virtual public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
var sql = @"UPDATE HR.EmployeeClassification
SET EmployeeClassificationName = @EmployeeClassificationName
WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
using (var con = OpenConnection())
con.Execute(sql, classification);
}
}
Info
The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.
Dapper.Contrib
The Dapper.Contrib library can elimiante the boilerplate for some common scenarios.
To enable it, models need to be decorated with Table and Key attributes.
[Table("HR.EmployeeClassification")]
public class EmployeeClassification : IEmployeeClassification
{
//Table and Key attributes are only used by Dapper.Contrib.
//They are not needed in the Dapper-only examples.
[Key]
public int EmployeeClassificationKey { get; set; }
public string? EmployeeClassificationName { get; set; }
public bool IsEmployee { get; set; }
public bool IsExempt { get; set; }
}
public class SingleModelCrudScenarioContrib : SingleModelCrudScenario
{
public SingleModelCrudScenarioContrib(string connectionString) : base(connectionString)
{
}
override public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var con = OpenConnection())
return (int)con.Insert(classification);
}
override public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var con = OpenConnection())
con.Delete(classification);
}
override public IList<EmployeeClassification> GetAll()
{
using (var con = OpenConnection())
return con.GetAll<EmployeeClassification>().ToList();
}
override public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
using (var con = OpenConnection())
return con.Get<EmployeeClassification>(employeeClassificationKey);
}
override public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var con = OpenConnection())
con.Update(classification);
}
}
DbConnector
The DbConnector library leverages ADO.NET and reduces the amount of boilerplate code required when projecting relational data into objects.
public class SingleModelCrudScenario : ScenarioBase, ISingleModelCrudScenario<EmployeeClassification>
{
public SingleModelCrudScenario(string connectionString) : base(connectionString)
{
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
OUTPUT Inserted.EmployeeClassificationKey
VALUES(@EmployeeClassificationName )";
return DbConnector.Scalar<int>(sql, classification).Execute();
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
DbConnector.NonQuery(sql, classification).Execute();
}
public void DeleteByKey(int employeeClassificationKey)
{
const string sql = @"DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @employeeClassificationKey;";
DbConnector.NonQuery(sql, new { employeeClassificationKey }).Execute();
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationName = @employeeClassificationName;";
return DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationName }).Execute();
}
public IList<EmployeeClassification> GetAll()
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;";
return DbConnector.ReadToList<EmployeeClassification>(sql).Execute();
}
public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
FROM HR.EmployeeClassification ec
WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;";
return DbConnector.ReadSingle<EmployeeClassification>(sql, new { employeeClassificationKey }).Execute();
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
const string sql = @"UPDATE HR.EmployeeClassification
SET EmployeeClassificationName = @EmployeeClassificationName
WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";
DbConnector.NonQuery(sql, classification).Execute();
}
}
Entity Framework 6
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
private Func<OrmCookbookContext> CreateDbContext;
public SingleModelCrudScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.EmployeeClassification.Add(classification);
context.SaveChanges();
return classification.EmployeeClassificationKey;
}
}
public virtual void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
//Find the row you wish to delete
var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
if (temp != null)
{
context.EmployeeClassification.Remove(temp);
context.SaveChanges();
}
}
}
public virtual void DeleteByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
//Find the row you wish to delete
var temp = context.EmployeeClassification.Find(employeeClassificationKey);
if (temp != null)
{
context.EmployeeClassification.Remove(temp);
context.SaveChanges();
}
}
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
using (var context = CreateDbContext())
{
return context.EmployeeClassification.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
}
}
public IList<EmployeeClassification> GetAll()
{
using (var context = CreateDbContext())
{
return context.EmployeeClassification.ToList();
}
}
public EmployeeClassification GetByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
return context.EmployeeClassification.Find(employeeClassificationKey);
}
}
public virtual void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
//Get a fresh copy of the row from the database
var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
if (temp != null)
{
//Copy the changed fields
temp.EmployeeClassificationName = classification.EmployeeClassificationName;
context.SaveChanges();
}
}
}
}
Info
The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.
Entity Framework 6 - Improved
The design of Entity Framework 6 requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.
public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
private Func<OrmCookbookContext> CreateDbContext;
public SingleModelCrudScenario2(Func<OrmCookbookContext> dBContextFactory) : base(dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public override void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.Entry(classification).State = EntityState.Deleted;
context.SaveChanges();
}
}
public override void DeleteByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
context.Entry(temp).State = EntityState.Deleted;
context.SaveChanges();
}
}
public override void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.Entry(classification).State = EntityState.Modified;
context.SaveChanges();
}
}
}
Entity Framework Core
To use Entity Framework, one needs to create a DbContext class. Here is a minimal example:
public partial class OrmCookbook : DbContext
{
public OrmCookbook()
: base("name=OrmCookbook")
{
}
public virtual DbSet<EmployeeClassification> EmployeeClassifications { get; set; }
}
Depending on how you setup the DbContext, the model requires some further annotations such as which table it applies to and what the primary key is.
[Table("HR.EmployeeClassification")]
public partial class EmployeeClassification
{
[Key]
public int EmployeeClassificationKey { get; set; }
[StringLength(30)]
public string EmployeeClassificationName { get; set; }
}
The context and model can be generated for you from the database using Entity Framework’s “Code First” tooling. (The name “code first” doesn’t literally mean the code has to be written before the database. Rather, it really means that you are not using EDMX style XML files.) For more information see https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx
Finally, there is the repository itself:
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
private Func<OrmCookbookContext> CreateDbContext;
public SingleModelCrudScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.EmployeeClassifications.Add(classification);
context.SaveChanges();
return classification.EmployeeClassificationKey;
}
}
public virtual void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
//Find the row you wish to delete
var temp = context.EmployeeClassifications.Find(classification.EmployeeClassificationKey);
if (temp != null)
{
context.EmployeeClassifications.Remove(temp);
context.SaveChanges();
}
}
}
public virtual void DeleteByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
//Find the row you wish to delete
var temp = context.EmployeeClassifications.Find(employeeClassificationKey);
if (temp != null)
{
context.EmployeeClassifications.Remove(temp);
context.SaveChanges();
}
}
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
using (var context = CreateDbContext())
{
return context.EmployeeClassifications.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
}
}
public IList<EmployeeClassification> GetAll()
{
using (var context = CreateDbContext())
{
return context.EmployeeClassifications.ToList();
}
}
public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
return context.EmployeeClassifications.Find(employeeClassificationKey);
}
}
public virtual void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
//Get a fresh copy of the row from the database
var temp = context.EmployeeClassifications.Find(classification.EmployeeClassificationKey);
if (temp != null)
{
//Copy the changed fields
temp.EmployeeClassificationName = classification.EmployeeClassificationName;
context.SaveChanges();
}
}
}
}
Info
The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.
Entity Framework Core - Improved
The design of Entity Framework Core requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.
public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
private Func<OrmCookbookContext> CreateDbContext;
public SingleModelCrudScenario2(Func<OrmCookbookContext> dBContextFactory) : base(dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public override void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.Entry(classification).State = EntityState.Deleted;
context.SaveChanges();
}
}
public override void DeleteByKey(int employeeClassificationKey)
{
using (var context = CreateDbContext())
{
//var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
//context.Entry(temp).State = EntityState.Deleted;
context.EmployeeClassifications.Remove(new EmployeeClassification { EmployeeClassificationKey = employeeClassificationKey });
context.SaveChanges();
}
}
public override void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var context = CreateDbContext())
{
context.Entry(classification).State = EntityState.Modified;
context.SaveChanges();
}
}
}
LINQ to DB
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = new OrmCookbook())
{
return db.InsertWithInt32Identity(classification);
}
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = new OrmCookbook())
{
db.EmployeeClassification
.Where(d => d.EmployeeClassificationKey == classification.EmployeeClassificationKey)
.Delete();
}
}
public void DeleteByKey(int employeeClassificationKey)
{
using (var db = new OrmCookbook())
{
db.EmployeeClassification
.Where(d => d.EmployeeClassificationKey == employeeClassificationKey)
.Delete();
}
}
public EmployeeClassification FindByName(string employeeClassificationName)
{
using (var db = new OrmCookbook())
{
var query = from ec in db.EmployeeClassification
where ec.EmployeeClassificationName == employeeClassificationName
select ec;
return query.Single();
}
}
public IList<EmployeeClassification> GetAll()
{
using (var db = new OrmCookbook())
{
return db.EmployeeClassification.ToList();
}
}
public EmployeeClassification GetByKey(int employeeClassificationKey)
{
using (var db = new OrmCookbook())
{
return db.EmployeeClassification.Where(d => d.EmployeeClassificationKey == employeeClassificationKey).Single();
}
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = new OrmCookbook())
{
db.Update(classification);
}
}
}
LLBLGen Pro
LLBLGen Pro offers multiple ways to perform CRUD operations: via entity instances or directly on the data in the database, to avoid a fetch of entities first. The code below illustrates this and multiple query systems. Entity types are derived from a common base class.
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassificationEntity>
{
public int Create(EmployeeClassificationEntity classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var adapter = new DataAccessAdapter())
{
adapter.SaveEntity(classification, true, recurse: false);
return classification.EmployeeClassificationKey;
}
}
public virtual void Delete(EmployeeClassificationEntity classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var adapter = new DataAccessAdapter())
{
// flag the entity as not-new, so we can delete it without fetching it first if the PK is set.
classification.IsNew = false;
adapter.DeleteEntity(classification);
}
}
public virtual void DeleteByKey(int employeeClassificationKey)
{
using (var adapter = new DataAccessAdapter())
{
// delete directly, so we don't have to fetch the entity first.
adapter.DeleteEntitiesDirectly(typeof(EmployeeClassificationEntity),
new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
.Equal(employeeClassificationKey)));
}
}
public EmployeeClassificationEntity FindByName(string employeeClassificationName)
{
using (var adapter = new DataAccessAdapter())
{
// let's use QuerySpec
return adapter.FetchFirst(new QueryFactory().EmployeeClassification
.Where(EmployeeClassificationFields.EmployeeClassificationName
.Equal(employeeClassificationName)));
}
}
public IList<EmployeeClassificationEntity> GetAll()
{
using (var adapter = new DataAccessAdapter())
{
// you know what, let's use the low level API for a change.
var toReturn = new EntityCollection<EmployeeClassificationEntity>();
adapter.FetchEntityCollection(toReturn, null);
return toReturn;
}
}
public EmployeeClassificationEntity? GetByKey(int employeeClassificationKey)
{
using (var adapter = new DataAccessAdapter())
{
return new LinqMetaData(adapter).EmployeeClassification
.FirstOrDefault(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
}
}
public virtual void Update(EmployeeClassificationEntity classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var adapter = new DataAccessAdapter())
{
// re-use existing entity (as it tracks changes internally) otherwise fetch the instance from the DB
EmployeeClassificationEntity toPersist = classification;
if (classification.IsNew)
{
toPersist = adapter.FetchNewEntity<EmployeeClassificationEntity>(
new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
.Equal(classification.EmployeeClassificationKey)));
toPersist.EmployeeClassificationName = classification.EmployeeClassificationName;
}
if (!toPersist.IsNew)
{
adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
}
}
}
}
NHibernate
public partial class EmployeeClassification : IEmployeeClassification
{
public virtual int EmployeeClassificationKey { get; set; }
public virtual string? EmployeeClassificationName { get; set; }
public virtual bool IsEmployee { get; set; }
public virtual bool IsExempt { get; set; }
}
Instead of attributes, a mapping file is used to associate the model with a database table. There is one file per table and each is set to Build Action: Embedded resource.
<hibernate-mapping
assembly="Recipes.NHibernate"
namespace="Recipes.NHibernate.Entities">
<!-- more mapping info here -->
<class
name="EmployeeClassification"
table="EmployeeClassification"
schema="HR">
<id
name="EmployeeClassificationKey">
<generator
class="native" />
</id>
<property
name="EmployeeClassificationName" />
<property
name="IsExempt" />
<property
name="IsEmployee" />
</class>
</hibernate-mapping>
A SessionFactory is needed to stitch the various configuration files together.
private static void ConfigureSessionFactory()
{
var jsonConfiguration = new ConfigurationBuilder().SetBasePath(AppContext.BaseDirectory).AddJsonFile("appsettings.json").Build();
var sqlServerConnectionString = jsonConfiguration.GetSection("ConnectionStrings")["SqlServerTestDatabase"];
var configuration = new Configuration();
configuration.Configure();
configuration.SetProperty(NHibernateCfg.Environment.ConnectionString, sqlServerConnectionString);
configuration.AddAssembly(typeof(Setup).Assembly);
SessionFactory = configuration.BuildSessionFactory();
try
{
(new Setup()).Warmup();
}
catch { }
}
Finally there is the repository itself.
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
readonly ISessionFactory m_SessionFactory;
public SingleModelCrudScenario(ISessionFactory sessionFactory)
{
m_SessionFactory = sessionFactory;
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var session = m_SessionFactory.OpenSession())
{
session.Save(classification);
session.Flush();
return classification.EmployeeClassificationKey;
}
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var session = m_SessionFactory.OpenSession())
{
session.Delete(classification);
session.Flush();
}
}
public void DeleteByKey(int employeeClassificationKey)
{
using (var session = m_SessionFactory.OpenSession())
{
session.Delete(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey });
session.Flush();
}
}
public EmployeeClassification FindByName(string employeeClassificationName)
{
using (var session = m_SessionFactory.OpenStatelessSession())
{
return session.QueryOver<EmployeeClassification>().Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
}
}
public IList<EmployeeClassification> GetAll()
{
using (var session = m_SessionFactory.OpenStatelessSession())
{
return session.QueryOver<EmployeeClassification>().List();
}
}
public EmployeeClassification GetByKey(int employeeClassificationKey)
{
using (var session = m_SessionFactory.OpenStatelessSession())
return session.Get<EmployeeClassification>(employeeClassificationKey);
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var session = m_SessionFactory.OpenSession())
{
session.Update(classification);
session.Flush();
}
}
}
The rules on when you need to call Flush are complex. In some cases it will be called for you implicitly, but as a general rule you need to invoke it before leaving a block that includes modifications.
RepoDb
When calling the raw-SQL operations, just like Dapper, RepoDB requires annotations on the classes. These are specific to RepoDb, you cannot use the standard Table, Column, and Key attributes from .NET.
[Map("[HR].[EmployeeClassification]")]
public class EmployeeClassification : IEmployeeClassification
{
public EmployeeClassification()
{
}
public EmployeeClassification(IReadOnlyEmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
EmployeeClassificationKey = classification.EmployeeClassificationKey;
EmployeeClassificationName = classification.EmployeeClassificationName;
IsExempt = classification.IsExempt;
IsEmployee = classification.IsEmployee;
}
public int EmployeeClassificationKey { get; set; }
public string? EmployeeClassificationName { get; set; }
public bool IsEmployee { get; set; }
public bool IsExempt { get; set; }
internal ReadOnlyEmployeeClassification ToImmutable()
{
return new ReadOnlyEmployeeClassification(this);
}
}
The repository resembles Dapper, but with far less SQL.
public class SingleModelCrudScenario : BaseRepository<EmployeeClassification, SqlConnection>,
ISingleModelCrudScenario<EmployeeClassification>
{
public SingleModelCrudScenario(string connectionString)
: base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
{ }
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
return Insert<int>(classification);
}
public void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
base.Delete(classification);
}
public void DeleteByKey(int employeeClassificationKey)
{
Delete(employeeClassificationKey);
}
public EmployeeClassification? FindByName(string employeeClassificationName)
{
return Query(e => e.EmployeeClassificationName == employeeClassificationName).FirstOrDefault();
}
public IList<EmployeeClassification> GetAll()
{
return QueryAll().AsList();
}
public EmployeeClassification? GetByKey(int employeeClassificationKey)
{
return Query(employeeClassificationKey).FirstOrDefault();
}
public void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
base.Update(classification);
}
}
ServiceStack
ServiceStack requires the use of annotations on its models. These are specific to ServiceStack, you cannot use the standard Table, Column, and Key attributes from .NET.
[Alias("EmployeeClassification"), Schema("HR")]
public partial class EmployeeClassification
{
[PrimaryKey, AutoIncrement, Alias("EmployeeClassificationKey")]
public int Id { get; set; }
[Required, StringLength(30)]
public string? EmployeeClassificationName { get; set; }
public bool IsExempt { get; set; }
public bool IsEmployee { get; set; }
[Reference]
[SuppressMessage("Usage", "CA2227:Collection properties should be read only", Justification = "Required by ServiceStack")]
public List<Employee> Employees { get; set; } = new List<Employee>();
}
The repository resemebles Dapper, but with far less SQL.
public class SingleModelCrudScenario : ISingleModelCrudScenario<EmployeeClassification>
{
private readonly IDbConnectionFactory _dbConnectionFactory;
public SingleModelCrudScenario(IDbConnectionFactory dbConnectionFactory)
{
_dbConnectionFactory = dbConnectionFactory;
}
public int Create(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = _dbConnectionFactory.OpenDbConnection())
{
return (int)db.Insert(classification, true);
}
}
public virtual void Delete(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = _dbConnectionFactory.OpenDbConnection())
{
db.Delete(classification);
}
}
public virtual void DeleteByKey(int employeeClassificationKey)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
db.DeleteById<EmployeeClassification>(employeeClassificationKey);
}
}
public EmployeeClassification FindByName(string employeeClassificationName)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
return db.Single<EmployeeClassification>(
r => r.EmployeeClassificationName == employeeClassificationName);
}
}
public IList<EmployeeClassification> GetAll()
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
return db.Select<EmployeeClassification>();
}
}
public EmployeeClassification GetByKey(int employeeClassificationKey)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
{
return db.SingleById<EmployeeClassification>(employeeClassificationKey);
}
}
public virtual void Update(EmployeeClassification classification)
{
if (classification == null)
throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");
using (var db = _dbConnectionFactory.OpenDbConnection())
{
db.Update(classification);
}
}
}