using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.SqlClient;
using System.Web.Configuration;

using System.Text;  // Encoding

namespace BasicAjaxCSharp
{
    public partial class SelectAll : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection connection;
            SqlCommand command;
            SqlDataReader reader;

            // Set the content type
            Response.ContentType = "text/plain";
            Response.ContentEncoding = Encoding.UTF8;

            try
            {
                connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["StudentConnectionString"].ConnectionString);
                command = new SqlCommand("SELECT * FROM Student", connection);

                using(connection)
                {
                    connection.Open();

                    reader = command.ExecuteReader();

                    if(reader.HasRows)
                    {
                        // We could return this as JSON
                        // For example, two records...
                        // [
                        // {"Name":"Bill", "Age":22, "Course":"CIS"},
                        // {"Name":"Tom", "Age":22, "Course":"WD"}
                        // ]
                        //
                        //StringBuilder sb = new StringBuilder();
                        //sb.Append("[");
                        //while (reader.Read())
                        //{
                        //    sb.Append("{");
                        //    sb.AppendFormat("\"Name\":\"{0}\",", reader["Name"]);           // "Name":"Bill",
                        //    sb.AppendFormat("\"Age\":{0},", reader["Age"]);                 // "Age":22
                        //    sb.AppendFormat("\"Course\":\"{0}\"", reader["Course"]);        // "Course":"CIS"
                        //    sb.Append("},");
                        //}
                        //sb.Remove(sb.Length - 1, 1);    // Remove last comma we added
                        //sb.Append("]");
                        //Response.Write(sb.ToString());

                        while (reader.Read())
                        {
                            Response.Write(reader["Name"] + "<br/>");
                            Response.Write((int)reader["Age"] + "<br/>");
                            Response.Write(reader["Course"] + "<br/>");
                        }
                    }
                    else
                    {
                        Response.Write("No matching row found");
                    }

                    connection.Close();
                }
            }
            catch
            {
                Response.Write("An error occurred");
            }

        }
    }
}