The QueryCommand object in SubSonic makes writing SQL Commands a whole lot easier and less time consuming. When would you use it? Well sometimes the Query object can't perform complex queries, especially ones involving JOINS. So the QueryCommand is an excellent object to use when you need to run a complex query. Below is an example:
// The SQL (notice I am using parameters: @projectid)
string cmd1 = "SELECT ListName FROM tList a INNER JOIN tMain b ON b.tId = a.tId WHERE a.tId = @projectid";
// The QueryCommand object...
// SubSonic knows which connection to use based on the provider name “Master” which is defined in Web.Config
// Or leave it blank if you have a default provider selected (also defined in Web.Config)
QueryCommand q = new QueryCommand( cmd1, "Master" );
// Set the parameter(s)
q.AddParameter( "projectid", intProjectId, DbType.Int32, ParameterDirection.Input );
// Execute the command
string strValue = Convert.ToString( DataService.ExecuteScalar( q ) );
// You use the DataService object to execute your QueryCommand object.
// There are several methods you can use with the DataService object including:
//
// DataService.ExecuteQuery(...);
// DataService.ExecuteTransaction(...);
// DataService.GetDataSet(...);
// DataService.GetReader(...);
So in 4 lines of code (subtracting comments and breaks) you can run a custom SQL command. Thanks SubSonic!