Creating the Employee Stored Procedures


As before, creating the Employee stored procedures is a straightforward process. In addition to the four standard stored procedures (getone, getall, save, delete), there are several additional stored procedures. These stored procedures handle the Employee/Territory relationship. However, you will note there are some additional complexities in your stored procedures now because you are handling relationships between tables. Because of this, you will look at these stored procedures one at a time. To begin with, execute the SQL in Listing 9-1 against the Northwind database.

Listing 9-1: The Employee Delete Stored Procedure

start example
 CREATE PROCEDURE usp_employee_delete @id int AS DELETE FROM EmployeeTerritories WHERE EmployeeID = @id UPDATE Orders SET EmployeeID = null WHERE EmployeeID = @id DELETE FROM Employees WHERE EmployeeID = @id 
end example

Tip

You can handle some of these complexities by cascade updates and cascade deletes. Although these make programming the stored procedures easier, they can be difficult to manage (you do not always get the result you want because you are relying on a mechanism you do not really control). The choice to use cascading updates and deletes is entirely yours. I prefer not to use them because I like to have the maximum amount of control over my code that I can.

First, you need to delete or alter information in all of the related tables. You can delete the information in the EmployeeTerritory table, but you cannot just delete orders because an employee was deleted. So, because you are allowed to have an order that is not associated with an employee, you will just set the EmployeeID equal to null in the Orders table. In a real application, the employee would probably be marked as inactive in some way but never deleted. After all, you would want to keep track of the employees you have had and who was responsible for an order. Execute the SQL in Listing 9-2 against the Northwind database.

Listing 9-2: The Employee Getall Stored Procedure

start example
 CREATE PROCEDURE usp_employee_getall AS SELECT EmployeeID, LastName, FirstName, Title FROM Employees 
end example

Second, this getall routine returns all of the rows in the Employees table, but it does not return all of the columns in the table. It returns enough information to fill a list (in this case your list form) and to load a single, full object. This is the reason that the name of the method that calls this stored procedure is called LoadProxy because you are not loading up a full object. Whenever you create a proxy object, that object must have the ability to fully load itself. In this case you are supplying the table key, so you know you can retrieve the full record from this.

The getone stored procedure, shown in Listing 9-3, retrieves a single employee, but it also returns additional information that is required because of the relationships of which this table is a part. Execute this SQL against the Northwind database.

Listing 9-3: The Employee getone Stored Procedure

start example
 CREATE PROCEDURE usp_employee_getone @id int AS SELECT a.*, b.FirstName 'ReportsToFirstName', b.LastName 'ReportsToLastName', b.Title 'ReportsToTitle' FROM Employees a LEFT OUTER JOIN Employees b ON a.ReportsTo = b.EmployeeID WHERE a.EmployeeID = @id SELECT TerritoryID FROM EmployeeTerritories WHERE EmployeeID = @id 
end example

This procedure looks a little different than what you have seen before. There are two select statements here. So how do you handle this? Well, the beauty of the dataset is that it simply creates multiple tables within the dataset. This allows you to return information from multiple tables with one call to the database. It also allows you to return the information without duplicating the records that are being returned. The first select statement returns a complete Employee object and to whom that employee reports. This is enough information to load up a proxy of another employee (the manager). The second select statement returns all of the territories with which an employee is associated.

Caution

Be careful when using multiple select statements in a stored procedure. SQL Server is the only Relational Database Management System (RDBMS) that can return multiple result sets. This stored procedure would not work in Oracle. This can have an impact in the future if the database needs to be migrated to a different type of database. If you expect to perform this type of migration in the future, break this up into two separate stored procedures.

Saving an employee is a bit more complicated than saving a Region or a Territory. First, you are required to save the employee because you need the primary key from the Employees table to use in the EmployeeTerritories table. Then you need to save all of the territories in which an employee works. The solution to this is two stored procedures: one to save the employee and one to save the Employee/Territory relationship (this stored procedure can be called multiple times, one per related territory). Execute the save stored procedure (as shown in Listing 9-4) against the Northwind database.

Listing 9-4: The Employee Save Stored Procedure

start example
 CREATE PROCEDURE usp_employee_save @id int, @lname nvarchar(20), @fname nvarchar(10), @title nvarchar(30), @courtesy nvarchar(25), @birth datetime, @hire datetime, @address nvarchar(60), @city nvarchar(15), @region nvarchar(15), @postal nvarchar(10), @country nvarchar(15), @phone nvarchar(24), @extension nvarchar(4), @photo image, @notes ntext, @reports int, @photopath nvarchar(255), @new_id int output AS IF @id = 0    BEGIN       INSERT INTO Employees (LastName, FirstName, Title,       TitleOfCourtesy, BirthDate, HireDate, Address, City, Region,       PostalCode, Country, HomePhone, Extension, Photo, Notes,       ReportsTo, PhotoPath)       VALUES(@lname, @title, @title,       @courtesy, @birth, @hire, @address, @city, @region,       @postal, @country, @phone, @extension,       @photo, @notes, @reports, @photopath)       SET @new_id = (SELECT @@IDENTITY)    END ELSE    BEGIN       UPDATE  Employees       SET     LastName = @lname,               FirstName = @fname,               Title = @title,               TitleOfCourtesy = @courtesy,               BirthDate = @birth,               HireDate = @hire,               Address = @address,               City = @city,               Region = @region,               PostalCode = @postal,               Country = @country,               HomePhone = @phone,               Extension = @extension,               Photo = @photo,               Notes = @notes,               ReportsTo = @reports,               PhotoPath = @photopath       WHERE   EmployeeID = @id       SET @new_id = @id    END 
end example

The save stored procedure is identical to the save stored procedures you have already seen. But you have the added requirement of needing to save the territories to which an employee is related. For this, enter the SQL in Listing 9-5.

Listing 9-5: The Employee Territory Relationship Stored Procedure

start example
 CREATE PROCEDURE usp_employee_territory_insert @employee_id int, @territory_id nvarchar(20) AS INSERT INTO EmployeeTerritories (EmployeeID, TerritoryID)    VALUES (@employee_id, @territory_id) 
end example

Because you already have the code for an update in your save stored procedure, you only need to add one more stored procedure. Listing 9-6 shows this procedure. Execute this stored procedure against the Northwind database.

Listing 9-6: The Employee Territory Relationship Delete Stored Procedure

start example
 CREATE PROCEDURE usp_employee_territory_delete @employee_id int AS DELETE FROM EmployeeTerritories WHERE EmployeeID = @employee_id 
end example

Because there are only two columns in the EmployeeTerritories join table, performing updates on the table is not worthwhile. The reason for this is that there is no single column primary key on the table. When you perform the update, you are updating the primary key. Although this is perfectly acceptable for a join table, it is just as easy to delete the record and re-create it.

Note

You could argue quite easily that a single call to the database to update the record is more efficient, but I am always uncomfortable with an update that alters the primary key. Other than that, there really is no reason why you cannot have an employee territory update stored procedure.

Now that you have the stored procedures built, you can start looking at the business rules.




Building Client/Server Applications with VB. NET(c) An Example-Driven Approach
Building Client/Server Applications Under VB .NET: An Example-Driven Approach
ISBN: 1590590708
EAN: 2147483647
Year: 2005
Pages: 148
Authors: Jeff Levinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net