SQL Server(2005) With Data Add, Delete, Update and GridView

Form1.cs

using System;
using System.Data;
using System.Windows.Forms;

namespace GridView
{
    public partial class GridView : Form
    {
        Form1DataHandler callForm1DataHandler = new Form1DataHandler();
        public GridView()
        {
            InitializeComponent();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string id = txbID.Text;
            string name = txbName.Text;
            string contact = txbContact.Text;
            string department = txbDepartment.Text;
            callForm1DataHandler.AddData(id, name, contact, department);
            ClearText();
        }

        private void btnView_Click(object sender, EventArgs e)
        {
            DataSet dataset = new DataSet();
            dataGridView1.Refresh();
            dataset = callForm1DataHandler.BindData();
            dataGridView1.DataSource = dataset.Tables[0];
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string id = txbID.Text;
            string name = txbName.Text;
            string contact = txbContact.Text;
            string department = txbDepartment.Text;
            callForm1DataHandler.UpDate(id, name, contact, department);
            ClearText();
        }
        private void ClearText()
        {
            txbContact.Clear();
            txbDepartment.Clear();
            txbID.Clear();
            txbName.Clear();
        }

        private void txbID_KeyUp(object sender, KeyEventArgs e)
        {
           
            if (e.KeyValue == 13)       // ENTER KET EVENT CAPTURING
            {
                string id = txbID.Text;
                DataSet fillText = new DataSet();
                fillText = callForm1DataHandler.CheckText(id);
                if (fillText != null)
                {
                    txbName.Text = fillText.Tables[0].Rows[0][1].ToString();
                    txbContact.Text = fillText.Tables[0].Rows[0][2].ToString();
                    txbDepartment.Text = fillText.Tables[0].Rows[0][3].ToString();
                }
            }
        }

        private void btnDelet_Click(object sender, EventArgs e)
        {
            string id = txbID.Text;
            callForm1DataHandler.Delet(id);
            ClearText();
        }
    }
}



Form1DataHandler.cs


using System.Data;

namespace GridView
{
    class Form1DataHandler : DataBaseHandler
    {
        public void AddData(string id,string name,string contact,string department)
        {
            string quary="INSERT INTO client VALUES('"+id+"','"+name+"','"+contact+"','"+department+"')";
            ExecuteQuary(quary);  
        }

        public DataSet BindData()
        {
            string quary1 = "SELECT * FROM client";
            DataSet bindDataSet = new DataSet();
            bindDataSet = GetData(quary1);
            return bindDataSet;
        }

        public void UpDate(string id, string name, string contact, string department)
        {
            string quary = "UPDATE client SET NAME='" + name + "',CONTACT='" + contact + "',DEPARTMENT='" + department + "' WHERE ID='"+id+"'";
            ExecuteQuary(quary);
        }

        public void Delet(string id)
        {
            string quary = "DELETE FROM vin WHERE ID='" + id + "'";
            ExecuteQuary(quary);
        }

        public DataSet CheckText(string id)
        {
            string quary2 = "SELECT * FROM client WHERE ID='"+id+"'";
            DataSet checkData = new DataSet();
            checkData = GetData(quary2);
            return checkData;
        }
    }
}



DataBaseHandler.cs
 

 using System;
using System.Data;
using System.Data.SqlClient;

namespace GridView
{
    class DataBaseHandler
    {
        SqlConnection connection = new SqlConnection("Data Source =VINDANA-PC; database=vin; User =sa;Password=vinx");
        SqlCommand command = null;
        SqlDataAdapter adapter = null;
        public void ExecuteQuary(string quary)
        {
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    command = new SqlCommand();
                }
                command.CommandText = quary;
                command.Connection = connection;
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception)
            {
          
            }
        }
        public DataSet GetData(string quary)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                adapter = new SqlDataAdapter(quary, connection);
            }
            DataSet getDataSet = new DataSet();
            adapter.Fill(getDataSet);
            connection.Close();
            return getDataSet;
        }
    }
}

UPDATE DROP DOWN

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

namespace UpdateDropDown
{
class Employee
{

SqlConnection sqlcon = null;
SqlDataAdapter Adptr = null;

public Employee() {

}

private void connectDB() {
string constr = "Data Source=VINDANA-PC;Initial Catalog=W3School;Persist Security Info=True;User ID=sa;Password=saranga";
sqlcon = new SqlConnection(constr);
sqlcon.Open();
}

public DataTable updateDropDown() {

connectDB();
Adptr = new SqlDataAdapter("SELECT Emp_Id,Emp_Name FROM Employee", sqlcon);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
Adptr.Fill(dt);
sqlcon.Close();

return dt;

}

public DataTable display(int id) {
connectDB();

Adptr = new SqlDataAdapter("SELECT * FROM Employee where Emp_Id= '" + id + " ' ", sqlcon);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
Adptr.Fill(dt);
sqlcon.Close();

return dt;
}

}
}



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

namespace UpdateDropDown
{
class Department
{

SqlConnection sqlcon = null;
SqlDataAdapter Adptr = null;

public Department() {

}


private void connectDB()
{
string constr = "Data Source=.;Initial Catalog=W3School;Persist Security Info=True;User ID=sa;Password=saranga";
sqlcon = new SqlConnection(constr);
sqlcon.Open();
}

public DataTable depData(int id) {
connectDB();
Adptr = new SqlDataAdapter("SELECT * FROM Department where Dep_Id= '" + id + " ' ", sqlcon);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
Adptr.Fill(dt);
sqlcon.Close();

return dt;
}

}
}



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

namespace UpdateDropDown
{
public partial class Form1 : Form
{

int ID;
string str;
public Form1()
{
InitializeComponent();

loadComboBox();


}

public void loadComboBox()
{
Employee emp = new Employee();
DataTable dt = emp.updateDropDown();
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "Emp_Id";
comboBox1.ValueMember = "Emp_Id";


}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{

}

private void btnsubmit_Click(object sender, EventArgs e)
{
Employee em = new Employee();
ID = Int16.Parse(comboBox1.SelectedValue.ToString());

//MessageBox.Show(ID.ToString());
DataTable dt = em.display(ID);

txtName.Text = dt.Rows[0].ItemArray.GetValue(1).ToString();
txtAge.Text = dt.Rows[0].ItemArray.GetValue(2).ToString();
txtDepId.Text = dt.Rows[0].ItemArray.GetValue(3).ToString();
int depid = Int16.Parse(txtDepId.Text);
//MessageBox.Show(depid.ToString());
Department dep = new Department();
DataTable dt2 = dep.depData(depid);
txtDepName.Text = dt2.Rows[0].ItemArray.GetValue(1).ToString();

}

private void Form1_Load(object sender, EventArgs e)
{

}

}
}