C++/CLI Code Snippet - ExecuteNonQuery against the SQL Server Database

C++/CLI Code Snippet - ExecuteNonQuery against the SQL Server Database

This .Net C++/CLI code snippet connects to SQL server and executes a SQL statement and returns the number of rows affected. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to executes a Transact-SQL statement against the connection and returns the number of rows affected. Modify the exception handling section for your project requirements.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. If SQL statement fail to execute it returns 0. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

int ExecuteNonQuery(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    int _SqlRetVal = 0;

    try
    {
        // Executes a Transact-SQL statement against the connection 
        // and returns the number of rows affected.
        System::Data::SqlClient::SqlCommand ^_SqlCommand = gcnew System::Data::SqlClient::SqlCommand(_SQL, _SqlConnection);

        _SqlRetVal = _SqlCommand->ExecuteNonQuery();

        // Dispose command
        delete _SqlCommand;
        _SqlCommand = nullptr;
    }
    catch (Exception ^_Exception)
    {
        // Error occurred while trying to execute reader
        // send error message to console (change below line to customize error handling)
        Console::WriteLine(_Exception->Message);

        return 0;
    }

    return _SqlRetVal;
}

Here is a simple example showing how to use above function (ExecuteNonQuery) to login to SQL server and insert new record to existing table in the SQL server database table and update existing record in the database table.

// set temporary variable for database connection
System::Data::SqlClient::SqlConnection ^_SqlConnection = gcnew System::Data::SqlClient::SqlConnection();

// assign database connection string
_SqlConnection->ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

// Connect to database
try
{
    _SqlConnection->Open();
}
catch (Exception ^_Exception)
{
    // Error occurred while trying to connect to database
    Console::WriteLine(_Exception->Message);
}


// Check for valid open database connection before query database
if (_SqlConnection != nullptr && _SqlConnection->State == ConnectionState::Open)
{
    // add new record to database table
    if (ExecuteNonQuery(_SqlConnection, "INSERT INTO sampletable (name, price) VALUES ('sample product name', 22.75)") > 0)
    {
        // Record successfully insert into database
        Console::WriteLine("Record added to database table");
    }
    else
    {
        // Record failed to insert into database
        Console::WriteLine("Failed to add new record to database table");
    }


    // Update record
    if (ExecuteNonQuery(_SqlConnection, "UPDATE sampletable SET price = 34.25 WHERE productid = 2") > 0)
    {
        // update record successfull
        Console::WriteLine("Record updated.");
    }
    else
    {
        // Record failed to update
        Console::WriteLine("Failed to update record");
    }

    // close database connection
    _SqlConnection->Close();
}

C++/CLI Keywords Used:

• ExecuteNonQuery
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, C++, CLI, SQL, ExecuteNonQuery, SQL Server, SQL Client, Connection String, Database Connection
• Programming Language : C++/CLI

External Resources:

• SqlCommand.ExecuteNonQuery Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

sibadutta nayak :: July 14-2009 :: 09:42 AM

how to select data from database  in visual c++ .please tell me its very urgent

Leave a comment