Returning data from ExecuteReader

Question

I've a problem of fetching data from database, Because I'm new in c# Language. I thought that the ExecuteReader should retrieve all rows. by the way I can't access the data like full of object. and the reader.GetString(0) it's return to me only the index of array object.

Is any properties from c# to access the row from database?

I'm looking for the answer to solve this problem. I'd appropriated for the helping!

Here is the code I have so far:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Npgsql;

namespace Sample.Controllers
{
    public class HomeController : Controller
    {
        public string Index()
        {
            var connString = "Host=localhost;Username=postgres;Password=123456;Database=dotnet_core_db";
            using (var conn = new NpgsqlConnection(connString))
            {
                conn.Open();
                // Retrieve all rows
                using (var cmd = new NpgsqlCommand("SELECT * FROM Persons", conn))
                using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                {   
                    Console.WriteLine(reader.GetString(0));
                    Console.WriteLine(reader.GetString(1));
                    Console.WriteLine(reader.GetString(2));
                    Console.WriteLine(reader.GetString(3));
                    Console.WriteLine(reader.GetString(4));
                    ViewData["connString"] = connString;
                }


            }

            return connString;
        }

        public IActionResult Error()
        {
            ViewData["RequestId"] = Activity.Current?.Id ?? HttpContext.TraceIdentifier;
            return View();
        }
    }
}

Expectation Result

{
        {
            id: 92,
            name: "foo",
            last_name: "bar",
            address: "adress Of foo",
            city: "city of foo"
        },
        {
            id: 872,
            name: "foo1",
            last_name: "bar 2",
            address: "address of foo1",
            city: "city of foo1"
        }
    }

Btw, connection-strings should be stored in configuration files — Tim Schmelter, Nov 23 '17 at 08:47
@TimSchmelter This is just a sample, but the main thing that I focused on the data retrieve. — Doungdara, Nov 23 '17 at 08:49
@RanjitSingh I've got reader.GetString(0) = 92, reader.GetString(1) = 'foo', reader.GetString(2) = 'bar' ... but my Expectation is {{id: 92, name: "foo", last_name: "bar", address: "adress Of foo", city: "city of foo"}} — Doungdara, Nov 23 '17 at 08:51
You can try `reader["ColumnName"]` to retrieve the data for a specific column. You then have to call tostring() or whatever cast you want for this. — Stefan Kert, Nov 23 '17 at 08:52
After reading your comment about the result: I think your question is confusing. Your issue isn´t the reading from the Database but the resulting data structure, right? If so, you either have to create the json on your own, or serialize it with JSON.NET — Stefan Kert, Nov 23 '17 at 08:54
@Doungdara: why you dont mention it in your question? Your question is very unclear so far. I guess this is a duplicate, isn't it? https://stackoverflow.com/questions/5083709/convert-from-sqldatareader-to-json — Tim Schmelter, Nov 23 '17 at 08:56
@TimSchmelter Sorry! I have added my Expectation Result with data sample data on the top. — Doungdara, Nov 23 '17 at 08:57
If you are new to C# then I would suggest considering using Dapper rather than raw ADO.NET - https://thespons.net/2015/04/28/dapper-and-npgsql-part-two.html — mjwills, Nov 23 '17 at 08:57
@mjwills: dapper doesn't make it easier for new C# developers and also doesnt help to convert it to json — Tim Schmelter, Nov 23 '17 at 08:59

lamandy · Answer 1 · 2017-11-23T09:16:48.670

The data retrieval is okay. What you need is a json writing package. Consider library like json.net to ease the job.

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter writer = new JsonTextWriter(sw))
{
    writer.Formatting = Formatting.Indented;
    while (dataReader.Read())
    {
        writer.WriteStartObject();
        for (int i = 0; i < dataReader.fieldCount; ++i)
        {
            writer.WritePropertyName(dataReader.GetName(i));
            writer.WriteValue(dataReader.GetValue(i).ToString());
        }
        writer.WriteEndObject();
    }
}

fileWriter.Write(sb.ToString());

score 0 · Answer 2 · answered Nov 23 '17 at 09:02

you can do it like below one

 using (var cmd = new NpgsqlCommand("SELECT * FROM Persons", conn))
 {
 using (var reader = cmd.ExecuteReader())
 {
     var columns = new List<string>();
     for(int i=0;i<reader.FieldCount;i++)
        columns.Add(reader.GetName(i));

    JsonArrayCollection jsonArray = new JsonArrayCollection();
     while (reader.Read())
    {   
        JsonObjectCollection jsonObject = new JsonObjectCollection();
        for(string columnName in columns)
            jsonObject.Add(new JsonStringValue(columnName, reader[columnName]));
        jsonArray.Add(jsonObject);
    }
 }
}

Written in notepad, so please excuse any compilation error.

Note: you can use any json library, only the syntax will change.

Returning data from ExecuteReader

2 Answers2