C# Programs and SQL Queries

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 WindowsFormsApplication3
{
public partial class Form1 : Form
{
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
SqlDataAdapter da;
DataSet ds;
CurrencyManager cmobj;
string str;
int result;
public Form1()
{
InitializeComponent();
}
private void label8_Click(object sender, EventArgs e)
{
panel1.Visible = true;
}
private void label9_Click(object sender, EventArgs e)
{
panel2.Visible = true;
}
private void label13_Click(object sender, EventArgs e)
{
panel3.Visible = true;
try
{
con.Open();
cmd = new SqlCommand("select empid from cemploy order by empid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr.GetValue(0));
}
}
catch (Exception e3)
{
MessageBox.Show(e3.Message);
}
finally
{
con.Close();
}
}
private void label14_Click(object sender, EventArgs e)
{
panel4.Visible = true;
try
{
con.Open();
cmd = new SqlCommand("select deptid from department order by deptid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox2.Items.Add(dr.GetValue(0));
}
}
catch (Exception e6)
{
MessageBox.Show(e6.Message);
}
finally
{
con.Close();
}
}
private void label24_Click(object sender, EventArgs e)
{
panel5.Visible = true;
try
{
con.Open();
cmd = new SqlCommand("select empid from cemploy order by empid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox3.Items.Add(dr.GetValue(0));
}

}
catch (Exception e8)
{
MessageBox.Show(e8.Message);
}
finally
{
con.Close();
}
}
private void label25_Click(object sender, EventArgs e)
{
panel6.Visible = true;
}
private void label35_Click(object sender, EventArgs e)
{
panel7.Visible = true;

try
{
con.Open();
cmd = new SqlCommand("select empid from cemploy order by empid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox5.Items.Add(dr.GetValue(0));
}
}
catch (Exception e9)
{
MessageBox.Show(e9.Message);
}
finally
{
con.Close();
}
}
private void label36_Click(object sender, EventArgs e)
{
panel8.Visible = true;
try
{
con.Open();
cmd = new SqlCommand("select deptid from department order by deptid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox6.Items.Add(dr.GetValue(0));
}
}
catch (Exception e11)
{
MessageBox.Show(e11.Message);
}
finally
{
con.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
con = new SqlConnection("data source=.;Initial Catalog=wiproempdetails; uid=sa;password=wipro123");
}
private void button1_Click(object sender, EventArgs e)
{

DialogResult diares;
try
{
con.Open();
if (textBox5.Text == "null")
str = "insert into cemploy values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + "," + textBox5.Text + ")";
else
str = "insert into cemploy values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + ",'" + textBox5.Text + "')";
cmd = new SqlCommand(str, con);
result = cmd.ExecuteNonQuery();
diares = MessageBox.Show(result.ToString() + " Rows got inserted.", "RESULT", MessageBoxButtons.OK);
if (diares == DialogResult.OK)
{
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox1.Focus();
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
}
finally
{
con.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox1.Focus();
}
private void button3_Click(object sender, EventArgs e)
{
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox6.Focus();
}
private void button4_Click(object sender, EventArgs e)
{

DialogResult diares;
try
{
con.Open();
str = "insert into department values('" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
cmd = new SqlCommand(str, con);
result = cmd.ExecuteNonQuery();
diares = MessageBox.Show(result.ToString() + " Rows got inserted.", "RESULT", MessageBoxButtons.OK);
if (diares == DialogResult.OK)
{
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox6.Focus();
}
}
catch (Exception e2)
{
MessageBox.Show(e2.Message);
}
finally
{
con.Close();
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con.Open();

string str1 = "select fname,lname,salary,deptid from cemploy where empid= '" + comboBox1.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
textBox10.Text = dr.GetValue(0).ToString();
textBox11.Text = dr.GetValue(1).ToString();
textBox12.Text = dr.GetValue(2).ToString();
textBox13.Text = dr.GetValue(3).ToString();
}
catch (Exception e5)
{
MessageBox.Show(e5.Message);
}
finally
{
con.Close();
}

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

DialogResult diares, diares1;
try
{
con.Open();
diares1 = MessageBox.Show("Are you sure you want to update", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (diares1 == DialogResult.Yes)
{

str = "update cemploy set fname='" + textBox10.Text + "',lname='" + textBox11.Text + "',salary=" + textBox12.Text + ",deptid='" + textBox13.Text + "' where empid='" + comboBox1.Text + "'";
cmd = new SqlCommand(str, con);
result = cmd.ExecuteNonQuery();
diares = MessageBox.Show(result.ToString() + " Row(s) got updated.", "RESULT", MessageBoxButtons.OK);
if (diares == DialogResult.OK)
{
textBox10.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox13.Text = "";
textBox10.Focus();
}
}
else
{
string str1 = "select fname,lname,salary,deptid from cemploy where empid= '" + comboBox1.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
textBox10.Text = dr.GetValue(0).ToString();
textBox11.Text = dr.GetValue(1).ToString();
textBox12.Text = dr.GetValue(2).ToString();
textBox13.Text = dr.GetValue(3).ToString();
}
}
catch (Exception e4)
{
MessageBox.Show(e4.Message);
}
finally
{
con.Close();
}
}
private void button6_Click(object sender, EventArgs e)
{
comboBox1.ResetText();
textBox10.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox13.Text = "";
comboBox1.Focus();
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con.Open();

string str1 = "select deptname,depthead from department where deptid= '" + comboBox2.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
textBox15.Text = dr.GetValue(0).ToString();
textBox16.Text = dr.GetValue(1).ToString();

}
catch (Exception e5)
{
MessageBox.Show(e5.Message);
}
finally
{
con.Close();
}
}
private void button7_Click(object sender, EventArgs e)
{

DialogResult diares, diares1;
try
{
con.Open();
diares1 = MessageBox.Show("Are you sure you want to update?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (diares1 == DialogResult.Yes)
{
str = "update department set deptname='" + textBox15.Text + "',depthead='" + textBox16.Text + "' where deptid='" + comboBox2.Text + "'";
cmd = new SqlCommand(str, con);
result = cmd.ExecuteNonQuery();
diares = MessageBox.Show(result.ToString() + " Row(s) got updated.", "RESULT", MessageBoxButtons.OK);
if (diares == DialogResult.OK)
{
textBox15.Text = "";
textBox16.Text = "";
textBox15.Focus();
}
}
else
{
string str1 = "select deptname,depthead from department where deptid= '" + comboBox2.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
textBox15.Text = dr.GetValue(0).ToString();
textBox16.Text = dr.GetValue(1).ToString();
}
}
catch (Exception e7)
{
MessageBox.Show(e7.Message);
}
finally
{
con.Close();
}
}
private void button8_Click(object sender, EventArgs e)
{
comboBox2.ResetText();
textBox15.Text = "";
textBox16.Text = "";
comboBox2.Focus();
}
private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con.Open();

string str1 = "select fname,lname,salary,deptid from cemploy where empid= '" + comboBox3.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
label31.Text = dr.GetValue(0).ToString();
label26.Text = dr.GetValue(1).ToString();
label20.Text = dr.GetValue(2).ToString();
label15.Text = dr.GetValue(3).ToString();
label31.Visible = true;
label26.Visible = true;
label20.Visible = true;
label15.Visible = true;
}
catch (Exception e5)
{
MessageBox.Show(e5.Message);
}
finally
{
con.Close();
}
}

private void button9_Click(object sender, EventArgs e)
{

DialogResult diares, diares1;
try
{
con.Open();
diares1 = MessageBox.Show("Are you sure you want to delete", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (diares1 == DialogResult.Yes)
{
str = "delete from cemploy where empid='" + comboBox3.Text + "'";
cmd = new SqlCommand(str, con);
result = cmd.ExecuteNonQuery();
diares = MessageBox.Show(result.ToString() + " Row(s) got deleted", "RESULT", MessageBoxButtons.OK);
if (diares == DialogResult.OK)
{
label31.Text = "";
label26.Text = "";
label20.Text = "";
label15.Text = "";
}
comboBox3.ResetText();
comboBox3.Update();
cmd = new SqlCommand("select empid from cemploy order by empid", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox3.Items.Add(dr.GetValue(0));
}
}
else
{
string str1 = "select fname,lname,salary,deptid from cemploy where empid= '" + comboBox3.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
label31.Text = dr.GetValue(0).ToString();
label26.Text = dr.GetValue(1).ToString();
label20.Text = dr.GetValue(2).ToString();
label15.Text = dr.GetValue(3).ToString();
}
}
catch (Exception e4)
{
MessageBox.Show(e4.Message);
}
finally
{
con.Close();
}
}

private void button13_Click(object sender, EventArgs e)
{
try
{
con.Open();
string str1 = "select fname,lname,salary,deptid from cemploy where empid= '" + comboBox5.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
label46.Text = dr.GetValue(0).ToString();
label47.Text = dr.GetValue(1).ToString();
label48.Text = dr.GetValue(2).ToString();
label49.Text = dr.GetValue(3).ToString();
label46.Visible = true;
label47.Visible = true;
label48.Visible = true;
label49.Visible = true;
}
catch (Exception e10)
{
MessageBox.Show(e10.Message);
}
finally
{
con.Close();
}
}

private void button14_Click(object sender, EventArgs e)
{
try
{
con.Open();
string str1 = "select deptname,depthead from department where deptid= '" + comboBox6.Text + "'";
cmd = new SqlCommand(str1, con);
dr = cmd.ExecuteReader();
dr.Read();
label51.Text = dr.GetValue(0).ToString();
label52.Text = dr.GetValue(1).ToString();
label51.Visible = true;
label52.Visible = true;
}
catch (Exception e12)
{
MessageBox.Show(e12.Message);
}
finally
{
con.Close();
}
}

private void label50_Click(object sender, EventArgs e)
{
panel9.Visible = true;
try
{
ds = new DataSet();
cmd = new SqlCommand("select empid,fname,lname,salary,deptid from cemploy", con);
da = SqlDataAdapter(cmd);
da.Fill(ds);
cmobj = (CurrencyManager)this.BindingContext[ds.Tables[0]];
label68.DataBindings.Add("Text", ds.Tables[0], "empid");
label71.DataBindings.Add("Text", ds.Tables[0], "fname");
label72.DataBindings.Add("Text", ds.Tables[0], "lname");
label70.DataBindings.Add("Text", ds.Tables[0], "salary");
label69.DataBindings.Add("Text", ds.Tables[0], "deptid");
label68.Visible = true;
label71.Visible = true;
label72.Visible = true;
label70.Visible = true;
label69.Visible = true;
}
catch (Exception e13)
{
MessageBox.Show(e13.Message);
}
}

private void button15_Click(object sender, EventArgs e)
{
if (cmobj.Position > 0)
cmobj.Position = 0;
else
cmobj.Position -= 1;
}

private void button16_Click(object sender, EventArgs e)
{
if (cmobj.Position > 0)
MessageBox.Show("You are at the first element");
else
MessageBox.Show("You are at the first element");
}

private void button17_Click(object sender, EventArgs e)
{

if (cmobj.Position == (cmobj.Count - 1))
MessageBox.Show("You are at the last element");
else
cmobj.Position += 1;
}

private void button18_Click(object sender, EventArgs e)
{

if (cmobj.Position == (cmobj.Count - 1))
MessageBox.Show("You are at the last element");
else
cmobj.Position = cmobj.Count - 1;
}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
panel10.Visible = true;
}

private void button24_Click(object sender, EventArgs e)
{

cmd = new SqlCommand("select empid,fname,lname,salary,deptid from cemploy", con);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "ds1");
dg1.DataSource = ds;
dg1.DataMember = "ds1";
}

private void label59_Click(object sender, EventArgs e)
{
try
{
ds = new DataSet();
cmd = new SqlCommand("select deptid,deptname,depthead from department", con);
da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmobj = (CurrencyManager)this.BindingContext[ds.Tables[0]];
label75.DataBindings.Add("Text", ds.Tables[0], "deptid");
label74.DataBindings.Add("Text", ds.Tables[0], "deptname");
label73.DataBindings.Add("Text", ds.Tables[0], "depthead");
label75.Visible = true;
label74.Visible = true;
label73.Visible = true;
}
catch (Exception e14)
{
MessageBox.Show(e14.Message);
}
}

private void button20_Click(object sender, EventArgs e)
{
if (cmobj.Position > 0)
cmobj.Position = 0;
else
cmobj.Position -= 1;
}

private void button22_Click(object sender, EventArgs e)
{
if (cmobj.Position > 0)
MessageBox.Show("You are at the first element");
else
MessageBox.Show("You are at the first element");
}

private void button21_Click(object sender, EventArgs e)
{

if (cmobj.Position == (cmobj.Count - 1))
MessageBox.Show("You are at the last element");
else
cmobj.Position += 1;
}

private void button19_Click(object sender, EventArgs e)
{
if (cmobj.Position == (cmobj.Count - 1))
MessageBox.Show("You are at the last element");
else
cmobj.Position = cmobj.Count - 1;
}

private void textBox1_Leave(object sender, EventArgs e)
{
try
{
con.Open();
cmd = new SqlCommand("select count(*) from cemploy where empid=ltrim('" + textBox1.Text + "')", con);
dr = cmd.ExecuteReader();
dr.Read();
label76.Text = dr.GetValue(0).ToString();
if (textBox1.Text.Length == 0)
{
err1.SetError(textBox1, "Emp ID cannot be left blank");
textBox1.Focus();
}
else if (Convert.ToInt32(label76.Text) > 0)
{
err1.SetError(textBox1, "Employee ID already exists");
label76.Text = err1.GetError(textBox1);
slbl1.Text = label76.Text;
textBox1.Focus();
}
else
{
textBox2.Focus();
}
}
catch (Exception e15)
{
MessageBox.Show(e15.Message);
}
finally
{
con.Close();
}
}

private void textBox6_Leave(object sender, EventArgs e)
{
try
{
con.Open();
cmd = new SqlCommand("select count(*) from department where deptid=ltrim('" + textBox6.Text + "')", con);
dr = cmd.ExecuteReader();
dr.Read();
label76.Text = dr.GetValue(0).ToString();
if (textBox6.Text.Length == 0)
{
err1.SetError(textBox6, "Department ID cannot be left blank");
textBox6.Focus();
}
else if (Convert.ToInt32(label76.Text) > 0)
{
err1.SetError(textBox6, "Department ID already exists");
label76.Text = err1.GetError(textBox6);
slbl1.Text = label76.Text;
textBox6.Focus();
}
else
{
textBox7.Focus();
}
}
catch (Exception e16)
{
MessageBox.Show(e16.Message);
}
finally
{
con.Close();
}
}

private void textBox5_Leave(object sender, EventArgs e)
{
try
{
con.Open();
cmd = new SqlCommand("select count(*) from department where deptid=ltrim('" + textBox5.Text + "')", con);
dr = cmd.ExecuteReader();
dr.Read();
label76.Text = dr.GetValue(0).ToString();
if (textBox5.Text.Length == 0)
{
textBox5.Text = "null";
}
else if (Convert.ToInt32(label76.Text) == 0)
{
err1.SetError(textBox5, "Invalid Department ID");
label76.Text = err1.GetError(textBox5);
slbl1.Text = label76.Text;
textBox5.Focus();
}
else
{
button1.Focus();
}
}
catch (Exception e16)
{
MessageBox.Show(e16.Message);
}
finally
{
con.Close();
}
}
}
}

Cheers..!!
https://techiesss.blogspot.in/

C# Programs and SQL Queries

Tuesday, August 6, 2013

C# Code for Employee Management Application