Database Reference
In-Depth Information
Note
With
ExecuteNonOuery()
, you can submit virtually any SQL statement, including Data Definition
Language (DDL) statements, to create and drop database objects like tables and indexes. But in industry, the most
common use of
ExecuteNonQuery()
by developers is to insert, update, and delete the rows.
As you have seen so far, all the SQL statements to carry out tasks such as
SELECT
,
INSERT
,
UPDATE
, or
DELETE
have been hard-coded into the C# code. But most of the time, developers write stored procedures
to perform SQL operations; stored procedures work just like functions and can take parameters to
perform a task. One of the advantages of stored procedures is having a unified SQL statement created at
the SQL Server level, because your ADO.NET code just calls it.
Working with Stored Procedures
As you learned in Chapter 6, a stored procedure is a collection of SQL statements that allows you to
perform a task repeatedly. Rather than hard-coding the SQL statements in the C# code, it is an advisable
practice to use stored procedures, because it takes advantage of SQL Server's compilation and offers
performance-based reuse. A stored procedure simply replaces the hard-coded occurrences of code that
performs actions such as inserting, updating, and deleting.
Creating a Stored Procedure to Perform a Delete Operation
In the earlier CommandNonQuery exercise, you saw an insert operation on the Sales.Currency table of
the
AdventureWorks2008
database. Now let's do a delete operation on the same table, but through a
stored procedure.
Try It: Creating a Stored Procedure to Be Used with C#
Let's create a stored procedure using SQL Server Management Studio that will take the CurrencyCode
value as a parameter to delete a currency from the Sales.Currency table in the
AdventureWorks
database.
It requires only one input parameter.
1.
Open SQL Server Management Studio. In the Connect to Server dialog, select
localhost\<Your SQL Server Name> as Server name and then click Connect.
2.
In the Object Explorer, expand the Databases node, select the AdventureWorks
database, and click the New Query window. Enter the following query and
click Execute. You should see the results shown in Figure 13-11.
Create procedure sp_DeleteCurrency
@currCode nvarchar
(
3
)
As
Delete From Sales
.
Currency
Where CurrencyCode
=
@currCode
