C# Code Snippet - ExecuteNonQuery against the SQL Server Database

C# Code Snippet - ExecuteNonQuery against the SQL Server Database

This .Net C# 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.

public int ExecuteNonQuery(
        ref System.Data.SqlClient.SqlConnection _SqlConnection, 
        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 
                = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        _SqlRetVal = _SqlCommand.ExecuteNonQuery();

        // Dispose command
        _SqlCommand.Dispose();
        _SqlCommand = null;
    }
    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 = new 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 != null && _SqlConnection.State == ConnectionState.Open)
{
    // add new record to database table
    if (ExecuteNonQuery(ref _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(ref _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# 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# (C-Sharp)

External Resources:

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

Leave a comment