Call stored procedure from Entity Framework 6.1.3 code-first

Question

Please consider this code - I created a stored procedure:

CREATE PROCEDURE InsertRegion
    @RegionID int,
    @RegionDesc nvarchar(50)
AS
BEGIN
    INSERT INTO [dbo].[Region]
    VALUES (@RegionID, @RegionDesc)
END

and I want to call this procedure from my repository class:

 using (Model1 ent = new Models.Model1())
 {
     List<SqlParameter> parameters = new List<SqlParameter>();
     parameters.Add(new SqlParameter("RegionID", 100));
     parameters.Add(new SqlParameter("RegionDesc", "Nima"));

     ent.Database.SqlQuery<Region>("exec InsertRegion", parameters.ToArray());
  }

but nothing happens. I change the last line to:

ent.Database.SqlQuery<Region>("exec InsertRegion @RegionID, @RegionDesc ", parameters.ToArray());

but again, nothing happens.

Where is the problem?

Thanks

Don't you get any error? What happens if you try to execute the procedure directly against DB using SQL Management Studio? — Viktors Telle, Dec 25 '16 at 12:01
it pass to end of method without any error and nothing happens. When I exec sp, a record would insert to table — Arian, Dec 25 '16 at 12:04
You could try to debug and see the generated SQL (http://stackoverflow.com/a/20751723/2804621). Or you could run SQL profiler to see if the call to DB is made. — Viktors Telle, Dec 25 '16 at 12:11
Instead of `SqlQuery` method which is for data retrieval, try `ExecuteSqlCommand` method. — Ivan Stoev, Dec 25 '16 at 12:18

Iraj · Accepted Answer · 2016-12-25T16:04:17.850

1

Use without exec :

using (Model1 ent = new Models.Model1())
{
    var regionID= new SqlParameter("@RegionID", 100);
    var regionDesc= new SqlParameter("@RegionDesc", "Nima");

    ent.Database.SqlQuery<Region>("InsertRegion @RegionID ,@RegionDesc", regionID ,regionDesc);
}

but in your sp not returning any recored so you can use this also :

using (Model1 ent = new Models.Model1())
{
    var regionID= new SqlParameter("@RegionID", 100);
    var regionDesc= new SqlParameter("@RegionDesc", "Nima");

    ent.Database.ExecuteSqlCommand("InsertRegion @RegionID ,@RegionDesc", regionID ,regionDesc);
}

edited Dec 25 '16 at 16:04

answered Dec 25 '16 at 14:13

Iraj

1,492
5
18
42

Since this call is really **not** returning any data (no result set), using `.SqlQuery` doesn't really make a lot of sense here...... – marc_s Dec 25 '16 at 14:34

Call stored procedure from Entity Framework 6.1.3 code-first

1 Answers1