• 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

    • In my experience I have used a paid programme I can recommend people try the full featured trial version of . It is Ez Cd Extractor . It is so complete when it came to burning cds and I loved the simplicity of importing a saved playlist file to make any kind of CD audio to share with family and friends to mail out . Piece of cake . and the intergrated CD Text editor is kind of amazing to see the edited text on a car cd player actually read . amazing . I missed thinking about this topic an ancient version of Roxio suite that had all these soft-wares built in I loved the functionality of it . I had a complete box with full key. too bad I threw all that away lol Another software to check out is DB power amps free Cd Writer progamme if you want to try a professional solution . You'll have to find out how to install it properly with the codecs you have your files music for . You can always opt out for Windows Media Player legacy to burn audio discs . G'day everyone ! I bought on ebay a week or two ago a brand new HP slot loading drive DVD writer it is amazing . Make sure you look at ebay for a portable slot loading HP dvd writer hopefullly if you spot one brand new like i did BUY IT and enjoy your dvd movies , cd burning remotely . I highly recommend ! So nice in black and slot loading ! Cheers !
    • "What's that? Microsoft recommends Intel CPUs? My next computer must have Intel then." - Nobody
    • Apple warns Australia against joining EU in mandating iPhone app sideloading by David Uzondu Apple has issued a warning to Australia, essentially telling the country not to follow the European Union's lead in making iPhone app sideloading a requirement. This communication comes as the Australian federal government considers new rules that could force Apple to open up its iOS ecosystem, much like what happened in Europe with recent legislation. Since iOS 17.4 and iPadOS 18, users in the EU have been able to get apps from outside the official App Store, a direct consequence of the DMA designating Apple as a "gatekeeper". The Australian government floated a proposal in a paper released late last year. The paper suggested "designating" digital platforms like Apple's App Store. Being designated this way means these platforms would have to follow new rules intended to keep them from limiting competition. The government pointed to Apple's in-app payment system, which usually comes with a commission, and the lack of sideloading as likely targets for regulation. Right now, apps like Netflix and Spotify can't let users subscribe through their iOS apps without giving Apple a big cut, and they're not even allowed to tell users where to find a better deal. Apple, in its response to this Australian paper, stated that Australia should not use the EU's Digital Markets Act "as a blueprint". The company's core argument is that the changes mandated by the EU's DMA, which came into full effect in March 2024, introduce serious security and privacy risks for users. Apple claims that allowing sideloading and alternative app stores effectively opens the door for malware, fraud, scams, and other harmful content. The tech company also highlighted specific concerns from its European experience, alleging that its compliance there has led to users being able to install pornography apps and apps that facilitate copyright infringement, things its curated App Store aims to prevent. Apple maintains that its current review process is vital for user protection, and that its often criticized 30% commission applies mainly to the highest earning apps, with most developers paying a lower 15% rate or nothing. It is worth noting that Apple's implementation of DMA requirements in the EU, which includes a "Core Technology Fee" for apps distributed outside its store, has already drawn scrutiny from European Commission officials who question if these measures truly comply with the DMA's idea of keeping the market fair. The Australian government has not yet detailed its next steps in this process, and the Treasury still needs to publish the full submissions to its proposal paper, including Apple's complete arguments. Source: The Guardian
    • Oh the Chinese crybabies who couldn't accept the fact that they lost GOTY to Astro Bot? Not interested
    • Download this SQL Essentials For Dummies eBook (worth $10) for free by Steven Parker FOR DUMMIES is a trademark of John Wiley & Sons, Inc. A right-to-the-point guide on all the key topics of SQL programming SQL Essentials For Dummies is your quick reference to all the core concepts of SQL—a valuable common standard language used in relational databases. This useful guide is straightforward—with no excess review, wordy explanations, or fluff—so you get what you need, fast. Great for a brush-up on the basics or as an everyday desk reference, this book is one you can rely on. Strengthen your understanding of the basics of SQL Review what you've already learned or pick up key skills Use SQL to create, manipulate, and control relational databases Jog your memory on the essentials as you work and get clear answers to your questions Perfect for supplementing classroom learning, reviewing for a certification, and staying knowledgeable on the job, SQL Essentials For Dummies is the convenient, direct, and digestible reference you've been looking for. Claim your complimentary eBook worth $10 for free, before the offer ends on June 17. How to get it Please ensure you read the terms and conditions to claim this offer. Complete and verifiable information is required in order to receive this free offer. If you have previously made use of these free offers, you will not need to re-register. While supplies last! Download the SQL Essentials For Dummies eBook (worth $10) for free Offered by Wiley, view other free resources The below offers are also available for free for a limited time: Winxvideo AI V3.0 Lifetime License for PC ($69.95 Value) FREE – Expires 6/8 Aiarty Image Enhancer for PC/Mac ($85 Value) FREE – Expires 6/8 Solutions Architect's Handbook, Third Edition ($42.99 Value) FREE – Expires 6/10 AI and Innovation ($21 Value) FREE – Expires 6/11 Unruly: Fighting Back when Politics, AI, and Law Upend [...] ($18 Value) FREE - Expires 6/17 SQL Essentials For Dummies ($10 Value) FREE – Expires 6/17 Continuous Testing, Quality, Security, and Feedback ($27.99 Value) FREE – Expires 6/18 VideoProc Converter AI v7.5 for FREE (worth $78.90) – Expires 6/18 Macxvideo AI ($39.95 Value) Free for a Limited Time – Expires 6/22 The Ultimate Linux Newbie Guide – Featured Free content Python Notes for Professionals – Featured Free content Learn Linux in 5 Days – Featured Free content Quick Reference Guide for Cybersecurity – Featured Free content We post these because we earn commission on each lead so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin The above deal not doing it for you, but still want to help? Check out the links below. Check out our partner software in the Neowin Store Buy a T-shirt at Neowin's Threadsquad Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: An account at Neowin Deals is required to participate in any deals powered by our affiliate, StackCommerce. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through the branded deals site.
  • Recent Achievements

    • One Month Later
      EdwardFranciscoVilla earned a badge
      One Month Later
    • One Month Later
      MoyaM earned a badge
      One Month Later
    • One Month Later
      qology earned a badge
      One Month Later
    • One Year In
      Frinco90 earned a badge
      One Year In
    • Apprentice
      Frinco90 went up a rank
      Apprentice
  • Popular Contributors

    1. 1
      +primortal
      449
    2. 2
      +FloatingFatMan
      247
    3. 3
      snowy owl
      235
    4. 4
      ATLien_0
      199
    5. 5
      Xenon
      145
  • Tell a friend

    Love Neowin? Tell a friend!