Best way to check if column exist in the DataReader

Question

We have number of store procedures against each data layer. For an example , we have an Employee table with 20 columns and there are about seven store procedures where this table has been referenced. We have one data binding method used against all employee store procedures. Every time i add a new column in the table, i have to add the column reference to all seven store procedure (even though it is not required in all of them). which is bit pain.

As we are using one data binding method, what would be the best way to make this process more efficient?

What if i add a column reference to just in those sp where it is required and then check during data binding if column exists in the dataReader. I don't want to loop through each row and then loop through all columns to find out if column exists. If i have 1000 rows and 20 columns then it would be a loop of 1000 x 20 which is not very efficient.

Would that be okay if i add dataReader results in ArrayList and then use contain method to find if column exists in the ArrayList?

I think your problem is that you have on data binding method for your stored procedures. Instead, you should use Entity Framework or something like it, which abstracts the data access a bit. In the absence of that, you should not be using a single data access method when your stored procedures clearly don't need all the columns. It makes no sense to bend your database to fit your code. — John Saunders, Feb 18 '14 at 22:17
@JohnSaunders: yes you are absolutely right. Is there any Entity Framework example available which is fit for my situation. I will also try to do some research on it. — user1263981, Feb 19 '14 at 08:13

score 0 · Answer 1 · edited May 23 '17 at 10:31

Here's an extension method I found a while back to check for column existence:

Should note that it's not very efficient.

    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i = 0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
            {
                return true;
            }
        }
        return false;
    }

Perhaps you could use it on the first record and cache the results via some boolean values.

Something like the following:

    public void test()
    {
        //DataBrokerSql is my own helper.
        using (DataBrokerSql db = new DataBrokerSql(m_ConnString))
        {
            bool columnsChecked = false;
            bool hasFirstName = false;
            bool hasLastName = false;
            using (DbDataReader reader = db.GetDataReader("Select * From Person"))
            {
                while (reader.Read())
                {
                    //Only check for columns on the first row.
                    if (!columnsChecked)
                    {
                        hasFirstName = reader.HasColumn("FirstName");
                        hasLastName = reader.HasColumn("LastName");
                        columnsChecked = true;
                    }

                    if (hasFirstName)
                    {
                        //Read FirstName
                        var firstName = reader["FirstName"];
                    }

                    if (hasLastName)
                    {
                        //Read LastName
                        var lastName = reader["LastName"];
                    }
                }
            }
        }
    }

i am aware of this method but it is not very efficient. – user1263981 Feb 18 '14 at 19:34 — user1263981, Feb 18 '14 at 19:34

Best way to check if column exist in the DataReader

1 Answers1