I made some changes after suggestions from BCdotWEB
For the class: DebenturChangesRepository
I changed the name of CreateForChangedCalledIn
to CreateForNewDebenturs
so that if I need to monitor additional fields than I only need to change my repository.
I change the sql to use Dapper
which shortens the code by aprox 50%!.
Here the new code:
public class DebenturChangesRepository : IDebenturChangesRepository
{
private readonly string connectionString;
public DebenturChangesRepository(string connectionString)
{
if (connectionString == null) throw new ArgumentNullException("connectionString");
this.connectionString = connectionString;
}
public List<DebenturChange> CreateForNewDebenturs()
{
var sql = "Select ClaimNo, CalledIn " +
"From Debentur " +
"Where ClaimNo Not In (Select ClaimNo From DebenturChange) ";
using (var connection = new SqlConnection(connectionString))
{
return connection.Query<DebenturChange>(sql).ToList();
}
}
public List<DebenturChange> CreateForChangedDebenturs()
{
var sql = "Select d.ClaimNo, d.CalledIn " +
"From Debentur d join DebenturChange c on d.ClaimNo = c.ClaimNo " +
"Where IsNull(d.CalledIn, 0) <> IsNull(c.CalledIn, 0) ";
using (var connection = new SqlConnection(connectionString))
{
return connection.Query<DebenturChange>(sql).ToList();
}
}
public void InsertDebenturChange(DebenturChange debenturChange)
{
var sql = "Insert Into DebenturChange (ClaimNo, CalledIn) " +
"Values (@ClaimNo, @CalledIn)";
using (var connection = new SqlConnection(connectionString))
{
connection.Execute(sql, debenturChange);
}
}
public void UpdateDebenturChange(DebenturChange debenturChange)
{
var sql = "Update DebenturChange " +
"Set CalledIn = @CalledIn " +
"Where ClaimNo = @ClaimNo";
using (var connection = new SqlConnection(connectionString))
{
connection.Execute(sql, debenturChange);
}
}
}