Hi,

I am trying to retrieve the following result set form and SQL Server 2005 database:

select cast(FIELD1 as varchar)+ ' (' + FIELD2 + ')' [text], FIELD2 [Value] from SOMETABLE order by FIELD1 

Were FIELD1 is an integer and FIELD2 is varchar.

I am using TypedListDAO.GetMultyAsDatatable with a custom ResultsetFields.

Is this possible using LLBLGen 2.0?

Thanks

I would recommend fetching the data as seperate fields and then merging the formatted data in code

string format = "[MyDescription] + ( + [MyId] + )";
MyTypedList.Columns.Add("FormmattedColumn", typeof(string), format);

I haven't tested this but it should work. You will need to add the column to the typedlist's datatable columns and also add a new TypedListRow so you can access this with a property.

Thank you jmeckley. But what if my data set would have 10 - 20 such fields and 10.000 or more records?

the number of records is irellevant (sp). applying a column expression to a column affects all rows.

if you have 10-20 columns then you would add a column for each pair. you could create a helper function to speed up the process as well.

public class DataTableHelper
{
     private DataTable table;

     public DataTableHelper(DataTable table)
     {
          if(table == null)
          {
               throw new ArgumentNullException("table");
          }
          this.table = table;
     }

     public void AddColumn(DataColumn desc, DataColumn id)
     {
          string expression = string.Format(@"[{0}] + " (" + [{1}] + ")" ", desc.ColumnName, id.ColumnName);
          string columnName = desc.ColumnName + id.ColumnName;
          this.table.Columns.Add(columnName, typeof(string), expression);
     }
}

it would be implemented like this

DataTable t = new DataTable();
adapter.FetchTypedList(fields, t, ...);
DataTableHelper helper = new DataTableHelper(t);
helper.AddColumn(t.Columns[1], t.Columns[0]);
helper.AddColumn(t.Columns[x], t.Columns[y]);
...

i altered the format expression string slightly. again this may need to be adjusted to produce the correct results, but you get the idea.

Yes, your approach is interesting. My previous concern was for the server resources. You're code will create a new DataTable object or only an interface for retrieving the values from the original table object?

now matter what 10,000 records is a lot of records As for resources. i would think processing the formatting on the server (db) would be more intensive than the client (code).

The Helper function I created only works if the table already contains columns. technically it doesn't need data. but if you fill the dynamic list first the columns are created automatically. once you use the helper function return the table object as you normally would and the new expression columns will be present.

I am trying to retrieve the following result set form and SQL Server 2005 database:

select cast(FIELD1 as varchar)+ ' (' + FIELD2 + ')' [text], FIELD2 [Value] from SOMETABLE order by FIELD1

But what if my data set would have 10 - 20 such fields and 10.000 or more records?

My previous concern was for the server resources.

For all the above statements I'd say a database view would be a good approach.

Thanks Waala for your input. My company is using LLBLGen for more than a year now and since a couple of months ago we've switched to LLBLGen 2.0; However the limited time didn't allowed us to explore the full potential of LLBLGen 2.0 and a reason for that is the minimal documentation provided with the product. So far, this forum was the best and "to the point" documentation I could find. I would be grateful if you could provide me any documentation based on examples of solving specific problems (other than this forum); After that I will mark my topic as solved.

However the limited time didn't allowed us to explore the full potential of LLBLGen 2.0 and a reason for that is the minimal documentation provided with the product

The forums are a fast and good, thanks to the active users, and the support team But the documentations are too rich, it's more than 400 pages, so it is far away from being minimal.

I would be grateful if you could provide me any documentation based on examples of solving specific problems (other than this forum);

Believe the documentations already covers most of the issues and questions you may encounter, and the rest can be handled by the forums, like your main question in this thread, which is a very user/project specific question.

But for example, to know how to use a database view with LLBLGen Pro, you may check the following manual sections: 1- Using the designer -> Adding and editing typed views 2- Using the generated code -> SelfServicing/Adapter -> Using TypedViews, TypedLists and Dynamic Lists -> Using the typed view classes

Best of Luck

As I said: I need "to the point" documentation, based on examples of solving specific problems. The documentation may have 400 pages, but the section Using the generated code -> SelfServicing/Adapter -> Using TypedViews, TypedLists and Dynamic Lists has no more than 6 pages and there is nothing about how to retrieve a string concatenation of values from two columns where one of them is integer. However, thank you all for your help. Based on your help I was able to solve my problem (avoid using SQL ). I'll open a new topic...