Query

#Query commands

Query commands (msdn) or simply queries execute code and potentially retrieve 1 or multiple result sets. These can be SELECT statements or Stored Procedures.

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

##Prerequisite Steps

###Create a POCO In this example we will map a single result set to a POCO. We will setup a simple object now, nothing special.

public class SomeObject
{
	int Id { get; set; }
	string SomeColumn { get; set; }
}

###Database Manager The next step is to build a database manager, which is responsible for running our SQL.

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.

##Usage

###Define Command

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).

var command = 
	CommandManager
		.DefineCommand(@"SELECT * FROM dbo.SomeTable",
			CommandType.Text);

###Define Results Now that we have described the command to send to our database, we need to describe what we expect back. This id done by chaining the .DefineCommand with .DefineResults<TResult>() where TResult is our object type (SomeObject).

var command = 
	CommandManager
		.DefineCommand(@"SELECT * FROM dbo.SomeTable",
			CommandType.Text)
		.DefineResults<SomeObject>();

At this point we could also changes which properties Susanoo maps results to in a couple ways, but for now we are just going to wrap the command up into something we can execute, what Susanoo calls a CommandProcessor, by calling .Realize on our CommandExpression.

var command = 
	CommandManager
		.DefineCommand(@"SELECT * FROM dbo.SomeTable",
			CommandType.Text)
		.DefineResults<SomeObject>()
		.Realize();

We now have an executable ADO.NET command that we have saved to a variable named command 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 Execute. Which has at most the following parameters.

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

For this example we could use an anonymous object and let Susanoo build our DbParameters for us. This is known as our Filter, but we actually don't need any SQL parameters for this command.

##Complete Example

public class SomeObject
{
	int Id { get; set; }
	string SomeColumn { get; set; }
}

using(var databaseManager =
    CommandManager.BuildDatabaseManager("ConnectionString"))
{
	var command = 
		CommandManager
			.DefineCommand(@"SELECT * FROM dbo.SomeTable",
				CommandType.Text)
			.DefineResults<SomeObject>()
			.Realize();
		
    IEnumerable<SomeObject> results = 
	    command.Execute(databaseManager);
}

Note: Parameters with a value of null are NOT sent to the database by default in ADO.NET. See SendNullValues to change this behavior or send DBNull.Value instead.