I am using asp.net mvc 5 and C# with Entity Framework... I have model and domain classes for function... now I need to use stored procedure.... which I am struggling at the movement.
我使用的是asp.net mvc 5和c#的实体框架…我有函数的模型和域类…现在我需要使用存储过程....我在运动中挣扎。
I am following code first existing database and I have stored procedure written there. My question is how I can call that stored procedure in my web application.
我正在遵循代码第一现有数据库,我已经在那里编写了存储过程。我的问题是如何在web应用程序中调用这个存储过程。
Stored procedure:
存储过程:
ALTER PROCEDURE [dbo].[GetFunctionByID](
@FunctionId INT
)
AS
BEGIN
SELECT *
FROM Functions As Fun
WHERE Function_ID = @FunctionId
END
Domain class:
域类:
public class Functions
{
public Functions()
{
}
public int Function_ID { get; set; }
public string Title { get; set; }
public int Hierarchy_level { get; set; }
}
Function model:
功能模型:
[Table("Functions")]
public class App_Functions
{
public App_Functions()
{
}
[Key]
public int Function_ID { get; set; }
[StringLength(50)]
[Required]
public string Title { get; set; }
public int Hierarchy_level { get; set; }
//public virtual ICollection<App_Controllers> App_Controllers { get; set; }*/
}
BaseContext:
BaseContext:
public class BaseContext<TContext> : DbContext where TContext : DbContext
{
static BaseContext()
{
Database.SetInitializer<TContext>(null);
}
protected BaseContext()
: base("name = ApplicationDbConnection")
{ }
}
Function context:
函数上下文:
public class FunctionsContext : BaseContext<FunctionsContext>
{
public DbSet<App_Functions> Functions { get; set; }
}
6 个解决方案
#1
39
You need to create a model class that contains all stored procedure properties like below. Also because Entity Framework model class needs primary key, you can create a fake key by using Guid.
您需要创建一个包含所有存储过程属性的模型类,如下所示。另外,由于实体框架模型类需要主键,所以可以使用Guid创建伪键。
public class GetFunctionByID
{
[Key]
public Guid? GetFunctionByID { get; set; }
// All the other properties.
}
then register the GetFunctionByID
model class in your DbContext
.
然后在DbContext中注册GetFunctionByID模型类。
public class FunctionsContext : BaseContext<FunctionsContext>
{
public DbSet<App_Functions> Functions { get; set; }
public DbSet<GetFunctionByID> GetFunctionByIds {get;set;}
}
When you call your stored procedure, just see below:
调用存储过程时,请参见下面:
var functionId = yourIdParameter;
var result = db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());
#2
9
After importing stored procedure, you can create object of stored procedure pass the parameter like function
导入存储过程后,您可以创建存储过程的对象传递参数like函数。
using (var entity = new FunctionsContext())
{
var DBdata = entity.GetFunctionByID(5).ToList<Functions>();
}
or you can also use SqlQuery
也可以使用SqlQuery
using (var entity = new FunctionsContext())
{
var Parameter = new SqlParameter {
ParameterName = "FunctionId",
Value = 5
};
var DBdata = entity.Database.SqlQuery<Course>("exec GetFunctionByID @FunctionId ", Parameter).ToList<Functions>();
}
#3
9
You can call a stored procedure using SqlQuery
(See here)
可以使用SqlQuery调用存储过程(请参见这里)
// Prepare the query
var query = context.Functions.SqlQuery(
"EXEC [dbo].[GetFunctionByID] @p1",
new SqlParameter("p1", 200));
// add NoTracking() if required
// Fetch the results
var result = query.ToList();
#4
0
Mindless passenger has a project that allows you to call a stored proc from entity frame work like this....
盲目的乘客有一个项目,允许您调用一个存储过程从实体框架这样....
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>();
}
... and I am working on a stored procedure framework (here) which you can call like in one of my test methods shown below...
…我正在开发一个存储过程框架(这里),您可以在我的一个测试方法中调用它,如下所示……
[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
[TestMethod]
public void GetTenantForName_ReturnsOneRecord()
{
// ARRANGE
const int expectedCount = 1;
const string expectedName = "Me";
// Build the paraemeters object
var parameters = new GetTenantForTenantNameParameters
{
TenantName = expectedName
};
// get an instance of the stored procedure passing the parameters
var procedure = new GetTenantForTenantNameProcedure(parameters);
// Initialise the procedure name and schema from procedure attributes
procedure.InitializeFromAttributes();
// Add some tenants to context so we have something for the procedure to return!
AddTenentsToContext(Context);
// ACT
// Get the results by calling the stored procedure from the context extention method
var results = Context.ExecuteStoredProcedure(procedure);
// ASSERT
Assert.AreEqual(expectedCount, results.Count);
}
}
internal class GetTenantForTenantNameParameters
{
[Name("TenantName")]
[Size(100)]
[ParameterDbType(SqlDbType.VarChar)]
public string TenantName { get; set; }
}
[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
: StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
public GetTenantForTenantNameProcedure(
GetTenantForTenantNameParameters parameters)
: base(parameters)
{
}
}
If either of those two approaches are any good?
如果这两种方法中有哪一种是好的?
#5
0
// Add some tenants to context so we have something for the procedure to return! AddTenentsToContext(Context);
//添加一些租户到上下文,这样我们就有程序返回的东西了!AddTenentsToContext(上下文);
// ACT
// Get the results by calling the stored procedure from the context extention method
var results = Context.ExecuteStoredProcedure(procedure);
// ASSERT
Assert.AreEqual(expectedCount, results.Count);
}
#6
0
Simple. Just instantiate your entity, set it to an object and pass it to your view in your controller.
简单。只需实例化实体,将其设置为对象,并将其传递给控制器中的视图。
Entity
实体
VehicleInfoEntities db = new VehicleInfoEntities();
车辆信息实体数据库=新车辆信息实体();
Stored Procedure
存储过程
dbo.prcGetMakes()
dbo.prcGetMakes()
or
或
you can add any parameters in your stored procedure inside the brackets ()
可以在方括号()中添加存储过程中的任何参数
dbo.prcGetMakes("BMW")
dbo.prcGetMakes(“宝马”)
Controller
控制器
public class HomeController : Controller
{
VehicleInfoEntities db = new VehicleInfoEntities();
public ActionResult Index()
{
var makes = db.prcGetMakes(null);
return View(makes);
}
}
#1
39
You need to create a model class that contains all stored procedure properties like below. Also because Entity Framework model class needs primary key, you can create a fake key by using Guid.
您需要创建一个包含所有存储过程属性的模型类,如下所示。另外,由于实体框架模型类需要主键,所以可以使用Guid创建伪键。
public class GetFunctionByID
{
[Key]
public Guid? GetFunctionByID { get; set; }
// All the other properties.
}
then register the GetFunctionByID
model class in your DbContext
.
然后在DbContext中注册GetFunctionByID模型类。
public class FunctionsContext : BaseContext<FunctionsContext>
{
public DbSet<App_Functions> Functions { get; set; }
public DbSet<GetFunctionByID> GetFunctionByIds {get;set;}
}
When you call your stored procedure, just see below:
调用存储过程时,请参见下面:
var functionId = yourIdParameter;
var result = db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());
#2
9
After importing stored procedure, you can create object of stored procedure pass the parameter like function
导入存储过程后,您可以创建存储过程的对象传递参数like函数。
using (var entity = new FunctionsContext())
{
var DBdata = entity.GetFunctionByID(5).ToList<Functions>();
}
or you can also use SqlQuery
也可以使用SqlQuery
using (var entity = new FunctionsContext())
{
var Parameter = new SqlParameter {
ParameterName = "FunctionId",
Value = 5
};
var DBdata = entity.Database.SqlQuery<Course>("exec GetFunctionByID @FunctionId ", Parameter).ToList<Functions>();
}
#3
9
You can call a stored procedure using SqlQuery
(See here)
可以使用SqlQuery调用存储过程(请参见这里)
// Prepare the query
var query = context.Functions.SqlQuery(
"EXEC [dbo].[GetFunctionByID] @p1",
new SqlParameter("p1", 200));
// add NoTracking() if required
// Fetch the results
var result = query.ToList();
#4
0
Mindless passenger has a project that allows you to call a stored proc from entity frame work like this....
盲目的乘客有一个项目,允许您调用一个存储过程从实体框架这样....
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>();
}
... and I am working on a stored procedure framework (here) which you can call like in one of my test methods shown below...
…我正在开发一个存储过程框架(这里),您可以在我的一个测试方法中调用它,如下所示……
[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
[TestMethod]
public void GetTenantForName_ReturnsOneRecord()
{
// ARRANGE
const int expectedCount = 1;
const string expectedName = "Me";
// Build the paraemeters object
var parameters = new GetTenantForTenantNameParameters
{
TenantName = expectedName
};
// get an instance of the stored procedure passing the parameters
var procedure = new GetTenantForTenantNameProcedure(parameters);
// Initialise the procedure name and schema from procedure attributes
procedure.InitializeFromAttributes();
// Add some tenants to context so we have something for the procedure to return!
AddTenentsToContext(Context);
// ACT
// Get the results by calling the stored procedure from the context extention method
var results = Context.ExecuteStoredProcedure(procedure);
// ASSERT
Assert.AreEqual(expectedCount, results.Count);
}
}
internal class GetTenantForTenantNameParameters
{
[Name("TenantName")]
[Size(100)]
[ParameterDbType(SqlDbType.VarChar)]
public string TenantName { get; set; }
}
[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
: StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
public GetTenantForTenantNameProcedure(
GetTenantForTenantNameParameters parameters)
: base(parameters)
{
}
}
If either of those two approaches are any good?
如果这两种方法中有哪一种是好的?
#5
0
// Add some tenants to context so we have something for the procedure to return! AddTenentsToContext(Context);
//添加一些租户到上下文,这样我们就有程序返回的东西了!AddTenentsToContext(上下文);
// ACT
// Get the results by calling the stored procedure from the context extention method
var results = Context.ExecuteStoredProcedure(procedure);
// ASSERT
Assert.AreEqual(expectedCount, results.Count);
}
#6
0
Simple. Just instantiate your entity, set it to an object and pass it to your view in your controller.
简单。只需实例化实体,将其设置为对象,并将其传递给控制器中的视图。
Entity
实体
VehicleInfoEntities db = new VehicleInfoEntities();
车辆信息实体数据库=新车辆信息实体();
Stored Procedure
存储过程
dbo.prcGetMakes()
dbo.prcGetMakes()
or
或
you can add any parameters in your stored procedure inside the brackets ()
可以在方括号()中添加存储过程中的任何参数
dbo.prcGetMakes("BMW")
dbo.prcGetMakes(“宝马”)
Controller
控制器
public class HomeController : Controller
{
VehicleInfoEntities db = new VehicleInfoEntities();
public ActionResult Index()
{
var makes = db.prcGetMakes(null);
return View(makes);
}
}