Calling a stored procedure and returning the results as a collection of a POCO is a lot easier to write in Entity Framework than traditional Ado.Net. The following example makes a comparison.
To begin this editorial, I create a stored procedures in the AdventureWorks sample database for SQL Server, where I return a few columns from the HumanResources.Employee table, and join it to the Person.Person table to get the first and last name. I pass in a unique BusinessEntityId to the stored procedure to identify the Employee records to be retrieved. The procedure is as follows:
/*
-- TEST - Retrieve Employee Information by BusinessEntityId
EXEC HumanResources.Employee_GetById 1
*/
CREATE PROCEDURE HumanResources.Employee_GetById (
@BusinessEntityId INT
)
AS
SELECT e.BusinessEntityId, e.LoginID, e.JobTitle
,e.Gender, p.FirstName, p.LastName
FROM HumanResources.Employee e
JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID = @BusinessEntityId
GO
This procedure simply returns one record in a record set. You could make a stored procedure that returns more than one record if you wish. We’ll stick to one record for this comparison. Regardless, the stored procedure returns a record set, which is converted into a System.Data.DataTable. Your code will have to manage the fact that the query returns a collection.
First we are going to create a POCO (plain old CLR object) matching the definition of the columns in our result set.
public class Person
{
public int BusinessEntityId {get; set;}
public string LoginID {get; set;}
public string JobTitle {get; set;}
public string Gender {get; set;}
public string FirstName {get; set;}
public string LastName {get; set;}
}
Note that the properties of the Person class we created match the columns returned from the stored procedure by the order of the data, the data type for each column, and the Name of each column. The names are case specific, matching exactly since Dot Net is a case specific language. If the column names do not match, when you return the converted data, any property that does not match a column in the stored procedure result set will be null, or have a default value if specified in your class.
Following is an Entity Framework solution that calls the stored procedure, retrieves the record set, and converts the results into a collection based on our POCO.
public ICollection<Person> GetPersonById(Context context, int businessEntityId)
{
try
{
var param = new SqlParameter
{
ParameterName = "@BusinessEntityId",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Input,
Value = businessEntityId
};
result = context.Database.SqlQuery<Person>(
"EXEC [dbo].[JobTypeGroup_SearchDefinition] @BusinessEntityId", param)
.ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return result;
}
The context that I am passing into my method is the same database context that Entity Framework is using. The Entity Framework context has a Database property with a number of methods you may execute. I am calling the SqlQuery<> method, passing a generic type defined by my POCO, “Person”. Passing in this type causes the SqlQuery method to automatically convert the data returned from the stored procedure into a Person class. The .ToList() clause converts the results into a List<Person> object for me to consume at a later time.
If we were to make the same call to the stored procedure using Ado.Net we replace the following line of code with Ado.Net specific syntax:
result = context.Database.SqlQuery<Person>( "EXEC [HumanResources].[JobTypeGroup_SearchDefinition] @BusinessEntityId", param) .ToList();
This is the code that actually connects to the database, calls the database stored procedure, gathers the result set, and converts the data into a List<Person> object. To emphasize the simplicity of the Entity Framework code, here is a sample of what would be written if you built all of the Ado.Net code from scratch.
var result = new List<Person>();
using (var conn = (SqlConnection)context.Database.Connection)
using (var cmd = new SqlCommand("[HumanResources].[JobTypeGroup_SearchDefinition]", conn))
using (var dt = new DataTable())
using (var da = new SqlDataAdapter(cmd))
{
if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();
cmd.Parameters.Add(param);
da.Fill(dt);
using (var dr = new DataTableReader(dt))
{
while (dr.Read()
{
var item = new Person
{
businessEntityId = businessEntityId,
LoginID = dr.GetString("LoginId") ?? string.Empty,
JobTitle = dr.GetString("JobTitle") ?? string.Empty,
Gender = dr.GetString("Gender") ?? string.Empty,
FirstName = dr.GetString("FirstName") ?? string.Empty,
LastName = dr.GetString("LastName") ?? string.Empty,
};
result.Add(item);
}
}
}
return result;
Remember that the Ado.Net code only replaces the actual call to the stored procedure in the first example. You still have to include the method call, creating the parameters, try/catch, and returning the results. So the whole thing is even larger with the duplicate code.
To be fair in the comparison, very few of us write Ado.Net code at this low level. I have a number of frameworks I use that make all of the setup work a lot easier, and the data conversion into a list of objects can be done easily through reflection. Entity Framework is doing very much the same thing behind the scenes. So, we aren’t talking a big (if any) difference in performance here. The simplicity is in the code that you have to write, and not forgetting to close a connection, or some other error, including handling null values.
So, if you are using Entity Framework in your project, consider using stored procedures when they make sense. You may already have your POCO defined for you existing tables, which saves time. When the time is right for a stored procedure, now you have another tool under your belt.
Cheers,
Ben