How to list aliased columns on reader

I'm trying to populate a chart and I need to grab data from an SQL Server database, I got my query and its quite simple but is made out of functions so the columns are all aliased, so when I try to add the columns to the reader I get no results, it just wont find them.

Here's my sample code...

public static List ListNaturalResources(int directorateId)
{
var newList = new List();
using (var connection = new SqlConnection(Settings.Default.ServicesConnectionString))
{
connection.Open();
string sql = "SELECT " +
"MONTH(DateCreated) AS dMonth," +
"YEAR(DateCreated) AS dYear," +
"COUNT(*) AS dCount" +
"FROM Works" +
"WHERE DirectorateID = " + directorateId + " " +
"GROUP BY " +
"MONTH(DateCreated), " +
"YEAR(DateCreated);";
using (var command = new SqlCommand(sql, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var countPerDirectorate = new Work();
countPerDirectorate. ?????? = reader["WorkDescription"].ToString();
newList.Add(countPerDirectorate);
}
}
}
}
return newList;
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Guest User10y ago
BUZZZ!!!
I got it, create a new class
public partial class WorksPerDirectorate
{
public int DMonth {get;set;}
public int DYear {get;set;}
public int DCount {get;set;}
}
and then:
using(var reader = command.ExecuteReader())
{
while(reader.Read())
{
var nw = new WorksPerDirectorate();
nw.DMonth = (int)reader["dMonth"];
nw.DYear = (int)reader["dYear"];
nw.DCount = (int)reader["dCount"];
newList.Add(nw);
}
}
Sorry, I just rushed into asking...