DataTable - Checking for existence of column (by name)

How do I check for the existence of a particular column in a datatable (by name)?

My problem is that I fill a dataset by using an XML file with  optional elements. This means that certain rows do not contain those columns (missing elements). This is especially a problem with nested elements, since they will cause mutiple datatables in my dataset, but that is another story. Anyway, I need to do something like:

if (ColumnExists("ColumnName") {
  string myvalue = AccessColumnValue();
  // do other stuff with the column
}

How do I accomplish this? I have tried using IsNull and other things, but what I really need to know is if a column of a specified name exists in a given datatable for a given datarow. Any help suggestions are appreciated. I'm at wits end on this one.
brian_dallasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
You can read schema. her eis an example how to do it in Vb.NET

Dim cn As New OleDbConnection()
Dim cmd As New OleDbCommand()
Dim schemaTable As DataTable
Dim myReader As OleDbDataReader
Dim myField As DataRow
Dim myProperty As DataColumn

'Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;" & _
                      "Password=password;Initial Catalog=Northwind"
cn.Open()

'Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn
cmd.CommandText = "SELECT * FROM Employees"
myReader = cmd.ExecuteReader()

'Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable()

'For each field in the table...
For Each myField In schemaTable.Rows
    'For each property of the field...
     For Each myProperty In schemaTable.Columns
        'Display the field name and value.
         Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())

'here check if field exists

     Next
     Console.WriteLine()

     'Pause.
     Console.ReadLine()
Next

'Always close the DataReader and Connection objects.
myReader.Close()
cn.Close()
brian_dallasAuthor Commented:
I'm sure that I'm missing something here, but my data is already in the form of an XMLDataDocument and has a structure similar to this:

<element1>
  <element2>
     <nested-element1/>
     <nested-element2/>
  </element2>
  <element3/>
</element1>
<second-element1>
  <element2>
     <nested-element1/>
  </element2>
  <element3/>
</second-element1>

Note how the <nested-element2/> tag does not appear in the second set of data, since it is optional in the schema. This causes a problem for me when the datatable gets built because when I loop through the rows, all of a sudden I have nothing called <nested-element2> in the second row. You see what I mean? How do I check as I move through the rows to make sure that the column is there?

Thanks.
brian_dallasAuthor Commented:
"<second-element1>" is only for distinction from the first "<element1>" -- in the real schema/xml, they have the same name.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

brian_dallasAuthor Commented:
AND, this is the .NET exception that I am trying to avoid:

"Column 'MISSINGCOLUMN' does not belong to table MYTABLE."

Thanks!
iboutchkineCommented:
I was talking about reading Db schema to check if col exist, not XML schema.
You might try another approach, to execute SQL with the col name and see if it will create an error

sSQL = "select count(YourCol) from Table"

then execute scalar and see if error is generated
brian_dallasAuthor Commented:
I don't think I'm explaining this correctly or clearly enough. Let me try again. I build my own datatable with the columns the following way:

Add any columns outside the dataset using:

DataColumn dc = new DataColumn("FirstColumn")
dt.Columns.Add(dc)

Then, I add a bunch of columns from a dataset using:

for (System.Int32 iCol = 0; iCol < m_dsDataSource.Tables[0].Columns.Count; iCol ++ )
{
   //...do setup of column name and such
   dtDataTable.Columns.Add(dcColumn);
}

Then I add row information (this is where the problem occurs):

for (i = 0; i < m_dsDataSource.Tables[0].Rows.Count; i++)
{
    DataRow drDataRow = dtDataTable.NewRow();
    drDataRow["MyColumn"] = m_dsDataSource.Tables[0].Rows[i]["IAMHERE"];

    // PROBLEM AREA !!!
    drDataRow["MyPossiblyMissingColumn"] = m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"];

    // ...etc...adding additional row information to the table created
}

How do I check to make sure that MAYBEMISSING is there before trying to access it and assign it to the drDataRow["MyPossiblyMissingColumn"]. If it is not there in the row information that is where I will get the exception.

Thanks, and I hope this is more clear...
thomasdoddsCommented:
did you try

if not isNothing(m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"]) then
  ...
end if


not sure of the C# syntax (i'm a vb coder)

if (! IsNothing(m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"])) {
...
}
timmyt851Commented:
How about:

C#:       If (! IsNothing(m_dsDataSource.Tables[1].Columns["MAYBEMISSING"])) Then...
VB:        If Not IsNothing(m_dsDataSource.Tables(1).Columns("MAYBEMISSING")) Then...

?
brian_dallasAuthor Commented:
Nope, neither of the above solutions work -- I still get the exception when trying to access the column...even to compare it to null (or Nothing in the VB.NET world).
timmyt851Commented:
VB: If ds.tables("mytable").columns.contains("mycolumn") then...

Make sure you are referencing the correct table, use tables(0) if all else fails.

TIM

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomasdoddsCommented:
just noticed that your table index is different in these lines of code ... is that a typo or on purpose?

   DataRow drDataRow = dtDataTable.NewRow();
    drDataRow["MyColumn"] = m_dsDataSource.Tables[0].Rows[i]["IAMHERE"]; // index = 0

    // PROBLEM AREA !!!
    drDataRow["MyPossiblyMissingColumn"] = m_dsDataSource.Tables[1].Rows[i]["MAYBEMISSING"]; // index = 1
brian_dallasAuthor Commented:
Yes, sorry...that was a typo in the example code only; I actually have several tables in my dataset, so I just forgot to change the index for one of the tables.

The answer was to use the .columns.contains(...)....I knew it had to be something simple.

Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.