[RESOLVED] Predicting IDataReader column names in joined query

[NickThissen]

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

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:

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:

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

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:

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.