using System;
using System.Data;
using System.Configuration;
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.Collections.Generic;
using System.Data.SqlClient;        // Using SqlConnection, etc
using System.Web.Configuration;     // Using WebConfigurationManager

/// <summary>
/// Summary description for ConnectedData
/// </summary>
public class ConnectedData
{

	public ConnectedData()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public List<Student> SelectStudents()
    {
        List<Student> studentList = new List<Student>();
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

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

        using (connection)
        {
            connection.Open();

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                int id;
                String name;
                int age;
                string course;
                Student student;

                id = (int)reader["Id"];
                name = (string)reader["Name"];
                age = (int)reader["Age"];
                course = (string)reader["Course"];

                student = new Student(id, name, age, course);
                studentList.Add(student);
            }
        }

        return studentList;
    }

    public void InsertStudent(string name, int age, string course)
    {
        SqlConnection connection;
        SqlCommand command;

        connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

        command = new SqlCommand("INSERT Student(Name, Age, Course) VALUES(@Name, @Age, @Course)", connection);
        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Age", age);
        command.Parameters.AddWithValue("@Course", course);

        using (connection)
        {
            connection.Open();

            command.ExecuteNonQuery();
        }
    }

    public void DeleteStudent(int key)
    {
        SqlConnection connection;
        SqlCommand command;

        connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

        command = new SqlCommand("DELETE FROM Student WHERE Id=@Id", connection);
        command.Parameters.AddWithValue("@Id", key);

        using (connection)
        {
            connection.Open();

            command.ExecuteNonQuery();
        }
    }

    public void UpdateStudent(int key, string name, int age, string course)
    {
        SqlConnection connection;
        SqlCommand command;

        connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

        command = new SqlCommand("UPDATE Student SET Name=@Name, Age=@Age, Course=@Course WHERE Id=@Id", connection);
        command.Parameters.AddWithValue("@Id", key);
        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Age", age);
        command.Parameters.AddWithValue("@Course", course);

        using (connection)
        {
            connection.Open();

            command.ExecuteNonQuery();
        }
    }

    // Here's how to execute an SQL statement that returns a scalar value.
    // Get the maximum age of a student
    public int GetMax()
    {
        SqlConnection connection;
        SqlCommand command;
        Object obj;
        int result = 0;

        connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);

        command = new SqlCommand("SELECT MAX(Age) FROM Student", connection);

        using(connection)
        {
            connection.Open();

            obj = command.ExecuteScalar();
            if(obj != null)
            {
                result = (int)obj;
            }
        }

        return result;
    }

}