ExecuteNonQuery Method

Visual Basic
C#
C++/CLI

'Declaration
 
Public Overrides NotOverridable Function ExecuteNonQuery() As Integer

'Usage
 
Dim instance As TdCommand
Dim value As Integer
 
value = instance.ExecuteNonQuery()

public override int ExecuteNonQuery()

public:
int ExecuteNonQuery(); override

Return Value

Returns the number of rows affected (Activity Count). The number of rows affected is only returned for DELETE, INSERT, and UPDATE statements. For all other statements, the activity count that is returned will be -1.

Exception	Description
System.IndexOutOfRangeException	Can be thrown because of one of the following errors: 1) The TdParameter.Offset is outside of 0 through array size � 1 2) More in-out/out parameters have been specified than returned from the stored procedure.
System.InvalidCastException	One or more parameters cannot be converted to Teradata Database native types.
System.InvalidOperationException	Can be thrown because of one of the following errors: 1) `TdConnection` is not in Open state or is null or 2) The local transaction associated with the `TdCommand` is not the same local transaction associated with the `TdConnection`. Assign the local transaction to `Transaction` property. 3) TdCommand.CommandText is null or empty.
System.ObjectDisposedException	The `TdCommand` is closed / disposed.
TdException	Can be thrown because of one of the following errors: 1) Teradata returned an error or 2) The Data Provider detected an error.

Executes a SQL statement and returns the Activity Count. Teradata always returns an Activity Count which reflects the number of rows affected for UPDATE, DELETE and INSERT statements. ExecuteNonQuery returns the sum of all activity counts if TdCommand represents a multi-statement request. For example ExecuteNonQuery return 3 if a multi-statement request that contains three INSERT statements is executed.

If the activity count overflows Int32.MaxValue, Int32.MaxValue value will be returned. The provider will post an event to TdConnection.InfoMessage when an overflow condition occurs. The event will include a warning code and message. The warning message will have the actual activity count. The message code is 111002 and the message text is "An overflow occurred while calculating the activity count, actual value is [ActivityCount]"

NOTE: In order for an application to be aware that an overflow occurred, it must register for the callback with TdConnection.InfoMessage event.

When the CommandType is set to StoredProcedure, any output parameters are populated with data.

The following example creates a TdConnection, sets up the TdConnection.InfoMessage event and executes a SQL statement. If the activity count overflows Int32.MaxValue, Int32.MaxValue is returned and a warning message with the actual activity count is exposed through the TdConnection InfoMessage event.

C#

// This method is invoked when the TdConnection.InfoMessage is fired.
public void OnInfoMessage(Object sender, TdInfoMessageEventArgs i)
{
    // The TdConnection.InfoMessage event gets fired when the activity count
    // exceeds Int32.MaxValue. The actual activity count can be extracted
    // from the message as below. 
    if( i.Errors[0].Number == 111002 )
    {
        Decimal activityCount = 
                  Convert.ToDecimal(i.Message.Split('[')[3].Split(']')[0]);
    
        Console.WriteLine("Actual Activity count is : ", activityCount);
    }
}
             
public int ExecuteQuery(string connectionString, string commandText)
{
    // Open a session to Teradata
    TdConnection cn = new TdConnection(connectionString);
    
    // Setting up the event that will get fired if the activity count exceeds 
    // the Int32.MaxValue
    cn.InfoMessage += new TdInfoMessageEventHandler(this.OnInfoMessage);
    
    cn.Open();
            
    // Execute the command
    TdCommand cmd = new TdCommand(commandText, cn);
    int activityCount = cmd.ExecuteNonQuery();
            
    // Close the command and session
    cmd.Dispose();
    cn.Close();
            
    // Return the activity count
    return activityCount;
}

Target Platforms: Windows 8.1, Windows 10, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019

Reference

TdCommand Class
TdCommand Members