using System;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Data.Common;

namespace SqlTest_CSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            connectedLevel();
            unconnectedLevel();
        }

        static void connectedLevel()
        {
            // Create the connection to the resource
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SQLClient"].ConnectionString;
                Console.OutputEncoding = Encoding.UTF8;

                selectConnected(conn);

                insertConnected(conn);

                selectConnected(conn);

                updateConnected(conn);

                selectConnected(conn);

                deleteConnected(conn);

                selectConnected(conn);

            }
            // ReadLine to prevent the console from closing.
            Console.ReadLine();
        }

        static void selectConnected(SqlConnection conn)
        {
            try
            {
                // Create the connectionString
                // Trusted_Connection is used to denote the connection uses Windows Authentication
                //"Server=RAZER-BLADE;Database=SteamDB;Trusted_Connection=true
                conn.Open();

                //SELECT COMMAND
                SqlCommand command = new SqlCommand("SELECT * FROM SteamDB.Users", conn);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("UserID\tUserName\t\tFirstName\tSecondName\tPhoneNumber");
                    while (reader.Read())
                    {
                        Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t | {4}",
                        reader[0], reader[1], reader[2], reader[3], reader[4]));
                    }
                }

                Console.WriteLine("Data displayed! Now press enter to move to the next section!");
                Console.ReadLine();
                Console.Clear();
                conn.Close();
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
                conn.Close();

            }
        }

        static void insertConnected(SqlConnection conn)
        {
            try
            {
                conn.Open();

                //INSERT COMMAND
                Console.WriteLine("INSERT INTO command");

                SqlCommand insertCommand = new SqlCommand("INSERT INTO SteamDB.Users (UserName, FirstName, SecondName, PhoneNumber) " +
                    "VALUES (@UserID, @FirstName, @SecondName, @PhoneNumber)", conn);

                insertCommand.Parameters.Add(new SqlParameter("UserID", "Romashkovoe pole"));
                insertCommand.Parameters.Add(new SqlParameter("FirstName", "Alexandr"));
                insertCommand.Parameters.Add(new SqlParameter("SecondName", "Gromkov"));
                insertCommand.Parameters.Add(new SqlParameter("PhoneNumber", "89672628861"));


                // Execute the command and print the values of the columns affected through
                // the command executed.
                Console.WriteLine("INSERT Commands executed! Total rows affected are " + insertCommand.ExecuteNonQuery());
                conn.Close();
                Console.WriteLine("Done! Press enter to move to the next step");
                Console.ReadLine();
                Console.Clear();

            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
                conn.Close();

            }
        }

        static void updateConnected(SqlConnection conn)
        {
            try
            {

                conn.Open();

                //UPDATE COMMAND
                Console.WriteLine("UPDATE command");

                SqlCommand updateCommand = new SqlCommand("UPDATE SteamDB.Users SET PhoneNumber = @PhoneNumber WHERE UserName =  @UserName", conn);

                updateCommand.Parameters.Add(new SqlParameter("PhoneNumber", "11111111111"));
                updateCommand.Parameters.Add(new SqlParameter("UserName", "Romashkovoe pole"));


                Console.WriteLine("UPDATE Commands executed! Total rows affected are " + updateCommand.ExecuteNonQuery());
                Console.WriteLine("Done! Press enter to move to the next step");
                Console.ReadLine();
                Console.Clear();
                conn.Close();
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
                conn.Close();

            }

        }

        static void deleteConnected(SqlConnection conn)
        {
            try
            {

                conn.Open();
                //DELETE COMMAND
                Console.WriteLine("DELETE command");

                SqlCommand deleteCommand = new SqlCommand("DELETE FROM SteamDB.Users WHERE " +
                    "UserID > @UserID", conn);

                deleteCommand.Parameters.Add(new SqlParameter("UserID", "4"));

                Console.WriteLine("DELETE Commands executed! Total rows affected are " + deleteCommand.ExecuteNonQuery());
                Console.WriteLine("Done! Press enter to move to the next step");
                Console.ReadLine();
                Console.Clear();
                conn.Close();
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
                conn.Close();

            }
        }



        static void unconnectedLevel()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLClient"].ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM SteamDB.Users", conn);
            DataTable dataTable = new DataTable();

            //Автоматически создает и выполняет INSERT, UPDATE, DELETE
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.Fill(dataTable);



            //SELECT FROM TABLE
            Console.WriteLine("UserID\tUserName\t\tFirstName\tSecondName\tPhoneNumber");
            foreach (DataRow dr in dataTable.Rows)
            {
                Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t | {4}",
                        dr[0], dr[1], dr[2], dr[3], dr[4]));
            }
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();



            //INSERT INTO TABLE
            dataTable.Rows.Add(5, "Romashrovoe pole", "Alexandr", "Gromkov", "89672628861");


            SqlCommand insertCommand = new SqlCommand("INSERT INTO SteamDB.Users (UserName, FirstName, SecondName, PhoneNumber) " +
                    "VALUES (@UserID, @FirstName, @SecondName, @PhoneNumber)", conn);

            insertCommand.Parameters.Add(new SqlParameter("UserID", "Romashkovoe pole"));
            insertCommand.Parameters.Add(new SqlParameter("FirstName", "Alexandr"));
            insertCommand.Parameters.Add(new SqlParameter("SecondName", "Gromkov"));
            insertCommand.Parameters.Add(new SqlParameter("PhoneNumber", "89672628861"));

            da.InsertCommand = insertCommand;

            try
            {
                da.Update(dataTable);
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("INSERT Commands executed!");
            Console.WriteLine("Done! Press enter to move to the next step");
            Console.ReadLine();
            Console.Clear();



            //SELECT FROM TABLE
            Console.WriteLine("UserID\tUserName\t\tFirstName\tSecondName\tPhoneNumber");
            foreach (DataRow dr in dataTable.Rows)
            {
                Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t | {4}",
                        dr[0], dr[1], dr[2], dr[3], dr[4]));
            }
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();



            //UPDATE TABLE
            foreach (DataRow dr in dataTable.Rows)
            {
                if (dr["UserName"] == "Romashrovoe pole")
                    dr["PhoneNumber"] = "11111111111";
            }

            SqlCommand updateCommand = new SqlCommand("UPDATE SteamDB.Users SET PhoneNumber = @PhoneNumber WHERE UserName =  @UserName", conn);

            updateCommand.Parameters.Add(new SqlParameter("PhoneNumber", "11111111111"));
            updateCommand.Parameters.Add(new SqlParameter("UserName", "Romashkovoe pole"));

            da.UpdateCommand = updateCommand;

            try
            {
                da.Update(dataTable);
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("UPDATE Commands executed!");
            Console.WriteLine("Done! Press enter to move to the next step");
            Console.ReadLine();
            Console.Clear();



            //SELECT FROM TABLE
            Console.WriteLine("UserID\tUserName\t\tFirstName\tSecondName\tPhoneNumber");
            foreach (DataRow dr in dataTable.Rows)
            {
                Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t | {4}",
                        dr[0], dr[1], dr[2], dr[3], dr[4]));
            }
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();



            //DELETE FROM TABLE
            List<DataRow> rowsToDelete = new List<DataRow>();
            foreach (DataRow dr in dataTable.Rows)
            {
                if ((int)dr["UserID"] > 4)
                    rowsToDelete.Add(dr);
            }
            foreach (DataRow dr in rowsToDelete)
            {
                dataTable.Rows.Remove(dr);
            }

            SqlCommand deleteCommand = new SqlCommand("DELETE FROM SteamDB.Users WHERE " +
                    "UserID > @UserID", conn);

            deleteCommand.Parameters.Add(new SqlParameter("UserID", "4"));

            da.DeleteCommand = deleteCommand;

            try
            {
                da.Update(dataTable);
            } catch (DbException e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("DELETE Commands executed!");
            Console.WriteLine("Done! Press enter to move to the next step");
            Console.ReadLine();
            Console.Clear();



            //SELECT FROM TABLE
            Console.WriteLine("UserID\tUserName\t\tFirstName\tSecondName\tPhoneNumber");
            foreach (DataRow dr in dataTable.Rows)
            {
                Console.WriteLine(String.Format("{0} \t | {1} \t | {2} \t | {3} \t | {4}",
                        dr[0], dr[1], dr[2], dr[3], dr[4]));
            }
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();
        }
    }
}