Notes on Stored ProceduresOutlineTiered ArchitecturesN-Tiers and so on …Web Services for Data AccessPersistence FrameworkO/R MappingO/R Mapping - Additional ConceptsThe CRUD PatternWhy use stored procedures?There is a debateTheory & PracticeAdditional WisdomStored Procedures Are Code!!!New in SQL Server 2005Today’s LabRead Example - get_Order Details_1Update Example - update_Order Details_1Notes on Stored ProceduresCSSE333 Intro to Database Systems--Winter 2005-2006Outline•Application Design•Tiered Architectures•O/R Mapping•The CRUD Pattern•Why use stored procedures?•Advice•LabTiered Architectures•The standard three-tiered architecture consists of the presentation, application and data access tiers. •The problem is many applications require a high coupling between the bottom two tiers. Changing database architectures often dramatically affects the system at the application tier. •Can be due to poor design, but also strongly influenced by changing database connection technologies.N-Tiers and so on …•There are many designs variations based on the 3-tier architecture.•Many introduce a data services layer to abstract the database connection technology.From: www.novicksoftware.comWeb Services for Data Access•Web services are Web-based enterprise applications that use open, XML-based standards and transport protocols to exchange data with calling clients. - Sun.com•SOAP ("Simple Object Access Protocol") is a simple XML-based protocol to let applications exchange information over HTTP. – W3C•Useful for distributed application designs.•Can be used to reduce coupling to database connection technologies.•Especially common in modern websites.Persistence Framework•The application layer should use a persistence framework (or layer) to solve the coupling problem.•A good persistence framework will hide the fact that objects are even being stored in a relational database.O/R Mapping•Most modern business application development projects use object technology such as Java or C# to build the application software and relational databases to store the data.•There is an impedance mismatch between object and relational design methods.•Object-relational mapping (also ORM) is used to map object-oriented programming objects to relational databases.O/R Mapping - Additional Concepts•Shadow information is any data that objects need to maintain, above and beyond their normal domain data, to persist themselves. This typically includes primary key information, particularly when the primary key is a surrogate key that has no business meaning, concurrency control markings such as timestamps or incremental counters, and versioning numbers. … A common practice is for each class to implement an isPersistent boolean flag that is set to true when the data is read in from the database and set to false when the object is newly created. •Lazy Reads - An important performance consideration is whether the attribute should be automatically read in when the object is retrieved. When an attribute is very large, for example the picture of a person could be 100k whereas the rest of the attributes are less than 1k, and rarely accessed you may want to consider taking a lazy read approach. The basic idea is that instead of automatically bringing the attribute across the network when the object is read you instead retrieve it only when the attribute is actually needed. This can be accomplished by a getter method, an operation whose purpose is to provide the value of a single attribute, that checks to see if the attribute has been initialized and if not retrieves it from the database at that point. •From: http://www.ambysoft.com/essays/mappingObjects.html•Another decent reference: http://www.chimu.com/publications/objectRelational/The CRUD Pattern•Organizes the persistence operations of an application into Create, Retrieve, Update and Delete operations that are implemented by a persistence layer. - Patterns in Java, Volume 3.•The CRUD pattern is usually implemented on the database. Stored procedures are the recommended method for SQL Server.•It is suggested that they be created in an automated manner.•They will have to deal with O/R mapping, but are not expected to contain application logic!!!Why use stored procedures? The reasons for using Stored Procedures to implement the Data Storage Layer instead of allowing ad hoc SQL statements are:•The best possible performance •Removes the SQL code from the other layers of the application •Prevents SQL injection attacks •Prevents casual table browsing and modifications•Easy to create atomic transactions•Can catch exceptions•Recommended by vendors-Derived from: www.novicksoftware.com and Rob Howard's BlogThere is a debateFlame war really…•Issues with performance•Issues with DBA vs Developer control•Issues with DB portability•Issues with source control•Issues with dogma•Dynamically SQL & Parameterized Queries can be preferred•In general concatenated SQL is to be avoided to prevent injection attacks.string query = "SELECT * FROM Users WHERE UserID = '" + tbxUserId.Text + "'";Theory & Practice•Business logic often gets into data layer•Many times designers model objects after data structure (and visa versa)•Many glue layers are tedious to create and maintain manually.•A many tiered application can seem over architected and may not have immediate advantages•Abstraction increases load•Legacy code complicates things•Best solution requires consideration of caseAdditional Wisdom•Do not use a highly privileged SQL account (like SA) to access from the application. (Also do not use a highly privileged domain account).•Encrypt the DB connection string.•Avoid fetching static information from the DB every time you need it. Cache it in the application.•Do not use SELECT * … ALWAYS define the table columns once you have determined them.•Do not prefix user sprocs with sp_. This actually stands for ‘system procedure’. Your procedure may become inaccessible.•Use parameter validation at all layers. You may get unexpected results otherwise.Derived from Doug Seven’s “10 Things You Shouldn't Do with SQL Server”Stored Procedures Are Code!!!•Use source control. (This can be difficult.)•Always include comments, good variable names, and exception handling.•Try to re-use code. T-SQL does not support OOP. However, you can call other sprocs and UDFs.•Test and validate