NonQuery

##NonQuery commands

NonQuery commands (msdn) or simply commands are the simplest to define so we will cover them first. These are commonly INSERT, UPDATE, DELETEstatements or Stored Procedures without results.

Note: A complete example is available at the bottom of this page.

###Database Manager The first step is to build a database manager.

using(var databaseManager = new DatabaseManager("ConnectionString"))
{
}

Note: Remember when building a new DatabaseManager to always use the using keyword so that resources are cleaned up when you are done with it.

###Define Command

Next we need to tell Susanoo what SQL command to execute. We do this be build a CommandExpression using the CommandManager.DefineCommand method which takes 2 parameters, the first is the CommandText and the second is the System.Data.CommandType enum which controls how ADO.NET sends the command to your SQL environment (Text or StoredProcedure, Table is not supported in Susanoo).

CommandManager.DefineCommand(@"UPDATE dbo.SomeTable 
          SET SomeColumn = 'New Value'
          WHERE Id = @Id", System.Data.CommandType.Text);

At this point we could also change how Susanoo treats the command in a few different ways, but for now we are just going to wrap the command up into something we can execute, what Susanoo calls a CommandProcessor. We do this by calling .Realize on our CommandExpression.

var updateCommand = CommandManager.DefineCommand(@"UPDATE dbo.SomeTable 
       SET SomeColumn = 'New Value'
       WHERE Id = @Id", CommandType.Text)
	.Realize();

We now have an executable ADO.NET command that we could save to a variable and use elsewhere or use it immediately, the latter which is what we will do now.

###Execute To execute a command we need to call one of the available Execute methods on our CommandProcessor we just built, for this example we will use ExecuteNonQuery. All Execute methods share the same 3 parameters:

• An instance of the DatabaseManager class
• [Optional] An anonymous OR strongly typed object with properties matching DbParameters needed for executing
• [Optional] An array of DbParameters you want to include for the command. (Useful for output parameters.)

For this example we will use an anonymous object and let Susanoo build our DbParameters for us. This is known as our Filter.

###Complete Example

using(var databaseManager =
    CommandManager.BuildDatabaseManager("ConnectionString"))
{
	var updateCommand = CommandManager.DefineCommand(@"UPDATE dbo.SomeTable 
	       SET SomeColumn = 'New Value'
	       WHERE Id = @Id", CommandType.Text)
		.Realize();
		
    updateCommand
	    .ExecuteNonQuery(databaseManager, new { Id = 5 });
}

Note: 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. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.*

MSDN: SqlCommand.ExecuteNonQuery Method