CODEWITHSUNDEEP
sqlsql-server.net-coreado.net
Manuel Prinz
Manuel Prinz

Reputation: 11

Is there a way to use fully qualified sql column names in ado .net?

I am working on connecting a database (around 30 tables) with my .net Web API. Right now I use the following code:

public static int GetIntNullCheck(IDataRecord reader, string columnName, int valueIfNull = -1)
{
    if (reader == null)
        throw new ArgumentNullException(nameof(reader));
    int ordinal = reader.GetOrdinal(columnName);
    return reader.IsDBNull(ordinal) ? valueIfNull : reader.GetInt32(ordinal);
}

Because some of my queries join many tables, I want to use fully qualified column names to access the results. Is there a way to do this in a clean way in ado.net? There are multiple answered questions on this topic on the internet but every solution is a hack or not practical in my case.

Things I thought about but do not want to use:

1. Use SQL column aliases for conflicting column names

This would be theoretically possible but my queries are already very large and it would not be practical to do that for 20+ columns per query

2. Alter the database to have unique column names across the whole database

This is possibly the most realistic solution but the column names become very cluttered. If I have no other option I have to do this but I would really like to avoid it.

3. Use SQLDataReader.GetSchemaTable() to write this functionality myself

Firstly I don't like this because it feels like a hack and is probably bad for performance. Additionally this would not work with table aliases but I have to use those on some of my queries for self joins. Nevertheless I tried to implement it but I only get null values on the BaseTableName field of the table although I use command.ExecuteReader(CommandBehavior.KeyInfo))

3. Use a different database connection library

Ado.Net is widely used and I use mostly libraries which are used by many people because they are easier to maintain - If I have a problem with them almost certainly 100 other people on the internet had the same issue and there are blogposts discussing the solutions. I would swith the library though if there is no clean way in ado.net.

There has to be a good way to do this. Like I mentioned there are many people having this question and ado.net is big enough so it shoul support it. I just want to be able to use reader.GetOrdinal(tableName + "." + columnName);. I always used it with the JDBC in java so it has to be possible from the SQL-side

Therefore here my resulting question:

Did I miss some possible solution or do you think one of my described solutions is better than I thought?

Upvotes: 0

Views: 481

Answers (2)

Moe Sisko
Moe Sisko

Reputation: 12015

I kind of agree with Zohar regarding column aliases.

Depending on how your queries are written, and your level of comfort in the coding language, it might be feasible to write a relatively simple utility (in say, c#) which can take as input, your original sql query, automatically add the required column aliases, and output it in another window ?

Then your code could just use the query with the required column aliases.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

tl;dr; Usa aliases. That's what they are there for.

Is there a way to use fully qualified sql column names in ado .net?

No. The resultset gets it's column names based on the last part of the identifier, even if you're using a multi-part identifier - I'm pretty sure it's not an ADO.Net thing, because it also happens in SSMS (or any other database client I know of).

If you run the following query on any SQL Server database you'll see it yourself:

SELECT c.Name
     , c.Name As [Column Name]
     , T.Name
     , T.Name As [Table Name]
FROM sys.Tables As T
JOIN sys.Columns As C 
    ON C.object_id = T.object_id

The column names you get back are these:

Name    Column Name     Name    Table Name

Things I thought about but do not want to use:

  1. Use SQL column aliases for conflicting column names

That's actually the best solution to this problem as it's the easiest to implement and maintain.
You don't have to alias all the columns of every query, only the columns that cause the conflict. Usually, it's not that many.

  1. Alter the database to have unique column names across the whole database

That's might might seem like an acceptable idea, but it can become quite cumbersome very quickly.
I once designed a database that had all the columns prefixed with their containing table's name.
It's easy when you have tables such as Users, Orders or Products, and it even kinda makes sense - when you see Order_Id in the query you know exactly what table it's coming from and you (at least in theory) never need to alias anything - However - The database I was working on had some really long-named tables - tables with 4, 5 and 6 words in their names - So I've started to use abbreviations - and then you get things like PMTM_Something_Id and oops - there's goes the readability out the window - and if you're using the entire table name it's even worst - and I'm not even talking about bridge tables for many-to-many relationships.
I'm not going to do that mistake again.

  1. Use SQLDataReader.GetSchemaTable() to write this functionality myself

Don't go there. Really, don't. You're only going to get yourself into an even bigger mess than what you have now.

  1. Use a different database connection library

As I wrote at the beginning - it's not an ADO.Net thing - it's an SQL thing - so using a different database connection library will not solve this. I'm not sure how JDBC works, but it's fairly reasonable to assume reader.GetOrdinal(tableName + "." + columnName); simply does exactly what reader.GetOrdinal(columnName); does. It would be interesting to check that, however the last time I wrote anything in Java was literally in the previous millennium - so I'm not even going to try.

Upvotes: 1

Related Questions