using DataSet in Console Application?

Question

I am trying to figure out how to present the information that I (should've) gotten from an SQL query. This is my code:

        this.cmd = connection.CreateCommand();
        this.cmd.CommandText = "SELECT * FROM servers WHERE id > " + x + " AND id < " + y;
        this.adapter = new MySqlDataAdapter( this.cmd );
        this.adapter.SelectCommand = this.cmd;
        this.dataset = new DataSet();
        this.adapter.Fill( dataset );
        return this.dataset;

How can I then using this.dataset present the information, when I am using a console application? Thank you

The results from the query will be in the first table (and only table) in the DataSet. Simply take the first table and loop throw the rows, printing out the data. — Tim, Nov 12 '15 at 00:54
I don't fully understand how to do this, do you have an example? — user2454991, Nov 12 '15 at 01:19
Look at this post and see if this helps. http://stackoverflow.com/questions/10822304/iterate-through-dataset — Nate, Nov 12 '15 at 01:37
Be very wary of using string substitution like that to put values into an sql query. It's a good way to get your app hacked. — Joel Coehoorn, Nov 12 '15 at 03:33

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

This is a surprisingly involved (yet straightforward) question. The collection classes they used are hard to work with. I came up with a basic implementation. It doesn't attempt to line up row values in the correct column, but the output is a little nicer than Juran's.

The main answer is in the ToPrettyString() extension method. Most of the other extension methods are just there to make life easier due to shortcomings in the framework.

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var ds = new DataSet();
            var customersTable = ds.Tables.Add("Customers");
            customersTable.Columns.AddRange("FirstName", "LastName", "Id", "Address");
            customersTable.Rows.Add("Bob", "Sagget", 1, "123 Mockingbird Lane");
            customersTable.Rows.Add("John", "Doe", 2, "1600 Pennsylvanie Ave");
            customersTable.Rows.Add("Jane", "Doe", 3, "100 Main St");

            Console.WriteLine(ds.ToPrettyString());
            Console.WriteLine("Press any key to exit.");
            Console.ReadKey();
        }
    }

    static class ExtensionMethods
    {    
        public static string ToPrettyString(this DataSet ds)
        {
            var sb = new StringBuilder();
            foreach (var table in ds.Tables.ToList())
            {
                sb.AppendLine("--" + table.TableName + "--");
                sb.AppendLine(String.Join(" | ", table.Columns.ToList()));
                foreach (DataRow row in table.Rows)
                {
                    sb.AppendLine(String.Join(" | ", row.ItemArray));
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        public static void AddRange(this DataColumnCollection collection, params string[] columns)
        {
            foreach (var column in columns)
            {
                collection.Add(column);
            }
        }       

        public static List<DataTable> ToList(this DataTableCollection collection)
        {
            var list = new List<DataTable>();
            foreach (var table in collection)
            {
                list.Add((DataTable)table);
            }
            return list;
        }

        public static List<DataColumn> ToList(this DataColumnCollection collection)
        {
            var list = new List<DataColumn>();
            foreach (var column in collection)
            {
                list.Add((DataColumn)column);
            }
            return list;
        }
    }
}

Output:

--Customers--

FirstName | LastName | Id | Address

Bob | Sagget | 1 | 123 Mockingbird Lane

John | Doe | 2 | 1600 Pennsylvanie Ave

Jane | Doe | 3 | 100 Main St

score 0 · Answer 2 · answered Nov 12 '15 at 01:58

0

foreach (DataTable table in dataSet.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn column in table.Columns)
        {
            object item = row[column];
            // read column and item
            Console.WriteLine("item ", item );
        }
    }
}

answered Nov 12 '15 at 01:58

Juran

177
1
8

That prints all the values, one value per line. I imagine it would be kind of ugly. Could you maybe spruce up your implementation to print all the column headers, then have a single line for each row? – mason Nov 12 '15 at 02:05
@user2454991 didn't specify on how to present it. Just giving him some hint. And right, it would be better to add column headers – Juran Nov 12 '15 at 02:32

using DataSet in Console Application?

2 Answers2