How to: Execute Delete Statements

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.

This topic provides examples of how to execute delete operations by using Telerik Data Access Commands or Context API.

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

Executing Delete Commands

The following example demonstrates how to execute a delete command against the Category table from the SofiaCarRental database.

You need to perform the following steps:

1. First, you need an instance of the generated OpenAccessContext.
2. Initialize your SQL query.
3. Create a new instance of the OACommand class and set the CommandText property. If the command is parameterized, you need to initialize one or more command parameters.
4. Invoke the ExecuteNonQuery method of the command.
5. The last step is to invoke the SaveChanges method of the context.

private static void DeleteCategory()
{
   // 1. Create a new instance of the generated OpenAccessContext.
   using ( EntitiesModel dbContext = new EntitiesModel() )
   {
       Category lastCategory = dbContext.Categories.LastOrDefault();
       using ( IDbConnection connection = dbContext.Connection )
       {
           // 2. Create a new command.
           using ( IDbCommand command = connection.CreateCommand() )
           {
               // 3. Initialize the CommandText property.
               command.CommandText = "Delete from Categories WHERE CategoryID = @CategoryID";

               // 4. Create command parameters.
               IDataParameter categoryIdParam = command.CreateParameter();
               categoryIdParam.ParameterName = "@CategoryID";
               categoryIdParam.Value = lastCategory.CategoryID;
               command.Parameters.Add( categoryIdParam );

               // 5. Invoke the ExecuteNonQuery method of the command object.
               int rowsAffected = command.ExecuteNonQuery();

               // 6. Invoke the SaveChanges method of the OpenAccessContext.
               dbContext.SaveChanges();
               Console.WriteLine( "Rows affected: {0}", rowsAffected );
           }
       }
   }
}

Private Sub DeleteCategory()
    ' 1. Create a new instance of the generated OpenAccessContext.
    Using dbContext As New EntitiesModel()
        Dim lastCategory As Category = dbContext.Categories.LastOrDefault()
        Using connection As IDbConnection = dbContext.Connection
            ' 2. Create a new command.
            Using command As IDbCommand = connection.CreateCommand()
                ' 3. Initialize the CommandText property.
                command.CommandText = "Delete from Categories WHERE CategoryID = @CategoryID"

                ' 4. Create command parameters.
                Dim categoryIdParam As IDataParameter = command.CreateParameter()
                categoryIdParam.ParameterName = "@CategoryID"
                categoryIdParam.Value = lastCategory.CategoryID
                command.Parameters.Add(categoryIdParam)

                ' 5. Invoke the ExecuteNonQuery method of the command object.
                Dim rowsAffected As Integer = command.ExecuteNonQuery()

                ' 6. Invoke the SaveChanges method of the OpenAccessContext.
                dbContext.SaveChanges()
                Console.WriteLine("Rows affected: {0}", rowsAffected)
            End Using
        End Using
    End Using
End Sub

Optionally, if the execution of the statement is expected to take long time, you can set an appropriate value for the CommandTimeout property of command. 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.

Executing Delete Statements by Using the Context API Approach

Instead of creating a new OACommand, you could use directly the OpenAccessContext.ExecuteNonQuery method.

private static void DeleteCategoryContextApi()
{
   // 1. Create a new instance of the generated OpenAccessContext.
   using ( EntitiesModel dbContext = new EntitiesModel() )
   {
       Category lastCategory = dbContext.Categories.LastOrDefault();

       // 2. Create a string containing the statement.
       const string SqlNonQueryString = "Delete from Categories WHERE CategoryID = @CategoryID";

       // 3. Create command parameters.
       DbParameter categoryIdParam = new OAParameter
       {
           ParameterName = "@CategoryID",
           Value = lastCategory.CategoryID
       };

       // 4. Invoke the ExecuteNonQuery method of the context.
       int rowsAffected = dbContext.ExecuteNonQuery( SqlNonQueryString, categoryIdParam );

       // 5. Invoke the SaveChanges method of the OpenAccessContext.
       dbContext.SaveChanges();
       Console.WriteLine( "Rows affected: {0}", rowsAffected );
   }
}

Private Sub DeleteCategoryContextApi()
 ' 1. Create a new instance of the generated OpenAccessContext.
 Using dbContext As New EntitiesModel()
  Dim lastCategory As Category = dbContext.Categories.LastOrDefault()

  ' 2. Create a string containing the statement.
  Const SqlNonQueryString As String = "Delete from Categories WHERE CategoryID = @CategoryID"

  ' 3. Create command parameters.
  Dim categoryIdParam As DbParameter = New OAParameter _
        With {.ParameterName = "@CategoryID", .Value = lastCategory.CategoryID}

  ' 4. Invoke the ExecuteNonQuery method of the context.
  Dim rowsAffected As Integer = dbContext.ExecuteNonQuery(SqlNonQueryString, categoryIdParam)

  ' 5. Invoke the SaveChanges method of the OpenAccessContext.
  dbContext.SaveChanges()
  Console.WriteLine("Rows affected: {0}", rowsAffected)
 End Using
End Sub