title: create JSON string from SqlDataReader
body: <h2>UPDATE</h2>
  
  <p>I figured it out. <a href="https://stackoverflow.com/questions/5554472/create-json-string-from-sqldatareader/5597472#5597472">Check out my answer below</a>.</p>
  
  <hr>
  
  <p>I'm trying to create a JSON string representing a row from a database table to return in an HTTP response. It seems like <a href="http://json.codeplex.com/" rel="nofollow noreferrer">Json.NET</a> would be a good tool to utilize. However, I'm not sure how to do build the JSON string <strong><em>while</em></strong> I'm reading from the database.</p>
  
  <p>The problem is marked by the obnoxious comments <code>/******** ********/</code></p>
  
  <pre><code>// connect to DB
  theSqlConnection.Open(); // open the connection
  
  SqlDataReader reader = sqlCommand.ExecuteReader();
  if (reader.HasRows) {
  
      while(reader.Read()) {
  
          StringBuilder sb = new StringBuilder();
          StringWriter sw = new StringWriter(sb);
  
          using (JsonWriter jsonWriter = new JsonTextWriter(sw)) {
  
              // read columns from the current row and build this JsonWriter
              jsonWriter.WriteStartObject();
              jsonWriter.WritePropertyName("FirstName");
  
              // I need to read the value from the database
  /******** I can't just say reader[i] to get the ith column. How would I loop here to get all columns? ********/
              jsonWriter.WriteValue(... ? ...);
              jsonWriter.WritePropertyName("LastName");
              jsonWriter.WriteValue(... ? ...);
              jsonWriter.WritePropertyName("Email");
              jsonWriter.WriteValue(... ? ...);
  
              // etc...
              jsonWriter.WriteEndObject();
          }
      }
  }
  </code></pre>
  
  <p>The problem is that I don't know how to read each column from the row from the <code>SqlReader</code> such that I can call <code>WriteValue</code> and give it the correct information and attach it to the correct column name. So if a row looks like this...</p>
  
  <pre><code>| FirstName | LastName | Email |
  </code></pre>
  
  <p>... how would I create a <code>JsonWriter</code> for each such row such that it contains all column names of the row and the corresponding values in each column and then use that <code>JsonWriter</code> to build a JSON string that is ready for returning through an HTTP Response?</p>
  
  <p>Let me know if I need to clarify anything.</p>