using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    public class PagesJSON
    {
        public string template;
        public List<PageRowDTO> pages;

        public PagesJSON()
        {
            template = "";
            pages = new List<PageRowDTO>();
        }
    }
    public class PageRowDTO
    {
        public int id;
        public string path;
        public string title;
        public int views;

        public PageRowDTO()
        {
            id = 0;
            path = "";
            title = "";
            views = 0;
        }
    }
    class JSONiser2
    {
        public void all2()
        {

            string query = "SELECT * FROM pages;";
            string config = "server=localhost;username=root;password=;database=json";

            MySqlConnection connection = new MySqlConnection(config);
            MySqlCommand command = new MySqlCommand(query, connection);
            connection.Open();
            MySqlDataReader Reader = command.ExecuteReader();

            PagesJSON pj = new PagesJSON();
            pj.template = "template.html";

            while (Reader.Read())
            {
                PageRowDTO pr = new PageRowDTO();
                // loop on columns
                pr.id = Int32.Parse(Reader[0].ToString());
                pr.path = Reader[1].ToString();
                pr.title = Reader[2].ToString();
                pr.views = Int32.Parse(Reader[3].ToString());

                pj.pages.Add(pr);

            }

            Console.WriteLine(JsonConvert.SerializeObject(pj));

            connection.Close();
        }
    }
}

/**
DROP TABLE IF EXISTS pages;
CREATE TABLE `pages` (
  `page_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page_path` VARCHAR(255) NOT NULL DEFAULT '',
  `page_title` VARCHAR(255) NOT NULL DEFAULT '',
  `page_views` INT(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`page_id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO pages (page_id, page_path, page_title, page_views) VALUES (NULL, '/', 'Root', 0);
INSERT INTO pages (page_id, page_path, page_title, page_views) VALUES (NULL, '/index', 'Index', 0);
INSERT INTO pages (page_id, page_path, page_title, page_views) VALUES (NULL, '/home', 'Home', 0);
INSERT INTO pages (page_id, page_path, page_title, page_views) VALUES (NULL, '/default', 'Default', 0);

SELECT * FROM pages;
SHOW CREATE TABLE pages;


{
    "template": "template.html",
    "pages": [
        {
            "path": "name",
            "title": "home"
        },
        {
            "path": "name",
            "title": "home"
        },
        {
            "path": "name",
            "title": "home"
        }
    ]
}

<?php
header("Content-Type: text/plain");

$data = array(
	'template' => 'template.html',
	'pages' => array(
		array(
			'path' => 'name',
			'title' => 'home',
		),
		array(
			'path' => 'name',
			'title' => 'home',
		),
		array(
			'path' => 'name',
			'title' => 'home',
		),
	),
);

echo json_encode($data, JSON_PRETTY_PRINT);

*/