Adding Data To a Table That Isn't Part Of the Original SQL Query

[Robin Thomas]

I am fairly new to ASP.NET so I think I am missing something fundamental.
Anyway, quite often I am pulling data from a database, but then I need to
use that data to produce more data. A simple example would be: Let's say
Column1=StartDate and Column2=EndDate. In addition to displaying Column1 and
Column2, I need to do some calculations and display in as Column3.

The calculations are easy and can be done in the code-behind. How to display
it is another question.
However, they might not be math calculations. Take for example, a SQL query
that returns a list of servers and then for each server I use WMI to get
some additional information and then I need to display the servername (from
SQL) and the OS and Service Pack Version (from WMI) on a web page.

The way I have been handling it is creating a new table in memory and adding
the columns from the original SQL query and then doing the calculations and
adding that as a column. Then I'll use this new table to DataBind my
DataGrid or DataList.

Is this the best way to do it? I feel like this isn't the smartest way to
acomplish this.

 

[vMike]

Here is an example of adding a column to a datatable this is based on two
other columns

Dim myDataColumn As DataColumn= new datacolumn("ShipDisplay")
myDataColumn.datatype=System.Type.GetType("System.String")
myDataColumn.expression = ("Description + ' cost $' + Charge")
dt.columns.add(myDatacolumn)

Please note that the expression not an asp.net statement, it is like a
database statement. In the above example the expression is the data in the
Description column concatenated with the word 'cost $' and data in the
Charge column. So if I had and row item in my table with the Description
'Express' and the Charge column was "20" then the new column would display
'Express cost $20' for that data row. I don't think you can use variables
(except for constant strings) in the expression statement. You can use math
and other functions like you would in a database statement but it has to be
based on the columns of the data base.

Hope this helps.

 

[Robin Thomas]

Yes, this helps very much. What if I didn't want that column to be a
calculation based on other columns (as your example shows), but rather
additional information for that record.

Is there something other than "myDataColumn.expression=" that I could set
the value of the new column to a string value?

 

[vMike]

Well you can set it to some contant value here. Otherwise you add the column
with no expression statement and then
you will have to iterate through the entire datatable and add a value to
each row using a for i = 0 to dt.rows.count - 1 ..
dt.rows(i)("yournewcolumnname") = i (or somevalue) ... next i . (this is
common for adding sequential row numbers to the datatable) This will have
to be done every time you create the datatable unless you are able to save
the table in a database or xml file.