//unbound

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name ="SQLClient"
             providerName="System.Data.SqlClient"
             connectionString = "Server=localhost; Database=Lab12; User ID=sa; Password=Nikusha1299"/>

        //connectionString="Server=localhost;Database=Lab12;trusted_connection=true"/>
        // connectionString="Server=192.168.0.92\sqls2005; Database=Lab12; User ID=sa;
        Password=Nikusha1299"/>
    </connectionStrings>
</configuration>






using System;
using System.Configuration;
using System.Data;
//using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics.Contracts;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lab12_2
{
    internal class Program
    {
        //private static SqlConnection cn;
        private static DataSet dataSet;
        private static SqlDataAdapter dataAdapter;
        private static string connection_string;

        private static void In()
        {
            SqlConnection conn =
                new SqlConnection(ConfigurationManager.ConnectionStrings["SQLClient"].ConnectionString);
            dataSet = new DataSet();
            dataAdapter = new SqlDataAdapter("SELECT *FROM FilmLibrary", conn);
            dataAdapter.Fill(dataSet, "FilmLibrary");
            DataColumn[] key = new DataColumn[1];
            key[0] = dataSet.Tables["FilmLibrary"].Columns["id"];
            dataSet.Tables["FilmLibrary"].PrimaryKey = key;

           // Out();


            //операция просмотра содержимого таблиц

            foreach (DataRow dataRow in dataSet.Tables["FilmLibrary"].Rows)
            {
                if (dataRow.RowState != DataRowState.Deleted)
                {
                    Console.WriteLine("| " + dataRow[0].ToString() + " | " + dataRow[1].ToString() + " |");
                }
            }

            Console.WriteLine();
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);
         //   Out();


            //операция вставки данных в таблицах


            //In();
            DataRow dataRows = dataSet.Tables["FilmLibrary"].NewRow();

            dataRows["id"] = 100;
            dataRows["film"] = "Unforgetable";
            dataSet.Tables["FilmLibrary"].Rows.Add(dataRows);

            SqlCommand insertCommand = new SqlCommand("INSERT INTO FilmLibrary (id, film) " +
                                                      "VALUES (@id, @film)", conn);

            insertCommand.Parameters.Add(new SqlParameter("id", 100));
            insertCommand.Parameters.Add(new SqlParameter("film", "Unforgetable"));

            dataAdapter.InsertCommand = insertCommand;

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);
          //  Out();

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

            //операция просмотра содержимого таблиц


            foreach (DataRow dataRow in dataSet.Tables["FilmLibrary"].Rows)
            {
                if (dataRow.RowState != DataRowState.Deleted)
                {
                    Console.WriteLine("| " + dataRow[0].ToString() + " | " + dataRow[1].ToString() + " |");
                }
            }

            Console.WriteLine();
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);
          //  Out();

            //операция удаления данных в таблицах

            dataRows = dataSet.Tables["FilmLibrary"].Rows.Find(3);
            int index = dataSet.Tables["FilmLibrary"].Rows.IndexOf(dataRows);
            dataSet.Tables["FilmLibrary"].Rows[index].Delete();

            SqlCommand deleteCommand = new SqlCommand("DELETE FROM FilmLibrary WHERE " +
                                                      "id > @id", conn);

            deleteCommand.Parameters.Add(new SqlParameter("id", "3"));

            dataAdapter.DeleteCommand = deleteCommand;

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);

            //Out();

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


            //операция просмотра содержимого таблиц



            foreach (DataRow dataRow in dataSet.Tables["FilmLibrary"].Rows)
            {
                if (dataRow.RowState != DataRowState.Deleted)
                {
                    Console.WriteLine("| " + dataRow[0].ToString() + " | " + dataRow[1].ToString() + " |");
                }
            }

            Console.WriteLine();
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);

          //  Out();

            //операция изменения данных в таблицах

            //foreach (DataRow dr in dataSet.Tables["FilmLibrary"].Rows)
            //{
           //     if (dr["id"] == "1")
           //         dr["film"] = "something wrong";
           // }

            dataRows = dataSet.Tables["FilmLibrary"].Rows.Find(1);
            index = dataSet.Tables["FilmLibrary"].Rows.IndexOf(dataRows);
            dataSet.Tables["FilmLibrary"].Rows[index]["film"] = "something wrong";

            SqlCommand updateCommand =
                new SqlCommand("UPDATE FilmLibrary SET film = @film WHERE id =  @id", conn);

            updateCommand.Parameters.Add(new SqlParameter("id", "1"));
            updateCommand.Parameters.Add(new SqlParameter("film", "something wrong"));

            dataAdapter.UpdateCommand = updateCommand;
            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);

       //     Out();

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

            //операция просмотра содержимого таблиц

            foreach (DataRow dataRow in dataSet.Tables["FilmLibrary"].Rows)
            {
                if (dataRow.RowState != DataRowState.Deleted)
                {
                    Console.WriteLine("| " + dataRow[0].ToString() + " | " + dataRow[1].ToString() + " |");
                }
            }

            Console.WriteLine();
            Console.WriteLine("Data displayed! Now press enter to move to the next section!");
            Console.ReadLine();
            Console.Clear();

            //dataAdapter.Update(dataSet.Tables["FilmLibrary"]);

            Out();
        }

        private static void Out()
        {
            try
            {
                //SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
                dataAdapter.Update(dataSet.Tables["FilmLibrary"]);
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex);
            }
        }

        public static void Main(string[] args)
        {
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = ConfigurationManager.ConnectionStrings["SQLClient"].ConnectionString;
                Console.OutputEncoding = System.Text.Encoding.UTF8;
                //SelectAll();
                //Insert(7, "Покорители волн");
                //Insert(8, "Kjdb djkye");
                //Insert(9, "Лови волну");
                //SelectAll();
                //Update(8, "Can not be readable");
                //SelectAll();
                //Delete(9);
                //SelectAll();
                In();
                //InsertDataSet(cn, 10, "h");
                //SelectDataSet();
                // UpdateDataSet(2, "change");
                // SelectDataSet();
                // DeleteDataSet(4);
                // SelectDataSet();
                //SelectAll();
                Out();
                //SelectAll();
                Console.ReadKey(); //Получает следующий нажатый пользователем символ или функциональную клавишу. Нажатая клавиша отображается в окне консоли.
            }
        }
    }
}