我正试图在ASP.NET MVC核心中使用MS SQL中的存储过程将数据(实际上是视频(插入数据库
我有这个代码:
[HttpPost]
public async Task<IActionResult> Index(string title, IFormFile file, string filesize ,string filePath )
{
var Title= new SqlParameter("@Title", title);
var UploadType = new SqlParameter("@UploadType", file.ToString());
var FileSize = new SqlParameter("@FileSize", filesize);
var FilePath = new SqlParameter("@FilePath", filePath);
var insertVideo = _context.Lectures.FromSql("usp_AddNewVideoFile (@Title,@UploadType, @FileSize, @FilePath) ",parameters:new[] {Title,FileSize,FilePath,UploadType }).ToList();
await _context.SaveChangesAsync();
return RedirectToAction("Source",insertVideo);
}
存储过程:
CREATE procedure [dbo].[usp_AddNewVideoFile]
@Title nvarchar(50),
@UploadType nvarchar(50),
@FileSize int = null,
@FilePath nvarchar(MAX)
as
begin
insert into Lectures(Name,FileSize,FilePath,UploadType)
values (@Title,@FileSize,@FilePath,@UploadType)
end
得到一个错误说:
SqlException:"@Title"附近的语法不正确。
原因可能是什么?
更改如下:
[HttpPost]
public async Task<IActionResult> Test(string title, IFormFile file, int filesize, string filePath)
{
var Title = new SqlParameter("@Title", title);
var UploadType = new SqlParameter("@UploadType", file.ToString());
var FileSize = new SqlParameter("@FileSize", filesize);
var FilePath = new SqlParameter("@FilePath", filePath);
//change this...
var insertVideo = _context.Database.ExecuteSqlCommandAsync("usp_AddNewVideoFile @Title,@UploadType,@FileSize, @FilePath", parameters: new[] { Title, UploadType, FileSize, FilePath });
await _context.SaveChangesAsync();
return RedirectToAction("Source", insertVideo);
}
问题就在这里:
usp_AddNewVideoFile (@Title,@UploadType, @FileSize, @FilePath)
这并不是你所说的SP;理想的方式是将命令类型指定为CommandType.StoredProcedure
,而仅将"usp_AddNewVideoFile"
指定为CommandText
。如果由于FromSql
而无法实现,则:
EXEC usp_AddNewVideoFile @Title,@UploadType, @FileSize, @FilePath;
然而,请注意,如果这是EFFromSql
,那么将其用于插入命令可能并不理想;您真正在寻找一个"执行非查询"API。
请检查:
[HttpPost]
public async Task<IActionResult> Index(string title, IFormFile file, string filesize ,string filePath )
{
var Title= new SqlParameter("@Title", title);
var UploadType = new SqlParameter("@UploadType", file.ToString());
var FileSize = new SqlParameter("@FileSize", filesize);
var FilePath = new SqlParameter("@FilePath", filePath);
var insertVideo = _context.Lectures.FromSqlRaw("EXECUTE usp_AddNewVideoFile @Title, @FilePath, @FilePath, @UploadType", parameters:new[] {Title,FileSize,FilePath,UploadType }).ToList();
await _context.SaveChangesAsync();
return RedirectToAction("Source",insertVideo);
}