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

what is the error you get, try putting a breakpoint inside while loop, where you able to reach to that point. — jero
– jero, Commented Dec 10, 2015 at 21:12
Your using return Json(... inside the foreach loop so it immediately exits the method and returns a result as soon as the first value of first row is read. — user3559349
– user3559349, Commented Dec 10, 2015 at 21:15
I am pretty sure that this does't compile at all. What is supposed to mean foreach(int i in reader) ? — Steve
– Steve, Commented Dec 10, 2015 at 21:15
@StephenMuecke Actually it isn't inside the foreach. He's just single the single command version of it rather than the block. It is, however, inside the while loop. — Robert McKee
– Robert McKee, Commented Dec 10, 2015 at 21:29
Step 1. Move the code for reading to a separate service (it should not be inside a controller). Step 2. Create a model with properties Date, Name and Numbers and inside the while loop, initialize an instance of your model, set its properties and add it to a collection and finally return a collection of your model. Step 3. Call the service in your controller to get List<yourModel> and then return it to the view. — user3559349
– user3559349, Commented Dec 10, 2015 at 21:38

Robert McKee · Accepted Answer · 2015-12-11 20:50:53Z

1

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;
}

edited Dec 11, 2015 at 20:50

answered Dec 10, 2015 at 21:36

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

QIWEN HU Over a year ago

Thank you for your help! In the third method, how can I init arr[] or what type of arr[] would be?

Robert McKee Over a year ago

@QIWENHU Sorry, the reference to arr[] was supposed to be be a reference to result[]. I've updated the answer to reflect the correction.

QIWEN HU Over a year ago

Alright, actually all methods worked and the third one applied to my situation. Thank you again for your help

SZL · Accepted Answer · 2019-11-29 20:31:06Z

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)
{
}

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related