1 / 14

Creating a dynamic search form with database paging

Creating a dynamic search form with database paging. Tony Rogerson SQL Server MVP Torver Computer Consultants. What we trying to do?. The presentation. Methods of coding the stored procedure Static SQL method Dynamic SQL method Pros and Cons of each approach SQL Injection/Security

sinjin
Télécharger la présentation

Creating a dynamic search form with database paging

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating a dynamic search form with database paging Tony Rogerson SQL Server MVP Torver Computer Consultants

  2. What we trying to do?

  3. The presentation • Methods of coding the stored procedure • Static SQL method • Dynamic SQL method • Pros and Cons of each approach • SQL Injection/Security • The execution cache • ASP.NET (VB.NET) interface • Overview • Coding specifics

  4. Static SQL method • Same stored procedure parameters create procedure paging_orders_method2_get @page_number smallint, @rows tinyint, @pages smallint = NULL OUTPUT, @EmployeeID int = NULL, @CustomerID nchar(10) = NULL

  5. Static SQL method – ISNULL select * from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID where o.CustomerID = isnull( @CustomerID, o.CustomerID ) and o.EmployeeID = isnull( @EmployeeID, o.EmployeeID ) order by o.ShippedDate desc Table 'Customers'. Scan count 5, logical reads 10. Table 'Employees'. Scan count 5, logical reads 10. Table 'Orders'. Scan count 1, logical reads 21.

  6. Static SQL method – AND/OR select * from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID where ( o.CustomerID = @CustomerID or o.CustomerID is null ) and ( o.EmployeeID = @EmployeeID or o.EmployeeID is null ) order by o.ShippedDate desc Table 'Customers'. Scan count 5, logical reads 10. Table 'Employees'. Scan count 5, logical reads 10. Table 'Orders'. Scan count 2, logical reads 40.

  7. Static SQL method – IF THEN ELSE if @EmployeeID is not null if @CustomerID is not null select .... from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID where o.CustomerID = @CustomerID and o.EmployeeID = @EmployeeID order by o.ShippedDate desc else select .... from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID where o.EmployeeID = @EmployeeID order by o.ShippedDate desc else select .... from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID where o.CustomerID = @CustomerID order by o.ShippedDate desc

  8. Static SQL - summary • Inflexible for optional parameters • IF then ELSE is efficient but will give a big plan which increases chances of a recompile. Greater maintenance. • ISNULL and AND/OR will give a general plan and perhaps a very bad plan for the parameter combination. • More secure then dynamic SQL.

  9. Dynamic SQL - coding if @EmployeeID is not null set @nwhere_clause = @nwhere_clause + ' and o.EmployeeID=@EmployeeID' if @CustomerID is not null set @nwhere_clause = @nwhere_clause + ' and o.CustomerID=@CustomerID' set @nsql = ' select o.OrderID, c.CompanyName, EmployeeName = e.LastName + '', '' + e.FirstName, ShippedDate = CONVERT( varchar(20), o.ShippedDate, 106 ) from Orders o left outer join Employees e on e.EmployeeID = o.EmployeeID left outer join Customers c on c.CustomerID = o.CustomerID ' + @nwhere_clause + ' order by o.ShippedDate desc ' EXEC sp_executesql @nsql, N'@EmployeeID int, @CustomerID nchar(10)', @EmployeeID, @CustomerID

  10. Dynamic SQL - summary • Specific plan for each iteration. • Plan is reused. • Use sp_executesql. • Parameterise instead of hard coding constants. • Be-careful of SQL Injection. • Security implications.

  11. Security • Use ADO.NET SqlCommand object instead of building a SQL string. • SQL Injection: remember to replace a single quote with two single quotes…REPLACE( <parm>, ''', '''' ) • Permission required on base tables used within the dynamic SQL block. However, you can create views over the base tables and permission them instead.

  12. Execution cache • Check master..syscacheobjects Pages usecount refcount1 1 1 o.CustomerID=@CustomerID 2 1 1 o.EmployeeID=@EmployeeID and o.CustomerID=@CustomerID 1 1 1 o.EmployeeID=@EmployeeID After 5 executions – check usecount – plan reuse! Pages usecount refcount1 5 1 o.CustomerID=@CustomerID 2 5 1 o.EmployeeID=@EmployeeID and o.CustomerID=@CustomerID 1 5 1 o.EmployeeID=@EmployeeID

  13. ASP.NET Part • Use ViewState( <var> ) to keep track of page number and maximum pages. • Can’t pick up the output parameter until the datareader has been closed.

  14. Questions

More Related