How to: Execute Update Statements

This topic provides examples of how to execute update operations by using Telerik Data Access Commands or the 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 Update Commands

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

You need to perform the following steps:

1. First, you need an instance of 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 UpdateCategory()
{
   // 1. Create a new instance of OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       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 =
                   "UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID";

               // 4. Create command parameters.
               IDbDataParameter categoryName = command.CreateParameter();
               categoryName.ParameterName = "@CategoryName";
               categoryName.Value = "UpdateCategoryName";
               command.Parameters.Add( categoryName );

               IDbDataParameter 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 UpdateCategory()
    ' 1. Create a new instance of OpenAccessContext.
    Using dbContext As New FluentModel()
        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 = "UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID"

                ' 4. Create command parameters.
                Dim categoryName As IDbDataParameter = command.CreateParameter()
                categoryName.ParameterName = "@CategoryName"
                categoryName.Value = "UpdateCategoryName"
                command.Parameters.Add(categoryName)

                Dim categoryIdParam As IDbDataParameter = 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 Update Statements by Using the Context API Approach

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

private static void UpdateCategoryContextApi()
{
   // 1. Create a new instance of OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       Category lastCategory = dbContext.Categories.LastOrDefault();
       // 2. Create a string containing the statement.
       const string SqlNonQueryString = "UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID";

       // 3. Create command parameters.
       DbParameter categoryName = new OAParameter
       {
           ParameterName = "@CategoryName",
           Value = "UpdateCategoryName"
       };

       DbParameter categoryIdParam = new OAParameter
       {
           ParameterName = "@CategoryID",
           Value = lastCategory.CategoryID
       };

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

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

Private Sub UpdateCategoryContextApi()
    ' 1. Create a new instance of OpenAccessContext.
    Using dbContext As New FluentModel()
        Dim lastCategory As Category = dbContext.Categories.LastOrDefault()

        ' 2. Create a string containing the statement.
        Const SqlNonQueryString As String = "UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID"

        ' 3. Create command parameters.
        Dim categoryName As DbParameter = New OAParameter With {.ParameterName = "@CategoryName",
                                                                .Value = "UpdateCategoryName"}

        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, categoryName, categoryIdParam)

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