using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ADODOTNET
{
    class Program
    {
        static void Main(string[] args)
        {
            //SqlConnection northconnection=null;
            //SqlCommand Hellocmd = null;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Hellocmd = new SqlCommand(“Hello”, northconnection);
            //    Hellocmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = Hellocmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;

            //    northconnection.Open();
            //    int x = Hellocmd.ExecuteNonQuery();         //any reader or scalar , all 3 allowed
            //    Console.WriteLine(Hellocmd.Parameters[“@ret”].Value);
            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand HelloNamecmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    HelloNamecmd = new SqlCommand(“HelloName”, northconnection);
            //    HelloNamecmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = HelloNamecmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;
            //    Console.WriteLine(“enter a name”);
            //    String nam = Console.ReadLine();
            //    HelloNamecmd.Parameters.AddWithValue(“@name”,nam);

            //    northconnection.Open();
            //    int x = HelloNamecmd.ExecuteNonQuery();         
            //    Console.WriteLine(HelloNamecmd.Parameters[“@ret”].Value);
            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand HelloNamecmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    HelloNamecmd = new SqlCommand(“HelloAge”, northconnection);
            //    HelloNamecmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = HelloNamecmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;
            //    Console.WriteLine(“enter a name”);
            //    String nam = Console.ReadLine();
            //    HelloNamecmd.Parameters.AddWithValue(“@name”, nam);
            //    Console.WriteLine(“enter the age”);
            //    int age = Convert.ToInt32(Console.ReadLine());
            //    HelloNamecmd.Parameters.AddWithValue(“@age”, age);

            //    northconnection.Open();
            //    int x = HelloNamecmd.ExecuteNonQuery();
            //    Console.WriteLine(HelloNamecmd.Parameters[“@ret”].Value);
            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“EmployeeDetails”, northconnection);
            //    Empcmd.CommandType = CommandType.StoredProcedure;

            //    Console.WriteLine(“enter employee number”);
            //    String empno = Console.ReadLine();
            //    Empcmd.Parameters.AddWithValue(“@empId”, empno);

            //    northconnection.Open();
            //    SqlDataReader reader = Empcmd.ExecuteReader();

            //    while (reader.Read())
            //    {
            //        Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString() + reader[3].ToString());
            //    }

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“Empdetail”, northconnection);
            //    Empcmd.CommandType = CommandType.StoredProcedure;

            //    Console.WriteLine(“enter employee number”);
            //    String empno = Console.ReadLine();
            //    Empcmd.Parameters.AddWithValue(“@empId”, empno);

            //    northconnection.Open();
            //    SqlDataReader reader = Empcmd.ExecuteReader();

            //    while (reader.Read())
            //    {
            //        Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString() + reader[3].ToString());
            //    }

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“AddDept”, northconnection);
            //    Empcmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = Empcmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;

            //    northconnection.Open();
            //    int x=Empcmd.ExecuteNonQuery();
            //    Console.WriteLine(Empcmd.Parameters[“@ret”].Value);

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“ModifyEmployee”, northconnection);
            //    Empcmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = Empcmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;
            //    Console.WriteLine(“enter the employee id whose name need to change”);
            //    int employeeid = Convert.ToInt32(Console.ReadLine());
            //    Empcmd.Parameters.AddWithValue(“@empId”, employeeid);

            //    Console.WriteLine(“enter the name you want to enter”);
            //    String nam = Console.ReadLine();
            //    Empcmd.Parameters.AddWithValue(“@fname”, nam);

            //    northconnection.Open();
            //    int x = Empcmd.ExecuteNonQuery();
            //    Console.WriteLine(Empcmd.Parameters[“@ret”].Value);

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“RemoveEmployee”, northconnection);
            //    Empcmd.CommandType = CommandType.StoredProcedure;

            //    SqlParameter returnParameter = Empcmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;
            //    Console.WriteLine(“enter the employee id whose record need to be deleted”);
            //    int employeeid = Convert.ToInt32(Console.ReadLine());
            //    Empcmd.Parameters.AddWithValue(“@empId”, employeeid);

            //    northconnection.Open();
            //    int x = Empcmd.ExecuteNonQuery();
            //    Console.WriteLine(Empcmd.Parameters[“@ret”].Value);

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}
            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“Select * from Employee”, northconnection);

            //    northconnection.Open();
            //    SqlDataReader reader = Empcmd.ExecuteReader();

            //    while (reader.Read())
            //    {
            //        Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString() + reader[3].ToString());
            //    }

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();

            //}
            //……………………………………………………………………………………………
            //……………………………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlCommand Empcmd;
            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    Empcmd = new SqlCommand(“insert into Employee values (”  , northconnection);

            //    SqlParameter returnParameter = Empcmd.Parameters.Add(“@ret”, SqlDbType.Int);
            //    returnParameter.Direction = ParameterDirection.ReturnValue;

            //    northconnection.Open();
            //    int x = Empcmd.ExecuteNonQuery();
            //    Console.WriteLine(Empcmd.Parameters[“@ret”].Value);

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //finally
            //{
            //    northconnection.Close();
            //}

            //Disconnected starts here……………………………………………………………

            //SqlConnection northconnection = null;
            //SqlDataAdapter empadapdter = null;

            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    empadapdter = new SqlDataAdapter(“select * from Employee”, northconnection);
            //    DataSet ds = new DataSet();
            //    empadapdter.Fill(ds, “Employeenew”); // any name that be in cache

            //    foreach (DataColumn Column in ds.Tables[0].Columns)
            //    {
            //        Console.Write(Column.ColumnName);
            //    }

            //    foreach (DataRow Row in ds.Tables[0].Rows)
            //    {
            //        Console.WriteLine(Row[0].ToString() + Row[1].ToString() + Row[2].ToString() + Row[3].ToString());
            //    }

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //……………………………………………………………………………
            //……………………………………………………………………………

            //SqlConnection northconnection = null;
            //SqlDataAdapter empadapdter = null;

            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    empadapdter = new SqlDataAdapter(“select * from Employee”, northconnection);
            //    DataSet ds = new DataSet();
            //    empadapdter.Fill(ds, “Employeenew”); // any name that be in cache
            //    SqlCommandBuilder builder = new SqlCommandBuilder(empadapdter);

            //    Console.WriteLine(“enter the first name that u want to update”);
            //    String name = Console.ReadLine();

            //    Console.WriteLine(ds.Tables[0].Rows[2][“emp_fname”]= name);

            //    foreach (DataColumn Column in ds.Tables[0].Columns)
            //    {
            //        Console.Write(Column.ColumnName);
            //    }

            //    foreach (DataRow Row in ds.Tables[0].Rows)
            //    {
            //        Console.WriteLine(Row[0].ToString() + Row[1].ToString() + Row[2].ToString() + Row[3].ToString());
            //    }

            //    empadapdter.Update(ds, “Employeenew”);

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //……………………………………………………………………………
            //…………………………..prog 3………………………………

            //SqlConnection northconnection = null;
            //SqlDataAdapter empadapdter = null;

            //try
            //{
            //    northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
            //    empadapdter = new SqlDataAdapter(“select * from Employee”, northconnection);
            //    DataSet ds = new DataSet();
            //    empadapdter.Fill(ds, “Employeenew”); // any name that be in cache
                

            //    foreach (DataColumn Column in ds.Tables[0].Columns)
            //    {
            //        Console.Write(Column.ColumnName);
            //    }

            //    foreach (DataRow Row in ds.Tables[0].Rows)
            //    {
            //        if (Row[3].ToString() == “d3”)
            //        {
            //            Row[1] = Row[1].ToString() + Row[3].ToString();
            //        }

            //        Console.WriteLine(Row[0].ToString() + Row[1].ToString() + Row[2].ToString() + Row[3].ToString());
            //    }

                

            //}

            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            //……………………………………………………………………………
            //…………………………..CALLING A PROCEDURE………………………………

            SqlConnection northconnection = null;
            SqlDataAdapter empadapdter = null;
            SqlCommand Empcmd = null;

            try
            {
                northconnection = new SqlConnection(“Server=LOCALHOST;Database = DAY1;trusted_connection = true”);
                empadapdter = new SqlDataAdapter(“select * from Department”, northconnection);
                Empcmd = new SqlCommand(“AddDept”, northconnection);
                Empcmd.CommandType = CommandType.StoredProcedure;

                DataSet ds = new DataSet();
                empadapdter.Fill(ds, “Employeenew”);
                SqlCommandBuilder builder = new SqlCommandBuilder(empadapdter);

                foreach (DataColumn Column in ds.Tables[0].Columns)
                {
                    Console.Write(Column.ColumnName);
                }

                foreach (DataRow Row in ds.Tables[0].Rows)
                {
                    Console.WriteLine(Row[0].ToString() + Row[1].ToString() + Row[2].ToString());
                }

                empadapdter.Update(ds, “Employeenew”);
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

        }
    }

}