using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

​

namespace ADOPractice {

    class Countries {

​

        static string connectionString = "Data Source =.; Initial Catalog = Cricket; Integrated Security = true";

​

        static void Main(string[] args) {

            Console.WriteLine("What are you Looking for :");

            Console.WriteLine($"1) {Operation.All} : Getting all the countries records");

            Console.WriteLine($"2) {Operation.Insert} : Create a new Country to DataBase");

            Console.WriteLine($"3) {Operation.Delete} : Delete a country by Countries records");

            Console.WriteLine($"4) {Operation.Update} : Update/Modify Country");

            Console.WriteLine($"5) {Operation.Players} : Check the Playing Eleven");

            Console.WriteLine($"5) {Operation.Exit} : Return to code");

​

            var condition = true;

            while (condition) {

                Console.WriteLine("...........................");

                Console.WriteLine("Enter your options here...");

                var options = Console.ReadLine();

                switch (options.ToString().ToLower()) {

                    case "all":

                        Console.WriteLine();

                        ReadRecrodsFromDataBase();

                        break;

​

                    case "insert":

                        Console.WriteLine();

                        InsertRecrodsToDataBase();

                        break;

​

                    case "delete":

                        Console.WriteLine();

                        DeleteRecordsByDataBase();

                        break;

​

                    case "update":

                        Console.WriteLine();

                        UpdateRecordsInDataBase();

                        break;

​

                    case "players":

                        Console.WriteLine();

                        PlayersDetailFromDataBase();

                        break;

​

                    case "exit":

                        condition = false;

                        break;

​

                    default:

                        Console.WriteLine("Invalid Option, Please Select the Valid Option!!!!!!!!");

                        break;

                }

​

            }

            Console.ReadLine();

        }

​

        private static void ReadRecrodsFromDataBase() {

            //var query = @"SELECT  [Id]

            //              ,[Name]

            //              ,[Code]

            //              ,[Active]

            //              ,[CreatedAt]

            //          FROM[dbo].[Country]";

            var query = "Select * FROM country";

​

            using (var con = new SqlConnection(connectionString)) {

                var command = con.CreateCommand();

                command.CommandText = query;

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                using (var reader = command.ExecuteReader()) {

                    while (reader.Read()) {

                        Console.WriteLine(reader.GetString(1));

                    }

                }

            }

        }

​

        private static void PlayersDetailFromDataBase() {

            var query = "SELECT * FROM PLAYERS_DETAIL   ORDER BY CREATED_AT";

            using (var con = new SqlConnection(connectionString)) {

                var command = con.CreateCommand();

                command.CommandText = query;

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                using (var reader = command.ExecuteReader()) {

                    while (reader.Read()) {

                        Console.WriteLine(reader.GetString(1));

                    }

                }

            }

        }

​

        private static void InsertRecrodsToDataBase() {

            Console.WriteLine("Create a New Player data to Countries Records...");

            Console.Write("Enter the PLAYER NAME : ");

            var playerName = Console.ReadLine();

            Console.Write("Enter the PLAYER ROLE : ");

            var playerRole = Console.ReadLine();

            Console.Write("Enter the PLAYER D.O.B : ");

            var playerDOB = Console.ReadLine();

            Console.Write("ACTIVE : ");

            var active = Console.ReadLine();

            Console.Write("COUNTRY_ID : ");

            var countryId = Console.ReadLine();

​

            using (var con = new SqlConnection(connectionString)) {

                var query = @"INSERT INTO PLAYERS_DETAIL([ID], 

                    [PLAYER_NAME],

                    [PLAYER_ROLE],

                    [PLAYER_DOB],

                    [ACTIVE], 

                    [COUNTRY_ID])

                    VALUES(@ID, @PLAYER_NAME, @PLAYER_ROLE, @PLAYER_DOB, @ACTIVE, @COUNTRY_ID)";

                var command = con.CreateCommand();

                command.CommandText = query;

                command.Parameters.AddWithValue("@ID", Guid.NewGuid());

                command.Parameters.AddWithValue("@PLAYER_NAME", playerName);

                command.Parameters.AddWithValue("@PLAYER_ROLE", playerRole);

                command.Parameters.AddWithValue("@PLAYER_DOB", Convert.ToDateTime(playerDOB));

                command.Parameters.AddWithValue("@ACTIVE", active);

                command.Parameters.AddWithValue("@COUNTRY_ID", countryId);

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                command.ExecuteNonQuery();

            }

        }

​

       private static void DeleteRecordsByDataBase() {

            Console.Write("Enter the NAME which you want remove from the Countries Records : ");

            var PLAYER_NAME = Console.ReadLine();

​

            using(var con = new SqlConnection(connectionString)) {

                var query = "Delete From PLAYERS_DETAIL  Where PLAYER_NAME = @name";

                var command = con.CreateCommand();

                command.CommandText = query;

                command.Parameters.AddWithValue("@name", PLAYER_NAME);

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                command.ExecuteNonQuery();

            }

       }

​

        //private static void UpdateRecordsInDataBase() {

        //    Console.WriteLine("Enter the Data which you want Update/Modify into Database...");

        //    Console.Write("Enter the COUNTRY NAME which you want UPDATE: ");

        //    var name = Console.ReadLine();

        //    Console.Write("Enter the New Name : ");

        //    var newName = Console.ReadLine();

​

        //    using (var con = new SqlConnection(connectionString)) {

        //        var query = "Update Country  Set name=@newName  Where name =@name";

        //        var command = con.CreateCommand();

        //        command.CommandText = query;

        //        command.Parameters.AddWithValue("@name", name);

        //        command.Parameters.AddWithValue("@newName", newName);

        //        command.CommandType = System.Data.CommandType.Text;

        //        con.Open();

        //        command.ExecuteNonQuery();

        //    }

        //}

​

​

        private static void UpdateRecordsInDataBase() {

            Console.WriteLine("Enter the Data which you want Update/Modify into Database...");

​

            Console.Write("Enter the COUNTRY NAME which you want UPDATE: ");

            var name = Console.ReadLine();

            var data = FetchRecordsOnCountryName(name);

​

            Console.Write("Enter the New Name : ");

            data.Name = Console.ReadLine();

            if (!string.IsNullOrWhiteSpace(name)) {

                data.Name = name;

            }

​

            Console.Write("Enter the New Code : ");

            var code = Console.ReadLine();

            if (!string.IsNullOrWhiteSpace(code)) {

                data.Code = code;

            }

            using (var con = new SqlConnection(connectionString)) {

                var query = @"Update Country  Set name = @newName, code = @newCode  Where name = @name";

                var command = con.CreateCommand();

                command.CommandText = query;

                command.Parameters.AddWithValue("@name", name);

                command.Parameters.AddWithValue("@newName", data.Name);

                command.Parameters.AddWithValue("@newCode", data.Code);

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                command.ExecuteNonQuery();

            }

        }

​

        private static CountryData FetchRecordsOnCountryName(string name) 

        {

            var countryData = new CountryData();

            var query = @"SELECT  [Id]

                          ,[Name]

                          ,[Code]

                          ,[Active]

                          ,[CreatedAt]

                      FROM[dbo].[Country] WHERE name = @name";

​

            using (var con = new SqlConnection(connectionString)) {

                var command = con.CreateCommand();

                command.CommandText = query;

                command.Parameters.AddWithValue("@name", name);

                command.CommandType = System.Data.CommandType.Text;

                con.Open();

                using (var reader = command.ExecuteReader()) {

                    if (reader.Read()) 

                    {

                        countryData.Id =  Convert.ToInt32(reader.GetOrdinal("id"));

                        countryData.Name = reader.GetString(1);

                        countryData.Code = reader.GetString(2);

                        countryData.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedAt"));

                    }

                }

            }

            return countryData;

        }

​

        //private static PlayersDetail FetchRecordsOnPlayerName(string playerName) {

​

        //}

​

    }

}