How to execute a Stored Procedure with Entity Framework Code First

November 08, 2013 by Anuraj

.Net ASP.Net MVC EF Code First Entity Framework SQL Server

Recently I worked on a project, which I started as code first and then I forced to switch to Database first. This post is about executing procedures from EF code first. Here is my class structure and procedures.

class DatabaseContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}

class Book
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ISBN { get; set; }
    public int AuthorId { get; set; }
}

class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

And here is my stored procedures

CREATE PROCEDURE usp_CreateBook
@BookName VARCHAR(200), @ISBN VARCHAR(200), @BookId INT OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Books(Name, ISBN, AuthorId) VALUES(@BookName, @ISBN, 1)
SET @BookId = (SELECT SCOPE_IDENTITY())

CREATE PROCEDURE usp_CreateAuthor
@AuthorName VARCHAR(200), @Email VARCHAR(200) = NULL
AS
INSERT INTO Authors(Name, Email) VALUES(@AuthorName, @Email)

CREATE PROCEDURE usp_GetAuthorByName
@AuthorName VARCHAR(200)
AS
SELECT [Id] ,[Name] ,[Email] FROM [Authors]
WHERE Name = @AuthorName

And you can execute using DbContext.Database class. The DbContext.Database.ExecuteSqlCommand() method helps to executes the given DDL/DML command against the database. And it will return the number of rows affected.

var affectedRows = context.Database.ExecuteSqlCommand("usp_CreateAuthor @AuthorName, @Email",
    new SqlParameter("@AuthorName", "author"),
    new SqlParameter("@Email", "email"));

Or you can use without creating the SqlParameters.

var affectedRows = context.Database.ExecuteSqlCommand
    ("usp_CreateAuthor @AuthorName = {0}, @Email= {1}", 
    "author", "email");

The DbContext.Database.SqlQuery method helps to return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type.

var authors = context.Database.SqlQuery<Author>("usp_GetAuthorByName @AuthorName", 
    new SqlParameter("@AuthorName", "author"));

This method will return an DbRawSqlQuery, which you can enumerate using For / ForEach loop. For executing procedure with output parameter.

var bookIdParameter = new SqlParameter();
bookIdParameter.ParameterName = "@BookId";
bookIdParameter.Direction = ParameterDirection.Output;
bookIdParameter.SqlDbType = SqlDbType.Int;
var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT",
    new SqlParameter("@BookName", "Book"),
    new SqlParameter("@ISBN", "ISBN"),
    bookIdParameter);
Console.WriteLine(bookIdParameter.Value);

Happy Programming :)</author>

« Creating a Web API Controller with dynamic type

Preventing Cross-Site Request Forgery (CSRF) Attacks in WebAPI »

Copyright © 2024 Anuraj. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Powered by Jekyll. Hosted with ❤ by GitHub