Example to create a stored procedure in SQL Server to Insert a record into EmpDetails Table.
Go the SQL Server Database Employee
- Select the Database Employee
- Double Click on Programmability
- Select Stored procedure Option
- Click on New Query option i.e., Tool Button
- 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.
- Go the SQL Server Database Employee
- Select the Database Employee
- Double Click on Programmability
- Select Stored procedure Option
- Click on New Query option i.e., Tool Button
- 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.
- Go the SQL Server Database Employee
- Select the Database Employee
- Double Click on Programmability
- Select Stored procedure Option
- Click on New Query option i.e., Tool Button
- A Query window Will be opened ,Type the following Code
create procedure DelRec
@PEmpId int
as
begin
delete from EmpDetails where EmpId=@PEmpId
end
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 = "";
}
}
}
}
|
No comments:
Post a Comment