Program to Insert,Update and Delete Records by using Stored Procedure.

Example to create a stored procedure in SQL Server to Insert   a record into EmpDetails Table.
 
Go the SQL Server Database Employee

1. Select the Database Employee
2. Double Click on Programmability
3. Select Stored procedure Option
4. Click on New Query option i.e., Tool Button
5. A Query window will be opened, Type the following Code

                   create procedure InsertRec

                        @PEmpId int,@PEName varchar(50),@PDoj datetime,@PSalary money

                          as

                          begin

                          insert into EmpDetails values(@PEmpId,@PEName,@PDoj,@PSalary)

                          end

Click on Execute Button

You Get the Message “Command(s) completed successfully.”..

Example to create a stored procedure in SQL Server to Update   a record in EmpDetails Table.

1. Go the SQL Server Database Employee
2. Select the Database Employee
3. Double Click on Programmability
4. Select Stored procedure Option
5. Click on New Query option i.e., Tool Button
6. A Query window Will be opened ,Type the following Code

                    ceate procedure UpdateRec

                         @rPEmpId int,@pEName varchar(50),@PDoj datetime,@PSalary money

                            as

                           begin

                            update EmpDetails set EName=@pEName,Doj=@PDoj,Salary=@PSalary where  ............................EmpId=@PEmpId

                            end

                              

      Click on Execute Button

      You Get the Message “Command(s) completed successfully.”..

Example to create a stored procedure in SQL Server to Delete   a record in EmpDetails Table.

1. Go the SQL Server Database Employee
2. Select the Database Employee
3. Double Click on Programmability
4. Select Stored procedure Option
5. Click on New Query option i.e., Tool Button
6. A Query window Will be opened ,Type the following Code

             create procedure DelRec

              @PEmpId int

               as

              begin

              delete from EmpDetails where EmpId=@PEmpId

               end

                              

    Click on Execute Button

    You Get the Message “Command(s) completed successfully.”..

Design:-

using System.Data.SqlClient; namespace FormNPQInsert {     public partial class FormIUDCSP : Form     { SqlConnection Con=new SqlConnection("Server=.;User Id=sa;Password=Admin123;DataBase=Employee");         SqlCommand Cmd;         public FormIUDCSP()         {             InitializeComponent();         }         private void btnInsert_Click(object sender, EventArgs e)         {             Cmd = new SqlCommand("InsertRec",Con);             Cmd.CommandType = CommandType.StoredProcedure;             Cmd.Parameters.AddWithValue("@PEmpId",txtEmpId.Text);             Cmd.Parameters.AddWithValue("@PEName",txtEName.Text);             Cmd.Parameters.AddWithValue("@PDoj",txtDoj.Text);             Cmd.Parameters.AddWithValue("@PSalary",txtSalary.Text);             Con.Open();             int r=Cmd.ExecuteNonQuery();             Con.Close();             MessageBox.Show(r+" Records Inserted Successfully");         }         private void btnUpdate_Click(object sender, EventArgs e)         {             Cmd = new SqlCommand("UpdateRec",Con);             Cmd.CommandType =CommandType.StoredProcedure;             Cmd.Parameters.AddWithValue("@PEmpId",txtEmpId.Text);             Cmd.Parameters.AddWithValue("@PEName",txtEName.Text);             Cmd.Parameters.AddWithValue("@PDoj",txtDoj.Text);             Cmd.Parameters.AddWithValue("@pSalary",txtSalary.Text);             Con.Open();             int r = Cmd.ExecuteNonQuery();             Con.Close();             MessageBox.Show(r+"  Record(s) Executed Updated Succssfully");         }         private void btnDelete_Click(object sender, EventArgs e)         {             Cmd = new SqlCommand("DelRec",Con);             Cmd.CommandType = CommandType.StoredProcedure;             Cmd.Parameters.AddWithValue("@PEmpId",txtEmpId.Text);             Con.Open();             int r = Cmd.ExecuteNonQuery();             Con.Close();             MessageBox.Show(r+"  Record(s) Deleted Successfully");         }         private void btnClear_Click(object sender, EventArgs e)         {             foreach (Control x in this.Controls)             {                 if (x is TextBox)                     x.Text = "";             }         }     } }