• 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

    • Sure, I'm with you, but then again I don't run these companies and don't know all the details. As far as sales go, lots of MS software/services is sold through 3rd parties. I've always seen them for the longest time, they'll sell you the MS stuff and then also charge you a fee to act as direct support and so on.
    • It always makes me smile, when someone makes a statement that is talking for everybody. Trust me, just because you think no one wants it, does not make it true.
    • Windows 11 gets context menu changes, new accesibility features, and more in build 27881 by Taras Buria Microsoft has released a new Windows 11 preview build for Canary Channel Insiders. Build 27881 is now available for download with improved voice typing, speech recap in Narrator, context menu changes, some minor taskbar enhancements, and more. With today's release, voice typing received a new toggle that lets you turn off or on profanity filtering. This will let you say bad words without Windows 11 censoring them (or vice versa). You can find it by pressing Win + H and clicking the Settings icon > Filter Profanity. Narrator now has a new "Speech Recap" feature, which lets you access a transcript of everything that Narrator has spoken. To access this feature, press Narrator Key + Alt + X. You will find the last 500 strings of spoken content. If needed, you can export the transcript by pressing the Narrator key + Ctrl + X (copy to clipboard). Other changes in build 27881 include renewed security certificates, a new needy state pill on the taskbar (apps that require your attention), a reworked accessibility flyout with feature categories, a built-in image editor for the Share UI, and the ability to share files directly to apps that support Windows sharing (Right Click > Share with). The Settings app received a new section with frequently asked questions about your PC and its specs, and a redesigned printer rename UI. Here is the list of fixes: [General] Fixed an issue for Insiders on the previous build causing some of the text in Windows to not be using the correct characters, causing nonsense to display in places like Task Manager and more. This impacted characters outside of A-Z – including Chinese and accented characters in German and Polish. Fixed an issue where booting into safe mode wasn’t working, with a message saying “Something happened and your PIN isn’t available” for some people. [File Explorer] Fixed an issue where File Explorer would crash when tapping the View button with touch. [Start menu] Fixed an issue which was causing Start menu to crash on launch for some Insiders in the last few flights. [Taskbar] Fixed an underlying issue which was causing keyboard focus to get set to widgets in the taskbar on login, which could make widgets unexpectedly invoke. Fixed an issue which could cause the media controls in the preview windows for apps in the taskbar (that support it) to flicker. Fixed an issue where when using Tab and Shift + Tab to move keyboard focus around the taskbar, if you went backwards at least once you could get keyboard focus stuck on the taskbar frame, leading some screen readers to just say “pane”. Fixed an issue where WIN + CTRL + wasn’t working to cycle between windows of open apps for that position on the taskbar. [Input] Fixed an issue where typing Japanese with the touch keyboard might stop working after switching to typing with an English keyboard and back. Fixed an issue where voice typing wouldn’t start from the touch keyboard when using the Chinese (Simplified) narrow layout. Fixed an issue where when using the Symbols section of the touch keyboard, pressing the key to change pages might unexpectedly insert a character into password fields. [Settings] Fixed an issue where you couldn’t switch active hours from manually to automatic in Windows Update. Adjusted the location of the intensity and color boost sliders under Settings > Accessibility > Color Filters, so the color previews at the top of the page are visible while adjusting the sliders. [Voice access] Fixed an issue which was causing voice access crashes when using Spanish. [Other] Fixed an issue where if you clean installed Windows using the installation media, it would unexpectedly generate an empty Windows.old folder when you were done. Made another fix for Insiders unexpectedly seeing an Administrative Templates error message when opening Group Policy Editor. Here is the list of known bugs: [General] [IMPORTANT NOTE FOR COPILOT+ PCs] If you are joining the Canary Channel on a new Copilot+ PC from the Dev Channel, Release Preview Channel or retail, you will lose Windows Hello pin and biometrics to sign into your PC with error 0xd0000225 and error message “Something went wrong, and your PIN isn’t available”. You should be able to re-create your PIN by clicking “Set up my PIN”. We’re working on the fix for an underlying issue where the taskbar is unexpectedly not showing acrylic material after upgrading to the latest Canary builds. This is impacting a few other scenarios, leading to unexpected black or white in some UI. [NEW] The “Reset this PC” option under Settings > System > Recovery will not work after upgrading to Build 27881. You can still use the “Go back” option to roll back to the previous build or download the ISO for Build 27881 here to reinstall Windows. [Settings] [NEW] We’re investigating an issue in this build which may cause Settings to crash when opening microphone properties under Settings > System > Sound. You can find the announcement post here.
    • Companies are still reluctant to use full HDMI 2.1 FRL bandwidth ports and hide behind deceitful HDMI 2.1 TDMS which is nothing but HDMI 2.0 with a fancier name. I am pretty sure the wretched HDMI org will come up with another fancy naming scheme to bring all previous HDMI standards under 2.2 nomenclature. The next gen GPUs are at least 2 years away so lot can change at HDMI org.
    • Gears of War: Reloaded beta expanded following rocky start, adds more time and content by Pulasthi Ariyasinghe The original Gears of War is getting a fresh remaster later this year, but ahead of that, The Coalition is hosting a couple of multiplayer beta sessions to test out the returning features as well as new additions. While the first of these Gears of War: Reloaded technical tests just finished this weekend, all was not well with the experience, and the developer has now responded with a solution that should make fans happy. For many that logged in during the weekend looking to jump into the classic team deathmatch mode across the maps Gridlock, Raven Down, and Gold Rush, what actually faced them were matchmaking issues. Reports of not finding players to fill the 4v4 rounds and staying in the "Finding Opponents" stage were many. If a game did connect, there were also reports of major lag spikes, disconnections, and stutters, making the multiplayer unplayable. "We heard you," the development team said on a social media post. "More maps. More modes. More time." While the next session will still run from June 20 through June 22, nine more hours have been added to the upcoming play session, with the ending set for 9pm PDT. The content has also been expanded, with Execution coming to the competitive playlist, while Canals will be there as a new map. With that addition, the second weekend will carry classic Team Deathmatch as well as King of the Hill and Execution competitive modes across Gridlock, Raven Down, and Gold Rush maps. There don't seem to be any plans on making the weekend an open beta though, with the only ways of gaining access still being Xbox Game Pass Ultimate or PC Game Pass subscribers, Gears of War: Ultimate Edition digital owners, or having a Gears of War: Reloaded pre-order. Gears of War: Reloaded launches on PC, Xbox Series X|S, PlayStation 5, and Xbox Game Pass on August 26, 2025. It will also be a free upgrade to anyone who had purchased Gears of War: Ultimate Edition before May 5, 2025.
  • Recent Achievements

    • Week One Done
      Wayne Robinson earned a badge
      Week One Done
    • One Month Later
      Karan Khanna earned a badge
      One Month Later
    • Week One Done
      Karan Khanna earned a badge
      Week One Done
    • First Post
      MikeK13 earned a badge
      First Post
    • Week One Done
      OHI Accounting earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      695
    2. 2
      ATLien_0
      267
    3. 3
      Michael Scrip
      202
    4. 4
      +FloatingFatMan
      170
    5. 5
      Steven P.
      136
  • Tell a friend

    Love Neowin? Tell a friend!