Friday, March 28, 2014

Demo Project

  • Open SQL Server .
  • Create a new database, with name as demo.
  • In that database, create a table named Customers, as shown below.
  • Open the previous demo application.
  • Add the menu item Database Commands Demo and design the menu as shown below:

  • Click  on  Project‖  menu  –> Add  Windows  Form‖.  Enter  the  form  name  as  Insertion. Click on Add.
  • Click on Project menu –>Add Windows Form‖. Enter the form name as Deletion. Click on Add.
  • Click  on  Project  menu  –>Add  Windows  Form‖.  Enter  the  form  name  as  Updation. Click on Add.
  • Double click on menu items one-by-one and write the following code in the Form1.cs:

Program for Menu Items

Form1.cs

private void insertionToolStripMenuItem_Click(object sender, EventArgs e)
{
   Insertion f = new Insertion();
   f.MdiParent = this;
   f.Show();
}
private void deletionToolStripMenuItem_Click(object sender, EventArgs e)
{
   Deletion f = new Deletion();
   f.MdiParent = this;
   f.Show();
}
private void updationToolStripMenuItem_Click(object sender, EventArgs e)
{
   Updation f = new Updation();
   f.MdiParent = this;
   f.Show();
}
  • Design the "Insertion" form as follows

Insert

Program for Insert Command Code 

Insertion.cs

using System.Data.SqlClient;
private void button1_Click(object sender, EventArgs e)
{
//Connection impl
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "data source=localhost;user id=sa;password=123;initial 
catalog=demo";
cn.Open();
//Command impl
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "insert into customers values(5,'Arun','Male',8000)";
cmd.Connection = cn;
int n = cmd.ExecuteNonQuery();
if (n > 0)
MessageBox.Show(n + " Row Inserted.");
else
MessageBox.Show("Insertion failed.");
cn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
//Connection impl
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "data source=localhost;user id=sa;password=123;initial 
catalog=demo";
cn.Open();
string gen;
if (radioButton1.Checked == true)
gen = "Male";
else
gen = "Female";
//Command impl
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "insert into customers values(" + textBox1.Text + ",' " + 
textBox2.Text + " ',' " + gen + " '," + textBox3.Text + ")";
cmd.Connection = cn;
int n = cmd.ExecuteNonQuery();
if (n > 0)
MessageBox.Show(n + " Row Inserted.");
else
MessageBox.Show("Insertion failed.");
cn.Close();

Note:  While  you  are  preparing  the  CommandText  with  user-entered  values,  to  remember  the 
concatenation syntax in the above example, remember the following syntax:" + textBox1.Text + "

Deletion

  • Design the "Deletion" form as follows:

Program for Deletion Command

Deletion.cs
using System.Data.SqlClient;
private void button1_Click(object sender, EventArgs e)
{
//Connection impl
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "data source=localhost;user id=sa;password=123;initial 
catalog=demo";
cn.Open();
//Command impl
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "delete from customers where customerid=" + textBox1.Text 
+ " ";
cmd.Connection = cn;
int n = cmd.ExecuteNonQuery();
//presentation logic
if (n > 0)
MessageBox.Show(n + " Row Deleted.");
else
MessageBox.Show("Deletion failed.");
cn.Close();

Update

  • Design the ―Updation‖ form as follows

Program for Update Command 

Updation.cs
using System.Data.SqlClient;
private void button1_Click(object sender, EventArgs e)
{
//Connection impl
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "data source=localhost;user id=sa;password=123;initial 
catalog=demo";
cn.Open();
string gen;
if (radioButton1.Checked == true)
gen = "Male";
else
gen = "Female"; 
//Command impl
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "update customers set customername= ' " + textBox2.Text + 
" ' ,gender= ' " + gen + " ' ,amount=" + textBox3.Text + " where customerid=" + 
textBox1.Text + " ";
cmd.Connection = cn;
int n = cmd.ExecuteNonQuery();
//presentation logic
if (n > 0)
MessageBox.Show(n + " Row Updated.");
else
MessageBox.Show("Updation failed.");
cn.Close();
}