如何将参数传递给返回多个记录的存储过程
问题描述:
如何将参数传递给使用EF6返回多条记录的存储过程 - 使用带有加密列的SQL Server 2016存储过程的DbContext
?如何将参数传递给返回多个记录的存储过程
使用EF 6.1.3时,SQL Server 2016 - AlwaysEncrypted(确定性)
有一个few articles是谈论使用ADO .NET 4.6通过使用命令,参数对象做到这一点,但我无法找到一个从我的EF DbContext
做到这一点。
答
//make sure to provide the actual datatype and size on the parameter
//you can use reflection on the entity to dynamically get/set the property info by a common function.
var paratmeterNames = new StringBuilder();
string paratmeterNamesUpdated = null;
if (lastName != null)
{
var lastNameParam = new SqlParameter("@lastName", SqlDbType.VarChar, 60) {Value = lastName};
parameterList.Add(lastNameParam);
paratmeterNames.Append("@lastName,");
}
if (firstName != null)
{
var firstNameParameter = new SqlParameter("@firstName", SqlDbType.VarChar, 30) {Value = firstName};
parameterList.Add(firstNameParameter);
paratmeterNames.Append("@firstName,");
}
if (paratmeterNames != null && paratmeterNames.Length > 0)
{
paratmeterNamesUpdated = paratmeterNames.ToString().TrimEnd(',');
}
var result = _context.Database.SqlQuery<T>("exec sp_name " + paratmeterNamesUpdated, parameterList.ToArray()).ToList();
//this will make below **calls to SQL DB:**
//exec sp_describe_parameter_encryption N'exec sp_name @lastName,@firstName',N'@lastName varchar(60),@firstName varchar(30)'
//exec sp_executesql N'exec sp_name @lastName,@firstName',N'@lastName varchar(60),@firstName varchar(30)',@lastName=0x01A2256C34F97,@firstName=0x018EE4BD11BA7