title: C# - How to Connect and Query From Database and Return a JsonResult
body: <p>I had a question regarding displaying a table is .cshtml with controllers and views. I am attempting to return a JsonResult within my ItemController.cs and am wondering how I can reach my database and return the data based on a query.</p>
  
  <pre><code>query = Select * from myFunction(ID)
  using SqlConnection sqlConn = new SqlConnection("connectionString")
         {
             using (SqlCommand cmd = new SqlCommand(query, sqlConn)
             {
                  sqlConn.Open();
                  DataTable dt = new DataTable();
                  dt.Load(cmd.ExecuteReader());
              }
          }
  </code></pre>
  
  <p>Is this the general setup for connecting to my database? I attempted this but my dt variable showed as {}. Also, after this, to return an MVC JsonResult, I use:</p>
  
  <pre><code>System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
          List&lt;Dictionary&lt;string, object&gt;&gt; rows = new List&lt;Dictionary&lt;string, object&gt;&gt;();
          Dictionary&lt;string, object&gt; row;
          foreach (DataRow dr in dt.Rows)
          {
              row = new Dictionary&lt;string, object&gt;();
              foreach (DataColumn col in dt.Columns)
              {
                  row.Add(col.ColumnName, dr[col]);
              }
              rows.Add(row);
          }
          string json = serializer.Serialize(rows);
          return new JsonResult { Data = json, ContentType = "application/json" };
  </code></pre>
  
  <p>Is there a better way to do this/where could I have gone wrong with the connection above?</p>
  
  <p>Thanks!</p>