close

Вход

Забыли?

вход по аккаунту

CTEC2902 C_SHARP/Lec 19

код для вставкиСкачать
CTEC2902
Advanced Programming
Parameters
In
Stored Procedures
Parameters
1
CTEC2902
Advanced Programming
The story so far…
You know how to
•Use parameters in Function procedures
but ...
How to use parameters in SQL in stored procedures?
Let us find out…
Parameters
2
SQL Parameters
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘Leeds'
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘Hull'
Make the
warehouse location
a parameter
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘Coventry'
Parameters
3
SQL Parameters in Stored Procedures
CREATE PROCEDURE sp_Inventory_GetLocProductQuantity
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘Leeds’
Instead of hard-coding the data,
we place it in a parameter …
Parameters
4
SQL Parameters in Stored Procedures
CREATE PROCEDURE sp_Inventory_GetLocProductQuantity
@Location NVarChar(20)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @Location
Whatever value
you place here…
... gets used here
Q: how do you place values in parameters?
Parameters
5
Placing Values in Stored Procedure Parameters
Use the AddParameter method of clsSQLServer
•Create your data table
DataTable dtInventory = new DataTable;
•Connect to your SQL-Server database, via clsSQLServer
clsSQLServer DB = new clsSQLServer(“SomeDB.mdf”);
•Place your value in the named parameter
DB.AddParameter(“@Location”, “Leeds”);
•Run the stored procedure to select records for the given location
DB.Execute("sp_Inventory_GetLocProductQuantity");
•Save in your data table the records selected by stored procedure
dtInventory = DB.QueryResults;
Value
Same name as in the procedure
Parameters
6
Multiple SQL Parameters
CREATE PROCEDURE sp_Inventory_GetLocProductQuantity
@Location NVarChar(20),
@MinQ Int
As many as you need
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @Location
Used in SQL command
AND Quantity <= @MinQ
Q: how do we supply values for multiple parameters?
Parameters
7
Placing Values in Multiple Parameters
•Create your data table
DataTable dtInventory = new DataTable;
•Connect to your SQL-Server database, via clsSQLServer
clsSQLServer DB = new clsSQLServer(“SomeDB.mdf”);
•Place your values in the named parameters
DB.AddParameter(“@Location”, “Leeds”);
DB.AddParameter(“@MinQ”, 10);
•Run the stored procedure to select records for the given location
DB.Execute("sp_Inventory_GetLocProductQuantity");
•Save in your data table the records selected by stored procedure
dtInventory = DB.QueryResults;
It is good practice to match the order of parameters
Data types MUST match those in the procedure
Parameters
8
SQL Parameters Exercise
Given the table
Inventory (ID, Warehouse, Product, Quantity, Price, SupplierID)
Write a stored procedure, with parameters, to return the quantity and price
of any product at any warehouse.
Also show how you would supply actual values for the parameters defined.
CREATE PROCEDURE sp_Inventory_GetLocProductQuantity
@Product NVarChar(40),
@Warehouse NVarChar(20)
AS
SELECT Quantity, Price
FROM Inventory
WHERE Warehouse = @Warehouse
AND Product = @Product
•Place your values in the named parameters (in your code)
DB.AddParameter(“@Product”, “Window Frame 12x8”)
DB.AddParameter(“@Warehouse”, “Edinburgh”)
Parameters
9
SQL Parameters
Advice
Investigate how to use parameters in
•DELETE
•UPDATE
•INSERT INTO
Parameters
10
1/--страниц
Пожаловаться на содержимое документа