How to: Execute Insert Statements

This topic provides examples of how to execute insert 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 Insert Commands

The following example demonstrates how to execute an insert 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 InsertCategory()
{
   // 1. Create a new instance of OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       using ( IDbConnection connection = dbContext.Connection )
       {
           // 2. Create a new command.
           using ( IDbCommand command = connection.CreateCommand() )
           {
               // 3. Initialize the CommandText property.
               command.CommandText =
                   "INSERT INTO Categories(CategoryName, ImageFileName) " + 
                   "VALUES(@CategoryName, @ImageFileName)";

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

               IDbDataParameter imageFileName = command.CreateParameter();
               imageFileName.ParameterName = "@ImageFileName";
               imageFileName.Value = "NewImageFileName";
               command.Parameters.Add( imageFileName );

               // 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 InsertCategory()
    ' 1. Create a new instance of OpenAccessContext.
    Using dbContext As New FluentModel()
        Using connection As IDbConnection = dbContext.Connection
            ' 2. Create a new command.
            Using command As IDbCommand = connection.CreateCommand()
                ' 3. Initialize the CommandText property.
                command.CommandText = "INSERT INTO Categories(CategoryName, ImageFileName)" & 
                                      " VALUES(@CategoryName, @ImageFileName)"

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

                Dim imageFileName As IDbDataParameter = command.CreateParameter()
                imageFileName.ParameterName = "@ImageFileName"
                imageFileName.Value = "NewImageFileName"
                command.Parameters.Add(imageFileName)

                ' 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 Insert Statements by Using the Context API Approach

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

private static void InsertCategoryContextApi()
{
   // 1. Create a new instance of OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       // 2. Create a string containing the statement.
       const string SqlNonQueryString = "INSERT INTO Categories(CategoryName, ImageFileName) " + 
                                        "VALUES(@CategoryName, @ImageFileName)";

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

       DbParameter imageFileName = new OAParameter
       {
           ParameterName = "@ImageFileName",
           Value = "NewImageFileName"
       };

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

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

Private Sub InsertCategoryContextApi()
    ' 1. Create a new instance of OpenAccessContext.
    Using dbContext As New FluentModel()
        ' 2. Create a string containing the statement.
        Const SqlNonQueryString As String = "INSERT INTO Categories(CategoryName, ImageFileName) " & _
                                            "VALUES(@CategoryName, @ImageFileName)"

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

        Dim imageFileName As DbParameter = New OAParameter With 
            {.ParameterName = "@ImageFileName",
            .Value = "NewImageFileName"}

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

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