Tutorial :Use the Subsonic.Select() ExecuteTypedList Method with String



Question:

This is more a question regarding generics than subsonic:

Imagine if have the following code:

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

That works great and returns a generic list with the ids from my Product table.

But if I want to get a list of the ProductName:

    List<String> result =         DB.Select(Product.Columns.ProductName)          .From<Product>()          .ExecuteTypedList<String>();  

it throws a compiler message (translated from german):

"string" has to be a non-abstract type with a public Constructor without parameter, in order to be used as a generic type or in the generic method "SubSonic.SqlQuery.ExecuteTypedList()" as param "T".

cause: String has no empty contructor:

int i = new int;       // works  String s = new String; // compiler error: "string" does not contain a constructor that takes '0' argument  

If I use a List<Object> instead it works, but is there a more elegant way, where I can use List<String> ?

Update: List<Object> does not work. I indeed get a list of objects but that seem to be "empty" object that doesn't contain my ProductNames ( object.ToString() returns {Object} )


Solution:1

With a little bit dotnet magic it is possible without patching the subsonic code.

  1. Create a new class SubsonicSqlQueryExtensionMethods and drop this code:

    using System;  using System.Collections.Generic;  using System.Linq;  using System.Text;    using SubSonic;    namespace MyUtil.ExtensionMethods  {      public static class SubSonicSqlQueryExtensionMethods      {          public static List<String> ExecuteTypedList(this SqlQuery qry)          {              List<String> list = new List<String>();              foreach (System.Data.DataRow row in qry.ExecuteDataSet().Tables[0].Rows)              {                   list.Add((String)row[0]);              }              return list;          }      }  }  

Now add a reference to MyUtil.ExtensionMethods to your class:

    using MyUtil.ExtensionMethods;  

And finally this works:

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

Please note that the above extension method overloads the ExecuteTypedList() method with no type-argument (unfortunately this snippet requires dotnet 3.5, but for me it works)


Solution:2

I know I am late to this party but I found a neat way of 'tricking' this problem.

    List<String> result =     DB.Select()      .From<Product>()      .ExecuteTypedList<String>().Select(p => p.ProductName).ToList<String>();  

This works like a charm for me.

Hope is helps someone somewhere, As I am sure you are far past the issue.


Solution:3

Well, I can think of this, but it is hardly more elegant:

List<string>result=DB.Select(Products.Columns.ProductName)  .From<Product>()  .ExecutTypedList<StringBuilder>()  .ConvertAll(sb=>sb.ToString());  


Solution:4

It doesn't look like SubSonic has proper support for ExecuteTypedList with string values. The method that builds the list first looks for a SubSonic type and if there's no match checks to see if T is a value type. Since string isn't a value type it falls through to the last condition which tries to match property names to the column names returned. Since string doesn't have a property name that you can assign, it fails.

See: BuildTypedResult method: http://subsonicproject.googlecode.com/svn/trunk/SubSonic/SqlQuery/SqlQuery.cs


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »