C# return multiple columns and rows from SqlDataReader

Question

I have a scenario to query DB for two columns and return all the rows to a different method for further processing. I am not sure if my approach below is the best. Can you please share any better techniques to accomplish this. The below code works well for a single row returned from SQL.

public (Int32 SiteID, string SiteName) QueryDB(string ConnStr)
{
    Int32 SiteID = 0;
    string SiteName = "";
    using (SqlConnection con = new SqlConnection(ConnStr))
        {
            SqlCommand cmd = new SqlCommand("spGetSiteDetails", con);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                SiteID = Convert.ToInt32(reader[0]);
                SiteName = reader[1].ToString();
            }
        }
    return (SiteID, SiteName);
}

In order to address multiple rows result, I am using string concatenated in a List. I believe there must be a better way of doing this because I have an overhead of having to split the string in the list to use the values..

public List<string> QueryDB(string ConnStr)
{
    List<string> SiteDetails = new List<string>();
    using (SqlConnection con = new SqlConnection(ConnStr))
    {
        SqlCommand cmd = new SqlCommand("spGetSiteDetails", con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            SiteDetails.Add(reader[0] + "|" + reader[1]);
        }
    }
    return SiteDetails;
}

All ADO.NET tutorials show better ways. For starters, you could load all data into a DataTable. Or you could create strongly typed objects and set their properties to the column values. Or you could create a List<object> and add column values to it instead of appending strings. Use an ORM to automagically convert rows to objects, or a microORM like Dapper to do the same with a single line. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 12, 2020 at 11:11
if reader[0] or reader[1] is unique you can store as dictionary — Vivek Nuna
– Vivek Nuna, Commented Jun 12, 2020 at 11:11
Or you can use reader.GetValues() to fill an array with all values in a single operation — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 12, 2020 at 11:12
Is there any reason for not using class with properties SiteId and SiteName instead of two separate variables? — rabink
– rabink, Commented Jun 12, 2020 at 11:12

Marc Gravell · Accepted Answer · 2020-06-12 11:14:16Z

You can return a List<(int SiteID, string SiteName)>:

var list = new List<(int, string)>();
while (reader.Read())
{
    var siteID = Convert.ToInt32(reader[0]);
    var siteName = reader[1].ToString();
    list.Add((siteID, siteName));
}
return list;

However, personally I'd recommend not returning value-tuples in public APIs, and returning your own custom type instead - i.e. a List<SiteInfo> for a class SiteInfo or readonly struct SiteInfo that has an int SiteID and string SiteName as properties; i.e.

public sealed class SiteInfo
{
    public int SiteID {get;set;}
    public string SiteID {get;set;}
}

with

var list = new List<SiteInfo>();
while (reader.Read())
{
    var siteID = Convert.ToInt32(reader[0]);
    var siteName = reader[1].ToString();
    list.Add(new SiteInfo { SiteID = siteID, SiteName = siteName });
}
return list;

Either way: Dapper could really help you with this!

public List<(int SiteID, string SiteName)> QueryDB(string ConnStr)
{
    using (SqlConnection con = new SqlConnection(ConnStr))
    {
        return con.Query<(int,string)>("spGetSiteDetails",
              commandType: CommandType.StoredProcedure).AsList();
}

or

public List<SiteInfo> QueryDB(string ConnStr)
{
    using (SqlConnection con = new SqlConnection(ConnStr))
    {
        return con.Query<SiteInfo>("spGetSiteDetails",
              commandType: CommandType.StoredProcedure).AsList();
}

Magnus · Accepted Answer · 2020-06-12 11:11:51Z

1

To return a collection you can write:

public IEnumerable<(Int32 SiteID, string SiteName)> QueryDB(string ConnStr)
{
    using (SqlConnection con = new SqlConnection(ConnStr))
    {
            SqlCommand cmd = new SqlCommand("spGetSiteDetails", con);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                yield return (Convert.ToInt32(reader[0]), reader[1].ToString());
            }
    }
}

answered Jun 12, 2020 at 11:11

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Comments

Praveen Kumar · Accepted Answer · 2020-06-12 11:33:28Z

0

I found the DataTable which solves the problem straight away

dataTable.Load(reader);

answered Jun 12, 2020 at 11:33

Praveen Kumar

1634 silver badges15 bronze badges

1 Comment

Marc Gravell Over a year ago

Noooooooo..... DataTable is almost never the right solution to your problem, unless you're writing an ad-hoc query DSL or reporting UI. For app logic - things where the shape is predictable - literally anything else is preferable. DataTable was largely a bridge for people migrating from COM ADO RecordSet - from VB6!!!

Collectives™ on Stack Overflow

C# return multiple columns and rows from SqlDataReader

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related