How to: Manipulate Data With Stored Procedures

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

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. And the stored procedure is included in your domain model.

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

• Creating a Domain Method
• Using the Telerik Data Access ADO API – Context API Approach
• Using the Telerik Data Access ADO API – Telerik Data Access Commands Approach

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.

Creating a Domain Method

Executing the CreateCategory stored procedure as a method of the context is easy.

using ( EntitiesModel dbContext = new EntitiesModel() )
{
   dbContext.CreateCategory( "NewCategory" );
   dbContext.SaveChanges();
}

Using dbContext As New EntitiesModel()
    dbContext.CreateCategory("NewCategory")
    dbContext.SaveChanges()
End Using

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 ( EntitiesModel dbContext = new EntitiesModel() )
{
   DbParameter categoryNameParameter = new OAParameter
   {
       ParameterName = "@CategoryName",
       Value = "NewCategoryame"
   };

   dbContext.ExecuteNonQuery( "CreateCategory", CommandType.StoredProcedure,
       categoryNameParameter );
   dbContext.SaveChanges();
}

Using dbContext As New EntitiesModel()
    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 ( EntitiesModel dbContext = new EntitiesModel() )
{
   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 EntitiesModel()
    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.