SubSonic – Custom Query

SubSonic – how to execute custom query using QueryCommand and read a value from single row:

string result = String.Empty;
int id = 1;
string sqlCommand = "select Col1 from Table1 where id = @IdParam";

QueryCommand cmd = new QueryCommand(sqlCommand);

// Remember to set provider name in case if multiple databases are being
// used in the current project
// (because DataService remembers the latest executed connection string)!
cmd.ProviderName = DBTable1.Schema.Provider.Name;

cmd.AddParameter("@IdParam", id, DbType.Int32);

using (IDataReader rdr = DataService.GetReader(cmd))
    if (rdr.Read())
        result = Convert.ToString(rdr[rdr.GetOrdinal("Col1")]);