title: How can I determine whether a column exists in a SQL Server CE table with C#?
body: <p>The legacy code does it this way:</p>
  
  <pre><code>public bool isValidField(string tableName, string fieldName)
  {
      bool retVal;
      string tblQuery = string.Format("SELECT {0} FROM {1}", fieldName, tableName);
      checkConnection();
      try
      {
          SqlCeCommand cmd = objCon.CreateCommand();
          cmd.CommandText = tblQuery;
          object objvalid = cmd.ExecuteScalar();
          retVal = (null != objvalid);
      }
      catch
      {
          retVal = false; 
      }
      return retVal;
  }
  </code></pre>
  
  <p>...but I find it doesn't always work. After calling that method, and getting <code>false</code>, some code ALTERS the table to add some columns, but I'm getting:</p>
  
  <blockquote>
    <p>A column ID occurred more than once in the specification.</p>
  </blockquote>
  
  <p>I found here on StackOverflow a couple of promising SQL statements:</p>
  
  <pre><code>if exists(select * from sys.columns 
          where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))  
  </code></pre>
  
  <p>and</p>
  
  <pre><code>IF COL_LENGTH('table_name','column_name') IS NULL
  </code></pre>
  
  <p>...but am not sure how to implement this in C# / .NET 1.1</p>
  
  <p>Do I need to use ExecuteScalar and cast the returned value to a bool? Or something else?</p>
  
  <h2>UPDATE</h2>
  
  <p>Changing it to this didn't rectify matters:</p>
  
  <pre><code>public bool isValidField(string tableName, string columnName)
  {
      bool retVal;
      string tblQuery = string.Format(
          "COL_LENGTH({0},{1}) IS NULL",
          tableName,
          columnName);
  
      checkConnection();
      try
      {
          SqlCeCommand cmd = objCon.CreateCommand();
          cmd.CommandText = tblQuery;
          object objvalid = cmd.ExecuteScalar();
          retVal = Convert.ToBoolean(objvalid);
      }
      catch
      {
          retVal = false; 
      }
      return retVal;
  }
  </code></pre>
  
  <h2>UPDATE 2</h2>
  
  <p>Oddly enough, I see even more of those error messages with the new code.</p>
  
  <h2>UPDATE 3</h2>
  
  <p>It made no difference when I altered the code this way:</p>
  
  <pre><code>string tblQuery = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS"
                    + " WHERE TABLE_NAME = @tableName AND COLUMN_NAME"
                    + " = @columnName";
  
  checkConnection();
  try
  {
      SqlCeCommand cmd = objCon.CreateCommand();
      cmd.CommandText = tblQuery;
      SqlCeParameter tblNameParam = new SqlCeParameter(
          "@tableName",
          SqlDbType.NVarChar,
          128);
  
      tblNameParam.Value = tableName;
      cmd.Parameters.Add(tblNameParam);
      SqlCeParameter colNameParam = new SqlCeParameter(
          "@columnName",
          SqlDbType.NVarChar,
          128);
  
      colNameParam.Value = tableName;
      cmd.Parameters.Add(colNameParam);
      int i = (int)cmd.ExecuteScalar();
      retVal = i &gt; 0;
  }
  </code></pre>
  
  <p>...so I don't know which way is preferable; this does seem more straightforward to me...any thoughts, anyone?</p>