How to use SqlCommand and SqlDataReader to return a Json result in C#

Question

I'm trying to use a SQL query in SqlCommand and I'd like to see the complete result set that is returned from SQL Server database, and return Json format after that.

So here is the code in controller:

public ActionResult GetAllSummary()
{
    string connectionString ="Data Source=...;Initial Catalog=...;Integrated Security=True";  
    string query = "SELECT v.date, v.name, v.numbers FROM view as v GROUP BY v.date,v.mane,v.numbers ORDER BY v.date,v.mane,v.numbers";

    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, conn);

        try {
            conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            // In this part below, I want the SqlDataReader  to 
            // read all of the records from database returned, 
            // and I want the result to be returned as Array or 
            // Json type, but I don't know how to write this part.

            while(reader.Read())
            {
                ArrayList result = new ArrayList();

                foreach(int i in reader)
                     // this line below was the code I wrote before. 
                     // But since my query returns multiple 
                     // types (datetime, string, decimal, etc..), 
                     // I don't know what C# command I can use to return
                     // the results in foreach loop. Or say I even don't 
                     // need a for/foreach loop.
                     result.Add(reader.GetValue(i));

                return Json(result, JsonRequestBehavior.AllowGet);
            }

            reader.Close();
        }
        catch(Exception ex)
        {
            var error = ex.Message;
            return View(error);
        }
    }

    return View();
}

Anyone can help me to make this work? I will be greatly appreciated.

Kevin

Answer 1

You can use this extension:

public static string ExecuteToJson(this SqlCommand cmd)
{
    if (cmd.Connection.State == ConnectionState.Closed)
    {
        cmd.Connection.Open();
    }

    using (DataTable dt = new DataTable())
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);

            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }

            return JsonConvert.SerializeObject(rows);
        }
    }
}

and the usage:

string connectionString = "** connstr **";
string query = "SELECT * FROM `table`";

try
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(query, conn))
        {
            string json = command.ExecuteToJson(); 
        }
    }
}
catch (Exception)
{
}

Answer 2

public class data {
  public DateTime date {get;set;}
  public string name {get;set;}
  public int numbers {get;set;}
}
public ActionResult GetAllSummary()
{
    string connectionString ="Data Source=...;Initial Catalog=...;Integrated Security=True";  
    string query = "SELECT DISTINCT v.date, v.name, v.numbers FROM view as v ORDER BY v.date,v.name,v.numbers";

    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, conn);

        try {
            conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            // In this part below, I want the SqlDataReader  to 
            // read all of the records from database returned, 
            // and I want the result to be returned as Array or 
            // Json type, but I don't know how to write this part.

            while(reader.Read())
            {
                List<data> result = new List<data>();
                var d=new data();
                d.date=reader[0]; // Probably needs fixing
                d.name=reader[1]; // Probably needs fixing
                d.numbers=reader[2]; // Probably needs fixing
                result.Add(data);
            }

            reader.Close();
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        catch(Exception ex)
        {
            var error = ex.Message;
            return View(error);
        }
    }

    return View();
}

or

public class data {
  public DateTime date {get;set;}
  public string name {get;set;}
  public int numbers {get;set;}
}
public ActionResult GetAllSummary()
{
    string connectionString ="Data Source=...;Initial Catalog=...;Integrated Security=True";  
    string query = "SELECT DISTINCT v.date, v.name, v.numbers FROM view as v ORDER BY v.date,v.name,v.numbers";

    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, conn);

        try {
            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            var dt=new DataTable();
            dt.Load(myDataReader);
            List<DataRow> result=dt.AsEnumerable().ToList();
            reader.Close();
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        catch(Exception ex)
        {
            var error = ex.Message;
            return View(error);
        }
    }

    return View();
}

or (just the interesting part):

var dt=new DataTable();
dt.Load(myDataReader);
object[] result = new object[dt.Rows.Count + 1];

for (int i = 0; i <= dt.Rows.Count - 1; i++) {
    result[i] = dt.Rows[i].ItemArray;
}