Web API, Entity Framework insert stored procedure

Question

I am trying to learn Web API + Entity Framework. To use stored procedures for database access through Web API.

I've successfully done get call with select stored procedure. This is my code:

public class UsersController : ApiController
{
        [Authorize]
        public IEnumerable<spSelUsers_Result> GetUsers()
        {
            using(UsersEntities users = new UsersEntities())
            {
                return users.spSelUsers().ToList();
            }
        }
}

But I'm stuck here with insert procedure to create new record.

[Authorize]
[Route("AddUser")]
public class Users
{
        public string user_name { get; set; }
        public string first_name { get; set; }
        public string last_name { get; set; }
        public string mobile_no { get; set; }
        public string email { get; set; }
        public string user_pic { get; set; }
        public int strowner { get; set; }
        public DateTime dbtstamp { get; set; }
}

public IEnumerable<spInsUsers_Result> PostUsers([FromBody] UsersEntities Users)
{
        using(UsersEntities Usrs = new UsersEntities())
        {
            return Usrs.spInsUsers();
        }
}

From various tutorials online I managed to get up to here... I don't know how to assign values to parameters and pass them...

Also any suggestions on weather to use IEnumerable or anything else..

Currently I'm clueless...please help

Edit: 1

Stored Procedure

GO
ALTER PROCEDURE [dbo].[spInsUsers]
(
    @user_name nvarchar(50),
    @first_name nvarchar(50),
    @last_name nvarchar(50),
    @mobile_no varchar(10),
    @email nvarchar(50),
    @user_pic varchar(256),
    @strowner int,
    @dbtstamp datetime2
)
AS
    SET NOCOUNT OFF;
INSERT INTO [tbl_users] ([user_name], [first_name], [last_name], [mobile_no], [email], [user_pic], [strowner], [dbtstamp]) VALUES (@user_name, @first_name, @last_name, @mobile_no, @email, @user_pic, @strowner, @dbtstamp);

SELECT user_id, user_name, first_name, last_name, mobile_no, email, user_pic, strowner, dbtstamp FROM tbl_users WHERE (user_id = SCOPE_IDENTITY())

SP Mapping:

Whether you use web API of anything else isn't relevant. Therefore, you're showing the wrong code. You can't insert a new User. So please show the stored procedure and also how you mapped the User entity in your context class. — Gert Arnold
– Gert Arnold, Commented Sep 23, 2017 at 19:39
Updated the question with stored procedure and mapping. Pls check. — Mashhoor Gulati
– Mashhoor Gulati, Commented Sep 23, 2017 at 20:10
Well, for one, you're returning too much from the sp. EF only needs the database-generated values, if any (as in identity values and/or computed columns). — Gert Arnold
– Gert Arnold, Commented Sep 23, 2017 at 20:14

crevenant · Accepted Answer · 2017-09-23 18:29:33Z

1

If you are using a database first approach once the procedures have been imported to your model you can just pass parameters as you would with any normal function.

http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

If you are using code first you will need to do things a bit differently.

https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures

I hope these links help you out.

answered Sep 23, 2017 at 18:29

crevenant

463 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mashhoor Gulati Over a year ago

Well I've gone through "entityframeworktutorial.net/…" multiple times, but somehow was not getting it properly. Finally done. Final working solution in Answer. Thanks for pointing out to go through that tutorial again. Voting u up.

who-aditya-nawandar · Accepted Answer · 2017-09-23 19:44:02Z

0

something like this?

db.Database.ExecuteSqlCommand(
                        "EXEC SP_AddUpdateResponse @QuestionID, @UserID, @AnswerValue, @ReviewID",
                        new SqlParameter("@QuestionID", questionId),
                        new SqlParameter("@UserID", 9999),
                        new SqlParameter("@AnswerValue", answer),
                        new SqlParameter("@ReviewID", id)
                    );

taken from here : Executing a update/insert stored procedure with entity framework 4.1?

answered Sep 23, 2017 at 19:44

who-aditya-nawandar

1,25412 gold badges50 silver badges105 bronze badges

2 Comments

Mashhoor Gulati Over a year ago

Can you pls elaborate. This seems of way old version's answer.

who-aditya-nawandar Over a year ago

You said that you din't know how to assign parameters. This is how you assign parameters. db.Database.ExecuteSqlCommand will execute the stored procedure. If there's some specific error that you are getting, let us know what it is.

Mashhoor Gulati · Accepted Answer · 2017-09-24 07:12:32Z

0

Finally a working solution. Thanks all for contribution.

[Authorize]
        [Route("Add_User")]

        public HttpResponseMessage PostUsers1([FromBody] tbl_users Users)
        {
            using (var context = new UsersEntities())
            {
                var response = context.spInsUsers(Users.user_name, Users.first_name, Users.last_name, Users.mobile_no,
                    Users.email, Users.user_pic, Users.strowner, DateTime.Now);

                var message = Request.CreateResponse(HttpStatusCode.Created, Users);
                message.Headers.Location = new Uri(Request.RequestUri +
                    response.ToString());

                return message;

            }

        }

answered Sep 24, 2017 at 7:12

Mashhoor Gulati

1273 silver badges13 bronze badges

Collectives™ on Stack Overflow

Web API, Entity Framework insert stored procedure

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related