c# - 在 ASP.Net Core 2.2 中运行存储过程
问题描述
为什么我的asp.net web api 无法识别这行代码
db.Database.Initialize(force: false);
var connection = db.Database.Connection
它适用于我的桌面应用程序,但不适用于我的 webapi。我已经安装了 nuget 包“EntityFramework”6.2.0。我需要得到这个的原因是因为我需要运行这个命令:
decimal? paymentAmount = model.PaymentAmount;
int? paymentTypeId = model.PaymentTypeId;
string OrNumber = model.ORNumber;
string ChequeNo = model.ChequeNo;
decimal? CWTax = model.CWTax;
int? unitId = model.UnitId;
DateTime currentDateTime = DateTime.Now;
var db = new ApplicationDbContext();
db.Database.Initialize(force: false);
using(var connection = db.Database.Connection)
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "[dbo].[DUES_AUTO_PAYMENT]";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter pPaymentAmount = cmd.CreateParameter();
pPaymentAmount.ParameterName = "@PaymentAmount";
pPaymentAmount.DbType = DbType.Decimal;
pPaymentAmount.Direction = ParameterDirection.Input;
pPaymentAmount.Value = model.PaymentAmount;
DbParameter pCwTax = cmd.CreateParameter();
pCwTax.ParameterName = "@CwTax";
pCwTax.DbType = DbType.Decimal;
pCwTax.Direction = ParameterDirection.Input;
pPaymentAmount.Value = model.PaymentAmount;
DbParameter pPaymentTypeId = cmd.CreateParameter();
pPaymentTypeId.ParameterName = "@PaymentTypeID";
pPaymentTypeId.DbType = DbType.Int32;
pPaymentTypeId.Direction = ParameterDirection.Input;
pPaymentTypeId.Value = model.PaymentTypeId;
DbParameter pOrNumber = cmd.CreateParameter();
pOrNumber.ParameterName = "@ORNumber";
pOrNumber.DbType = DbType.String;
pOrNumber.Direction = ParameterDirection.Input;
pOrNumber.Value = model.ORNumber;
DbParameter pChequeNo = cmd.CreateParameter();
pChequeNo.ParameterName = "@ChequeNo";
pChequeNo.DbType = DbType.String;
pChequeNo.Direction = ParameterDirection.Input;
pChequeNo.Value = model.ChequeNo;
DbParameter pUnitId = cmd.CreateParameter();
pUnitId.ParameterName = "@UnitID";
pUnitId.DbType = DbType.Int32;
pUnitId.Direction = ParameterDirection.Input;
pUnitId.Value = model.UnitId;
DbParameter pCurrentDateTime = cmd.CreateParameter();
pCurrentDateTime.ParameterName = "@CurrentDateTime";
pCurrentDateTime.DbType = DbType.DateTime2;
pCurrentDateTime.Direction = ParameterDirection.Input;
pCurrentDateTime.Value = DateTime.Now;
cmd.Parameters.Add(pPaymentAmount);
cmd.Parameters.Add(pPaymentTypeId);
cmd.Parameters.Add(pOrNumber);
cmd.Parameters.Add(pUnitId);
cmd.Parameters.Add(pCurrentDateTime);
try
{
var x = cmd.ExecuteNonQuery();
if(x > 0)
{
return Request.CreateResponse(res.IsSuccessStatusCode);
} else
{
return Request.CreateErrorResponse(res.StatusCode = HttpStatusCode.BadRequest, "Server could not execute the procedure.");
}
}
catch (Exception ex)
{
return Request.CreateErrorResponse(res.StatusCode = HttpStatusCode.BadRequest, ex.Message);
}
}
我可以尝试什么来解决这个问题?
解决方案
对于运行过程,您可以通过Database.GetDbConnection()
.
试试下面的方法:
public class HomeController : Controller
{
private readonly ApplicationDbContext _context;
public HomeController(ApplicationDbContext context)
{
_context = context;
}
public async Task<IActionResult> Index()
{
using (var connection = _context.Database.GetDbConnection())
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "[dbo].[DUES_AUTO_PAYMENT]";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter pPaymentAmount = cmd.CreateParameter();
pPaymentAmount.ParameterName = "@Name";
pPaymentAmount.DbType = DbType.String;
pPaymentAmount.Direction = ParameterDirection.Input;
pPaymentAmount.Value = "Tom";
cmd.Parameters.Add(pPaymentAmount);
try
{
var x = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return Ok(ex.Message);
}
}
}
}
推荐阅读
- ios - 尽管用户在那里,但 Firebase Auth 不打印用户登录
- linux - 使用 GHC 的共享库
- sql - 将时间戳舍入到 30 分钟间隔以进行分组
- json - readyState 3 之前的响应输出
- compare - Tableau - 需要将过去 7 天的记录数与过去 7 天的记录数进行比较 -1
- kubernetes - 哪个隔离 Kubernetes 集群用于生产环境?
- javascript - 反转多维数组
- selenium-webdriver - 如何使用伊斯坦布尔为基于 selenium+java 的框架设置代码覆盖率
- spring-boot - Spring Security - 用于执行器端点的自定义身份验证提供程序和 HTTP 基础
- javascript - Coderbyte 挑战:问号