title: Select Column values from SqlDataReader command
body: <p>I have a table containing two columns in SQL that I want to extract values from.  The problem though is I do not know how to retrieve column 2's values in C# code.  I am using SQL Server 2008 and VS.  Do I have to use an OUTPUT parameter in the SP?  If so, how would I specify this SP?  I have experimented with OUTPUT parameters, but I am not able to create an OUTPUT parameter without errors because I am also trying to select the COLUMN_NAME.  There are 4 columns returned from this query and I just want to get this number of columns also.  There should be a much simpler way of doing this, no?  This works in SQL, but not in C#.</p>
  
  <p>Here is my SP:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[getColumnNames]
      @TableName varchar(25) 
  AS
      SELECT name 'COLUMN_NAME', (
           SELECT COUNT(ID)
           FROM syscolumns WHERE id = (
               SELECT id
               FROM sysobjects
               WHERE name= @TableName)) 'ROW_COUNT'
      FROM syscolumns
      WHERE id = (SELECT id FROM sysobjects WHERE name= @TableName)
      RETURN
  </code></pre>
  
  <p>And here is my C# code calling this SP:</p>
  
  <pre><code>public static SqlDataReader DisplayTableColumns(string tt)
  {
      SqlDataReader dr = null;
      string TableName = tt;
      string connString = "Data Source=.;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\...";
      string errorMsg;
  
      try
      {
          SqlConnection conn2 = new SqlConnection(connString);
          SqlCommand cmd = conn2.CreateCommand();
          cmd.CommandText = "dbo.getColumnNames";
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Connection = conn2;
          cmd.Parameters.Add(new SqlParameter("@TableName", TableName));
          conn2.Open();
          dr = cmd.ExecuteReader();
      }
      catch (Exception ex)
      {
          errorMsg = ex.Message;
      }
      return dr;
  }
  </code></pre>