How to pass parameters to a stored procedure that returns multiple records

always-encrypted dbcontext entity-framework-6 sql-server-2016

Question

How to use EF6 to send parameters to a stored procedure that returns a number of recordsDbContext using encrypted columns in SQL Server 2016 stored procedures?

SQL Server 2016 - AlwaysEncrypted with EF 6.1.3 (deterministic)

There are little articles that discuss utilising command, parameter objects in ADO.NET 4.6 to do this, but I was unable to locate a method to do it from my EF.DbContext .

1
0
11/10/2017 8:15:02 PM

Popular Answer

//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
0
8/16/2017 1:37:45 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow