SubSonic How to Execute a SQL Statement?

Question

Hello, My site is using Subsonic 2.2 on my site.

I have 1 weird situation where I need to run some ad-hoc SQL statements.

public IList<string> GetDistincList(string TableName, string FieldName)
{
    string sqlToRun = string.Format("SELECT DISTINCT {0} FROM {1} ORDER BY {0}", FieldName, TableName);

    Query query = new Query(TableName);
    query.PleaseRunThis(sqlToRun);
    query.ExecuteReader();

}

Can anyone help me here? As it appears, I just want to return a generic list of strings.

Thanks!

Answer 1

Use the CodingHorror class.

Here's the SubSonic 3 way of doing it: http://www.subsonicproject.com/docs/CodingHorror

The SubSonic 2 way is similar:

Dim ch As SubSonic.CodingHorror
ch.Execute("delete from @tablename", table)

Answer 2

Subsonic has a great method called ExecuteTypedList() so you can do somethink like this.

List<int> result = DB.Select(Table.Columns.Id)
  .Distinct()
  .From<Table>()
  .OrderBy(Table.Columns.Id)
  .ExecuteTypedList<int>();

or even with pocos:

public class UserResult
{
    public int Id {get;set;}
    public string Name {get;set;}
}


List<UserResult> users = DB.Select(
       User.Columns.UserId + " as Id",     // the as ... is only needed if your
       User.Columns.UserName + " as Name"  // column name differs from the
   ).From<User>()                          // property name of your class
    .ExecuteTypedList<UserResult>();

Unfortunately this method doesn't work for string since it requires a) a valuetype b) a class with a parameterless constructor since the method uses reflection to map the columns from the result to the properties of the class

However I wrote an extension method a while ago that works for string:

http://stackoverflow.com/questions/881929/use-the-subsonic-select-executetypedlist-method-with-string

Look at my own answer in the link.

If you add the extensionmethod to your code you can do:

 List<String> result = DB.Select(User.Columns.UserName)
                         .From<User>()
                         .ExecuteTypedList();