title: What is the SQL to get the first val in a column in a table?
body: <p>I want to refactor this code, because it seems wasteful and wacky and weird:</p>
  
  <pre><code>public string getVersion()
  {
      try
      {
          string dynSQL = "SELECT * FROM invHeader";
          DataSet workSites = dbconn.getDataSet(dynSQL);
  
          //Go thru dataset and display the working files
          //Only need one, although we'll be duplicating the version
          //per each site as a check value during upgrades
          //return workSites.Tables[0].Rows
          foreach (DataRow row in workSites.Tables[0].Rows)
          {
              sVersion = row["ID"].ToString();
              break;
          }
      }
      catch (Exception ex)
      {
          Duckbill.ExceptionHandler(ex, "InvHeader.getVersion");
      }
  
      return sVersion;
  } // getVersion
  </code></pre>
  
  <p>I was thinking I could change it to something like this:</p>
  
  <pre><code>public string getVersion()
  {
      try
      {
          string dynSQL = "SELECT FIRST ID FROM invHeader"; // I also tried "SELECT 1 ID FROM invHeader"
          DataSet workSites = dbconn.getDataSet(dynSQL);
          return workSites.Tables[0].Rows[0]["ID"].ToString();
      }
      catch (Exception ex)
      {
          Platypus.ExceptionHandler(ex, "InvHeader.getVersion");
      }
  } // getVersion
  </code></pre>
  
  <p>...but neither query returned what I want (the value of ID in the first row). So what is the SQL to do that.</p>
  
  <p>BTW, I know this <em>should</em> be some sort of scalar call, but so many funky Rube Goldbergesque things happen in these homegrown, self-rolled methods that rely on each other, I'm afraid to touch <em>that</em>; this little cleanup should be doable without quaking in my booties, though.</p>
  
  <h2>UPDATE</h2>
  
  <p>I guess I jumped the gun awarding the answer - "SELECT TOP 1 ID FROM invHeader" in SQL Server CE Query Analyzer results in:</p>
  
  <pre><code>FAILED: SELECT TOP 1 ID FROM invHeader 
  Error: x800...._E_ERRORSINCOMMAND
  Native Error: (25501)
  Description: There was an error parsing the query. [Token line number, Token line offset, Token in error,,]
  Interface defining error: IID_ICommand
  Paaram. 0:1
  Param. 1: 8
  Param. 2:0
  Param. 3: TOP
  Param. 4:
  Param. 5:
  </code></pre>
  
  <p>This seems cryptic as all get-out, but one thing I do grok is that SQL Server CE Query Analyzer is "not amused."</p>
  
  <h2>UPDATE 2</h2>
  
  <p>I found an even more egregious code smell of this ilk; not only does the name of the method lead you to believe it returns a value (well, it does, it's true); but while it's doing that, it gets ALL the records from a potentially large table in order to simply grab the first one (again).</p>
  
  <p>Is it really possible the cat who wrote this didn't get the memo warning against the profligate usage of "SELECT *"?</p>
  
  <p>Can I submit an anonymous entry into the code obfuscation contest?</p>
  
  <pre><code>public string getINVSite()
  {
      string  siteStr   = "";
      string  dSQL      = "";
      DataSet workSites;
  
      dSQL      = "SELECT * FROM inventory";
      dbconn    = DBConnection.GetInstance();
      workSites = dbconn.getDataSet( dSQL );
  
  #if true
  //            DataRow row = workSites.Tables[0].Rows[0];
  
      siteStr = "1";
      if (workSites.Tables.Count &gt; 0)
      {
          if(workSites.Tables[0].Rows.Count&gt;0)
              siteStr = workSites.Tables[0].Rows[0]["ID"].ToString();
      }
      return( siteStr );
  #else
      //Go thru dataset and display the working files
      foreach( DataRow row in workSites.Tables[0].Rows )
      {
          return( row["ID"].ToString() );
      }
  
      return( "" );
  #endif
  } // getINVSite
  </code></pre>
  
  <p>Now you know the burden I bear; if moved with pity, donate to your charity of choice.</p>
  
  <h2>UPDATE 3</h2>
  
  <p>Sorry, ctacke; thanks for your help, but this is just too much of a rabbit hole; the original code, although Mad Hatter-worthy, works; I've got stinkier fish to fry, so I'll leave it, at least for now. I tried this:</p>
  
  <pre><code>public string getVersion()
  {
      string conStr = "Data Source = " + dbconn.filename;
      MessageBox.Show(string.Format("conStr in InvHeader.getVersion() is {0}", conStr));//TODO: Remove after testing
      try
      {
          using (SqlCeConnection connection = new SqlCeConnection(conStr))
          {
              connection.Open();
              using (SqlCeCommand command = new SqlCeCommand("SELECT ID FROM invHeader", connection))
              using (SqlCeDataReader reader = command.ExecuteReader())
              {
                  if (reader.Read())
                  {
                      string s = reader.GetString(0);
                      MessageBox.Show(string.Format("version InvHeader.getVersion() is {0}", s));//TODO: Remove after testing
                      return s;// reader.GetString(0);
                  }
                  else
                  {
                      // no result
                      return null;
                  }
              }
          }
      }
      catch (Exception ex)
      {
          Pterodactyl.ExceptionHandler(ex, "InvHeader.getVersion");
          return string.Empty;
      }
  }
  </code></pre>
  
  <p>...and I still get an exception; after seeing the connection string in the first MessageBox.Show(), I don't see the second one.</p>