how to get values in c# from sql cte virtual columns?

[Jazz17]

this is my query from which i am calculating percentage as a virtual column that doesnt exist physically in table

i want to get calculated percentage in any variable to be stored for eg we can access column value by simple writing

string percentage = (read["Percentage"].ToString());

above statement will provide desired results only if Percentage column exist in table.

SqlCommand cmd = new SqlCommand(";with cte as (select distinct s.S_ID, s.Name, (select cast(count(distinct A_Date) as float)from Attendance where a.C_ID = C_ID) as Total_Classes, (select count(Pre) from Attendance where a.C_ID = C_ID and a.S_ID = S_ID and Pre = 'True') as Attended from Course c left outer join StudentCourse sc on c.C_ID = sc.C_ID left outer join Student s on s.S_ID = sc.S_ID left outer join Attendance a on a.S_ID = s.S_ID and a.C_ID = c.C_ID where c.C_ID = '"+id+"' and s.S_ID ='"+S_ID+"') select S_ID, Name, Total_Classes, Attended, cast(Attended * 100 / nullif(Total_Classes, 0) as decimal(10, 2)) Percentage from cte ", con3);
// cmd.Parameters.A
using (SqlDataReader read2 = cmd.ExecuteReader())
{
while(read2.Read())
{
string percentage = (read["Percentage from cte"].ToString());
}

}
in abve query how i can get value of the calculated percentage and then store it in any variable?

Continue reading...