Databases Reference
In-Depth Information
using (NorthwindEntities context = new NorthwindEntities())
{
IEnumerable<Product> products = context.
GetProductsToReorder
();
foreach (Product p in products)
Console.WriteLine(p.ProductName);
}
The generated method returns an
IEnumerable
and not an
IQueryable
. Although you can
use the familiar LINQ operators to manipulate it further, keep in mind that these opera-
tions are performed in the application memory and not by the database server. Instead, it
is often best to modify the definition of the stored procedure itself and have it return a
more focused result set.
Insert, Update, and Delete Stored Procedures
The Entity Framework also allows you to use stored procedures to perform insert, update,
and delete operations instead of generating SQL statements dynamically. When combined
with restriction of the application permissions to only read data, this technique can help
reduce accidental data corruption and locking due to casual browsing, when developers or
administrators use a tool, such as SQL Server Management Studio to interactively explore
live data in a production system.
Here's an example of the stored procedure that inserts a new Product entity into the
Products table:
CREATE PROCEDURE [dbo].[Products_Insert]
@ProductName nvarchar(40), @SupplierID int, @CategoryID int,
@QuantityPerUnit nvarchar(20), @UnitPrice money, @UnitsInStock smallint,
@UnitsOnOrder smallint, @ReorderLevel smallint, @Discontinued bit
AS
BEGIN
INSERT INTO Products
(ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock,
UnitsOnOrder, ReorderLevel, Discontinued)
VALUES
(@ProductName, @SupplierID, @CategoryID,
@QuantityPerUnit, @UnitPrice, @UnitsInStock,
@UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT SCOPE_IDENTITY() as ProductID;
END
Notice that this stored procedure takes a set of parameters, each representing a particular
table column, such as ProductName and SupplierID. The stored procedure performs the
INSERT
statement and a
SELECT
statement to return the
ProductID
value of the newly
inserted row.
