using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;    // Using Sql classes
using System.Web.Configuration; // Using web.config file

public partial class Lec7_Intro_ASPNet_ReadingDatabase : 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 = System.Text.Encoding.UTF8;

        // To include new lines in string, use verbatim string by adding @ symbol
        Response.Write(@"
            <html xmlns=""http://www.w3.org/1999/xhtml"">
            <head runat=""server"">
            <title>Untitled Page</title>
            </head>
            <body>");

        try
        {
            // Connect to the database using the connection string in the web.config file
            connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

            // Create an SELECT Sql statement
            command = new SqlCommand("SELECT * FROM Student", connection);

            using(connection)
            {
                // Open the connection
                connection.Open();

                // Execute the command, the records are returned in an SqlDataReader object
                reader = command.ExecuteReader();

                // Have an rows been returned?
                if(reader.HasRows)
                {
                    // Read each row returned in the reader object
                    while(reader.Read())
                    {
                        // Display all the fields of the current record
                        Response.Write((string)reader["Name"] + "<br/>");
                        Response.Write(((int)reader["Age"]).ToString() + "<br/>");
                        Response.Write((string)reader["Course"] + "<br/>");
                    }
                }
                else
                {
                    Response.Write("No matching row found");
                }

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

        Response.Write(@"
            </div>
            </form>
            </body>
            </html>
            ");

    }
}