C# OdbcDataReader or DataTable.Load column name issue

Question

I was happy coding my application and I faced this issue that scary me a little bit.

I have a SQLite db file and when I try to read a table using the OdcbDataReader and load it into a table using the DataTable.Load I get different results on the column name depending on the application I'm working on.

Sometimes it returns table_name.column_name and sometimes it returns only column_name.

The code is only this:

public DataTable GetTable(string table_name)
{
    table = null;
    if (conn_str != null)
    {
        try
        {
            using (OdbcConnection conn = new OdbcConnection(conn_str.ToString()))
            {

                StringBuilder query = new StringBuilder();

                query.Append("SELECT * ");
                query.Append("FROM [");
                query.Append(table_name + "]");

                using (OdbcCommand cmd = new OdbcCommand(query.ToString(), conn))
                {
                    conn.Open();
                    table = new DataTable();
                    using (OdbcDataReader dr = cmd.ExecuteReader())
                    {
                        DataSet ds = new DataSet();
                        ds.EnforceConstraints = false;
                        ds.Tables.Add(table);
                        table.Load(dr);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.Message);
            table = null;
        }
    }
    return table;
}

The connection string used is exactly the same:

"DRIVER={SQLite3 ODBC Driver};DATABASE=databesename.db3;"

Any ideas why this is happening?

Answer 1

Don't resolve the issue but at least gives an workaround.

Adding a replace to column name to remove the table_name if the Reader insert it.

foreach (DataColumn col in table.Columns)
{
    //Fix column names if the Reader insert the table name into the ColumnName
    col.ColumnName = col.ColumnName.Replace(table_name + ".", "");
}

Code After the change:

public DataTable GetTable(string table_name)
{
    table = null;
    if (conn_str != null)
    {
        try
        {
            using (OdbcConnection conn = new OdbcConnection(conn_str.ToString()))
            {
                StringBuilder query = new StringBuilder();

                query.Append("SELECT * ");
                query.Append("FROM [");
                query.Append(table_name + "]");

                using (OdbcCommand cmd = new OdbcCommand(query.ToString(), conn))
                {
                    conn.Open();
                    table = new DataTable();
                    using (OdbcDataReader dr = cmd.ExecuteReader())
                    {
                        DataSet ds = new DataSet();
                        ds.EnforceConstraints = false;
                        ds.Tables.Add(table);
                        table.Load(dr);
                        foreach (DataColumn col in table.Columns)
                        {
                            //Fix column names if the Reader insert the table name into the ColumnName
                            col.ColumnName = col.ColumnName.Replace(table_name + ".", "");
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.Message);
            table = null;
        }
    }
    return table;
}