Stored Procedure

Introduction

The Entity Framework allows you to use stored procedures to perform predefined logic on database tables. Raw SQL queries can be used to execute a stored procedure.

Here is a simple stored procedure, it will insert a customer record into a Customers table when executed.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
   OBJECT_ID(N'[dbo].[CreateCustomer]') AND type in (N'P', N'PC'))

BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[CreateCustomer]
        @FirstName Varchar(50),
        @LastName Varchar(50),
        @Address Varchar(100)
    AS
    INSERT INTO dbo.Customers(
        [FirstName],
        [LastName],
        [Address]
    )
    VALUES (@FirstName, @LastName,@Address)
    '
END
GO

The ExecuteSqlCommand() method can be used to execute database commands as a string, and it will return the number of affected rows.

using (var context = new MyContext())
{
    int affectedRows = context.Database.ExecuteSqlCommand("CreateCustomer @p0, @p1, @p2",
        parameters: new[] 
        {
            "Elizabeth",
            "Lincoln",
            "23 Tsawassen Blvd."
        });
}

Similarly, you can execute stored procedures for Update and Delete commands.

Prime Library

Performance

Expression Evaluator

More Projects...

Want to use EF6 in .NET Core?

Stored Procedure

Introduction

Performance

Expression Evaluator

Related