I am very new to Entity Framework 6 and I want to implement stored procedures in my project. I have a stored procedure as follows:
ALTER PROCEDURE [dbo].[insert_department]
@Name [varchar](100)
AS
BEGIN
INSERT [dbo].[Departments]([Name])
VALUES (@Name)
DECLARE @DeptId int
SELECT @DeptId = [DeptId]
FROM [dbo].[Departments]
WHERE @@ROWCOUNT > 0 AND [DeptId] = SCOPE_IDENTITY()
SELECT t0.[DeptId]
FROM [dbo].[Departments] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[DeptId] = @DeptId
END
Department class:
public class Department
{
public int DepartmentId { get; set; }
public string Name { get; set; }
}
modelBuilder
.Entity<Department>()
.MapToStoredProcedures(s =>
s.Update(u => u.HasName("modify_department")
.Parameter(b => b.Department, "department_id")
.Parameter(b => b.Name, "department_name"))
.Delete(d => d.HasName("delete_department")
.Parameter(b => b.DepartmentId, "department_id"))
.Insert(i => i.HasName("insert_department")
.Parameter(b => b.Name, "department_name")));
protected void btnSave_Click(object sender, EventArgs e)
{
string department = txtDepartment.text.trim();
// here I want to call the stored procedure to insert values
}
My problem is: how can I call the stored procedure and pass parameters into it?
This question is related to
c#
entity-framework
stored-procedures
I found that calling of stored procedures in code-first approach is not convenient.
I prefer to use Dapper instead.
The following code was written with Entity Framework:
var clientIdParameter = new SqlParameter("@ClientId", 4);
var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();
The following code was written with Dapper:
return Database.Connection.Query<ResultForCampaign>(
"GetResultsForCampaign ",
new
{
ClientId = 4
},
commandType: CommandType.StoredProcedure);
I believe the second piece of code is simpler to understand.
object[] xparams = {
new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
new SqlParameter("@In_Parameter", "Value"),
new SqlParameter("@Out_Parameter", SqlDbType.Int) {Direction = ParameterDirection.Output}};
YourDbContext.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", xparams);
var ReturnValue = ((SqlParameter)params[2]).Value;
Create Procedure in MYsql.
delimiter //
create procedure SP_Dasboarddata(fromdate date, todate date)
begin
select count(Id) as count,date,status,sum(amount) as amount from
details
where (Emidate between fromdate and todate)
group by date ,status;
END;
//
Create class which contains stored procedure return result set values
[Table("SP_reslutclass")]
public class SP_reslutclass
{
[Key]
public int emicount { get; set; }
public DateTime Emidate { get; set; }
public int ? Emistatus { get; set; }
public int emiamount { get; set; }
}
Add Class in Dbcontext
public class ABCDbContext:DbContext
{
public ABCDbContext(DbContextOptions<ABCDbContext> options)
: base(options)
{
}
public DbSet<SP_reslutclass> SP_reslutclass { get; set; }
}
Call entity in repository
var counts = _Dbcontext.SP_reslutclass.FromSql("call SP_Dasboarddata
('2019-12-03','2019-12-31')").ToList();
Using MySql and Entity framework code first Approach:
public class Vw_EMIcount
{
public int EmiCount { get; set; }
public string Satus { get; set; }
}
var result = context.Database.SqlQuery<Vw_EMIcount>("call EMIStatus('2018-3-01' ,'2019-05-30')").ToList();
Mindless passenger has a project that allows for multiple results sets to be returned from a stored proc using entity framework. One of his examples below....
using (testentities te = new testentities())
{
//-------------------------------------------------------------
// Simple stored proc
//-------------------------------------------------------------
var parms1 = new testone() { inparm = "abcd" };
var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
var r1 = results1.ToList<TestOneResultSet>();
}
You can now also use a convention I created which enables invoking stored procedures (including stored procedures returning multiple resultsets), TVFs and scalar UDFs natively from EF.
Until Entity Framework 6.1 was released store functions (i.e. Table Valued Functions and Stored Procedures) could be used in EF only when doing Database First. There were some workarounds which made it possible to invoke store functions in Code First apps but you still could not use TVFs in Linq queries which was one of the biggest limitations. In EF 6.1 the mapping API was made public which (along with some additional tweaks) made it possible to use store functions in your Code First apps.
I pushed quite hard for the past two weeks and here it is – the beta version of the convention that enables using store functions (i.e. stored procedures, table valued functions etc.) in applications that use Code First approach and Entity Framework 6.1.1 (or newer). I am more than happy with the fixes and new features that are included in this release.
You are using MapToStoredProcedures() which indicates that you are mapping your entities to stored procedures, when doing this you need to let go of the fact that there is a stored procedure and use the context as normal.
Something like this (written into the browser so not tested)
using(MyContext context = new MyContext())
{
Department department = new Department()
{
Name = txtDepartment.text.trim()
};
context.Set<Department>().Add(department);
}
If all you really trying to do is call a stored procedure directly then use SqlQuery
It work for me at code first. It return a list with matching property of view model(StudentChapterCompletionViewModel)
var studentIdParameter = new SqlParameter
{
ParameterName = "studentId",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.BigInt,
Value = studentId
};
var results = Context.Database.SqlQuery<StudentChapterCompletionViewModel>(
"exec dbo.sp_StudentComplettion @studentId",
studentIdParameter
).ToList();
Updated for Context
Context is the instance of the class that Inherit DbContext like below.
public class ApplicationDbContext : DbContext
{
public DbSet<City> City { get; set; }
}
var Context = new ApplicationDbContext();
Using your example, here are two ways to accomplish this:
Note that this code will work with or without mapping. If you turn off mapping on the entity, EF will generate an insert + select statement.
protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var department = new Department();
department.Name = txtDepartment.text.trim();
db.Departments.add(department);
db.SaveChanges();
// EF will populate department.DepartmentId
int departmentID = department.DepartmentId;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var name = new SqlParameter("@name", txtDepartment.text.trim());
//to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset
var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault();
//alternately, you can invoke SqlQuery on the DbSet itself:
//var department = db.Departments.SqlQuery("dbo.insert_department @name", name).SingleOrDefault();
int departmentID = department.DepartmentId;
}
}
I recommend using the first approach, as you can work with the department object directly and not have to create a bunch of SqlParameter objects.
public IList<Models.StandardRecipeDetail> GetRequisitionDetailBySearchCriteria(Guid subGroupItemId, Guid groupItemId)
{
var query = this.UnitOfWork.Context.Database.SqlQuery<Models.StandardRecipeDetail>("SP_GetRequisitionDetailBySearchCriteria @SubGroupItemId,@GroupItemId",
new System.Data.SqlClient.SqlParameter("@SubGroupItemId", subGroupItemId),
new System.Data.SqlClient.SqlParameter("@GroupItemId", groupItemId));
return query.ToList();
}
public static string ToSqlParamsString(this IDictionary<string, string> dict)
{
string result = string.Empty;
foreach (var kvp in dict)
{
result += $"@{kvp.Key}='{kvp.Value}',";
}
return result.Trim(',', ' ');
}
public static List<T> RunSproc<T>(string sprocName, IDictionary<string, string> parameters)
{
string command = $"exec {sprocName} {parameters.ToSqlParamsString()}";
return Context.Database.SqlQuery<T>(command).ToList();
}
I solved it with ExecuteSqlCommand
Put your own method like mine in DbContext as your own instances:
public void addmessage(<yourEntity> _msg)
{
var date = new SqlParameter("@date", _msg.MDate);
var subject = new SqlParameter("@subject", _msg.MSubject);
var body = new SqlParameter("@body", _msg.MBody);
var fid = new SqlParameter("@fid", _msg.FID);
this.Database.ExecuteSqlCommand("exec messageinsert @Date , @Subject , @Body , @Fid", date,subject,body,fid);
}
so you can have a method in your code-behind like this :
[WebMethod] //this method is static and i use web method because i call this method from client side
public static void AddMessage(string Date, string Subject, string Body, string Follower, string Department)
{
try
{
using (DBContext reposit = new DBContext())
{
msge <yourEntity> Newmsg = new msge();
Newmsg.MDate = Date;
Newmsg.MSubject = Subject.Trim();
Newmsg.MBody = Body.Trim();
Newmsg.FID= 5;
reposit.addmessage(Newmsg);
}
}
catch (Exception)
{
throw;
}
}
this is my SP :
Create PROCEDURE dbo.MessageInsert
@Date nchar["size"],
@Subject nchar["size"],
@Body nchar["size"],
@Fid int
AS
insert into Msg (MDate,MSubject,MBody,FID) values (@Date,@Subject,@Body,@Fid)
RETURN
hope helped you
Take a look to this link that shows how works the mapping of EF 6 with Stored Procedures to make an Insert, Update and Delete: http://msdn.microsoft.com/en-us/data/dn468673
Addition
Here is a great example to call a stored procedure from Code First:
Lets say you have to execute an Stored Procedure with a single parameter, and that Stored Procedure returns a set of data that match with the Entity States, so we will have this:
var countryIso = "AR"; //Argentina
var statesFromArgentina = context.Countries.SqlQuery(
"dbo.GetStatesFromCountry @p0", countryIso
);
Now lets say that we whant to execute another stored procedure with two parameters:
var countryIso = "AR"; //Argentina
var stateIso = "RN"; //Río Negro
var citiesFromRioNegro = context.States.SqlQuery(
"dbo.GetCitiesFromState @p0, @p1", countryIso, stateIso
);
Notice that we are using index-based naming for parameters. This is because Entity Framework will wrap these parameters up as DbParameter objects fro you to avoid any SQL injection issues.
Hope this example helps!
You can pass parameters to sp_GetById and fetch the results either in ToList() or FirstOrDefault();
var param = new SqlParameter("@id", 106);
var result = dbContext
.Database
.SqlQuery<Category>("dbo.sp_GetById @id", param)
.FirstOrDefault();
This works for me by pulling back data from a stored procedure while passing in a parameter.
var param = new SqlParameter("@datetime", combinedTime);
var result =
_db.Database.SqlQuery<QAList>("dbo.GetQAListByDateTime @datetime", param).ToList();
_db is the dbContext
You can call a stored procedure in your DbContext class as follows.
this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);
But if your stored procedure returns multiple result sets as your sample code, then you can see this helpful article on MSDN
When EDMX create this time if you select stored procedured in table select option then just call store procedured using procedured name...
var num1 = 1;
var num2 = 2;
var result = context.proc_name(num1,num2).tolist();// list or single you get here.. using same thing you can call insert,update or delete procedured.
.NET Core 5.0 does not have FromSql instead it has FromSqlRaw
All below worked for me. Account class here is Entity in C# with exact same table and column names as in the database.
App configuration class as below
class AppConfiguration
{
public AppConfiguration()
{
var configBuilder = new ConfigurationBuilder();
var path = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
configBuilder.AddJsonFile(path, false);
var root = configBuilder.Build();
var appSetting = root.GetSection("ConnectionStrings:DefaultConnection");
sqlConnectionString = appSetting.Value;
}
public string sqlConnectionString { get; set; }
}
DbContext class:
public class DatabaseContext : DbContext
{
public class OptionsBuild
{
public OptionsBuild()
{
setting = new AppConfiguration();
opsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
opsBuilder.UseSqlServer(setting.sqlConnectionString);
dbOptions = opsBuilder.Options;
}
public DbContextOptionsBuilder<DatabaseContext> opsBuilder { get; set; }
public DbContextOptions<DatabaseContext> dbOptions { get; set; }
private AppConfiguration setting { get; set; }
}
public static OptionsBuild ops = new OptionsBuild();
public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
{
//disable initializer
// Database.SetInitializer<DatabaseContext>(null);
}
public DbSet<Account> Account { get; set; }
}
This code should be in your data access layer:
List<Account> accounts = new List<Account>();
var context = new DatabaseContext(DatabaseContext.ops.dbOptions);
accounts = await context.Account.ToListAsync(); //direct select from a table
var param = new SqlParameter("@FirstName", "Bill");
accounts = await context.Account.FromSqlRaw<Account>("exec Proc_Account_Select",
param).ToListAsync(); //procedure call with parameter
accounts = context.Account.FromSqlRaw("SELECT * FROM dbo.Account").ToList(); //raw query
Nothing have to do... when you are creating dbcontext for code first approach initialize namespace below the fluent API area make list of sp and use it another place where you want.
public partial class JobScheduleSmsEntities : DbContext
{
public JobScheduleSmsEntities()
: base("name=JobScheduleSmsEntities")
{
Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
}
public virtual DbSet<Customer> Customers { get; set; }
public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
public virtual DbSet<RoleMaster> RoleMasters { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//modelBuilder.Types().Configure(t => t.MapToStoredProcedures());
//modelBuilder.Entity<RoleMaster>()
// .HasMany(e => e.Customers)
// .WithRequired(e => e.RoleMaster)
// .HasForeignKey(e => e.RoleID)
// .WillCascadeOnDelete(false);
}
public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
{
//return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
// this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
{
return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();
}
}
}
}
public partial class Sp_CustomerDetails02
{
public long? ID { get; set; }
public string Name { get; set; }
public string CustomerID { get; set; }
public long? CustID { get; set; }
public long? Customer_ID { get; set; }
public decimal? Amount { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public int? CountDay { get; set; }
public int? EndDateCountDay { get; set; }
public DateTime? RenewDate { get; set; }
public bool? IsSMS { get; set; }
public bool? IsActive { get; set; }
public string Contact { get; set; }
}
This is what EF (DB first) generates in the DbContext class:
public ObjectResult<int> Insert_Department(string department)
{
var departmentParameter = new ObjectParameter("department", department);
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("insert_department", departmentParameter);
}
if you wanna pass table params into stored procedure, you must necessary set TypeName property for your table params.
SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);
codesParam.Value = tbCodes;
codesParam.TypeName = "[dbo].[MES_CodesType]";
factoriesParam.Value = tbfactories;
factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";
var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
, new SqlParameter[] {
codesParam,
factoriesParam
}
).ToList();
All you have to do is create an object that has the same property names as the results returned by the stored procedure. For the following stored procedure:
CREATE PROCEDURE [dbo].[GetResultsForCampaign]
@ClientId int
AS
BEGIN
SET NOCOUNT ON;
SELECT AgeGroup, Gender, Payout
FROM IntegrationResult
WHERE ClientId = @ClientId
END
create a class that looks like:
public class ResultForCampaign
{
public string AgeGroup { get; set; }
public string Gender { get; set; }
public decimal Payout { get; set; }
}
and then call the procedure by doing the following:
using(var context = new DatabaseContext())
{
var clientIdParameter = new SqlParameter("@ClientId", 4);
var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();
}
The result will contain a list of ResultForCampaign objects. You can call SqlQuery using as many parameters as needed.
Source: Stackoverflow.com