我试图从EntityFramework调用使用表值参数的存储过程。但是当我尝试执行函数导入时,我不断收到警告消息,指出:“InsertPerson”函数具有在参数索引0处的“InsertPerson_TVP”参数,其数据类型为“表类型”,目标.NET Framework版本当前不支持。该函数已被排除。我在这里进行了初步搜索,并发现一些帖子说它可以通过一些解决方法在EntityFrameWork中实现,few表示它在当前版本中不受支持。有人知道更好的方法或解决此问题的方案吗?
我最终做了这个。请注意我们正在使用EF DataContext
(而不是ObjectContext
)。
执行一个带有输出参数的存储过程
using (DataContext context = new DataContext())
{
////Create table value parameter
DataTable dt = new DataTable();
dt.Columns.Add("Displayname");
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("TimeStamp");
DataRow dr = dt.NewRow();
dr["Displayname"] = "DisplayName";
dr["FirstName"] = "FirstName";
dr["LastName"] ="LastName";
dr["TimeStamp"] = "TimeStamp";
dt.Rows.Add(dr);
////Use DbType.Structured for TVP
var userdetails = new SqlParameter("UserDetails", SqlDbType.Structured);
userdetails.Value = dt;
userdetails.TypeName = "UserType";
////Parameter for SP output
var result = new SqlParameter("ResultList", SqlDbType.NVarChar, 4000);
result.Direction = ParameterDirection.Output;
context.Database.ExecuteSqlCommand("EXEC UserImport @UserDetails, @ResultList OUTPUT", userdetails, result);
return result == null ? string.Empty : result.Value.ToString();
}
我的表值参数(UDT表)脚本如下:
CREATE TYPE [dbo].[UserType] AS TABLE (
[DisplayName] NVARCHAR (256) NULL,
[FirstName] NVARCHAR (256) NULL,
[LastName] NVARCHAR (256) NULL,
[TimeStamp] DATETIME NULL
)
我的存储过程是这样开始的:
CREATE PROCEDURE UserImport
-- Add the parameters for the stored procedure here
@UserDetails UserType Readonly,
@ResultList NVARCHAR(MAX) output
AS
如果存储过程没有output
参数,我们不需要为SP添加/传递任何输出参数。
希望能帮助到某些人。
[Invoke]
public SomeResultType GetResult(List<int> someIdList)
{
var idTbl = new DataTable();
idTbl.Columns.Add("Some_ID");
someIdList.ForEach(id => idTbl.Rows.Add(id));
var idParam = new SqlParamter("SomeParamName", SqlDbType.Structured);
idParam.TypeName = "SomeTBVType";
idParam.Value = idTbl;
// Return type will be IEnumerable<T>
var result = DbContext.Database.SqlQuery<SomeResultType>("EXEC SomeSPName, @SomeParamName", idParam);
// We can enumerate the result...
var enu = result.GetEnumerator();
if (!enu.MoveNext()) return null;
return enu.Current;
}
var detailTbl = new DataTable();
detailTbl.Columns.Add("DetailID");
detailTbl.Columns.Add("Qty");
txnDetails.ForEach(detail => detailTbl.Rows.Add(detail.DetailID, detail.Qty));
var userIdParam = new System.Data.SqlClient.SqlParameter("@UserID", SqlDbType.Int);
userIdParam.Value = 1;
var detailParam = new System.Data.SqlClient.SqlParameter("@Details", SqlDbType.Structured);
detailParam.TypeName = "DetailUpdate";
detailParam.Value = detailTbl;
var txnTypeParam = new System.Data.SqlClient.SqlParameter("@TransactionType", SqlDbType.VarChar);
txnTypeParam.Value = txnType;
var result = await db.Database.ExecuteSqlCommandAsync("MySP @UserID, @Details, @TransactionType", userIdParam, detailParam, txnTypeParam);
if(result >= 0)
return StatusCode(HttpStatusCode.OK);
else
return StatusCode(HttpStatusCode.InternalServerError);