[RESOLVED] Predicting IDataReader column names in joined query

[NickThissen]

Yes I've looked at the GetSchemaTable method (briefly), but even if I had a list of all column names in the IDataReader I still wouldn't really know what to do with it.

Take my example again, a table Users (Id, Username, CategoryId) and Categories (Id, CategoryName).
I would get this list of column names:

Users.Id
Username
CategoryId
Categories.Id
CategoryName

The only thing I can think of doing with this list is simply check if the list contains <Fieldname>, and if not, check if it contains <Tablename>.<Fieldname>. So for example when trying to get the user Id I'd check if the list contains "Id", that would fail, and then I'd check if it contains "Users.Id", which would succeed.

However, instead of retrieving the entire list of columns I could just check if the IDataReader contains the column name directly. There's a few different methods I found to do that (and most actually do use the GetSchemaTable method), the point is that that doesn't really seem like a good method. I never like "try option 1, if not try option 2" approaches, and that's exactly what this is.

I still can't think of a better method though... At the moment I'm no longer using the try/catch block but just checking if the reader contains the column, which works for now, but it might not work for every situation.


The best solution would be to somehow force Access to spit out the column names in the <Tablename>.<Fieldname> format always, even if I'm just querying a single table. That would give me this list of columns:

Users.Id
Users.Username
Users.CategoryId
Categories.Id
Categories.CategoryName

That's a bit redundant in some cases but at least it's consistent, predictable, and will always work. I've yet to find a way to make Access do this though if it's even possible... Even when I specify the query as this it doesn't work:

SELECT Users.Id, Users.CategoryId, Users.Username FROM Users

It still just uses Id, CategoryId and Username.