Calling a Stored Procedure at ASP.net

In SQL Server you create stored procedures that take some action or manipulate data in some way in your database. Once you create a stored procedure it becomes a compiled efficient set of code. You can call stored procedures from your ASP.NET pages. This tip shows you how to call a stored procedure that does not have any parameters nor does it return any value.

USE IT This ASP.NET page calls a stored procedure that adds a record to the Employees table. The stored procedure has this syntax:

CREATE PROCEDURE AddGarbageRecord AS

Insert Into Employees (LastName, FirstName, BirthDate, Salary, EmailAddress) values ('Smith', 'Jane', '12/17/1947', 55000, 'janegna.com')

GO and place the SQL text that calls the stored procedure into the Command object. Notice that the call consists of the name of the stored procedure preceded by the Exec keyword:

DBSP.CommandText = "Exec AddGarbageRecord"

The Command will connect to the database through the Connection object:

DBSP.Connection = DBConn DBSP.Connection.Open

And the stored procedure is executed:

DBSP.ExecuteNonQuery()
lblMessage.Text = "Stored procedure completed."