using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace lab12
{
    class Program
    {
        private static SqlConnection cn;
        private static string connection_string;

        //private static DataSet dataSet;
        private static DataTable dataTable;
        private static SqlCommandBuilder cb = new SqlCommandBuilder();
        private static SqlDataAdapter dataAdapter;

        private static void UpdateBound(int carId, string carName, int year)
        {
            try
            {
                cn = new SqlConnection(connection_string);
                cn.Open();
                string sql = string.Format("UPDATE Car SET CarName = @CarName, Year = @Year WHERE CarId = @CarId");
                SqlCommand command = new SqlCommand(sql, cn);

                SqlParameter paramCarName = new SqlParameter();
                paramCarName = new SqlParameter();
                paramCarName.ParameterName = "@CarName";
                paramCarName.Value = carName;
                paramCarName.SqlDbType = SqlDbType.Char;
                command.Parameters.Add(paramCarName);

                SqlParameter paramYear = new SqlParameter();
                paramYear.ParameterName = "@Year";
                paramYear.Value = year;
                paramYear.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(paramYear);

                SqlParameter paramCarId = new SqlParameter();
                paramCarId.ParameterName = "@CarId";
                paramCarId.Value = carId;
                paramCarId.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(paramCarId);

                command.ExecuteNonQuery();
                cn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private static void DeleteBound(int carId)
        {
            try
            {
                cn = new SqlConnection(connection_string);
                cn.Open();
                string sql = string.Format("DELETE FROM Car WHERE CarId = @CarId");
                SqlCommand command = new SqlCommand(sql, cn);
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@CarId";
                param.Value = carId;
                param.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                cn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private static void InsertBound(int carId, string carName, int year)
        {
            try
            {
                cn = new SqlConnection(connection_string);
                cn.Open();
                string sql = string.Format("INSERT Car VALUES (@CarId, @CarName, @Year)");
                SqlCommand command = new SqlCommand(sql, cn);

                SqlParameter paramCarId = new SqlParameter();
                paramCarId.ParameterName = "@CarId";
                paramCarId.Value = carId;
                paramCarId.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(paramCarId);

                SqlParameter paramCarName = new SqlParameter();
                paramCarName.ParameterName = "@CarName";
                paramCarName.Value = carName;
                paramCarName.SqlDbType = SqlDbType.Char;
                command.Parameters.Add(paramCarName);

                SqlParameter paramYear = new SqlParameter();
                paramYear.ParameterName = "@Year";
                paramYear.Value = year;
                paramYear.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(paramYear);

                command.ExecuteNonQuery();
                cn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private static void SelectBound()
        {
            try
            {
                cn = new SqlConnection(connection_string);
                cn.Open();
                String select = "SELECT * FROM  Car";
                SqlCommand command = new SqlCommand(select, cn);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    Console.WriteLine(dataReader[0] + " " + dataReader[1] + " " + dataReader[2]);
                }
                dataReader.Close();
                cn.Close();
                Console.WriteLine();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //+
        private static void StartUnbound()
        {
            //dataSet = new DataSet();
            dataTable = new DataTable();
            dataAdapter = new SqlDataAdapter("SELECT * FROM Car", connection_string);
            dataAdapter.Fill(dataTable);
            DataColumn[] primary_key = new DataColumn[1];
            primary_key[0] = dataTable.Columns["CarId"];
            dataTable.PrimaryKey = primary_key;
        }
        //как узнать какие строки отправляются в БД
        //для чего нужны INSERT, UPDATE, DELETE (как они используются dataAdapterom)
        //настроить эти инструкции в коде

        //11 лаба пункт 4
        private static void EndUnbound()
        {
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            dataAdapter.Update(dataTable);
        }

        //+
        private static void SelectUnbound()
        {
            foreach (DataRow dataRow in dataTable.Rows)
            {
                if (dataRow.RowState != DataRowState.Deleted)
                {
                    Console.WriteLine(dataRow[0].ToString() + "  " + dataRow[1].ToString() + " " + dataRow[2]);
                }
            }
            Console.WriteLine();
        }

        private static void InsertUnbound(int carId, string carName, int year)
        {
            dataTable.Rows.Add(carId, carName, year);

            SqlCommand insertCommand = new SqlCommand("INSERT INTO CAR (CarId, CarName, Year) " +
                                                      "VALUES (@CarId, @CarName, @Year)", cn);

            insertCommand.Parameters.Add(new SqlParameter("CarId", carId));
            insertCommand.Parameters.Add(new SqlParameter("CarName", carName));
            insertCommand.Parameters.Add(new SqlParameter("Year", year));

            dataAdapter.InsertCommand = insertCommand;



            //DataRow dataRow = dataSet.Tables["Car"].NewRow();
            //dataRow["CarId"] = carId;
            //dataRow["CarName"] = carName;
            //dataRow["Year"] = year;
            //dataSet.Tables["Car"].Rows.Add(dataRow);
        }

        private static void DeleteUnbound(int carId)
        {
            DataRow dataRow = null;
            dataRow = dataTable.Rows.Find(carId);
            int ind = dataTable.Rows.IndexOf(dataRow);
            dataTable.Rows[ind].Delete();

            SqlCommand deleteCommand = new SqlCommand("DELETE FROM Car WHERE " +
                                                      "CarId = @CarId", cn);

            deleteCommand.Parameters.Add(new SqlParameter("CarId", carId));

            dataAdapter.DeleteCommand = deleteCommand;


            //DataRow dataRow = null;
            //dataRow = dataSet.Tables["Car"].Rows.Find(carId);
            //int ind = dataSet.Tables["Car"].Rows.IndexOf(dataRow);
            //dataSet.Tables["Car"].Rows[ind].Delete();
        }

        private static void UpdateUnbound(int carId, string carName, int year)
        {
            DataRow dataRow = null;
            dataRow = dataTable.Rows.Find(carId);
            int ind = dataTable.Rows.IndexOf(dataRow);
            dataTable.Rows[ind]["CarName"] = carName;
            dataTable.Rows[ind]["Year"] = year;

            SqlCommand updateCommand = new SqlCommand("UPDATE Car SET CarName = @CarName, Year = @Year WHERE CarId =  @CarId", cn);

            updateCommand.Parameters.Add(new SqlParameter("CarName", carName));
            updateCommand.Parameters.Add(new SqlParameter("Year", year));
            updateCommand.Parameters.Add(new SqlParameter("CarId", carId));

            dataAdapter.UpdateCommand = updateCommand;


            //DataRow dataRow = null;
            //dataRow = dataSet.Tables["Car"].Rows.Find(carId);
            //int ind = dataSet.Tables["Car"].Rows.IndexOf(dataRow);
            //dataSet.Tables["Car"].Rows[ind]["CarName"] = carName;
            //dataSet.Tables["Car"].Rows[ind]["Year"] = year;

        }

        private static void Bound()
        {
            Console.WriteLine("select bound: ");
            SelectBound();
            InsertBound(3, "New Mashina", 2020);
            DeleteBound(1);
            UpdateBound(3, "Update Mashina", 2021);
            SelectBound();
        }

        private static void Unbound()
        {
            StartUnbound();
            Console.WriteLine("select unbound : ");
            SelectUnbound();
            InsertUnbound(5, "BMW x6", 2016);
            SelectUnbound();
            DeleteUnbound(2);
            SelectUnbound();
            UpdateUnbound(1, "Update", 1080);
            EndUnbound();
            SelectUnbound();
        }

        static void Main(string[] args)
        {
            connection_string = ConfigurationManager.ConnectionStrings["SQLClient"].ConnectionString;
            Console.OutputEncoding = System.Text.Encoding.UTF8;
            //Bound();

            Unbound();

            Console.Read();

        }
    }
}