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

  • Question

  • 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?

    Tuesday, July 9, 2019 12:31 PM

Answers

  • You'll do it as you would do it for ordinary columns. Just use the column name or alias. See also Sql​Data​Reader Class. E.g.

    using (SqlDataReader read2 = cmd.ExecuteReader())
    {
    	while(read2.Read())
    	{
    	string percentage = read2["Percentage"].ToString();
    	}
    } 

    • Marked as answer by Learner177 Tuesday, July 9, 2019 12:50 PM
    Tuesday, July 9, 2019 12:44 PM

All replies

  • You'll do it as you would do it for ordinary columns. Just use the column name or alias. See also Sql​Data​Reader Class. E.g.

    using (SqlDataReader read2 = cmd.ExecuteReader())
    {
    	while(read2.Read())
    	{
    	string percentage = read2["Percentage"].ToString();
    	}
    } 

    • Marked as answer by Learner177 Tuesday, July 9, 2019 12:50 PM
    Tuesday, July 9, 2019 12:44 PM
  • thanks i was making a small mistake i used previous reader for which i was giving other query that didnt have this column 

    thanks got it now

    • Edited by Learner177 Tuesday, July 9, 2019 12:51 PM
    Tuesday, July 9, 2019 12:47 PM