Build Your Own ASP.NET 3.5 Website Using C# & VB (62 page)

340

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

UPDATE

This clause identifies the statement as one that modifies the named table in the

database.

table name

We provide the name of the table we’re updating.

SET

The SET clause specifies the columns we want to modify, and gives their new

values.

column names and values

We provide a list of column names and values, separated by commas.

WHERE condition(s)

This condition specifies which records are being updated.

Updating Records

Be sure always to include a WHERE clause in your UPDATE statement. If you fail

to do so,
all
the records will be updated, which is not usually what you want!

The DELETE Statement

The DELETE statement removes records from the database. You could use it to delete

all records from the Departments table, like so:

FROM Departments

Fortunately, executing this command will throw an error if the foreign key that

links the Departments and Employees tables is in place, because removing the departments records would leave the employee records referencing nonexistent departments, which would make your data inconsistent (note that the reverse
isn’t
true: you
could
delete all the employees if you wanted to, but please don’t!).

In case you’re curious, here’s the error message that would be generated by the DELETE command above: Licensed to [email protected]

Speaking SQL

341

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint

"FK_Employees_Departments". The conflict occurred in database

"Dorknozzle", table "dbo.Employees", column 'DepartmentID'.

The statement has been terminated.

You could also delete that new department you created earlier:

FROM Departments

WHERE Department = 'Cool New Department'

The command above would execute successfully because there aren’t any employees

linked to the new department.

Real-world References

Remember that in real-world scenarios, items should be referenced by their IDs,

not by name (as is shown in the example above). Also note that if you mistype

the name of a department when you’re executing that command, no rows will be

affected.

Deleting Records

Like the UPDATE command, the WHERE clause is best used together with DELETE;

otherwise, you can end up deleting all the records in the table inadvertently!

Stored Procedures

Stored procedures are database objects that group one or more T-SQL statements.

Much like VB or C# functions, stored procedures can take parameters and return

values.

Stored procedures are used to group SQL commands that form a single, logical action.

For example, let’s say that you want to add to your web site functionality that allows

departments to be deleted. However, as you know, you must delete all of the department’s employees before you can delete the department itself. Licensed to [email protected]

342

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

To help with such management issues, you could have a stored procedure that

copies the employees of that department to another table (called EmployeesBackup),

deletes those employees from the main Employees table, then removes the department from the Department table. As you can imagine, having all this logic saved as a stored procedure can make working with databases much easier.

We’ll see a more realistic example of a stored procedure in the next chapter, when

we start to add more features to the Dorknozzle project, but until then, let’s learn

how to create a stored procedure in SQL Server, and how to execute it.

The basic form of a stored procedure is as follows:

(

@Parameter1 DataType,

@Parameter2 DataType,

⋮

)

AS

--an optional comment

⋮
SQL Commands

The leading “--” marks a comment. The parameter names, as well as the names of

variables we can declare inside stored procedures, start with @. As you might expect,

their data types are the same data types supported by SQL Server.

The stored procedure shown below creates a new department whose name is specified through the first parameter. It then creates a new employee whose name is specified as the second parameter, assigns the new employee to the new department,

and finally deletes both the new employee and the new department. Now, such a

stored procedure wouldn’t make much sense in reality, but this example allows

you to learn a few interesting details that you’ll be using frequently as you develop

applications, and it uses much of the theory you’ve learned in this chapter. Take a

look at it now:

(

@NewDepartmentName VARCHAR(50),

@NewEmployeeName VARCHAR(50),

@NewEmployeeUsername VARCHAR(50)

Licensed to [email protected]

Speaking SQL

343

)

AS

--Create a new department

INSERT INTO Departments (Department)

VALUES (@NewDepartmentName)

--Obtain the ID of the created department

DECLARE @NewDepartmentID INT

SET @NewDepartmentID = scope_identity()

--Create a new employee

INSERT INTO Employees (DepartmentID, Name, Username)

VALUES (@NewDepartmentID, @NewEmployeeName, @NewEmployeeUsername)

--Obtain the ID of the created employee

DECLARE @NewEmployeeID INT

SET @NewEmployeeID = scope_identity()

--List the departments together with their employees

SELECT Departments.Department, Employees.Name

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

--Delete the new employee

WHERE EmployeeID = @NewEmployeeID

--Delete the new department

WHERE DepartmentID = @NewDepartmentID

Execute this code to have the DoThings stored procedure saved to your Dorknozzle

database. You can now execute your new stored procedure by supplying the required

parameters as follows:

EXECUTE DoThings 'Research', 'Cristian Darie', 'cristian'

If you execute the procedure multiple times, you’ll get the same results, since any

data that’s created as part of the stored procedure is deleted at the end of the stored

procedure:

(1 row(s) affected)

(1 row(s) affected)

Department

Name

---------------------------------------------------------------Licensed to [email protected]

344

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Executive

Zak Ruvalcaba

Marketing

Jessica Ruvalcaba

Engineering

Ted Lindsey

Engineering

Shane Weebe

Marketing

David Levinson

Accounting

Geoff Kim

Research

Cristian Darie

(7 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

So, what does the stored procedure do? Let’s take a look at the code step by step.

The beginning of the stored procedure code specifies its name and its parameters:

(

@NewDepartmentName VARCHAR(50),

@NewEmployeeName VARCHAR(50),

@NewEmployeeUsername VARCHAR(50)

)

AS

The parameters include a department name, an employee name, and an employee

username.

CREATE PROCEDURE and ALTER PROCEDURE

To modify an existing stored procedure, you’ll need to use ALTER PROCEDURE

instead of CREATE PROCEDURE. Feel free to play with your existing procedure,