using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace myhelp
{
    public partial class Form1 : Form
    {
        int ctr;
        int jk;
        public Form1()
        {
            InitializeComponent();
            ctr = 0;
                    }
        private void button1_Click(object sender, EventArgs e)
        {
          

        }

        private void button2_Click(object sender, EventArgs e)
        {
          
        }


      
        private void button3_Click(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button3_Click_1(object sender, EventArgs e)
        {
           
        }
        //insert procedure
        private void button1_Click_1(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");

            cn.Open();
            if (cn.State == ConnectionState.Open)
            {
                SqlCommand insertCommand = new SqlCommand("insert into hell values('" + textBox1.Text + "','" + textBox2.Text + "')", cn);
                insertCommand.ExecuteNonQuery();
                textBox1.Text = textBox2.Text = "";
                textBox1.Focus();
            }
            else
            {
                MessageBox.Show("Connection is not open");
            }
        }
        //read procedure in text boxes
        private void button2_Click_1(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
            cn.Open();
            SqlCommand myReadCommand = new SqlCommand("select * from hell", cn);
            SqlDataAdapter da = new SqlDataAdapter(myReadCommand);
            DataSet ds = new DataSet();
            da.Fill(ds);
            MessageBox.Show("Total rows:" + ds.Tables[0].Rows.Count.ToString());
            textBox1.Text = ds.Tables[0].Rows[ctr][0].ToString();
            textBox2.Text = ds.Tables[0].Rows[ctr][1].ToString();
            ctr++;
            cn.Close();
        }
        //data grid view display procedure
        private void button3_Click_2(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
            cn.Open();
            SqlCommand myReadCommand = new SqlCommand("select * from hell", cn);
            SqlDataAdapter da = new SqlDataAdapter(myReadCommand);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }
        //delete all record procedure
        private void button4_Click(object sender, EventArgs e)
        {
           
           //delete specific id
              /*  string connectionString =("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
                using (SqlConnection conn =
                    new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd =
                        new SqlCommand("DELETE FROM hell " +
                            "WHERE Id=@Id", conn))
                    {
                        cmd.Parameters.AddWithValue("@Id",'2');

                        int rows = cmd.ExecuteNonQuery();

                        //rows number of record got deleted
                    }
                }*/
         
          
            SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "DELETE FROM hell WHERE Id=Id";
            cmd.Connection = con;
            con.Open();
            int numberDeleted = cmd.ExecuteNonQuery();
            //Response.Write(numberDeleted.ToString() + " employees were deleted.<br>");
            // use this instead of the above line if we're in a windows form rather than an ASP.NET page
            MessageBox.Show(numberDeleted.ToString() + " employees were deleted.<br>");
            con.Close();

        
        }
        //delete by id procedure
        private void button5_Click(object sender, EventArgs e)
        {
            jk = Convert.ToInt32(textBox3.Text);
            SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from hell where id='" + textBox3.Text + "'";
            cmd.Connection = con;
            con.Open();
            int numberDeleted = cmd.ExecuteNonQuery();

            con.Close();
            MessageBox.Show("Record is deleted", "Programming at kastrak");
        }

        //refresh procedure
        private void button6_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=\"c:\\users\\connecting people\\documents\\visual studio 2012\\Projects\\myhelp\\hell.mdf\";Integrated Security=True;Connect Timeout=30");
            cn.Open();
            SqlCommand myReadCommand = new SqlCommand("select * from hell", cn);
            SqlDataAdapter da = new SqlDataAdapter(myReadCommand);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }

   
        }
   
}