Should I Place My Business Logic In Code Or In Stored Procedures?

I see this age old question asked on forums, LinkedIn conversations, interviews, and in general developer discussions. To tell you the truth it has been discussed 1000 times before and I am going to add one more to the pile here.

This is a great debate/conversation, and I have been a part of it several times myself as well. I can honestly say there is no 'right' or 'wrong' in my opinion (OK maybe there is but let me be a little PC here as to not to offend those DB folks right from the start), but only 'pros' and 'cons' to each method. I think both have their advantages and disadvantages, and no option really screams out as the obvious choice on the surface unless there is say some significant performance advantage one way or the other. However I have my bias on 1 side of the argument as I will detail below.

In my experience this is usually who goes on each side of the debate: people with a stronger or more prominent SQL/Database background will opt for placing all the logic in the database where as true software engineers will tend to place those rules within the application and apply the appropriate architecture, design patterns, and frameworks to organize and describe them.

At the highest level Database folks will argue to place that logic in stored procedures or the database because then you can "make changes on the fly" without the hassle of "code recompile and pushing out new releases", where developers that want that logic in code will argue TSQL is such a limited language to debug and express complex business rules that can be evaluated so much better in code.

Well as far as I am concerned that is about as far as that side's (Database crowd) argument goes for me. While their argument is valid, recompiling and redeploying an app because of rule changes is typically not an impossible task. In fact with today's modern deployment options regardless of technology being used this argument is not as strong as it used to be.

In fact the whole idea of "making changes without redeployment" or "changes on the fly" is a bit skewed to begin with. Code of any type should never be changed in a production environment without being tested. Managed code is more likely to be tested using unit tests or at a minimum running the application through some end-user tests before deployment. The temptation to change a stored procedure without testing it fully is ever present, and without a stringent environment with DBAs locking down the database, this option could be exercised all too easily and is a bad idea.

As a developer I tend to try and place these rules (business rules, calculations, math, etc.) in .NET managed code. TSQL is not the easiest language to explain and implement complex business rules and is also more difficult to debug and test. My philosophy is typically to get the raw data back to the code and then manipulate it from there. You have so much more power with the .NET framework and managed code to explain the rules in code than with TSQL. While it is possible with TSQL, have you ever seen one of these 500 line stored procs with a gazillion rules that some database person was flexing their muscle at? Yuck! We are in the business of writing well formed code using OO principals and this is just not possible using TSQL and database languages.

Which side do I fall on? That's easy. I am a developer and engineer at heart and I believe in using the right design and architecture, and that those business rules (or a majority of them) should reside within the application. What's the database for? Getting data and executing some logic, but the brain of solving the business problem for me is not going to be within a stored procedure, view, or user defined function. The only time I flex a tad on this is with 1 off custom queries for say reporting where the Object Model does not currently support the data needed to create the output. In this case I might extend the logic a bit on the database side. If I can extend my Object Model to support the reporting without an extreme amount of work then by all means I will do it. However, when at all possible I keep any business rules or logic within the application.

So the answer is not always so straight forward, but hopefully this post help you think about where this logic should reside (......in the code. Wait, did I just say that!?!?). Now go ask this question on a SQL forum or blog and see what response you get. It will probably be along the lines of... "Put it all in Stored Procedures!!" Have fun coding :P

Applying and Using a SSL Certificate With A Self-Hosted WCF Service

(Please read my 1st post on this topic called Create A Self-Signed SSL Certificate Using IIS 7 if you need to know how to create a self signed SSL certificate)

If you create enough WCF services eventually you are probably going to need to self host your WCF Service as opposed to using IIS. The most common way of doing this is probably a Windows Service which is a great environment to host WCF services as they automatically begin and end with the server and OS being up or down and are always running in the background.

However when using a Windows Service you might find it is not as straight forward to use a SSL certificate with your exposed WCF service. This is true as there is no wizard style interface for applying SSL certificates to Windows Services like IIS provides, however after following the steps outlined here you will see that it is not so bad. Overall the process is easy to repeat for multiple services and ports once you are used to doing it.

The 1st step is to configure your WCF service to use a binding and configuration that supports HTTPS and SSL. For this example, we will use a simple WCF service that uses the 'basicHttpBinding' configuration. As with any WCF configuration, remember the references to the contract Interface and implementing class are case sensitive so if you run into any errors check your spelling. The main configuration changes to allow HTTPS on the service are to change the metadata publication binding to 'mexHttpsBinding' and by setting 'httpsGetEnabled' to 'true' on the service behavior. The SSL is handled by transport-level security using certificates. The entire service configuration is below:

 <system.serviceModel>

<!--WCF Services Defined-->
<services>
  <service name="WcfServiceTest.MyWCFService"
           behaviorConfiguration="MyWCFServiceBehavior">
    <endpoint address=""
              binding="basicHttpBinding"
              bindingConfiguration="BasicHttpSSLBinding"
              contract="WcfServiceTest.IMyWCFService" />
      <endpoint address="mex"
                binding="mexHttpsBinding"
                contract="IMetadataExchange" />
      <host>
        <baseAddresses>
          <add baseAddress="https://DevMachine1234:8050/WCFServices/MyWCFService" />
        </baseAddresses>
      </host>
  </service>
</services>

<!--WCF Service Behavior Configurations-->
<behaviors>
  <serviceBehaviors>
    <behavior name="MyWCFServiceBehavior">
      <serviceMetadata httpsGetEnabled="true" httpGetEnabled="false" />
      <serviceDebug includeExceptionDetailInFaults="false" />
    </behavior>
  </serviceBehaviors>
</behaviors>

<!--WCF Service Binding Configurations-->
<bindings>
  <basicHttpBinding>
    <binding name="BasicHttpSSLBinding">
      <security mode="Transport" />
    </binding>
  </basicHttpBinding>
</bindings>

</system.serviceModel>

Notice the 'baseAddress' value I used. The most important part here is that the host name of the service must match that of the SSL certificate I bind to the port selected. In my instance the machine name is 'DevMachine1234' and so is the name of my SSL certificate. You can actually create a SSL certificate named 'localhost' if you prefer and use that instead. If this is a true production service and you make the service address MyWcfService.com, then you will need to get a SSL cert with a matching name from a provider like Verisign or GoDaddy. Test this all and have a complete understanding before spending money on SSL certificates; no use wasting money on a misunderstood process.

Also notice I selected port '8050'. This is arbitrary, but remember there are certain port restrictions, and only 1 SSL certificate can be bound to any single port. We now need to apply the SSL certificate for our service (we are using a self-signed certificate for testing purposes) to the 8050 port before we can test our service.

The 1st thing we need is the Thumbprint value from the SSL certificate. We can use IIS to look at the installed SSL cert and grab the thumbprint value. Open up IIS, and select 'Server Certificates'. Double-click on the appropriate SSL certificate and go to the details tab. Scroll to the bottom and find the 'thumbprint' value of the certificate. Copy it out from the 1st character to the last into notepad and remove all spaces. Take caution to not copy the 1st space as it will translate into a '?' (question mark) when pasted into the command window to apply the SSL certificate in a later step. Save this for the next step.

Begin by clicking on the 'Start' button in windows and type in 'CMD' in the search box but do not press enter yet. Right-click the 'cmd' program and select 'Run as administrator' as shown below. This ensures we add the SSL certificate to the port with proper rights.

We need to use the 'netsh' tool (replacement in W2K8 and Windows 7 for the old httpcfg.exe tool) to apply the SSL certificate. We need the certificate thumbprint (gathered above) and an application ID value. The easiest way to get the 'appid' value is to use the GUID in the 'AssemblyInfo' file for the WCF project as pictured below. Copy it out as well and have ready for applying to the command window in the next step.

The command to add the SSL certificate to the port we configured in our WCF service is as follows:

netsh http add sslcert ipport=0.0.0.0:8050 certhash=3e49906c01a774c888231e5092077d3d855a6861 appid={2d6059b2-cccb-4a83-ae08-8ce209c2c5c1}

After applying the command and pressing enter you should see a message that the certificate was successfully added.

If you want, you can verify it has been added and view any SSL certificate that has been applied by entering the following command:

netsh http show sslcert

You will see the command window output in groups displaying any SSL certificates that have been applied as shown below. Notice you can see the SSL certificate 'hash' value which is the thumbprint value we used.

If you were doing this process in a production environment or on a remote server, make sure to apply the appropriate SSL certificate that has been imported and installed on the server 1st, to the port you need prior to starting the Windows Service (it actually will not work without it).

At this point we are ready to start our service on our local machine. In VS.NET I set my service (not the host in this instance) to be the startup project. I then begin debugging. You will notice the 'WCF Test Client' tool will pop-up and display all locally hosted services. This is probably were most folks will encounter some errors. Read the error description because they are typically meaningful and give direction on what to fix. Odds are it is caused by the SSL certificate not being applied to the same port configured, a mismatched name on the certificate to the service's domain name, or a mistake in configuration. Go over all steps again to make sure everything is correct. As you can see below our WCF service exposed via a HTTPS endpoint is up and running.

The last step to verify everything is working properly is to copy the address of the locally hosted service and consume it in another test application.

Add the copied address as a service reference to the project and make sure you do not receive any exceptions.

The successfully added service will be shown in Solution Explorer with the name you provided.

My recommendation is to get all of this working as I did in a local test environment 1st and understand all of the pieces. For those totally new to WCF it can be a lot to digest and fixing problems can take time and a deeper understanding of WCF so be patient.

For more information on the other commands available to the netsh.exe command line tool (like removing SSL certificates from a port), please see the following reference:

Netsh commands for HTTP