The result-set returned
by the stored procedure is also a strongly typed collection. We can create entity
methods for the stored procedure manually and map it to the corresponding stored
procedure or we can use the Object Relational Designer tool to map the
stored procedures.
Chapter 4
[ 111 ]
LINQ to SQL maps stored procedures to the methods using the function attribute,
and if required, it uses the parameter attribute. The function attribute supports name
property which specifies the name of the method that corresponds to the database
stored procedure object.
Let us create a simple stored procedure using entity classes, created in the previous
examples. This stored procedure will take Category as a parameter and return
the number of items present in the database for the category. Let us name the
stored procedure as GetNumberofItemsforCategory. The SQL text for the stored
procedure will look like the following:
CREATE PROCEDURE [dbo].[GetNumberofItemsforCategory]
@Category nvarchar(50)
AS
BEGIN
declare @itemCount int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Pages:
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188