I am doing an asp.net mvc application having a Data Access Layer (DAL). Having done 90% of my database CRUD code I asked myself wether I need a Business Layer.
But what should I put there? All my CRUD methods in the DAL are not single Selects on one sql table for example. Most time I do many joins + sql aggregate functions. Just to mention I use ADO.NET , NO Stored Procedures/Triggers.
Then again I asked myself wether such a method would belong in a Business Layer:
/// <summary>
/// Creates a testplan with all teststeps and their default values for a certain template
/// </summary>
/// <param name="testplan"></param>
/// <returns>true if transaction was successfull else false</returns>
public void CreateTestplan(Testplan testplan)
{
try
{
using (var con = new SqlConnection(_connectionString))
using (var trans = new TransactionScope())
{
con.Open();
_testplanDataProvider.AddTestplan(testplan,con);
_testplanDataProvider.CreateTeststepsForTestplan(testplan.Id, testplan.TemplateId,con);
trans.Complete();
}
}
catch (SqlException ex)
{
ExceptionManager.HandleException(ex);
}
}
This method is actually calling TWO other methods in the DAL.
Now I asked myself, why introduce an extra Business Layer, when I can put the CreateTestplan method also inside the TestplanDataProvider class with all the code from both methods AddTestplan + CreateTeststepsForTestplan.
What do you think? Is this a good approach?
I really ask this because the CreateTestplan method is only containing Data Access Logic in my opinion.
UPDATE:
public void AddTestplan(Testplan testplan, SqlConnection con)
{
using (var cmd = new SqlCommand("INSERT INTO TESTPLAN (ReleaseId,TemplateId,CreatedAt,UserId,Name,Duration) VALUES (@ReleaseId,@TemplateId,@CreatedAt,@UserId,@Name,@Duration);Select Scope_Identity();", con))
{
var p1 = new SqlParameter("@ReleaseId", testplan.ReleaseId);
var p2 = new SqlParameter("@TemplateId", testplan.TemplateId);
var p3 = new SqlParameter("@CreatedAt", testplan.CreatedAt);
var p4 = new SqlParameter("@UserId", testplan.UserId);
var p5 = new SqlParameter("@Name", testplan.Name);
var p6 = new SqlParameter("@Duration", testplan.Duration);
cmd.Parameters.AddRange(new[] { p1, p2, p3, p4, p5, p6 });
testplan.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
}
public void CreateTeststepsForTestplan(int testplanId, int templateId, SqlConnection con)
{
var teststeps = new List<Teststep>();
using (var selectCMD = new SqlCommand("SELECT ts.TeststepId, MAX(ts.CreatedAt)FROM Teststep ts INNER JOIN Unit u ON ts.UnitId = u.UnitId Where u.TemplateId = @TemplateId Group by TeststepId", con))
{
var p = new SqlParameter("@TemplateId", templateId);
selectCMD.Parameters.Add(p);
using (var reader = selectCMD.ExecuteReader())
{
Teststep teststep = null;
while (reader.Read())
{
teststep = new Teststep
{
Id = Convert.ToInt32(reader["TeststepId"]),
CreatedAt = Convert.ToDateTime(reader["CreatedAt"]),
};
teststeps.Add(teststep);
}
}
}
using (var insertCMD = new SqlCommand("INSERT INTO TestplanTeststep (TestplanId,TeststepId,TestState,ErrorText) VALUES (@TestplanId, @TeststepId, @TestState, @ErrorText)", con))
{
var p1 = new SqlParameter("@TeststepId", SqlDbType.Int);
var p2 = new SqlParameter("@CreatedAt", SqlDbType.DateTime);
var p3 = new SqlParameter("@TestplanId", testplanId);
var p4 = new SqlParameter("@ErrorText", DBNull.Value);
var p5 = new SqlParameter("@ErrorScreenshot", DBNull.Value);
var p6 = new SqlParameter("@TestState", (int)Teststep.TeststepTestState.Untested);
insertCMD.Parameters.AddRange(new[] { p1, p2, p3, p4, p5 });
foreach (Teststep step in teststeps)
{
p1.Value = step.Id;
p2.Value = step.CreatedAt;
insertCMD.ExecuteNonQuery();
}
}
}