Transcript Introduction to ASP.NET

Stored Procedures & User
Defined Functions
MacDonald Ch. 23
MIS 424
Professor Sandvig
Today
• Stored Procedures
– Store SQL statements on DB
• User Defined Functions (UDF)
• Database Transactions
– Rollback
Stored Procedures
•
So far:
– Putting SQL queries in .NET code
– Use command object to pass to database
•
Alternative:
– Stored procedures
– Store SQL query on database
– Call query from .aspx page or business
object
Stored Procedures
• Benefits of SP:
– Reusability
• Call same SP from many locations in code
– Loose coupling
• Create clear separation between code & data
• Reduces dependencies
– Changes in data source do not affect code
• N-Tier Programming
– Diagram (Source: Microsoft)
Benefits of Stored Procedures
• Division of duties
• Programmers vs. database admin
• Security
– Set permissions on procedures
• Users see only data accessible via procedure
Benefits of Stored Procedures
• Efficiency
– SQL compiled
– Execution plan saved
• Execute complex procedures
– Triggers
– Multiple SQL statements
– Programming languages
• Transact-SQL
• .NET languages
Disadvantages
• More code, greater abstraction
• Not fully supported by all databases
– MySQL
• Recently added
• Limited
– Microsoft Access
• Store basic SQL statements
Creating
• Visual Studio
Creating
Syntax & Example
Example:
•
GetTitlesSP.aspx
•
See handout
Views & Functions
– Views
• “Virtual” tables
– No data, definition only
• View defined by sql statement
• May include data from several tables
• Similar to stored procedure except no parameters
– Benefits:
• Hide table details
• Set permissions
User Defined Functions
• Functions (user defined functions)
• Retrieve only
• May use parameters
• May call from SPs, other functions, SQL
commands
• Benefits
– Reusability
– Hide complexity
User Defined Functions
• Creating in Visual Studio
User Defined Functions
• Syntax
Create FUNCTION dbo.GetProductCategories
(
@ProductID int
)
RETURNS TABLE
AS
RETURN Select c.CatLabel
FROM tblCategories c, tblProductCategories p
where c.CategoryID = p.CategoryID AND
p.ProductID = @ProductID
sql = "select * from dbo.GetProductCategories(16) order by CatLabel";
Transactions
• Many transactions have dependency
• Bank transfer
– Remove $$ from one account
– Add to another
• On-line purchase
– Charge credit card
– Ship item
• Item out-of-stock?
Transactions
If one task fails:
• Prior transactions are rolled back
• Supported by most commercial databases
– Not mySQL
• Database keeps a log of transaction
• Easy to use
• Example: source, output
Summary
• Advanced Data Techniques
– Stored Procedures
• Parameters
– Transactions
• Goal:
– Modularity
– Reusability
– Robust