Entity Framework - Stored Procedure (3)

In Entity Framework - Stored Procedure (2),
we explore ways to use the result set of a stored procedure in the ObectContext to materialize a collection of an entity type. We observe that EF treats such a stored procedure as a strongly-typed function returning a collection of entity objects. We also note that EF does not treat SELECT stored procedures as "first-class citizens", by which I mean that such stored procedures are not used to in the dynamic query auto-generated in association with a LINQ to Entities query.

In this and the subsequent few blogs, we review how to use stored procedures in EF to change data in the database. We will see that change-processing stored procedures are indeed treated as "first-class citizens", i.e., they can replace the dynamic queries. I use
Using Stored Procedures for Change Processing in the ADO.NET Entity Framework
as reference.

Due to the limitation of the current EF, for a given table, we must either use stored procedures for INSERT, UPDATE and DELETE or not use stored procedures at all. So we will implement all three types of stored procedures (INSERT, UPDATE and DELETE ) for the three tables (ef.Policy, ef.Driver, ef.DriverName) in our data model now and discuss only the INSERT part.
In this blog, we will introduce the stored procedures as defined on the SQL Server. The next blog will describe the necessary cutomization of the entity model so that EF wiil use those stored procedures to change the store data instead of dynamic queries. Subsequent blogs will describe each type (INSERT, UPDATE, DELETE) of stored procedures in detail.
The following lists the stored procedures created on the SQL Server:

---- Stored Procedures
-- ef.qp_InsertPolicy
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_InsertPolicy]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_InsertPolicy;
GO
CREATE PROCEDURE ef.qp_InsertPolicy (
@dtExpDt DATE,
@vcRiskState VARCHAR(25),
@vcDescription VARCHAR(100)
)
AS
BEGIN
INSERT ef.Policy (expDt, riskState, description) VALUES
(@dtExpDt, @vcRiskState, @vcDescription);
SELECT SCOPE_IDENTITY() AS policyId, P.ts AS ts
FROM ef.Policy P
WHERE policyId = SCOPE_IDENTITY();
END;
GO
GRANT EXEC ON [ef].qp_InsertPolicy TO Public;
GO

-- ef.qp_UpdatePolicy
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_UpdatePolicy]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_UpdatePolicy;
GO
CREATE PROCEDURE ef.qp_UpdatePolicy (
@iPolicyId INT,
@ts_orig TIMESTAMP,
@dtExpDt_curr DATE,
@vcRiskState_curr VARCHAR(25),
@vcDescription_curr VARCHAR(100)
)
AS
BEGIN
UPDATE ef.Policy SET
expDt = @dtExpDt_curr,
riskState = @vcRiskState_curr,
description = @vcDescription_curr
WHERE policyId = @iPolicyId AND ts = @ts_orig;
SELECT P.ts
FROM ef.Policy P
WHERE policyId = @iPolicyId;
END;
GO
GRANT EXEC ON [ef].qp_UpdatePolicy TO Public;
GO

-- ef.qp_DeletePolicy
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_DeletePolicy]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_DeletePolicy;
GO
CREATE PROCEDURE ef.qp_DeletePolicy (
@iPolicyId INT,
@ts_orig TIMESTAMP
)
AS
BEGIN
DELETE ef.Policy
WHERE policyId = @iPolicyId AND ts = @ts_orig;
END;
GO
GRANT EXEC ON [ef].qp_DeletePolicy TO Public;
GO

-- ef.qp_InsertDriver
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_InsertDriver]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_InsertDriver;
GO
CREATE PROCEDURE ef.qp_InsertDriver (
@uidDriverId UNIQUEIDENTIFIER,
@iPolicyId INT,
@iAge TINYINT
)
AS
/*
Ideally, we should let the default to generate NEWSEQUENTIALID() for driverId and
somewhere retrieve that value (similarly to SCOPE_IDENTITY()) and return that as
a result set and in MSL use tag to send it back to EDM.
But, for Primary Key, that technique only works for IDENTITY field.
This link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2808788&SiteID=1
proposes a solution via explicit transaction. We are currently not implementing it.
*/
BEGIN
--SELECT @uidDriverId = COALESCE(@uidDriverId, NEWID());
INSERT ef.Driver (driverId, policyId, age) VALUES
(@uidDriverId, @iPolicyId, @iAge);
SELECT ts
FROM ef.Driver
WHERE driverId = @uidDriverId;
END;
GO
GRANT EXEC ON [ef].qp_InsertDriver TO Public;
GO

-- ef.qp_UpdateDriver
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_UpdateDriver]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_UpdateDriver;
GO
CREATE PROCEDURE ef.qp_UpdateDriver (
@uidDriverId UNIQUEIDENTIFIER,
@ts_orig TIMESTAMP,
@iPolicyId_curr INT,
@iAge_curr TINYINT
)
AS
BEGIN
UPDATE ef.Driver SET
policyId = @iPolicyId_curr,
age = @iAge_curr
WHERE driverId = @uidDriverId AND ts = @ts_orig;
SELECT ts
FROM ef.Driver
WHERE driverId = @uidDriverId;
END;
GO
GRANT EXEC ON [ef].qp_UpdateDriver TO Public;

-- ef.qp_DeleteDriver
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_DeleteDriver]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_DeleteDriver;
GO
CREATE PROCEDURE ef.qp_DeleteDriver (
@uidDriverId UNIQUEIDENTIFIER,
@iPolicyId_orig INT,
@ts_orig TIMESTAMP
)
AS
BEGIN
DELETE ef.Driver
WHERE driverId = @uidDriverId AND ts = @ts_orig;
END;
GO
GRANT EXEC ON [ef].qp_DeleteDriver TO Public;

-- ef.qp_InsertDriverName
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_InsertDriverName]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_InsertDriverName;
GO
CREATE PROCEDURE ef.qp_InsertDriverName (
@uidNameId UNIQUEIDENTIFIER,
@uidDriverId UNIQUEIDENTIFIER,
@vcLastName VARCHAR(100),
@vcFirstName VARCHAR(25),
@vcMiddleName VARCHAR(25),
@dtEffDt DATE
)
AS
BEGIN
INSERT ef.DriverName (nameId, driverId, lastName, firstName, middleName, effDt) VALUES
(@uidNameId, @uidDriverId, @vcLastName, @vcFirstName, @vcMiddleName, @dtEffDt);
SELECT ts
FROM ef.DriverName
WHERE nameId = @uidNameId;
END;
GO
GRANT EXEC ON [ef].qp_InsertDriverName TO Public;
GO

-- ef.qp_UpdateDriverName
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_UpdateDriverName]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_UpdateDriverName;
GO
CREATE PROCEDURE ef.qp_UpdateDriverName (
@uidNameId UNIQUEIDENTIFIER,
@ts_orig TIMESTAMP,
@uidDriverId_curr UNIQUEIDENTIFIER,
@vcLastName_curr VARCHAR(100),
@vcFirstName_curr VARCHAR(25),
@vcMiddleName_curr VARCHAR(25),
@dtEffDt_curr DATE
)
AS
BEGIN
UPDATE ef.DriverName SET
driverid = @uidDriverId_curr,
lastName = @vcLastName_curr,
firstName = @vcFirstName_curr,
middleName = @vcMiddleName_curr,
effDt = @dtEffDt_curr
WHERE nameId = @uidNameId AND ts = @ts_orig;
SELECT ts
FROM ef.DriverName
WHERE nameId = @uidNameId;
END;
GO
GRANT EXEC ON [ef].qp_UpdateDriverName TO Public;
GO

-- ef.qp_DeleteDriverName
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[ef].[qp_DeleteDriverName]') AND OBJECTPROPERTY([object_id], N'IsProcedure') = 1)
DROP PROCEDURE [ef].qp_DeleteDriverName;
GO
CREATE PROCEDURE ef.qp_DeleteDriverName (
@uidNameId UNIQUEIDENTIFIER,
@uidDriverId_orig UNIQUEIDENTIFIER,
@ts_orig TIMESTAMP
)
AS
BEGIN
DELETE ef.DriverName
WHERE nameId = @uidNameId AND ts = @ts_orig;
END;
GO
GRANT EXEC ON [ef].qp_DeleteDriverName TO Public;