Using Stored Procedure with C#
What is Stored Procedure:
A Stored Procedure is a pre-compiled object that contains Sql statements, stored in the database. Stored Procedure can accept input parameters and returns values. So its principal advantage is the re-use in all project.Syntax of Stored Procedure:
The syntax of a Stored Procedure is simple:CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
For example this a stored procedure without parameters:
CREATE PROCEDURE dbo.display
AS
SELECT * FROM Employee
Example of Stored Procedure with C#:
In this example we will create a C# Windows Form application with Sql Server database in Visual Studio 2010. The database contains one Datatable Employee and two Stored Procedure DisplayEmp and AddEmp.First, let's create the Stored Procedure DisplayEmp. In Server Explorer we expand the database and we right-click on Stored Procedure and select Add new Stored Procedure.
Then we add the statement of the Stored Procedure:
CREATE PROCEDURE dbo.DisplayEmp
AS
SELECT * FROM Employee
And in DisplayButton click event we add this code to display list of Employee in the datagridview:
private void DisplayButton_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("DisplayEmp", con);
cmd.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
dataGridView1.DataSource = dt;
con.Close();
}
With the same way we create the second Stored Procedure AddEmp which is a Stored Procedure with parameters so its statements would be like:
CREATE PROCEDURE dbo.AddEmp
@code varchar(10),
@name varchar(10),
@address varchar(10)
AS
INSERT INTO Employee (Code,NameEmp,Address)
VALUES (@code,@name,@address)
RETURN
And in AddButton click event we add this code:
private void AddButton_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("AddEmp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@code", SqlDbType.VarChar).Value = textBox1.Text.Trim();
cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = textBox2.Text.Trim();
cmd.Parameters.AddWithValue("@address", SqlDbType.VarChar).Value = textBox3.Text.Trim();
cmd.ExecuteNonQuery();
MessageBox.Show("Employee added");
con.Close();
}
For more explanation watch the video below:
Using Stored Procedure with C#
Reviewed by Bloggeur DZ
on
09:52
Rating:
Reviewed by Bloggeur DZ
on
09:52
Rating:
Aucun commentaire: