Making database connectivity in WindowsForm is almost same as we do it in WebForms. If you how to do it with WebForms then this is just very easy for you to understand.
Open a new windows form application. You will see Form1.cs [Design] is opened. On the windows form add the necessary components. I have used some labels, corresponding textboxes and some buttons to submit the data entered in the textboxes. I have used some labels to display success or failure messages.
Now right click on the form and click on View Code. One more page will be opened with the name Form1.cs. Don't get confused between the names of the two as one is the Designer page and latter one is the implementation or coding page.
After that you can either copy & paste the below code as it is or you can just copy some snippets which are required. If you copy the whole code, make sure that you have added all the controls to your form which I have used and the names should also be the same.
Note: Many times I have noticed that we copy the code from somewhere and even add the same controls with the same name but still our application does not run successfully. That is because you have just added the controls and pasted their corresponding events, while in background we have not associated the events with the controls. So to run your application successfully you should map the events to their respective controls. You can do this by using the property window of your Visual Studio.
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;
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 WindowsFormsApplication2
{
public partial class Form1 : Form
{
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
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;
}
private void label36_Click(object sender, EventArgs e)
{
panel8.Visible = true;
}
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();
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();
}
}
}
}
Here, in the above code few things are important. First, I have declared a SQlConnection class object globally and on the Form1_Load event I am assigning the value of ConnectionString to it. In ConnectionString we have to give the values to 4 attributes: data source=ServerName; Initial Catalog=DatabaseName; uid=sa; password=password. If you are using windows authentication for the SQL Server then replace uid & password with Trusted_Connection=Yes. You will also notice that every time I am making a connection to the database I take a string variable (QueryString) and assign the SQL Query to it and if I want to take values from some textbox I add the textboxName.Text. So this completes my SQL Query.
After making my SQL Query I take SqlCommand object and initialize it. In the arguments I have given the QueryString and SQLConnection object. This SqlCommand object helps in executing the SQL Query specified in the QueryString.
I have also used DataReader class object in order to read the data which we are getting from SQL Server after our Query is executed. To execute the Query I am using cmd.ExecuteReader(). And to write the data to the database I have used cmd.ExecuteNonQuery(). ExecuteNonQuery() returns the number of rows effected after executing the query.
Note: Don't forget to Open & Close the SqlConnection object before and after every communication with the database respectively.
No comments:
Post a Comment