Converting SqlDataReader column values to json string in C#

Question

I'd like to output a string (or possibly other variable type if string isn't ideal) based on the output of a SQLDataReader read. I have been playing around with the "while" loop, while (reader.Read())

I need to end up with something I can serialize with json.net.

Here's my current code - it outputs to debug log just to make sure I was successfully reading from the database.

protected void Page_Load(object sender, EventArgs e)
    {

        string connectionString = "Data Source =.\\SQLEXPRESS; Initial Catalog = TeamProject; Integrated Security = True; MultipleActiveResultSets = True";

        string querystring = "RosterMake";

        using (SqlConnection connection = 
            new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(querystring, connection);
            command.CommandType = CommandType.StoredProcedure;

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

                while (reader.Read())
                {
                    System.Diagnostics.Debug.WriteLine("\t{0}\t{1}\t{2}",
                    reader[0], reader[1], reader[2]);

                    //  What do I put here to make the results something I can 
                    //  serialize with json.net???

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("oops");
            }
        }
    }
}

Thanks in advance!

and.. if you dont use sqldatareader, use SqlDataAdapter.Fill a DataTable, DataTable is easy to convert to xml — Lei Yang
– Lei Yang, Commented Jul 13, 2017 at 13:52
What has this question to do with SqlDataReader? Isn't it just about how to convert a string to a json object? You're showinjg a lot of code but actually you are asking for a json tutorial. — Tim Schmelter
– Tim Schmelter, Commented Jul 13, 2017 at 13:56
Newtonsoft.Json will help you, just need to create a class that represents needed JSON format, and then fill List<YourClass> with reader[i] values — Vladimir
– Vladimir, Commented Jul 13, 2017 at 13:56
@TimSchmelter The issue here is storing it somewhere i can access it with json.net. Unless you know of a way to pass the SQLDataReader straight to json... I just don't know what the best way to store it so I can serialize it later. — Kelton Crouse
– Kelton Crouse, Commented Jul 13, 2017 at 13:59
@KeltonCrouse: a SqlDataReader reads data which comes from a database and gives you an object. It has methods like reader.GetString(0) that gives you already the right type. But you have to process this string yourself, there is no automatism of the datareader. But haveyou tried this:JObject.Parse(reader.GetString(0))? — Tim Schmelter
– Tim Schmelter, Commented Jul 13, 2017 at 14:02

pmbAustin · Accepted Answer · 2017-07-13 14:53:12Z

7

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"
    }
]

edited Jul 13, 2017 at 14:53

pmbAustin

4,0101 gold badge27 silver badges35 bronze badges

answered Jul 13, 2017 at 14:02

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

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

5 Comments

Charles Okwuagwu Over a year ago

how does this work if the shape of the recordsets are different, in the case of multiple-recordsets?

Felipe Oriani Over a year ago

Because it can be deserialized to a List<Dictionary<string, object>>.

Charles Okwuagwu Over a year ago

so you get two arrays?

Felipe Oriani Over a year ago

A Dictionary is a key-pair value data structure. It will be deserializating a single object like { name: 'Charles', lastName: 'Okwuagwu }. string` is the proeprty name and object is the value. the value must be object beacuse it can be a string, number, etc.. Given you have a list on the json, then you define a List<Dictionary<string, object>> for many objects. That's why it works.

Charles Okwuagwu Over a year ago

Thanks, I found an alternative. I use WriteStartArray(); and WriteEndArray(); to delimit each NextResult(); into separate arrays

Jonathan Hall · Accepted Answer · 2019-12-05 09:02:03Z

6

It's simple to convert SqlDataReader to JSON. The thing to have is Newtonsoft.Json; Library Here is Example

            SqlDataReader rdr = cmd.ExecuteReader();
            //sqlDatoToJson(rdr);
            var datatable = new DataTable();
            datatable.Load(rdr);
            string JsonResponse = string.Empty;
            JsonResponse = JsonConvert.SerializeObject(datatable);
            System.Diagnostics.Debug.WriteLine("3");
            System.Diagnostics.Debug.WriteLine(rdr);
            con.Close();
            return new JsonResult() { Data = JsonResponse,JsonRequestBehavior = JsonRequestBehavior.AllowGet };

edited Dec 5, 2019 at 9:02

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

answered Nov 27, 2018 at 6:12

Rahul Jograna

90310 silver badges12 bronze badges

Comments

marc_s · Accepted Answer · 2017-07-13 14:01:56Z

0

Just use the GetString() method on the SqlDataReader. You want something like this:

string myString;

using (SqlDataReader rdr = command.ExecuteReader())
{
    do
    {
        while (rdr.Read())
        {
            myString = rdr.GetString(0);
        }
    } while (rdr.NextResult());
}

Note this just blindly reads strings until there's nothing left. It's up to you to put the logic in correctly.

edited Jul 13, 2017 at 14:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 13, 2017 at 13:52

pmbAustin

4,0101 gold badge27 silver badges35 bronze badges

Comments

Ruben Paz Chuspe · Accepted Answer · 2017-07-13 14:09:32Z

0

you can return a list:

 public List<EnEntity> methodname(string pParam1)
    {
        try
        {
            List<EnEntity> ListEntity = new List<EnEntity>();
            SqlCommand cmd = new SqlCommand("sp_name", conexion);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@vParam", SqlDbType.VarChar).Value = pParam1;

            SqlDataReader drd = cmd.ExecuteReader();

            if (drd.HasRows)//!=null
            {
                int pos_iIndex = drd.GetOrdinal("iColumnaName");
                while (drd.Read())
                {
                    EnEntity oEnEntity = new EnEntity();

                    oEnEntity.iPropertie = drd.IsDBNull(pos_iIndex) ? 0 : drd.GetInt32(pos_iIndex);

                    ListEnEntity.Add(oEnEntity);
                }
                drd.Close();

            }
            return (ListEnEntity);
        }
        catch (Exception ex)
        {                
            throw ex;
        }
    }

edited Jul 13, 2017 at 14:09

answered Jul 13, 2017 at 13:56

Ruben Paz Chuspe

13 bronze badges

2 Comments

Vladimir Over a year ago

It will be better if all variables and methods will be named in English, otherwise they meaning is a mystery for those who do not understand worlds like : ListarReporteSegunPeriodoFecha

Ruben Paz Chuspe Over a year ago

i did, i hope this help you

Collectives™ on Stack Overflow

Converting SqlDataReader column values to json string in C#

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related