namespace TDApplication
{
public partial class Contactform : Form
{
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=TDApplication;Integrated Security=true;");
SqlCommand cmd;
//ID variable used in Updating and Deleting Record
int ID = 0;
//int ID = @tdtext;
//////int ID = tdtext.Text;
public Contactform()
{
InitializeComponent();
DisplayData();
ClearData();
}
//Insert Data
private void btn_Insert_Click(object sender, EventArgs e)
{
if (TitalcomboBox.Text != "" && NametxtBox.Text != "")
{
cmd = new SqlCommand("insert into TDContact ( Title,Name,H_Phone,C_Phone,W_Phone,Address,City,State,Pin_code,P_Email,W_Email,Gender,Birthday,Marriage) VALUES (@Title,@Name,@H_Phone,@C_Phone,@W_Phone,@Address,@City,@State,@Pin_code,@P_Email,@W_Email,@Gender,@Birthday,@Marriage)", con);
con.Open();
cmd.Parameters.AddWithValue("@Title", this.TitalcomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Name", this.NametxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@H_Phone", this.HPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@C_Phone", this.CPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@W_Phone", this.WPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@Address", this.AddrstxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@City", this.CitytxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@State", this.StatecomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Pin_code", this.PintxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@P_Email", this.PEmailtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@W_Email", this.WEmailtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@Gender", this.GendrcomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Birthday", this.dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@Marriage", this.dateTimePicker2.Value);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Inserted Successfully");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Provide Details!");
}
}
//Display Data in Listview
private void DisplayData()
{
listView1.View = View.Details;
SqlDataAdapter ada = new SqlDataAdapter("select ID, Title,Name,Address from TDContact", con);
DataTable dt = new DataTable();
ada.Fill(dt);
listView1.Items.Clear();
foreach (DataRow row in dt.Rows)
{
ListViewItem item = new ListViewItem(row["ID"].ToString());
item.SubItems.Add(row["Title"].ToString());
item.SubItems.Add(row["Name"].ToString());
item.SubItems.Add(row["Address"].ToString());
listView1.Items.Add(item);
}
}
//Clear Data
private void ClearData()
{
TitalcomboBox.Text = "";
NametxtBox.Text = "";
HPtxtBox.Text = "";
CPtxtBox.Text = "";
WPtxtBox.Text = "";
AddrstxtBox.Text = "";
CitytxtBox.Text = "";
StatecomboBox.Text = "";
PintxtBox.Text = "";
PEmailtxtBox.Text = "";
WEmailtxtBox.Text = "";
GendrcomboBox.Text = "";
dateTimePicker1.Text = "";
dateTimePicker2.Text = "";
ID = 0;
}
//Update Record
private void btn_Update_Click_1(object sender, EventArgs e)
{
cmd = new SqlCommand("update TDContact set Title=@Title,Name=@Name,H_Phone=@H_Phone,C_Phone=@C_Phone,W_Phone=@W_Phone,Address=@Address,City=@City,State=@State,Pin_code=@Pin_code,P_Email=@P_Email,W_Email=@W_Email,Gender=@Gender,Birthday=@Birthday,Marriage=@Marriage where tdtext.Text = @id ", con);
con.Open();
cmd.Parameters.AddWithValue("@id", tdtext.Text);
cmd.Parameters.AddWithValue("@Title", this.TitalcomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Name", this.NametxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@H_Phone", this.HPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@C_Phone", this.CPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@W_Phone", this.WPtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@Address", this.AddrstxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@City", this.CitytxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@State", this.StatecomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Pin_code", this.PintxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@P_Email", this.PEmailtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@W_Email", this.WEmailtxtBox.Text.Trim());
cmd.Parameters.AddWithValue("@Gender", this.GendrcomboBox.Text.Trim());
cmd.Parameters.AddWithValue("@Birthday", this.dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@Marriage", this.dateTimePicker2.Value);
cmd.ExecuteNonQuery();
con.Close();
if (ID != 0)
{
MessageBox.Show("Record Updated Successfully");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Update");
}
}
// Select Listview Index
private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count == 0)
return;
ListViewItem item = listView1.SelectedItems[0];
ID = Convert.ToInt32(listView1.SelectedItems[0].SubItems[0].Text);
TitalcomboBox.Text = listView1.SelectedItems[0].SubItems[1].Text;
NametxtBox.Text = listView1.SelectedItems[0].SubItems[2].Text;
//HPtxtBox.Text = listView1.SelectedItems[0].SubItems[3].Text;
//CPtxtBox.Text = listView1.SelectedItems[0].SubItems[4].Text;
//WPtxtBox.Text = listView1.SelectedItems[0].SubItems[5].Text;
//AddrstxtBox.Text = listView1.SelectedItems[0].SubItems[6].Text;
//CitytxtBox.Text = listView1.SelectedItems[0].SubItems[7].Text;
//StatecomboBox.Text = listView1.SelectedItems[0].SubItems[8].Text;
//PintxtBox.Text = listView1.SelectedItems[0].SubItems[9].Text;
//PEmailtxtBox.Text = listView1.SelectedItems[0].SubItems[10].Text;
//WEmailtxtBox.Text = listView1.SelectedItems[0].SubItems[11].Text;
//GendrcomboBox.Text = listView1.SelectedItems[0].SubItems[12].Text;
//dateTimePicker1.Text = listView1.SelectedItems[0].SubItems[13].Text;
//dateTimePicker2.Text = listView1.SelectedItems[0].SubItems[14].Text;
}
//Delete Record
private void btn_Delete_Click_1(object sender, EventArgs e)
{
if (ID != 0)
{
cmd = new SqlCommand("delete TDContact where tdtext.Text=@id", con);
con.Open();
cmd.Parameters.AddWithValue("@id", tdtext.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Deleted Successfully!");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Delete");
}
}
// hidden TextBox onclick event
private void tdtext_TextChanged(object sender, EventArgs e)
{
SqlDataAdapter ada = new SqlDataAdapter("select ID, Title,Name,H_Phone,C_Phone,W_Phone,Address,City,State,Pin_code,P_Email,W_Email,Gender,Birthday,Marriage from TDContact Where ID = '"+ tdtext.Text+"' ", con);
}
}
}