Beginning ASP.NET 2

Performance

Finally

‘ dispose of resource

End Try

Your image code using this pattern is as follows:

Public Shared Sub GenerateThumbnail(ByVal SourceImagePath As String, _ ByVal TargetImagePath As String)

Dim newHeight As Short

Dim newWidth As Short

Dim sourceImage As Image = Nothing

Dim targetImage As Image = Nothing

Try

sourceImage = Image.FromFile(SourceImagePath)

newHeight = CShort(sourceImage.Height * 0.25) newWidth = CShort(sourceImage.Width * 0.25)

Dim cb As New Image.GetThumbnailImageAbort(AddressOf ThumbnailCallback)

Try

targetImage = sourceImage.GetThumbnailImage(newWidth, newHeight, _

cb, IntPtr.Zero) targetImage.Save(TargetImagePath, Imaging.ImageFormat.Gif)

Catch ex As Exception ‘ log exception

Finally

If targetImage IsNot Nothing Then targetImage.dispose()

End If End Try

Catch ex As Exception ‘ log exception

Finally

If sourceImage IsNot Nothing Then sourceImage.Dispose()

End If End Try

End Sub

You can immediately see this is a little hard to read. There are two Try/Catch blocks, one within the other. The outer one is for sourceImage — the original image. This is loaded from a file using Image.FromFile, and then the new width and height are calculated using the Height and Width properties of the source image; the new height and width are 25% of the original. After the new size is defined, a callback variable (cb) is created in case there is an error during the creation of the thumbnail — the GetThumbnailImage method will call the callback if an error occurs. We’re not actually handling any errors because we decided that it isn’t critical if thumbnails aren’t generated. If you have an application where it is critical to know about these errors, you could log the error in the callback routine.

529

Chapter 14

The inner Try/Catch block then surrounds targetImage, which is generated from sourceImage using GetThumbnailImage using the new width and height. This creates a new image based on the new size. Once generated, targetImage is then saved to a file as a GIF image.

The Finally blocks of each Try/Catch check that the Image object exists before disposing of the object, by calling the Dispose method.

Disposal with Using

The Using statement makes the preceding code much simpler, as shown here:

Public Shared Sub GenerateThumbnail(ByVal SourceImagePath As String, _ ByVal TargetImagePath As String)

Dim newHeight As Short

Dim newWidth As Short

Using sourceImage As Image = Image.FromFile(SourceImagePath)

newHeight = CShort(sourceImage.Height * 0.25) newWidth = CShort(sourceImage.Width * 0.25)

Dim cb As New Image.GetThumbnailImageAbort(AddressOf ThumbnailCallback) Using targetImage As Image = _

sourceImage.GetThumbnailImage(newWidth, newHeight, cb, IntPtr.Zero) targetImage.Save(TargetImagePath, Imaging.ImageFormat.Gif)

End Using End Using

End Sub

You can immediately see how much easier this is to read, as well as how much more sense it makes. The Using statement created the resource, which is then automatically disposed of when End Using is reached. The syntax for the Using statement is as follows:

Using resource

‘ code that uses the resource End Using

What happens is that when the End Using is reached the resource is disposed of immediately; there’s no waiting for the Garbage Collector to dispose of it. In the code you have:

Using sourceImage As Image = Image.FromFile(SourceImagePath)

This is similar to the Dim statement in that it declares a variable, sourceImage, and assigns it a value. Unlike the Dim statement, though, the variable is disposed of as soon as the End Using is reached. So as soon as End Using is done, the sourceImage variable is dead and gone. In fact, because its scope is defined as part of the Using statement, sourceImage isn’t accessible outside of the Using ... End Using code block.

You only need to explicitly dispose of resources if they are deemed expensive resources, such as files (images, text files, and so forth) or graphics resources that use lots of memory. For normal variables and objects, even those you might think take a lot of memory such as a DataSet, should not be disposed of explicitly.

530

Performance

Database Connections

Fetching data from a database can be done automatically by way of the data source controls, or manually though the objects in the System.Data namespaces. One of these objects applies to connecting to a database — for SQL Server or SQL Server Express that object is the SqlConnection. In general, databases are limited to the number of connections they can have; each connection takes resources and may stop another application from connecting, so these should be used as sparingly as possible, and only kept open for as short a time as possible. The general rule is to open the connection as late as possible, fetch the data, and close the connection as soon as possible, disposing of the connection once done. The Using statement is excellent for this:

Using conn As New SqlConnection(“. . .”) ‘ code that uses the connection

End Using

Here the Using statement keeps track of the connection object, conn, which is closed and disposed of when the End Using is reached.

In general, if you are manually creating connections, you should dispose of them as soon as you are finished. If you are using the data source controls, object disposal is handled automatically for you.

Stored Procedures

In the examples you’ve seen so far in this book the data has been fetched either using a data source control such as the SqlDataSource or using code, where a SqlCommand or SqlDataAdapter were used. In all cases the command that fetched the data, the SQL, was entered directly, such as:

SELECT [ProductID], [Name], [Description], [Price], [PictureURL] FROM [Products]

Now there’s nothing intrinsically wrong with this, it’s a standard SQL statement that works fine. However, it’s not the fastest way to fetch data, because the database has to work out exactly how it is going to fetch the data when the command is executed. This involves creating what’s known as an execution plan — a plan of how the SQL is going to be executed, how tables are to be joined if there are multiple tables, which indexes to use, and so on, and it does this every time. Once the plan has been created the stored procedure is also compiled and the compiled copy is the one that is executed.

A much better solution would be to work out the execution plan and store it for subsequent use. So when a query is executed the plan is ready and doesn’t need recalculating. You can do this by way of stored procedures, which are a way of wrapping up SQL into an easily manageable form; it’s a bit like a procedure or a function in that the SQL to be executed can be wrapped in a stored procedure and the stored procedure name used to execute it. Consider this SqlDataSource:

<asp:SqlDataSource id=”SqlDataSource1” runat=”server” ConnectionString=”<%$ConnectionStrings:WroxUnited%>”

SelectedCommand=”SELECT [ProductID], [Name], [Description], [Price], [PictureURL] FROM [Products]”

</asp:SqlDataSource>

531

Chapter 14

Using a stored procedure, the code would look like this:

<asp:SqlDataSource id=”SqlDataSource1” runat=”server” ConnectionString=”<%$ConnectionStrings:WroxUnited%>” SelectedCommand=”usp_Products” SelectCommandType=”StoredProcedure”

</asp:SqlDataSource>

From the code perspective this is already better for two reasons: It makes the code neater and easier to read; and it abstracts the SQL into a central place, the database. Having the SQL in the database is good because it’s the natural place for it; it’s code that deals directly with the tables and columns. It also means your code is easier to manage because you know that all of the SQL is in one place. A sensible naming scheme means stored procedures are easy to locate; we’ve used the naming scheme of usp_ followed by the name of the table. The prefix usp_ is a common one and denotes User Stored Procedure — many of system stored procedures that SQL Server provide are prefixed by sp_, so adding the u makes it obvious which procedures are ours and which are the server’s. The other addition when using stored procedures is the SelectCommandType attribute, which tells the SqlDataSource that the command being issued is a stored procedure, rather than a textual SQL statement.

The syntax for creating a stored procedure is as follows:

CREATE PROCEDURE ProcedureName

AS

SqlStatement

where ProcedureName is the name of the procedure (usp_Products) and SqlStatement is the SQL statement that will be run when the stored procedure is called. So, how do you actually go about creating stored procedures, and what exactly do they contain? You give this a go in the following Try It Out.

Try It Out

Creating and Using Stored Procedures

1.In the WroxUnited VWD application for this chapter (Chapters/Begin/Chapter14/ WroxUnited)e, load the Shop.aspx file and change the SelectCommand to usp_Products.

2.Add the following new attribute to the SqlDataSource:

SelectCommandType=”StoredProcedure”

3.Save the file.

532

Performance

4.Select the Database Explorer tab and expand WroxUnited.mdf, which will appear under Data Connections (see Figure 14-1).

Figure 14-1

5.Right-click Stored Procedures and select Add New Stored Procedure from the menu.

6.Modify the procedure so that it looks like this:

CREATE PROCEDURE dbo.usp_Products

AS

SELECT ProductID, [Name], Description, Price, PictureURL

FROM Products

ORDER BY [Name]

7.Save and close the procedure.

8.Right-click WroxUnited.mdf and select Close Connection from the menu. This ensures that when you run the application you don’t receive an error telling you that the data file couldn’t be opened because it is in use by another process.

9.On the Wrox United web site, navigate to the Shop page to confirm that the page displays as you expect it to. You’ll see no change from the user perspective, because stored procedures are a programming concept, and apart from speed, have no impact on what the user sees.

533

Chapter 14

How It Works

The first thing to look at is the stored procedure itself:

CREATE PROCEDURE dbo.usp_Products

AS

SELECT ProductID, [Name], Description, Price, PictureURL

FROM Products

ORDER BY [Name]

So you can see that the name of your stored procedure is usp_Products. The dbo part indicates the owner of the procedure, and in this case the owner is dbo — a synonym for the database owner. Anything after the AS statement is the actual procedure itself, and yours simply consists of a SELECT statement. In fact, this is the same SELECT statement that was generated by VWD when the SqlDataSource was created, with the addition of an ORDER BY clause to order the results by the product name. This is actually a good way to combine the ease of data source controls with stored procedures; let the designer create the control, then copy the SQL statement into a stored procedure and use the procedure name in the data source control. The column Name is wrapped within square brackets because Name is a keyword with in SQL. The stored procedure works without the brackets, but using them tells SQL Server that this use of Name is a column, and saves SQL Server having to work that out on its own.

The procedure name starts with usp_ for usability and historic reasons. You don’t want your stored procedure to have the same name as the table, but keeping the table name is useful, because it helps to identify which stored procedures are related to which tables. In SQL Server, the system stored procedures start with sp_ (an acronym for stored procedure), so using that as a prefix would confuse your stored procedures with the system ones. So usp_ (an acronym for user stored procedure) is generally used instead. This isn’t a requirement, but you’ll find that it is a common practice.

SQL is both case and context insensitive, meaning no line continuation characters are required.

In the ASP.NET page, the SQL statement has simply been replaced by the name of the stored procedure. By default, the SqlDataSource control expects a SQL statement, so when using a stored procedure you have to add the SelectCommandType attribute, setting its value to StoredProcedure. This tells ASP.NET that the command isn’t a SQL statement to be executed, but that the named stored procedure should be used instead.

Modifying Stored Procedures

To modify a stored procedure you simply open it (double-click or right-click and select Open from the menu) from within the Database Explorer, where you will be presented with the following:

ALTER PROCEDURE dbo.usp_Products

AS

SELECT ProductID, Name, Description, Price, PictureURL

FROM Products

ORDER BY Name

Notice that CREATE PROCEDURE has been replaced with ALTER PROCEDURE. The CREATE statement is only used when creating stored procedures, and subsequently ALTER is used. Once you’ve completed your changes, perhaps changing the columns being selected, you can just save the file, and the procedure in the database will be updated. In fact, just the matter of saving a new query changes the CREATE into ALTER.

534

Performance

Using the Query Builder

If you don’t want to use the copy-and-paste method for creating SQL statements, you can use the Query Builder. This can be accessed when creating or modifying a stored procedure by selecting the Insert SQL item from the right mouse menu — just click anywhere in the stored procedure editor window to get the menu. You first see a window allowing you to select the table (see Figure 14-2).

Figure 14-2

You can double-click a table to add it, or select the table and use the Add button. When you have selected your table and closed the window you see the Query Builder, shown in Figure 14-3.

Figure 14-3

535

Chapter 14

The Query Builder is an excellent way of learning SQL, because it presents you with an easy-to-understand graphical way of designing queries, and shows the actual SQL statement. When the Query Builder is closed, the SQL is inserted into the stored procedure; it doesn’t update the existing SQL but inserts the new SQL instead.

If you want to use the Query Builder to edit the existing SQL in a stored procedure, you need to place the cursor within the SQL block when editing the procedure — this block is outlined in blue — and select the Design SQL Block from the right mouse menu. When you select this option the Query Builder comes up right away with the existing SQL in place.

Modifying Data and Parameters

The query and stored procedure shown earlier only fetch data, but three other types of queries can be run: updates, inserts, and deletes. Just like SELECT queries these update queries can also be converted into stored procedures. As an example, take a look at the administrative Edit News page, where the SqlDataSource is defined like so:

<asp:sqldatasource id=”SqlDataSource2” runat=”server” ConnectionString=”<%$ ConnectionStrings:WroxUnited %>” SelectCommand=”SELECT * FROM [News] WHERE [NewsID] = @NewsID”

UpdateCommand=”UPDATE [News] SET [DateToShow] = @DateToShow, [Description] = @Description, [PictureURL] = @PictureURL, [Category] = @Category, [Title] = @Title WHERE [NewsID] = @NewsID”

InsertCommand=”INSERT INTO [News] ([DateToShow], [Description], [PictureURL], [Category], [Title]) VALUES (@DateToShow, @Description, @PictureURL, @Category, @Title)”

DeleteCommand=”DELETE FROM [News] WHERE [NewsID] = @NewsID” OnDeleted=”SqlDataSource2_Deleted” OnInserted=”SqlDataSource2_Inserted” OnUpdated=”SqlDataSource2_Updated”>

The important parts are the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand attributes, which define the SQL used when fetching and modifying data. There are also a number of parameters, which provide the mapping from the ASP.NET controls to the queries:

<DeleteParameters>

<asp:Parameter Type=”Int32” Name=”NewsID”></asp:Parameter> </DeleteParameters>

<UpdateParameters>

<asp:Parameter Type=”DateTime” Name=”DateToShow”></asp:Parameter> <asp:Parameter Type=”String” Name=”Description”></asp:Parameter> <asp:Parameter Type=”String” Name=”PictureURL”></asp:Parameter> <asp:Parameter Type=”String” Name=”Category”></asp:Parameter> <asp:Parameter Type=”String” Name=”Title”></asp:Parameter> <asp:Parameter Type=”Int32” Name=”NewsID”></asp:Parameter>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameter Name=”NewsID” Type=”Int32” ControlID=”GridView1” PropertyName=”SelectedValue”></asp:ControlParameter> </SelectParameters>

<InsertParameters>

<asp:Parameter Type=”DateTime” Name=”DateToShow”></asp:Parameter>

536

Performance

<asp:Parameter Type=”String” Name=”Description”></asp:Parameter> <asp:Parameter Type=”String” Name=”PictureURL”></asp:Parameter> <asp:Parameter Type=”String” Name=”Category”></asp:Parameter> <asp:Parameter Type=”String” Name=”Title”></asp:Parameter>

</InsertParameters>

When converting these queries to stored procedures, the parameters can remain the same, because the stored procedures will still require information to be passed into them. So in the next Try It Out, you convert this page and see how the parameters and other queries work.

Try it Out

Modifying Data and Parameters

1.Open the Admin\EditNews.aspx page and find the SqlDataSource2 control.

2.In the Database Explorer, expand the WroxUnited.mdf file and add a new stored procedure (select the Stored Procedures item in the Database Explorer and from the right mouse menu select Add New Stored Procedure).

3.Delete the existing contents of the new stored procedure and replace them with the following:

CREATE PROCEDURE dbo.usp_NewsByID @NewsID int

AS

SELECT * FROM News WHERE NewsID = @NewsID

You can copy and paste the SQL from EditNews.aspx if you like. You don’t have to worry about the square brackets — these were placed around column names when the data source control was created and ensure that the column names are not interpreted as keywords.

4.Save and close the procedure.

5.Create another new procedure, replacing the default text with the following code:

CREATE PROCEDURE dbo.usp_NewsUpdate @DateToShow datetime, @Description text,

@PictureUrl varchar(50), @Category varchar(50), @Title varchar(50), @NewsID int

AS

UPDATE News

SET DateToShow = @DateToShow, Description = @Description, PictureUrl = @PictureUrl, Category = @Category, Title = @Title

WHERE NewsID = @NewsID

6.Save and close the procedure.

7.Create another new procedure, replacing the default text with this code:

CREATE PROCEDURE dbo.usp_NewsInsert @DateToShow datetime, @Description text,

537

Chapter 14

@PictureUrl varchar(50), @Category varchar(50), @Title varchar(50)

AS

INSERT INTO News(DateToShow, Description, PictureUrl, Category, Title)

VALUES (@DateToShow, @Description, @PictureUrl, @Category, @Title)

8.Save and close the procedure.

9.Create another new procedure, replacing the default text with the following:

CREATE PROCEDURE dbo.usp_NewsDelete @NewsID int

AS

DELETE FROM News WHERE NewsID = @NewsID

10.Save and close the procedure.

11.In the Database Explorer, close the database connection by right-clicking WroxUnited.mdf and selecting the Close Connection menu item.

12.In EditNews.aspx change the SQL commands to their appropriate stored procedures, using the following table as a guide:

Command	Stored Procedure
SelectCommand	usp_NewsByID
UpdateCommand	usp_NewsUpdate
InsertCommand	usp_NewsInsert
DeleteCommand	usp_NewsDelete

13.Add the following attributes to SqlDataSource2:

SelectCommandType=”StoredProcedure”

UpdateCommandType=”StoredProcedure”

InsertCommandType=”StoredProcedure”

DeleteCommandType=”StoredProcedure”

14.Save the page and run the application to prove that the page still works as expected; like the previous example you won’t see anything different when running the application. To edit the news items you’ll have to be a user in one of the following roles: Reporter, Owner, Manager, or Admin. You can find out login details by using the “help” link on the login box when not logged in to the site, but you can use dave, dan, jim, chrish, chrisu, or john.

How It Works

From the first example you can see that the SQL statements in the data source control have been replaced by names of stored procedures, and the appropriate CommandType attributes set to indicate this. The stored procedures are rather different from the one used in the first example, though, so take a look at some of the specifics, starting with the one to fetch a row to be edited:

538