Move Data Access logic from the business layer to the data access layer

Question

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();
                }
            }
    }

score 0 · Accepted Answer · answered Jul 05 '12 at 17:43

0

A good reason to keep data access out of your BLL is so that you can switch databases or database frameworks with minimal changes to your business logic.

For example, if you moved all of your ADO.NET related code to your DAL, and then later decided to use Entity Framework instead, only your DAL would change, not your BLL.

Of course, if you tend to have very little business logic, and your BLL is simply handing off the work to your DAL, you may not gain as from having separate layers. This can be the case for very trivial apps, but it can also indicate that you have business logic hidden in your DAL.

answered Jul 05 '12 at 17:43

jrummell

41,300
17
110
165

I updated my question with code sample from dataprovider. You think I have hidden business logic in there? – Pascal Jul 05 '12 at 18:21
The only thing I see in those two methods is ADO.NET code - no business logic. – jrummell Jul 05 '12 at 18:46
Right. Therefore i moved my both dataprovider methods into ONE method again to call it directly. The service is not needed. – Pascal Jul 06 '12 at 05:58
I say now you was wrong with that statement:"The only thing I see in those..." No there is business logic. I understood this when I read this article from Martin Fowler: http://martinfowler.com/articles/dblogic.html – Pascal Jul 26 '12 at 18:41

score 0 · Answer 2 · answered Jul 05 '12 at 18:35

0

I know the commonly accepted best practice is to seperate your DAC from your BL, but my take on it is, if you are using something like L2S or Entity framework you already have a DAL, and your business logic can go into partial definitions for those classes. There is no need to add another DAL on top of that. I would even consider ADO an abstraction that constitutes a DAL. Btw, since you are using strait ADO, you might want to take a look at Dapper. It's a minimal and fast DAL.

answered Jul 05 '12 at 18:35

Master Morality

5,358
6
27
42

I am just looking at EF5 RC. I tried EF1 years ago that sucked. – Pascal Jul 05 '12 at 18:41
Yeah it did. I used linq to oracle for a bit and then we switched to linq to sql . The premise that it's easy because it's database agnostic is false. We had several business logic problems because of differences in how they were implemented and how the databases differ. The bottoms line is that changing a data store is a pain any way you look at it. It's better to look at what works for you. We are now moving to L2S for insert, edit, delete and wriiting our, fairly complex, select statements as views and grabbing them with dapper for performance. – Master Morality Jul 05 '12 at 18:57
We are already on L2S an I tried to write Some realtime reporting stuff in it last year and it's incredible slow to traverse the code trees for extremely large queries. We ultimately went with L2 oracle many years ago. It's a pharma company so oracle was pretty well entrenched. And large parts of our app were written specially to exploit linq to SQL data context stuff. So going from linq to oracle to EF seemed like too big of a jump. – Master Morality Jul 05 '12 at 19:45
ok. I hate oracle ;-) Everyone in our company although they are our fundamental doing BI stuff hehe – Pascal Jul 05 '12 at 20:15

Move Data Access logic from the business layer to the data access layer

2 Answers2