Page 223 - Open Soource Technologies 304.indd

Open Source Technologies

Notes @Param3 varchar(50)

) AS

...
For our GetProducts example, if @CategoryID was supplied with the value 1, the query would
equate to:

Select productid, productname from products where categoryid = 1
Which would select all the products that belong to CategoryID 1 or the Beverages category. To
call the stored procedure, use Query Analyzer to execute:

exec GetProducts X
where X is the @CategoryID parameter passed to the stored procedure. To call the stored
procedure from within a C# application using 1 as the @CategoryID parameter value, use the
following code:

SqlConnection conn = new SqlConnection(“Data

Source=localhost;Database=Northwind;Integrated Security=SSPI”);

SqlCommand command = new SqlCommand(“GetProducts”, conn);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(“@CategoryID”, SqlDbType.Int).Value = 1;

SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();

adapter.Fill(ds, “Products”);

this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = “Products”;
Note that you must now specify the CommandType property of the SqlCommand object. The
reason we did not do this in the first example was that it is not required if the stored procedure
does not accept parameters. Of course, specifying the CommandType property even if it is not
needed may improve readability. The next line actually combines two lines in one:

command.Parameters.Add(“@CategoryID”,
SqlDbType.Int);command.Parameters[“@CategoryID”].Value = 1;

The first line of this segment specifies that the command object (which calls the GetProducts
stored procedure) accepts a parameter named @CategoryID which is of type SqlDbType.Int. The
type must be the same as the data type specified by the stored procedure. The second line of
this code segment gives the parameter the value 1. For simplicity, especially when using more
than one parameter, I prefer to combine to two lines into a single line:

command.Parameters.Add(“@CategoryID”, SqlDbType.Int).Value = 1;

218 LOVELY PROFESSIONAL UNIVERSITY