Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
If memory serves me correctly, in the first Hitchhiker’s Guide published in 1995 (is that only 12 years ago?), I began a decade of discussions on managing business rules. In those days, SQL Server was a lot simpler, would run on an Intel 286 with about as much RAM as I have in my new Citizen watch—or so it seemed. Nevertheless, business rules are still a serious problem because developers were still spending an inordinate amount of time weaving these rules into client applications only to have to start over when the rules changed 30 minutes after the application was released to the users. We faced the same problems at EDS in the mid 70’s—I spent much of my day adjusting business rules used to adjudicate Part B healthcare claims. Since those days (over 30 years ago), I’ve been designing, implementing, and teaching others how to implement business rules using techniques that don’t require developers to recompile and redeploy the client applications. Sure, in today’s ASP world of browser-based applications, business rules are deployed into centrally managed applications hosted on IIS servers. However, consider that there might be two or twenty-two-hundred applications associated with a web-based application domain that might have to be modified, retested, and redeployed when a rule changes. Even with a mechanism to automatically imbed the business rules into the application on compile (perhaps a common class), developers still have to touch each affected application—not good.
Understanding Business Rules
I generally separate business rules into a couple of general categories as shown below. One might want to separate out data validation from other “business” logic, but I find that they are often too closely intertwined to do so.
Client-side UI rules and constraints: These limit or validate the values supplied by the user—the biggest source of errors your application will have to deal with. Remember one of my axioms: “All data is evil until proven innocent.” These constraints manage the minimum, maximum, string length, default, mask, last-used, data type, range of values, rights, or other properties of a data column and its associated data entry field. UI constraints can apply to a single data entry cell or to several cells taken as a group. For example, ensuring that a Zip code provided for Washington State is between 98001 and 99403 validates (to some extent) both the state and the postal code. Client-side UI constraints should also ensure that the data conforms to a datatype, but consider that if the value is only valid if it lies within a specified range or a specific set of values, simply verifying that it’s an Integer won’t help much.
Server-side rules and constraints: Like client-side business rules, these constraints revalidate the data values but do so with universal mechanisms applied to all applications that access (especially those that insert or update) the data. In this case, checks are made on individual column values but also against other columns in the same table or other tables—beyond simple Primary Key/Foreign Key relationships. For example, when selling an item, the business rules need to verify that there is stock on hand to fill the order. We’ve all seen web sites that seem to ignore this fundamental rule—you can’t sell what you don’t have. Other rules need to be executed that re-order at a given re-stocking point whereas other rules don’t re-order when the item is at long last sold out.
Implementing Client-Side Data Validation and Business Rules
Implementing these rules can be done in a variety of ways. One approach that developers can leverage with SQL Server places common business rule logic in stored procedures and Extended Properties. Not familiar with Extended Properties? Don’t feel alone—not many developers are. See this link for more information. Consider that every object managed by SQL Server can have zero or a dozen (dozen) named Extended Property values defined and managed by your code. This is a great place to store values or rule criteria that should not be hard-coded into client applications. If you implement a set of Extended Properties, your application (whether it’s an ASP page, Windows Forms program, or XML Web Service) can quickly fetch these values on first invocation, modify the locally cached UI controls or other validation logic, and provide an automatically morphing validation interface. Yes, I expect this approach lends itself better to Windows Forms applications and other UI approaches that don’t have to make a round trip to the server and rebuild the code every time the user clicks a button.
Implementing Server-Side Validation
Over the years, DBAs and developers (who play DBAs on TV) have found that stored procedures are an excellent way to implement business rules. IMHO, this is one of their remaining benefits—especially in a team-development environment. A stored procedure might not run any faster than an ad hoc (client-generated) query, but its judicious use can help protect the database (by requiring access to the database solely through rights-managed stored procedures). Stored procedures also can provide an efficient way to handle server-side business-rule management. That’s because they’re designed to handle complex inter-table query logic. If developers are not so sure about their TSQL skills, they can try to implement stored procedures using Common Language Runtime executables (as described in Chapter 13 of Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)). This is easier said than done and can bring your server (and your job) to its knees if you aren’t careful.
Using Rules—while they last
Another approach developers and DBAs have used for years is SQL Server “Rules”—especially rules coupled with user-defined (aliased) data types. Unfortunately, Microsoft does not want to continue to support this approach in future versions—but they’ve been saying that since SQL Server 2000 (and it’s still working in SQL Server 2008). A powerful feature of SQL Server Rules is the ability to assign the Rule to a user-defined (aliased) data type. For example, if you defined a UDT as “AreaCode” that maps to a smallinteger, you subsequently can assign a rule to that UDT to restrict what values can be applied to the columns defined with the UDT. If you choose to change the rule logic (which can be fairly complex) at a later time, it’s automatically applied to all data added from that point forward (and yes, there are other options as well).
That said, Microsoft has been telling us for several versions (since 2000) that it did not want developers to use this approach—they should use SQL Server Check Constraint instead. I would agree but AFAICT, the Constraint approach cannot apply a single Check Constraint to columns in more than one Table. If you use the bound Rule approach, I encourage you to get up on Microsoft Connect and vote for it.
So, how does Visual Studio help implement these business rules? Sadly, it doesn’t—at least not beyond helping developers code and debug stored procedures. That’s the bad news. The good news is that Billy Hollis has created a number of custom UI controls that can help deal with complex client-side validation rules. A free set of his controls is included on my book’s DVD. SQL Server Management Studio also leaves developers out in the cold when it comes time to debug stored procedures, but it can help create Rules and bind them to UDTs. It’s also where you can define and edit your own Extended Properties. It’s too bad the Visual Studio and SQL Server groups at Microsoft can’t learn to work together. It’s almost as if they work on different sides of the planet. Oh, that’s right, they do!
About the Author
 |
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He’s worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He’s written seven editions of the Hitchhiker’s Guide to Visual Basic and SQL Server and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker’s Guide to SQL Server 2000 Reporting Services. Bill is a top-rated speaker and frequents conferences all over the world, including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He’s also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer’s Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He’s available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog. |
