• 0

[ASP.NET, Design] Business Logic Layer or Stored procedures?


Question

Hello,

I'm trying to see which route is best. Having a Business Logic Layer or have all the logic as stored procedures in the database.

The application architecture looks like this: Data Access Layer (LINQ to SQL) -> Business Logic Layer (Needed?) -> UI Layer (Web).

Does it make sense to have the middle layer if everything is done via stored procedures? If not, what do you recommend for the BLL? Could you advice on both routes? Advantages and disadvantages? Kindly note that the application is married to the database (Sql Server 2005) so there is no need to take into concideration that the dbms will change in the future.

Thanks.

14 answers to this question

Recommended Posts

  • 0

I generally stick to the 3-layer architecture for my (admittedly quite small) ASP.NET systems. The business logic layer (even with stored procedures) allows you to add exception handling and more advanced input validation to your system between the presentation layer and the linq-to-sql layer, and for that I would recommend keeping it.

  • 0

Thanks for your input. What kind of class naming do you recommend for this layer? The DAC layer already has the entity objects (like Item, Subject).

  On 25/01/2010 at 08:21, Majesticmerc said:

I generally stick to the 3-layer architecture for my (admittedly quite small) ASP.NET systems. The business logic layer (even with stored procedures) allows you to add exception handling and more advanced input validation to your system between the presentation layer and the linq-to-sql layer, and for that I would recommend keeping it.

  • 0

I always use both

1) Presentation layer

2) Business layer (most of the time i still use ado.net though) that calls stored procedures

3) Stored procedures that contains most of the transactional stuff

4) Database with foreign keys for data integrity

  • 0

One thing I cannot understand is why some developers have a layer which talks to the db using one entity type and then converts to another so the main application can then use it......all your doing is adding more for to your already long list of things to do! Designing a database properly will combat this. So you can in a sense merge one the entity layer and business logic layer into one.

Have a look at this: http://www.west-wind.com/WebLog/posts/160237.aspx

The code files are here: http://www.west-wind.com/files/conferences/conn_LinqToSqlBusiness.zip

Basically it's a DataContext lifetime management and factory wrapper class.

How to use it:

Create a new Project, create two folders with the project called "BusinessFramework" and "BusinessObjects". Grab the "wwDataContext.cs" and "wwBusinessObject.cs" files and put them into the "BusinessFramework" folder. I rename them to "<shortprojectname>BusinessObject.cs" and "<shortProjectName>BusinessDataContext" but rename them as you see fit.

Create a DBML file (called YourData, which then becomes YourDataDataContext) in the "BusinessFramework" folder, right click WITHIN the DBML, select Properties. Under "Base Class" set to "<shortProjectName>BusinessDataContext", Under Connection set "Application Settings" to False, "Connection String" to None.

Right click on the DBML select "View Code".

You *may* need to change the namespace on this class first, it'll depend. it should be the full namespace of where the file is..... eg:

someNamespace.projectName.Data.BusinessFramework

rather than

someNamespace.projectName.Data

it should look something like this:

    public partial class DBMLNameDataContext         
    {

        public DBMLNameDataContext()
            : base(ConfigurationManager.ConnectionStrings["YourConnectionStringNameFromApp.Config"].ConnectionString, new AttributeMappingSource())
        {
            OnCreated();
        }

    }

Create an app.config file within the root of the Project. Add a new Connection String, putting the correct name into the class above.

Add a table into the DBML.....this will be signified by <entityName>

Create a new class file (within "BusinessObjects" folder) called "Bus<entityName>.cs", we have here tbl_Engineer which is named "Engineer" in the DBML, so the file becomes "BusEngineer.cs".

public class BusEngineers : &lt;shortprojectname&gt;BusinessObject&lt;tbl_engineer, YourDataDataContext&gt;
    {
    }

Within the root of the Project create a new class file called "<shortProjectName>DataFactory.cs", within this add:

        public static BusEngineers GetEngineers
        {
            get
            {
                return new BusEngineers();
           }
        }

So to use the whole Data Layer....in another project add:

<shortProjectName>DataFactory.GetEngineers. => this will then list all your public methods within your BusEngineer class.

You could even move all the Business Objects out into another project if you needed to.

Done - I think.....any questions feel free to PM me.

GE

  • 0

Thanks a lot, I'll look into that :).

  On 25/01/2010 at 11:04, garethevans1986 said:

One thing I cannot understand is why some developers have a layer which talks to the db using one entity type and then converts to another so the main application can then use it......all your doing is adding more for to your already long list of things to do! Designing a database properly will combat this. So you can in a sense merge one the entity layer and business logic layer into one.

Have a look at this: http://www.west-wind...sts/160237.aspx

The code files are here: http://www.west-wind...SqlBusiness.zip

Basically it's a DataContext lifetime management and factory wrapper class.

How to use it:

Create a new Project, create two folders with the project called "BusinessFramework" and "BusinessObjects". Grab the "wwDataContext.cs" and "wwBusinessObject.cs" files and put them into the "BusinessFramework" folder. I rename them to "<shortprojectname>BusinessObject.cs" and "<shortProjectName>BusinessDataContext" but rename them as you see fit.

Create a DBML file (called YourData, which then becomes YourDataDataContext) in the "BusinessFramework" folder, right click WITHIN the DBML, select Properties. Under "Base Class" set to "<shortProjectName>BusinessDataContext", Under Connection set "Application Settings" to False, "Connection String" to None.

Right click on the DBML select "View Code".

You *may* need to change the namespace on this class first, it'll depend. it should be the full namespace of where the file is..... eg:

someNamespace.projectName.Data.BusinessFramework

rather than

someNamespace.projectName.Data

it should look something like this:

 public partial class DBMLNameDataContext 
 {

 public DBMLNameDataContext()
 : base(ConfigurationManager.ConnectionStrings["YourConnectionStringNameFromApp.Config"].ConnectionString, new AttributeMappingSource())
 {
 OnCreated();
 }

 }

Create an app.config file within the root of the Project. Add a new Connection String, putting the correct name into the class above.

Add a table into the DBML.....this will be signified by <entityName>

Create a new class file (within "BusinessObjects" folder) called "Bus<entityName>.cs", we have here tbl_Engineer which is named "Engineer" in the DBML, so the file becomes "BusEngineer.cs".

public class BusEngineers : &lt;shortprojectname&gt;BusinessObject&lt;tbl_engineer, YourDataDataContext&gt;
 {
 }

Within the root of the Project create a new class file called "<shortProjectName>DataFactory.cs", within this add:

 public static BusEngineers GetEngineers
 {
 get
 {
 return new BusEngineers();
 }
 }

So to use the whole Data Layer....in another project add:

<shortProjectName>DataFactory.GetEngineers. => this will then list all your public methods within your BusEngineer class.

You could even move all the Business Objects out into another project if you needed to.

Done - I think.....any questions feel free to PM me.

GE

  • 0

Yeah I should probably mention too that I work mostly in ADO.NET, where business logic layers are pretty standard, so the method that Gareth mentioned might be more suited for you, although only you can make that decision :)

  • 0

A correction to the above post....because of this:

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=361577&wa=wsignin1.0

namespace CompanyName.ProjectName.Data.BusinessFramework
{

    // DO NOT REMOVE
    using System.Configuration;
    using System.Data.Linq.Mapping;
    // SEE HERE - https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=361577&wa=wsignin1.0
    // GE, 26/01/10

    public partial class DBMLNameDataContext
    {


        public DBMLNameDataContext()
            : base(ConfigurationManager.ConnectionStrings["YourConnectionStringNameFromApp.Config"].ConnectionString, new AttributeMappingSource())
        {
            OnCreated();
        }

    }
}

  • 0

I rarely use stored procedures anymore. I used to use them religiously. I had one for every function in the system. Looking back now I wonder what I was thinking. What a waste that was.

In most of my applications, I typically use an architecture that many people call the Onion Architecture. I design my apps so that they're testable (automated testing, not F5 testing) because it's important to me.

I'll have a bunch a repository classes that interface with my database via nhibernate, domain classes and service layer classes, when they're required. Outside of those, I'll have presentation model classes for strongly-typed views.

  • 0
  On 26/01/2010 at 15:59, sbauer said:

I rarely use stored procedures anymore. I used to use them religiously. I had one for every function in the system. Looking back now I wonder what I was thinking. What a waste that was.

In most of my applications, I typically use an architecture that many people call the Onion Architecture. I design my apps so that they're testable (automated testing, not F5 testing) because it's important to me.

I'll have a bunch a repository classes that interface with my database via nhibernate, domain classes and service layer classes, when they're required. Outside of those, I'll have presentation model classes for strongly-typed views.

Looks like a nice article, gonna give that a read. Cheers!

I must admit that I used to use Stored Procedures for ALL my SQL, and to be honest, it did little to nothing to help the structure of my systems. Now (since I'm fortunate enough to develop in ASP.NET (no MVC yet though which is a bummer)) I tend to take advantage of the ADO.NET dataset designer to handle all my SQL statements, which makes life much easier since I can manage the entire system from inside Visual Studio.

  • 0
  Quote

I rarely use stored procedures anymore. I used to use them religiously. I had one for every function in the system. Looking back now I wonder what I was thinking. What a waste that was.

Could you tell us why it was a waste? I'm interested in knowing.

  • 0
  On 26/01/2010 at 23:58, Majesticmerc said:

Looks like a nice article, gonna give that a read. Cheers!

I must admit that I used to use Stored Procedures for ALL my SQL, and to be honest, it did little to nothing to help the structure of my systems. Now (since I'm fortunate enough to develop in ASP.NET (no MVC yet though which is a bummer)) I tend to take advantage of the ADO.NET dataset designer to handle all my SQL statements, which makes life much easier since I can manage the entire system from inside Visual Studio.

Stored procedures are such a fun topic because many people love them. I did too. I loved how my code looked. I loved how I had SQL sitting in the database, not in my code. Eventually, though, I got tired of it like you. I don't use ADO.NET dataset designer because I don't feel it scales well to larger systems and starts to break down.

It's unfortunate that you can't use MVC. If I never work in a WebForms project again, I'll be happy.

  On 27/01/2010 at 07:35, Ali Koubeissi said:

Could you tell us why it was a waste? I'm interested in knowing.

  On 27/01/2010 at 10:02, garethevans1986 said:

Because your "business logic" should not be in the database...

That's part of the reasoning behind it.

We use unit tests on our projects. Our unit tests ensure that 1) our features work as designed and 2) our features still work even after we've added new features. When you add business logic to stored procedures, you're beyond unit tests now. Unit tests need to be fast, and isolated - a small unit. Testing the database is not quick, and it's not a small unit. You'll need to create integration tests and those will be much slower. When things run slow, people tend not to use them.

Another reason is that I stopped generating most of my SQL by hand. No more CRUD sprocs, or adhoc statements. NHibernate takes care of that for us. If we have a very complicated query that we need to execute and the query looks pretty ugly while using the NHibernate API, then we'll generate the SQL by hand.

So far, so good. We'll have a repository that inherits from a base repository for each entity in the system.

    public abstract class RepositoryWithTypedKey&lt;KEY,ENTITY&gt;: IRepositoryWithTypedKey&lt;KEY,ENTITY&gt; where ENTITY: class 
    {
        protected ISession session;

        protected RepositoryWithTypedKey(ISession session)
        {
            this.session = session;
        }
        public ENTITY Get(KEY id)
        {
            return Session.Get<ENTITY>(id);
        }

        public ENTITY Load(KEY id)
        {
            return Session.Load<ENTITY>(id);
        }

        public IEnumerable&lt;ENTITY&gt; GetAll()
        {
            return Session.CreateCriteria&lt;ENTITY&gt;().List&lt;ENTITY&gt;();
        }

        public void Save(ENTITY entity)
        {
            Session.Save(entity);
        }

        public void Update(ENTITY entity)
        {
            Session.Update(entity);
        }

        public void SaveOrUpdate(ENTITY entity)
        {
            Session.SaveOrUpdate(entity);
        }

        public ISession Session { get { return session; } }
    }

    public class EmployeeRepository: Repository&lt;Employee&gt;, IEmployeeRepository
    {
        public EmployeeRepository(ISession session) : base(session)
        {
        }

        public IList&lt;Employee&gt; SearchByLastName(string lastName)
        {
            var criteria = Session.CreateCriteria&lt;Employee&gt;();
            criteria.Add(Expression.Like("LastName" + lastName + "%"));
            criteria.AddOrder(Order.Asc("LastName            return criteria.List<Employee>;();
        }

    }

And the controller

public class HomeController : Controller
    {
        private readonly IEmployeeRepository _employeeRepository;

        public HomeController(IEmployeeRepository _employeeRepository)
        {
            this._employeeRepository = _employeeRepository;
        }

        [HttpGet]
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        [ActionName("Index")]
        public ActionResult IndexSearch(string search)
        {
            return RedirectToAction("Search", new { searchTerm = search });
        }

        [Transaction]
        public ActionResult Search(string searchTerm)
        {
            if(string.IsNullOrEmpty(searchTerm))
                return RedirectToAction("index");

            var model = new HomeControllerViewModel();
            model.Employees = _employeeRepository.GetEmployeeListByLastName(searchTerm);
            model.Search = searchTerm;
            return View(model);

        }

    }

  • 0

I've come accross NHibernate, Fluent NHibernate and Dependency Injection before.

The only problem I have with NHibernate is how do you script the changes to keep your SQL Servers up to date?

GE

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Google Maps expands cycling routes and alerts for low-emission zones in Europe by Aditya Tiwari Google announced some new Google Maps updates for users in Europe. These features are meant to help them get around more sustainably and reduce emissions in cities, according to the search giant. For starters, Google is expanding its AI-powered feature that predicts when walking or taking transit will take roughly the same amount of time as driving. Currently available in 60 cities, the feature recommends walking or transit routes and has helped avoid tens of millions of car trips. It will soon launch in Copenhagen, Stockholm, Warsaw, and other cities. Speaking of car trips, Google displays helpful alerts when a car enters an area designated as a low-emission or low-traffic zone in supported cities, such as London and Berlin. It informs users whether their vehicle is permitted in these zones, and they should select an alternative route if necessary. The feature will expand to more than 1,000 low-emission and low-traffic zones across Europe in the coming months, including Italy, Sweden, Austria, and other countries. For those interested in cycling on the roads, Maps will display helpful details about routes in more places. Riders will see bike lanes on their cycling route, as well as other useful information, such as whether there is heavy car traffic or a steep incline ahead. The feature will expand to 17 new cities, including nine in Europe, which will cover about 125,000 km (77,671 miles) of bike lanes globally. Google has partnered with local governments and public authorities in cities such as Hamburg, Madrid, Barcelona, Milan, Rome, Zurich, Budapest, Vienna, and Brussels to source the data. Additionally, Google announced that fuel-efficient routing, also known as eco-friendly routing, is now available globally. As the name suggests, it finds the most fuel or energy-efficient route for your trip. The feature was introduced in 2021, starting with the US, and later expanded to over 40 countries across Europe in its first major update in 2022. On average, about 500 million trips using fuel-efficient routing are taken each month. Google estimated that the feature collectively resulted in "more than 2.7 million metric tons of GHG [greenhouse gas] emissions avoided in 2024 alone," which is equivalent to taking "more than 630,000 gasoline-powered cars off the road for a year."
    • That's one impressive "baby step". Well done!
    • They're already dead. They've been through many rounds off layoffs due to mismanagement and Destiny 2 is basically over as the interest levels are the lowest theyve ever been.
    • Redesigned Windows 11 Start menu: What users wanted and what Microsoft delivered by Taras Buria Windows 11 is getting a redesigned Start menu. This is a big deal for the soon-to-be-four-year-old operating system and its highly controversial design bits. After years of slow to no progress in the Start menu area, Microsoft is finally delivering a much better variant with many new features and plenty of feedback addressed. How much has been addressed? In 2023, we posted a list of the "Top 10 Start menu features and changes Windows 11 users want," so it is now time to compare that to the new Start menu. Note: The new Start menu is not yet publicly available. Microsoft is testing it in Windows 11 preview builds from the Dev and Beta Channels, and you can check out this article to learn how to enable it so that you can try it yourself. 1. Allow users to turn off the "Recommended" section - Delivered (17K+ upvotes) Round of applause for Microsoft, everyone, as the company actually delivered the most requested Start menu feature. The redesigned variant lets you turn off the Recommended section and hide it altogether so that it does not waste any space. Recommended section, begone!2. More customization options - Nope (5.4K+ upvotes) Although Microsoft now allows turning off the Recommended section and switching between three views for the All apps list, the menu remains quite restricted when it comes to personalization, so if you want true customization, Windhawk and the Start menu styler mod are here for you, allowing some seriously cool Start menu designs, as one on the screenshot below: 3. Allow resizing the Start menu - Sort of delivered (3.8K+ upvotes) You still cannot change the size of the Start menu manually like you could in Windows 10. However, the menu is now more adaptive, which means you will see more content if you have a bigger screen. Still, I would like to have the ability to make the menu bigger, so this one remains standing. 4. Go back to the Windows 10-style Start menu - Nope (3.4K+ upvotes) Microsoft is not going back to the Windows 10 Start menu, so if you are one of the 3,400+ people who upvoted this in Feedback Hub, your best course of action is to install a third-party Start menu or just stick to Windows 10. 5. Use Grid view for the All apps list - Delivered (1.5K+ upvotes) Another popular request was delivered fully. I would even say that Microsoft over delivered it. Instead of just killing the standard list view in favor of grid view, Microsoft let users decide what kind of view they want. You can stick to the classic list, switch over to grid view, or enjoy a categorized view. Again, well done! 6. Display jump lists when right-clicking pinned apps - Needs fixing (1K+ upvotes) Microsoft has already fixed this problem, and you can access jump lists and recent files by right-clicking pinned applications in the current Start menu version. However, turning off the Recommended section also turns off jump lists on the Start menu and taskbar for some reason. A very frustrating design for those who use jump lists and do not want the Recommended section. This needs fixing. Microsoft punishes you with no jump lists if you dare to turn off "Recommended." Why?7. Make the Start menu open on the All apps list by default - Delivered (1K+ upvotes) The new Start menu ticks this box as well. There is no need to click "All apps" when you open the Start menu. It now features a single-view user interface with the list of all apps right below your pins and recommendations. All you need to do is start scrolling. 8. Add a full-screen Start menu - Nope (1K+ upvotes) Even though Microsoft "has got this," nothing indicates that the company plans to reintroduce a full-screen Start menu from the days of Windows 10 and Windows 8. A shame, if you ask me. Interestingly, it appears that Microsoft considered a full-screen Start menu for Windows 11. The company recently showed some of the prototypes it considered implementing, including a scrollable full-screen menu. 9. Bring back live tiles - Nope (1K+ upvotes) Tiles are no longer alive. They are as dead as Windows Phone, and there is no return. 10. Make the Start menu button follow the system accent color - Nope (760+ upvotes) Microsoft "has got this," but the blue Start button is here to stay. Do you like what Microsoft did to Windows 11's Start menu? What features are still missing in your opinion? Share your thoughts in the comments.
  • Recent Achievements

    • Week One Done
      slackerzz earned a badge
      Week One Done
    • Week One Done
      vivetool earned a badge
      Week One Done
    • Reacting Well
      pnajbar earned a badge
      Reacting Well
    • Week One Done
      TBithoney earned a badge
      Week One Done
    • First Post
      xuxlix earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      672
    2. 2
      ATLien_0
      286
    3. 3
      Michael Scrip
      223
    4. 4
      +FloatingFatMan
      195
    5. 5
      Steven P.
      141
  • Tell a friend

    Love Neowin? Tell a friend!