Check if a column exists while reading a database in C#

Question

In my database I made two different tables for objects, let's say OBJECT, and ORIGINAL_OBJECT(in French, in example it is Repere), the only difference between them is ORIGINAL_OBJECT has not any column where I save the modifies made.

I have a function that gets me all the fields :

public Repere Select_detail_repere(string query)
        {
            Repere det = null; ;
            if (this.OpenConnection() == true)
            {
                IDataReader dataReader = ExecuteReader(query);
                while (dataReader.Read())
                {
                    det = new Repere();
                    det.Name = (dataReader["DET_NOM"] ?? string.Empty).ToString().Trim();
                    det.Modifies = dataReader["MODIFICATIONS"].ToString().Trim();
                    det.Profil = (dataReader["DET_PRF"] ?? string.Empty).ToString().Trim();
                    det.Matiere = (dataReader["DET_MAT"] ?? string.Empty).ToString().Trim();
                    det.GroupeProfil = (dataReader["DET_GRP_PRF"] ?? string.Empty).ToString().Trim();
                    det.Longueur = double.Parse(dataReader["LONGUEUR"].ToString().Trim());
                    det.Largeur = double.Parse(dataReader["DET_LARGE"].ToString().Trim());
                    det.Hauteur = double.Parse(dataReader["DET_HAUT"].ToString().Trim());
                    det.Poids = double.Parse(dataReader["DET_PDS"].ToString().Trim());
                    det.Angle1 = double.Parse(dataReader["ANG_AME_1"].ToString().Trim());
                    det.Angle2 = double.Parse(dataReader["ANG_AME_2"].ToString().Trim());
                    det.AngleAile1 = double.Parse(dataReader["ANG_AILE_1"].ToString().Trim());
                    det.AngleAile2 = double.Parse(dataReader["ANG_AILE_2"].ToString().Trim());
                    det.PercageString = (dataReader["PERCAGE"] ?? string.Empty).ToString().Trim();
                    det.ScribingString = (dataReader["SCRIBING"] ?? string.Empty).ToString().Trim();
                    det.Mark = (dataReader["MARK"] ?? string.Empty).ToString().Trim();
                    det.ContInt0 = (dataReader["CONT_INT"] ?? string.Empty).ToString().Trim();
                    det.ContExt0 = (dataReader["CONT_EXT"] ?? string.Empty).ToString().Trim();
                    det.Revision = (dataReader["REVISION"] ?? string.Empty).ToString().Trim();
                }
                this.CloseConnection();
            }
            return det;
        }

I use the same function for both OBJECT and OBJECT_ORIGINAL. But when I want to read my OBJECT_ORIGINAL, I meet an error that says the field doesn't exist (obviously).

I already met the same problem in other situations, as this function will only work if I use SELECT * (if I don't read all columns this will return an error).

Until now the only way I found to solve it is using try/catch (in the catch I will apply a default value, ID=-1 for example), but I feel as it is not a very correct solution, and looking for another way to do that.

Answer 1

You need to check if the column MODIFICATIONS exists in the datareader, as you don't know it on forehand.

In the documentation of Microsoft I found a method called GetSchemaTable.
It will give you a datatable that describes the column metadata. So you could check that table to see if there is a column with the name MODIFICATIONS.
I would put that check in a different method so it doesn't clutter the code in your while loop that much.

OR

You could check this question on StackOverflow Check for column name in a SqlDataReader object which has a shorter solution to your problem.
I think that's even a nicer and easier solution, but I found it (with a simple google search) after I had almost completed my answer above. So that's why I give you both solutions (and also to help you a little with the MS documentation, which I pointed to in my comment)

Answer 2

It would be helpful to give as the query you use as well but I think you can use IF COL_LENGTH('table_name','column_name') IS NOT NULL in your query.

Answer 3

You can use the mysql stored procedure and do the checking inside on it. check it here. check if column exists before ALTER TABLE -- mysql

Answer 4

This question can be quoted as "Duplicate", I found the answer on stack

public Repere Select_detail_repere(string query)
{
    Repere det = null; ;
    if (this.OpenConnection() == true)
    {
        IDataReader dataReader = ExecuteReader(query);
        bool containsModification = CheckIfDataContains(dataReader, "MODIFICATIONS");
        while (dataReader.Read())
        {
            det = new Repere();
            det.Name = (dataReader["DET_NOM"] ?? string.Empty).ToString().Trim();
            if(containsModification)
            {
                 det.Modifies = dataReader["MODIFICATIONS"].ToString().Trim();
            }
            else
            {
                 det.Modifies = "";
            }
            det.Profil = (dataReader["DET_PRF"] ?? string.Empty).ToString().Trim();
            det.Matiere = (dataReader["DET_MAT"] ?? string.Empty).ToString().Trim();
            ...
         }
         this.CloseConnection();
      }
      return det;
  }
  public bool CheckIfDataContains(IDataReader dataReader,string columnName)
    {
        for (int i = 0; i < dataReader.FieldCount; i++)
        {
            if (dataReader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
            return true;
        }
        return false;
    }