How to: Retrieve Data With Stored Procedures
This topic provides basic examples of how to execute stored procedures by using the Context API and Telerik Data Access Commands. Generally, there are two approaches you can use to execute stored procedures. The first one is to use the OpenAccessContext.ExecuteQuery<T> method. It requires less code and it is the easier approach. The second approach gives you more control of the result because you can get a DbDataReader object. Both approaches will be discussed 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.
Suppose, you have a Cars table like the one shown in the figure below:
And you have a stored procedure named GetCarsByMake that returns all cars filtered by make.
CREATE PROCEDURE GetCarsByMake
@CarMake varchar(50)
AS
BEGIN
SELECT *
FROM Cars
WHERE Make = @CarMake
END
GO
Using the Context API Approach
In this section, you will see how to use the OpenAccessContext to execute a stored procedure. First, you need to create and initialize a new OAParameter object. The name of the parameter should match the name of the corresponding stored procedure parameter in the database. If your stored procedure doesn't accept any parameters, you don't need to create OAParameter objects. In this example one input parameter is used. By default, the Direction of the OAParameter is Input. In case, you have an output parameter, you need to change the Direction of the parameter to Output. For more information, refer to How to: Retrieve Out Parameters and Return Value. When the parameter is set up, the second step is to call the ExecuteQuery method of the OpenAccessContext instance, and get a materialized list of Car objects out of the context.
private static void StoredProcedureUseExecuteQuery()
{
// 1. Create a new instance of the OpenAccessContext.
using ( FluentModel dbContext = new FluentModel() )
{
// 2. Create a new parameter.
DbParameter carMakeParameter = new OAParameter
{
ParameterName = "@CarMake",
Value = "Honda"
};
// 3. Execute the stored procedure and get the result.
IEnumerable<Car> cars = dbContext.ExecuteQuery<Car>( "GetCarsByMake",
CommandType.StoredProcedure, carMakeParameter );
}
}
Private Sub StoredProcedureUseExecuteQuery()
' 1. Create a new instance of the OpenAccessContext.
Using dbContext As New FluentModel()
' 2. Create a new parameter.
Dim carMakeParameter As DbParameter = New OAParameter With {
.ParameterName = "@CarMake", .Value = "Honda"}
' 3. Execute the stored procedure and get the result.
Dim cars As IEnumerable(Of Car) = dbContext.ExecuteQuery(Of Car)("GetCarsByMake",
CommandType.StoredProcedure, carMakeParameter)
End Using
End Sub
Materialized persistent types are automatically attached to the context.
Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Car objects and call the SaveChanges method of the context, then all changes will be committed to the database.
Using OACommands
The second way to execute stored procedures is to use the OACommand class. With this approach you have the ability to work directly with ADO.NET. Again, you need a new OAParameter object. Next, you need to get an existing instance of the OAConnection class, by using the OpenAccessContext.Connection property. Create a new instance of the OACommand class, by using the OAConnection.CreateCommand method. Set the OACommand.CommandText property to the name of the stored procedure. Execute the command by calling the OACommand.ExecuteReader method. The final step is to materialize the result.
private static void StoredProcedureUseOACommand()
{
// 1. Create a new instance of the OpenAccessContext.
using ( FluentModel dbContext = new FluentModel() )
{
// 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 carMakeParameter = oaCommand.CreateParameter();
carMakeParameter.ParameterName = "@CarMake";
carMakeParameter.Value = "Honda";
// 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure;
oaCommand.CommandText = "GetCarsByMake";
oaCommand.Parameters.Add( carMakeParameter );
// 6. Execute the command and materialize the entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
// 7. Materialize entities
List<Car> cars = dbContext.Translate<Car>( dataReader as DbDataReader ).
ToList();
}
}
}
}
}
Private Sub StoredProcedureUseOACommand()
' 1. Create a new instance of the OpenAccessContext.
Using dbContext As New FluentModel()
' 2. Retrieve the OAConnection instance.
Using oaConnection As IDbConnection = dbContext.Connection
' 3. Create a new instance of the OACommand class.
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
' 4. Create a new parameter.
Dim carMakeParameter As IDbDataParameter = oaCommand.CreateParameter()
carMakeParameter.ParameterName = "@CarMake"
carMakeParameter.Value = "Honda"
' 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCarsByMake"
oaCommand.Parameters.Add(carMakeParameter)
' 6. Execute the command and materialize the entities
Using dataReader As IDataReader = oaCommand.ExecuteReader()
' 7. Materialize entities
Dim cars As List(Of Car) = dbContext.Translate(Of Car) _
(TryCast(dataReader, DbDataReader)).ToList()
End Using
End Using
End Using
End Using
End Sub
Optionally, if the execution of the stored procedure is expected to take long time, you can set an appropriate value for the CommandTimeout property of oaCommand. By default, it is the one specified in Runtime Configuration. If the command exceeds the timeout it will be terminated and a backend specific exception will be thrown.