Part 2: Insert/Search Data With SQL Server

Previously I had written a program to select data from SQL Server.
Using this program you can insert data as well as you can search
data from SQL Server. The output will give a sample C# Form with
some menu items.Instructions:

1) At first you have to create a table 'emp_test' in SQL Server. The code will be:

create table emp_test
(
ecode int primary key,
ename varchar(20),
ephone char(7)
)

2) Then create a User DSN called 'test'.

3) Save the programme in your disk and compile the programme as:

c:\>csc form2.cs /r:System.dll/r:System.WinForms.dll /r:System.Drawing.dll/r:Microsoft.Win32.Interop.dll /r:System.Data.dll

4) Run the form2.exe file.

//This is the code for form2.cs
using System;
using System.WinForms;
using System.Drawing;
using System.IO;
using System.Data.ADO;

public class frm2 : Form
{
DialogResult s;
static TextBox tb1= new TextBox();
static TextBox tb2= new TextBox();
static TextBox tb3= new TextBox();

static Label lbl1 = new Label();
static Label lbl2 = new Label();
static Label lbl3 = new Label();
static Label lblFooter1 = new Label();
static Label lblFooter2 = new Label();
static Label lblFooter3 = new Label();

BorderStyle x= new BorderStyle();
Button button1 = new Button();
Button button2 = new Button();
Button button3 = new Button();

MainMenu myMainMenu = new MainMenu();

public frm2()
{
this.Text="Employee Details Data-Entry Form";
this.EventHandler();
}

public void InitializeForm()
{
this.BorderStyle = FormBorderStyle.FixedDialog;
this.MaximizeBox = false;
this.button1.Text="Accept";
this.button2.Text="Search";
this.button3.Text="Exit";
this.BackColor=Color.DarkKhaki;

this.button1.Location=new Point(20,150);
this.button2.Location=new Point(this.button1.Left + this.button1.Left + 60,this.button1.Top);
this.button3.Location=new Point(this.button1.Left + this.button1.Left + 140,this.button1.Top);
this.button1.BackColor=Color.Silver;
this.button2.BackColor=Color.Silver;
this.button3.BackColor=Color.Silver;
this.button1.TabIndex=3;

lbl1.Text ="Employee Code :";
lbl1.Location=new Point(8,30);

lbl2.Text ="Employee Name :";
lbl2.Location=new Point(8,60);

lbl3.Text="Employee Phone : ";
lbl3.Location=new Point(8,90);

lblFooter1.Text="";
lblFooter1.Location=new Point(10,180);
lblFooter1.Width=400;

lblFooter2.Text="";
lblFooter2.Location=new Point(10,210);
lblFooter2.Width=400;

lblFooter3.Text="";
lblFooter3.Location=new Point(10,240);
lblFooter3.Width=400;

tb1.Location=new Point(100,30);
tb1.BackColor=Color.Beige;
tb1.Width=20;

tb2.Location=new Point(100,60);
tb2.BackColor=Color.Beige;
tb2.Width=180;

tb3.Location=new Point(100,90);
tb3.BackColor=Color.Beige;
tb3.Width=60;

this.Controls.Add(this.button1);
this.Controls.Add(this.button2);
this.Controls.Add(this.button3);
this.Controls.Add(tb1);
this.Controls.Add(tb2);
this.Controls.Add(tb3);
this.Controls.Add(lbl1);
this.Controls.Add(lbl2);
this.Controls.Add(lbl3);
this.Controls.Add(lblFooter1);
this.Controls.Add(lblFooter2);
this.Controls.Add(lblFooter3);
this.StartPosition = FormStartPosition.CenterScreen;

MenuItem myDataMenuItem = myMainMenu.MenuItems.Add("&Data");
MenuItem myExitItem = myMainMenu.MenuItems.Add("E&xit");

myDataMenuItem.MenuItems.Add(new MenuItem("&Accept" , new EventHandler(button1_Click) , Shortcut.CtrlA));
myDataMenuItem.MenuItems.Add("-");
myDataMenuItem.MenuItems.Add(new MenuItem("&Search" , new EventHandler(button2_Click) , Shortcut.CtrlS));

myExitItem.MenuItems.Add(new MenuItem("&Quit Application" , new EventHandler(OnExit) , Shortcut.CtrlQ));

this.Menu = myMainMenu;
}

public static int Main(string[] args)
{
frm2 myForm= new frm2();
myForm.InitializeForm();
myForm.ShowDialog();
return 0;
}

protected void button1_Click(object sender, EventArgs e)
{
if((tb1.Text == "") || (tb2.Text == ""))
{
s=MessageBox.Show("Employee Code and Enployee Name is mandatory." , "Error" , MessageBox.OKCancel | MessageBox.IconExclamation);
if(String.Format("{0}" , s) == "Cancel")
{
this.Close();
}
else
{
tb1.Focus();
}
}
else
{
lblFooter1.Text = "Hello " + tb2.Text;
lblFooter2.Text = "Your Employee Code is " + tb1.Text;
if(tb3.Text == "")
{
lblFooter3.Text = "You don't have any Phone.";
}
else
{
lblFooter3.Text = "Your Phone No. is " + tb3.Text;
}
insert_data();
}
}

public void insert_data()
{
string command1=String.Format("select count(*) from emp_test where ecode = {0}", Int32.Parse(tb1.Text));
string command2=String.Format("insert into emp_test(ecode, ename, ephone) values({0},'{1}','{2}')", Int32.Parse(tb1.Text), tb2.Text, tb3.Text);
string source = "Provider=MSDASQL; User ID=sa;Initial Catalog=master; Data Source=test";

ADOCommand mCommand = new ADOCommand();
ADOConnection mConnection=new ADOConnection(source);

mConnection.Open();
mCommand.ActiveConnection=mConnection;
mCommand.CommandText=command1;
ADODataReader mReader1;
mCommand.Execute(out mReader1);

mReader1.Read();
if(mReader1.GetInt32(0) != 0)
{
MessageBox.Show("This Employee Code Already Present. Data Not Inserted", "Sorry", MessageBox.IconExclamation);
mReader1.Close();
mConnection.Close();
}
else
{
mReader1.Close();
mCommand.CommandText=command2;
ADODataReader mReader2;
mCommand.Execute(out mReader2);
MessageBox.Show("Your details is inserted " , "Congratulations" , MessageBox.OK);
tb1.Text="";
tb2.Text="";
tb3.Text="";
}
}

protected void button2_Click(object sender, EventArgs e)
{
if(tb1.Text == "")
{
MessageBox.Show("Please Enter Employee Code. Then Click SEARCH Button." , "Search Info", MessageBox.IconExclamation);
tb1.Focus();
}
else
{
search();
}
}

public void search()
{
string command1=String.Format("select count(*) from emp_test where ecode = {0}", Int32.Parse(tb1.Text));
string command2=String.Forma

t("
select * from emp_test where ecode = {0}", Int32.Parse(tb1.Text));

string source = "Provider=MSDASQL; User ID=sa;Initial Catalog=master; Data Source=test";
ADOCommand mCommand = new ADOCommand();
ADOConnection mConnection=new ADOConnection(source);

mConnection.Open();
mCommand.ActiveConnection=mConnection;
mCommand.CommandText=command1;
ADODataReader mReader1;
mCommand.Execute(out mReader1);

mReader1.Read();
if(mReader1.GetInt32(0) == 0)
{
MessageBox.Show("Record not found. Try Again …","Sorry");
mReader1.Close();
mConnection.Close();
tb1.Focus();
}
else
{
mReader1.Close();
mCommand.CommandText=command2;
ADODataReader mReader2;
mCommand.Execute(out mReader2);
mReader2.Read();

tb2.Text=mReader2.GetString(1);
tb3.Text=mReader2.GetString(2);

mReader2.Close();
mConnection.Close();
}
}

protected void button3_Click(object sender, EventArgs e)
{
this.Close();
}

public void EventHandler()
{
this.button1.Click+=new System.EventHandler(this.button1_Click);
this.button2.Click+=new System.EventHandler(this.button2_Click);
this.button3.Click+=new System.EventHandler(this.button3_Click);
}

private void OnExit(Object mySender , EventArgs myArgs)
{
this.Close();
}
}