c# - C#中如何使用SqlCommand和SqlDataReader返回Json结果

Question

我正在尝试在 SqlCommand 中使用 SQL 查询，我想查看从 SQL Server 数据库返回的完整结果集，然后返回 Json 格式。

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

任何人都可以帮助我完成这项工作吗？我将不胜感激。

凯文

Answer 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();
}

或

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

或(只是有趣的部分):

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