Page 222 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

13.2.8 Calling a Stored Procedure Notes

A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in
almost the exact same way as a standard SQL statement.

1. Create a new C# Windows Application project.
2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.
3. Double-click on the Form to generate the Form_Load event handler. Before entering any
code, add “using System.Data.SqlClient” at the top of the file.
Enter the Following Code

private void Form1_Load(object sender, System.EventArgs e)

{
SqlConnection conn = new SqlConnection(“Data

Source=localhost;Database=Northwind;Integrated Security=SSPI”);
SqlCommand command = new SqlCommand(“GetProducts”, conn);

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet ds = new DataSet();
adapter.Fill(ds, “Products”);

this.dataGrid1.DataSource = ds;

this.dataGrid1.DataMember = “Products”;

}
As you can see, calling a stored procedure in this example is exactly like how you would use
SQL statements, only that instead of specifying the SQL statement, you specify the name of the
stored procedure. Aside from that, you can treat it exactly the same as you would an ordinary
SQL statement call with all the advantages of a stored procedure.
13.2.9 Specifying Parameters

Most of the time, especially when using non-queries, values must be supplied to the stored
procedure at runtime. For instance, a @CategoryID parameter can be added to our GetProducts
procedure in order to specify to retrieve only products of a certain category. In SQL Server,
parameters are specified after the procedure name and before the AS clause.
Create procedure [dbo].[getproducts] (@categoryid int) as select productid, productname from
products where categoryid = @categoryid

Parameters are enclosed within parenthesis with the parameter name first followed by the data
type. If more than one parameter is accepted, they are separated by commas:

CREATE PROCEDURE [dbo].[SomeProcedure] (

@Param1 int,
@Param2 varchar(50),

LOVELY PROFESSIONAL UNIVERSITY 217