using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Data.Linq.Mapping;
using System.Text;
using System.Threading.Tasks;

namespace Final_ChangTimothy
{
    //[Table(Name = "Movie")]
    //public class Movie
    //{
    //    private int _MovieId;
    //    [Column(IsPrimaryKey = true, Storage = "_MovieId")]
    //    public int MovieId
    //    {
    //        get
    //        {
    //            return this._MovieId;
    //        }
    //        set
    //        {
    //            this._MovieId = value;
    //        }

    //    }

    //    private string _Title;
    //    [Column(Storage = "_Title")]
    //    public string Title
    //    {
    //        get
    //        {
    //            return this._Title;
    //        }
    //        set
    //        {
    //            this._Title = value;
    //        }
    //    }
    //}

    [Table(Name = "Movie")]
    class Movie
    {

        [Column()]
        public int MovieId { get; set; }

        [Column()]
        public string Title { get; set; }

        [Column()]
        public int Year { get; set; }

        [Column()]
        public int Time { get; set; }

        [Column()]
        public string Language { get; set; }

        [Column()]
        public int Rating { get; set; }

        [Column()]
        public decimal Price { get; set; }


    }
    class Program
    {
        //private static SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;" +
        //                                                         "AttachDbFilename=C:\\Users\\timothychang\\source\\repos\\CSharp2\\Assignments\\Week9_10\\Final_ChangTimothy\\Final_ChangTimothy\\CineworldEntertainment_ChangTimothy.mdf;" +
        //                                                         "Integrated Security=True;" +
        //                                                         "MultipleActiveResultSets=True");

        //DataContext db2 = new DataContext(@"Data Source=(localdb)\v11.0;
        //                                 Integrated Security=true;
        //                                 AttachDbFileName=C:\\Users\\timothychang\\source\\repos\\CSharp2\\Assignments\\Week9_10\\Final_ChangTimothy\\Final_ChangTimothy\\CineworldEntertainment_ChangTimothy.mdf");

        //DataContext db = new DataContext(@"Data Source=(localdb)\MSSQLLocalDB;
        //                                 Integrated Security=true;
        //                                 AttachDbFileName=C:\\databases\\CineworldEntertainment_ChangTimothy.mdf");

        static void Main(string[] args)
        {

            Console.SetWindowSize(130, 30);

            //OpenConnection();
            //PrintTable();

            Console.WriteLine();
            PrintTableSorted();

            //CloseConnection();

            Console.WriteLine("\nPress any key to quit...");
            Console.ReadKey();


        }

        //private static void OpenConnection()
        //{
        //    try
        //    {
        //        sqlConn.Open();
        //        Console.WriteLine("Connection Opened");
        //    }
        //    catch (SqlException ex)
        //    {
        //        Console.WriteLine("Error: " + ex.ToString());
        //    }
        //}

        //private static void CloseConnection()
        //{
        //    try
        //    {
        //        sqlConn.Close();
        //        Console.WriteLine("Connection Closed");
        //    }
        //    catch (SqlException ex)
        //    {
        //        Console.WriteLine("Error: " + ex.ToString());
        //    }
        //}

        //private static void PrintTable()
        //{
        //    try
        //    {
        //        // query
        //        string sql = "SELECT * FROM Movie";
        //        SqlCommand cmd = new SqlCommand(sql, sqlConn);
        //        SqlDataReader dr = cmd.ExecuteReader();

        //        // headers as string
        //        string movieId = "Movie ID";
        //        string title = "Title";
        //        string year = "Year";
        //        string time = "Time";
        //        string language = "Language";
        //        string rating = "Rating";
        //        string price = "Price";

        //        Console.WriteLine("Cineworld Entertainment POS System");
        //        Console.WriteLine("{0} | {1} | {2} | {3} | {4} | {5} | {6}",
        //                          movieId.PadRight(9), title.PadRight(40), year.PadRight(5),
        //                          time.PadRight(5), language.PadRight(9), rating.PadRight(7),
        //                          price.PadRight(6));
        //        Console.WriteLine("============================================================================" +
        //                          "====================================");

        //        // iterate through Movie table and print results
        //        while (dr.Read())
        //        {
        //            Console.WriteLine("{0} | {1} | {2} | {3} | {4} | {5} | {6}",
        //            dr["MovieId"].ToString().PadRight(9),
        //            dr["Title"].ToString().PadRight(40),
        //            dr["Year"].ToString().PadRight(5),
        //            dr["Time"].ToString().PadRight(5),
        //            dr["Language"].ToString().PadRight(9),
        //            dr["Rating"].ToString().PadRight(7),
        //            Convert.ToDecimal(dr["Price"]).ToString("0.00").PadRight(6));
        //        }
        //        dr.Close();
        //    }
        //    catch (SqlException ex)
        //    {
        //        Console.WriteLine("Error: " + ex.ToString());
        //    }
        //}

        // linq
        private static void PrintTableSorted()
        {
            //DataContext db = new DataContext(@"Data Source=(localdb)\MSSQLLocalDB;
            //                             Integrated Security=true;
            //                             AttachDbFileName=C:\\Users\\timothychang\\source\\repos\\CSharp2\\Assignments\\Week9_10\\Final_ChangTimothy\\Final_ChangTimothy\\CineworldEntertainment_ChangTimothy.mdf");
            DataContext db = new DataContext(@"C:\databases\CineworldEntertainment_ChangTimothy.mdf");

            //DataContext db = new DataContext(@"Server=DESKTOPD3CL53A\SQLEXPRESS;
            //                    Data Source=(localdb)\\MSSQLLocalDB;

            //                             Integrated Security=true;
            //                             AttachDbFileName=C:\\databases\\CineworldEntertainment_ChangTimothy.mdf");
            Table<Movie> Movies = db.GetTable<Movie>();
            var movieQuery =
                from movie in Movies
                where movie.Title == "Captain Marvel"
                select movie;

            foreach (var movie in movieQuery)
            {
                Console.WriteLine("ID={0}, City={1}", movie.MovieId,
                    movie.Title);
            }
        }
    }
}