Make your own free website on Tripod.com

Online Resume - VB.Net - C#.Net SQL Server Developer

DataBase / Stored Proc Example

Home | Tim Levito Resume | C# .Net Multi-Tiered Application Example | DataBase / Stored Proc Example | ADO.Net / DataAccess Example | WinForm / Business Layer Example | ASP.Net / Javascript / Html Web Example | Contact Me

Now it's time to Connect To, Query, and Update the Database.

One of the major concerns in any multi-tiered application is encapsulation of the company’s data storage and intellectual property. In this section of my work example, I will illustrate how the database is accessed by means of Stored Procedure calls providing a layer of protection for the database from .Net and other unmanaged accessing code. 

 

This example will incorporate the use of SQL Server Management Studio Development Environment and MicroSoft TSQL.

databasetight.gif

The table Diagram above is the SQLServer 2005 documentation tool for Viewing the Data Structures and Relationships in a SQL Database.

memberinsertdiagram.gif

In the above subsection of the database diagram, 3 related tables are shown. These tables are the Member, Adult, and Juvenile tables and are the data storage structures used in the Database to store and retrieve Membership Information.
 
Is part of the Design Specification, the member is the base table for storing all primary member information. Its Primary key is member_no and enforces a One to One Primary Key Constraint with the Adult and Juvenile tables. As I would never recommend this structure as a DBA, as a developer we often have to work with structures we normally wouldn't design ourselves.
 
The Specification for member is that a member can either be an Adult or a Juvenile. A Juvenile is any member under the age of 18 and must have a sponsoring adult to be a member. With these business policies in place, any information for address or expiration for any member is kept in the record of the sponsoring adult.
 
Instead of using literal TSQL code anywhere outside of the Database, the safest most reusable technique for processing data manipulation language is in a Store Procedure or Stored PROC. Below is a section of the Stored Procedure Responsible for Adding a new member.
 

addmemberstproc1.gif

In a normal Stored Procedure, Parameters are defined and passed in, local variables are declared for use within the Procedure, and the data validations are performed before any inserts, updates, or deletes are performed.
 
Most business operation transactions require changes to multiple tables in the database in order for the information system to accurately and correctly reflect the entire transaction.
 
Because database transactions often behave in an "all or nothing" type of requirement.
 
The use of the "begin tran" and "end tran" keywords are used to logically group a series of database manipulations which, if all complete successfully, will then save and be applied to the database.
 
In the case of an error, the transaction can be "rollback" ed in the catch block for the error.

addmemberstproc.gif

There are several advantages to using Stored Procedures but for the purposes of this example I will list a few.

1. More Secure -  you are passing parameters not TSQL to the database and that creates another layer of protection and helps to prevent SQL Injection.
2. Source is easily found - If twenty different processes were capable of adding a member they could all be funneled through the stored procedure for consistent transactions.
3. Centralized logic - if the rules changed, one central update is better than twenty changes throughout the application code.
4. No One but the local development team can access the actual code- all outside developers ever see is the signature and parameter list. Makes things a lot tougher for malicious programmers.
 
With all that being said.... the next section will take a look at the Data Access Layer which is written in C# and ADO.net for a safe and reliable connection between the database and the n-tiered .Net Application.

Click Here to Contact Me

To Discuss a Current Project Opportunity further please send me an Email with your contact information and a good time to reach you to TLevito@yahoo.com