How to: Manipulate Data With Stored Procedures

Telerik Data Access provides several approaches for manual executions of insert, update and delete stored procedures. The common thing between them is that Telerik Data Access cannot verify what exactly these methods do and roles back the changes they make when the context is disposed. So, in order to commit to the database the changes done by a procedure, you need to explicitly call the SaveChanges method of the context.

Suppose you have a stored procedure named CreateCategory that accepts a single parameter CategoryName and inserts a new record in the Categories database.

Generally, there are two ways to execute the CreateCategory procedure:

Regardless of which approach you decide to apply, make sure to invoke the SaveChanges method immediately after the call to the procedure. The same logic is valid if your stored procedures perform update or delete operations.

To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces.

Using the Telerik Data Access ADO API – Context API Approach

The following example demonstrates how to execute the CreateCategory method by using the Context API approach.

using ( FluentModel dbContext = new FluentModel() )
{
   DbParameter categoryNameParameter = new OAParameter
   {
       ParameterName = "@CategoryName",
       Value = "NewCategoryame"
   };

   dbContext.ExecuteNonQuery( "CreateCategory", CommandType.StoredProcedure,
       categoryNameParameter );
   dbContext.SaveChanges();
}
Using dbContext As New FluentModel()
    Dim categoryNameParameter As DbParameter = New OAParameter With {
        .ParameterName = "@CategoryName",
        .Value = "NewCategoryame"}

    dbContext.ExecuteNonQuery("CreateCategory", CommandType.StoredProcedure, categoryNameParameter)
    dbContext.SaveChanges()
End Using

Using the Telerik Data Access ADO API – Telerik Data Access Commands Approach

The following example demonstrates how to execute the CreateCategory method by using the command approach.

using ( FluentModel dbContext = new FluentModel() )
{
   using ( IDbConnection oaConnection = dbContext.Connection )
   {
       using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
       {
           IDataParameter categoryNameParameter = oaCommand.CreateParameter();
           categoryNameParameter.ParameterName = "@CategoryName";
           categoryNameParameter.Value = "NewCategoryName";

           oaCommand.CommandType = CommandType.StoredProcedure;
           oaCommand.CommandText = "CreateCategory";
           oaCommand.Parameters.Add( categoryNameParameter );

           oaCommand.ExecuteNonQuery();
           dbContext.SaveChanges();
       }
   }
}
Using dbContext As New FluentModel()
    Using oaConnection As IDbConnection = dbContext.Connection
        Using oaCommand As IDbCommand = oaConnection.CreateCommand()
            Dim categoryNameParameter As IDataParameter = oaCommand.CreateParameter()
            categoryNameParameter.ParameterName = "@CategoryName"
            categoryNameParameter.Value = "NewCategoryName"

            oaCommand.CommandType = CommandType.StoredProcedure
            oaCommand.CommandText = "CreateCategory"
            oaCommand.Parameters.Add(categoryNameParameter)

            oaCommand.ExecuteNonQuery()
            dbContext.SaveChanges()
        End Using
    End Using
End Using

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.