This sample demonstrates how to execute a parameterized stored procedure and materialize entities from the result set. There are two possible scenarios. The first one includes materializing persistent types from the result set. The second case includes materializing complex (non-persistent) types from the result set. Both scenarios will be demonstrated in this topic.
 |
To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces. |
How to Materialize Persistent Types
Suppose, you have a Categories table like the one shown on the image below:
And you have a stored procedure named GetCategoryId that returns information for a specific category by id.
| SQL |
 Copy Code |
|
CREATE PROCEDURE GetCategoryById(
@CategoryId INT)
AS
BEGIN
SELECT *
FROM Categories
WHERE CategoryID = @CategoryId
END |
You have created a domain model that looks like:
The following code snippet demonstrates how to execute a parameterized stored procedure and materialize an entity from the result set. The steps involve creating a new OAConnection, creating an OACommand, setting the command text to the name of the stored procedure, and calling the ExecuteReader method to get a data reader. With the reader in hand, you use the generic Translate<T> method on the OpenAccessContext to materialize instances of the Category entity from the reader.
| C# |
Copy Code |
|
private static void StoredProcedureMaterializeResultSet()
{
// 1. Create a new instance of the OpenAccessContext.
using ( EntitiesModel dbContext = new EntitiesModel() )
{
// 2. Retrieve the OAConnection instance.
using ( IDbConnection oaConnection = dbContext.Connection )
{
// 3. Create a new instance of the OACommand class.
using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
{
// 4. Create a new parameter.
IDbDataParameter categoryIdParameter = oaCommand.CreateParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = "1";
// 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure;
oaCommand.CommandText = "GetCategoryById";
oaCommand.Parameters.Add( categoryIdParameter );
// 7. Execute the command and materialize the category entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
// 8. Materialize Categories entities
List<Category> categories = dbContext.Translate<Category>( dataReader as DbDataReader ).ToList();
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Private Sub StoredProcedureMaterializeResultSet()
Using dbContext As New EntitiesModel()
Using oaConnection As IDbConnection = dbContext.Connection
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
Dim categoryIdParameter As IDbDataParameter = oaCommand.CreateParameter()
categoryIdParameter.ParameterName = "@CategoryId"
categoryIdParameter.Value = "1"
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCategoryById"
oaCommand.Parameters.Add(categoryIdParameter)
Using dataReader As IDataReader = oaCommand.ExecuteReader()
Dim categories As List(Of Category) = dbContext.Translate(Of Category)(TryCast(dataReader, DbDataReader)).ToList()
End Using
End Using
End Using
End Using
End Sub |
Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Category objects and call the SaveChanges method of the context, then all changes will be committed to the database.
How to Materialize Complex Types
Sometimes the shape of the result returned by the stored procedure may not map to any of the available persistent types in your domain model. In this case, you would need to define a custom CLR type that can hold the result. The requirements for this CLR type are:
- It should provide a parameterless constructor, so that Telerik OpenAccess ORM can create instances of that type.
- The property names should match the column names that are returned by the query. The case is ignored. In case a corresponding property for a column is not found the value of this column is ignored.
- The property types should be compatible with the column types.
Suppose, you have two tables named Cars and Categories.
You have a domain model representing both tables and the following stored procedures.
| SQL |
Copy Code |
|
CREATE PROCEDURE GetCarsDetails
@CarMake varchar(50)
AS
BEGIN
SELECT car.CarID, car.Make, car.Model,
category.CategoryName
FROM Cars car
JOIN Categories category ON car.CategoryID = category.CategoryID
WHERE car.Make = @CarMake
END
GO |
Here is what a simple class representing the GetCarsDetails stored procedure result could look like:
| C# |
Copy Code |
|
namespace ExecutingStoredProcedures
{
public class CarsDetails
{
public int CarID { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public string CategoryName { get; set; }
}
} |
| VB.NET |
Copy Code |
|
Public Class CarsDetails
Public Property CarID() As Integer
Public Property Make() As String
Public Property Model() As String
Public Property CategoryName() As String
End Class |
The code for executing the stored procedure and materializing is shown on the code snippet below.
| C# |
Copy Code |
|
private static void StoredProcedureMaterializeNonPersistentType()
{
// 1. Create a new instance of the OpenAccessContext.
using ( EntitiesModel dbContext = new EntitiesModel() )
{
// 2. Retrieve the OAConnection instance.
using ( IDbConnection oaConnection = dbContext.Connection )
{
// 3. Create a new instance of the OACommand class.
using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
{
// 3. Create a new parameter.
IDbDataParameter carMakeParameter = oaCommand.CreateParameter();
carMakeParameter.ParameterName = "@CarMake";
carMakeParameter.Value = "Honda";
// 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure;
oaCommand.CommandText = "GetCarsDetails";
oaCommand.Parameters.Add( carMakeParameter );
// 6. Execute the command and materialize the entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
// 7. Materialize entities
IEnumerable<CarsDetails> carDetails = dbContext.Translate<CarsDetails>( dataReader as DbDataReader );
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Private Sub StoredProcedureMaterializeNonPersistentType()
Using dbContext As New EntitiesModel()
Using oaConnection As IDbConnection = dbContext.Connection
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
Dim carMakeParameter As IDbDataParameter = oaCommand.CreateParameter()
carMakeParameter.ParameterName = "@CarMake"
carMakeParameter.Value = "Honda"
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCarsDetails"
oaCommand.Parameters.Add(carMakeParameter)
Using dataReader As IDataReader = oaCommand.ExecuteReader()
Dim carDetails As IEnumerable(Of CarsDetails) = dbContext.Translate(Of CarsDetails)(TryCast(dataReader, DbDataReader))
End Using
End Using
End Using
End Using
End Sub |