Convert from SQLdatareader to JSON

Converting SqlDataReader column values to json string in C#

You could use a List<Dictionary<string, object>> to hold the values and serialize it. The Newtonsoft.Json will serialize it as a simple json. See the code as an example and the comments:

// define the list
var values = new List<Dictionary<string, object>>();
try
{
    connection.Open();
    // Use the using block to make sure you are disposing the dataReader object.
    using (SqlDataReader reader = command.ExecuteReader())
    {
        do
        {               
            while (reader.Read())
            {
                // define the dictionary
                var fieldValues = new Dictionary<string, object>();

                // fill up each column and values on the dictionary                 
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    fieldValues.Add(reader.GetName(i), reader[i]);
                }

                // add the dictionary on the values list
                values.Add(fieldValues);

            }
        } while (reader.NextResult());   // if you have multiple result sets on the Stored Procedure, use this. Otherwise you could remove the do/while loop and use just the while.
    }
}
catch (Exception ex)
{
    System.Diagnostics.Debug.WriteLine("oops");
}

Now you can serialize the values using JsonConvert.Serialize(values) and you will get it as a json like this:

[
    {
        "Name": "John", Age: 30, Sex: "M"
    },
    {
        "Name": "Maria", Age: 28, Sex: "F"
    }
]

Convert SQLDataReader results to JSON, with nested JSON objects

You could use something like this to convert the data reader to a Dictionary<object, Dictionary<string, object>> and then use Json.NET to convert that to JSON:

var items = new Dictionary<object, Dictionary<string, object>>();
while (sqlDataReader.Read())
{
    var item = new Dictionary<string, object>(sqlDataReader.FieldCount - 1);
    for (var i = 1; i < sqlDataReader.FieldCount; i++)
    {
        item[sqlDataReader.GetName(i)] = sqlDataReader.GetValue(i);
    }
    items[sqlDataReader.GetValue(0)] = item;
}
var json = Newtonsoft.Json.JsonConvert.SerializeObject(items, Newtonsoft.Json.Formatting.Indented);

Update: JSON "names" are always strings, so used object and GetValue for the keys.

Retrieving data from a database through SqlDataReader and converting it to JSON

You can't directly serialize a SqlDataReader and expect the output to contain the data from the SQL query. That's not the way that SqlDataReader works. SqlDataReader is a means to retrieve data from the SQL result. You can use it to populate some other object (such as a Dictionary<string, object> or a strongly-typed class you define) which you can then hand to the serializer to produce JSON.

Try the code below instead. (Also note the use of using statements and parameterized SQL in keeping with good coding practices, as mentioned by @Jon Skeet.)

public static string GetDetails(int Id)
{
    string con = "server=FACULTY01\\SQLSERVER2012ENT; database=SampleDb; uid=sa; pwd=sa9";
    using (SqlConnection scon = new SqlConnection(con))
    {
        string qry = "Select * from Information where ID = @id";
        SqlCommand cmd = new SqlCommand(qry, scon);
        cmd.Parameters.AddWithValue("@id", Id);
        scon.Open();

        var details = new Dictionary<string, object>();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.HasRows && rdr.Read())
            {
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    details.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i));
                }
            }
        }

        JavaScriptSerializer jss = new JavaScriptSerializer();
        string jsonDoc = jss.Serialize(details);
        scon.Close();
        return jsonDoc;
    }
}

Note that the above code is expecting to get a single row back from the reader. If you are expecting more than one row then you will need to use another loop and put the resulting data into a List<Dictionary<string, object>> instead. Here is the part you would need to change:

        ...

        var details = new List<Dictionary<string, object>>();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    var dict = new Dictionary<string, object>();

                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        dict.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i));
                    }

                    details.Add(dict);
                }
            }
        }

        ...

Most efficient way and fastest way to read JSON string from SqlDataReader

Your code reallocates the string variable in memory at each loop. This is detrimental to the performances of your code. Instead the class StringBuilder has an internal buffer that allows a lot less memory reallocations and you can also control the size of this buffer to avoid the reallocation to happen at all if you know the total length of your data.

So

// Set an initial capacity of 1MB
StringBuilder str = new StringBuidler(1024*1024);
while (reader.Read())
{
    str.Append(reader[0].ToString());
}

....
return str.ToString();

More about the C# string immutable concept here

Creating nested JSON with data from SQLDataReader

MySqlDataReader implements the IDataReader interface, so we can use a LINQ lambda expression to iterate through its rows and transform them into an appropriate .Net data model. Then afterwards the data model can be serialized to JSON using json.net.

First, grab the following extension method from this answer by Joel Coehoorn:

public static class DataReaderExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/1202973
    // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
    // By https://stackoverflow.com/users/3043/joel-coehoorn
    public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
    {
        while (reader.Read())
        {
            yield return select(reader);
        }
    }
}

And then you can generate your required JSON as follows:

using (IDataReader reader = cmd.ExecuteReader())
{
    var query = reader
        .SelectRows(r => 
                    new
                    {
                        id = r.GetInt64(r.GetOrdinal("pid")),
                        name = r["contributor"].ToString(),
                        project = new {id = r.GetInt64(r.GetOrdinal("projectID")), name = r["projectName"].ToString() },
                    }
                   )
        .GroupBy(r => new { r.id, r.name })
        .Select(g => new { g.Key.id, g.Key.name, projects = g.Select(i => i.project) });

    var json = JsonConvert.SerializeObject(new { contributors = query }, Formatting.Indented);

    Console.WriteLine(json);
}

Notes:

• I am converting the data reader's rows to a data model comprised of anonymous type objects, but you could use explicitly typed objects for your final results, if you prefer.

• GroupBy is used to group all contributions by contributor id and name.

• Select is used to project elements of an enumerable into a new form.

• This approach completely skips the intermediate steps of serializing the raw query results to JSON, then parsing and restructuring that initial JSON.

• The query itself is lazy so be sure to evaluate it before disposing of the data reader. In the code above Json.NET actually performs the evaluation during serialization, inside the call to JsonConvert.SerializeObject.

• I wasn't sure whether the id columns were 32-bit or 64-bit integers, so I assumed the larger, for safety.

• The code assumes none of the record values are null.

Demo fiddle here that mocks up the above using a DataTable and DataTableReader.

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

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

Dynamically create JSON object for datareader results, regardless of what they look like?

You can use the dynamic type of c#

public class payload
{
    public string id;
    public string type;
    public DateTime timestmap;
    public dynamic data;
}

payload result = new payload();
var resultList = new List<Dictionary<string, dynamic>>();
result.id = "someid";

//connection stuff

while (reader.Read())
{

    var t = new Dictionary<string, dynamic>();
    for (var i = 0; i<reader.FieldCount; i++)
    {
        t[reader.GetName(i)] = reader[i];
    }
    resultList.Add(t);
}

result.data = resultList;
result.timestmap = DateTime.Now;
result.type = "complex";
string output = JsonConvert.SerializeObject(result);

The dynamic type is handled by JsonConvert automatically.

You can also make the data field of the payload to a dynamic to handle single field results like in your first JSON example.

---

Related Topics