[RESOLVED] Predicting IDataReader column names in joined query

Hi,

I am building a database code generator using T4 Text Templates, where all the code for loading and saving values to and from a database is automatically generated for me. The idea is that the generator reads an XML file in which I have to write the tables and their fields. It then generates two classes for every table, one to hold the data (simply a class with a property for every field) and one with methods to load/save this data.

For this to work I generate SQL queries on the fly, based on the table name and the field names.

In the most simple examples, this works great. For example if I have a table Users with fields Id and Username then I generate a Select query that looks like

Code:

---

SELECT * FROM Users

---

Then I use an OleDbCommand and its ExecuteReader method to get an OleDbReader object. From that reader I can read the values (Id and Username) back into a new User object:

Code:

---

User u = new User();
u.Id = Database.ConvertType<int>(reader["Id"]);
u.Username = Database.ConvertType<string>(reader["Username"]);

---

(Database.ConvertType<T> is simply a method that converts an object to the desired type if it's not DBNull, besides some other checks)


The problem occurs when I am doing slightly more complex queries. For example, if I also have a table Categories, and give the Users table a CategoryId field (that should link to the Id field of the Categories table, like a foreign field I guess), then my SQL looks like this:

Code:

---

SELECT Users.*, Categories.*
FROM (Users INNER JOIN Categories ON Users.CategoryId = Categories.Id)

---

I can generate the query on the fly, but the problem is that the IDataReader will have slightly unpredictable column names. For example, if I now try to get the Id of the User then I cannot use

Code:

---

reader["Id"]

---

because there is no column 'Id'. There is a column 'Users.Id' and 'Categories.Id', to distinguish between the two.

My question really is: how do I predict the values that Access gives to the column names in the IDataReader?

As far as I have seen, it simply puts the table name in front of the field name whenever there are conflicting names (such as Id in this case). But is this the only case? I'm not sure if I can rely on this assumption, just because I haven't seen other cases doesn't mean there aren't any.

At the moment I have been playing it dirty and doing this:

Code:

---

object id;
try
{
    // try normal column name
    id = reader["Id"];
}
catch (IndexOutOfRangeException ex)
{
    try
    {
        // nope? try to add table name
        id = reader["Users.Id"];
    }
    catch (IndexOutOfRangeException ex)
    {
        // not it either? Well then I don't have a clue...
        id = null;
    }
}
return id;

---

Obviously this is horrible, not to mention it is ridiculously slow, especially if there's lots of these conflicts, so that the first try errors each time and jumps into the catch block.


So basically I'm looking for a better way to predict the column names that Access is giving to the IDataReader. Is there any way for me to determine which database field relates to which column name without simply trying and trying another option if it fails?


Oh yeah, I'm using an Access 2003 format (mdb) database, and C#.NET in VS 2010 in case that matters.


Thanks!


EDIT
Just to clarify, I am getting those strings ("Id", "Username", etc) from an XML file, and they are then automatically generated for me. Hence I cannot 'trial and error' until I found the right name ("Users.Id" or "Users_Id" or "Id"), the generator has to generate the correct name whatever happens.

Interesting in regards to what you are up against with column names.

I wonder if this might work (shows working against SQL-Server but you think it would also work against MS-Access) even thou they are using one table instead of two it might be worth trying if you have not yet.

http://support.microsoft.com/kb/310107

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:

Code:

---

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:

Code:

---

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:

Code:

---

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

---

It still just uses Id, CategoryId and Username.

So MS-Access is ensuring there is not an ambiguous column and makes an internal decision on how to handle the issue which of course is a known thing but appears to not be under outside control i.e. the developer.

So out of wonderment what if you take

Code:

---

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

---

to aliasing

Code:

---

SELECT Users.Id as UsersId, Users.CategoryId as UsersCategoryId , Users.Username as UsersUsername FROM Users

---

Not sure if this is of any help but it can not hurt to suggest.

Ah, cool, I didn't know you could alias the fields as well. That seems to work fine so far :)
Thanks!