1. Thomas Chiu
  2. SnapObjects
  3. Wednesday, 24 February 2021 14:06 PM UTC

Our team is starting to develop WebApi's using Appeon's Snapobjects.  One of the main libraries we are using is the SqlExecutor class to create our data access layer.

One of the things that got us confused was the ability to use the SqlExecutor.Select function to select values from one single column in a table.  Take the example below:

string selectSql = "SELECT col_1 FROM table";
List<string> list = new List<string>();
var templist = _context.SqlExecutor.Select<DynamicModel>(selectSql);
for (int i = 0; i < templist.Count; i++)
{
    list.Add(templist[i].GetValue<string>("col_1"));
}

The above code works as expected but before arriving at that solution we attempted to do this:

string selectSql = "SELECT col_1 FROM table";
List<string> list = new List<string>();
list = _context.SqlExecutor.Select<string>(selectSql);

OR 

string selectSql = "SELECT col_1 FROM table";
List<string> list = new List<string>();
list = _context.SqlExecutor.Select<string>(selectSql) as List<string>;

OR 

string selectSql = "SELECT col_1 FROM table";
List<string> list = new List<string>();
list = _context.SqlExecutor.Select<string>(selectSql).ToList();

All of the 3 examples above DO NOT work.  I was just wondering why.  Can we not use standard data types in the Select<TModel> field?  It seems weird we have to manually populate a List<string> manually by looping through all of the values.  

Arnd Schmidt Accepted Answer Pending Moderation
  1. Thursday, 25 February 2021 12:28 PM UTC
  2. SnapObjects
  3. # 1

I think the exceptions that you are getting for your attempts, say it all.

If you want to use a "One-Liner":

var list =_context.SqlExecutor.Select<DynamicModel>(selectSql).Select(r => r.GetValue<string>(0)).ToList();

hth

Arnd

 

 

Comment
There are no comments made yet.
Thomas Chiu Accepted Answer Pending Moderation
  1. Thursday, 25 February 2021 13:02 PM UTC
  2. SnapObjects
  3. # 2

Thank Arnd!

Appreciate the "one-liner" example.  My main question was does the <TModel> only support custom classes and the "DynamicModel" class?  It doesn't support standard data types (i.e. _context.SqlExecutor.Select<int> or _context.SqlExecutor.Select<string>).  Is that a correct statement? 

Thanks!

Comment
  1. Logan Liu @Appeon
  2. Thursday, 25 February 2021 17:45 PM UTC
Yes, the <TModel> only supports custom classes and the "DynamicModel" class. The select method gets a result set after executing the SQL SELECT statement. There are usually one or more columns in the result set.

If you need to only get one column by executing a SQL in your project frequently, you can extend a custom method (Such as SelectFirstColumn() or SelectColumn("ColumnName")) on the SqlExecutor. You can also submit an enhancement requirement at https://www.appeon.com/standardsupport/.

Regards, Logan
  1. Helpful
  1. Thomas Chiu
  2. Thursday, 25 February 2021 17:55 PM UTC
Thank you Logan! This was the answer I was looking for. It's not a huge deal, but it would be nice if it accepts a standard datatype too.



Thanks!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.