MySQL Stored procedure Tutorial

Introduction to SQL Stored Procedures

Definition of stored procedure
A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.
A stored procedure, which calls itself, is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. Check your version of MySQL before using recursive stored procedure
Stored procedure in MySQL
MySQL certainly is the most open source RDBMS which is widely used by both community and enterprise but during the first decade of its existence, it did not support stored procedure, trigger, event…Since MySQL version 5.0, those features has been added to MySQL database engine to allow MySQL more flexible and powerful. Therefore before start the tutorial series about Stored procedure, it is required that you have MySQL version > 5.x installed in your computer.
Stored procedures advantages
Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application
Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.
Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported in stored procedure in all programs.
Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.Beside those advantages, stored procedure still has its own disadvantages which are bellow
Stored procedures disadvantages
Stored procedure make the database server high load in both memory for and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the role of it.
Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++…
You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.
Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase.
Stored procedure has it own advantages and disadvantages as mentioned above. So when developing application you should balance between them to choose whether to use stored procedure or not. The following tutorial we will guide you how to leverage stored procedure in your database programming task with a couple of practical examples.

Getting Started with MySQL Stored Procedures
In this tutorial, you will write the first simple stored procedure and invoke it from command line of MySQL.
Writing the first stored procedure
The first stored procedure is very simple. It retrieves all products from products table. First let’s take a look at the stored procedure source code bellow:
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;

The first command you see is DELIMITER //. This command is not related to the stored procedure. DELIMITER statement is used to change the standard delimiter (semicolon) to another, in this case the delimiter is changed to //, so you can have multiple SQL statements inside stored procedure which can separate by the semicolon. After the END keyword we use delimiter // to show the end of the stored procedure. The last command changes the delimiter back to the standard one (semicolon).
In order to create a new stored procedure you use CREATE PROCEDURE statement. After the CREATE PROCEDURE statement you can specify the name of stored procedure, in this case it is GetAllProducts.
The body part of the stored procedure started with between BEGIN and END block. You can write declarative SQL code here. We can analysis more details of each part later. Now we have created a new stored procedure, but we also need to know however to invoke it in program or in command line of MySQL.
Calling the stored procedure
In order to invoke a stored procedure we use the following SQL command:
CALL STORED_PROCEDURE_NAME()
For example, we can call the stored procedure we have created like this
CALL GetAllProducts();
We get all products in the products database table.
In this tutorial, you’ve learn how to change the delimiter by using DELIMITER statement. It allows you to type multiple SQL statements inside stored procedure. You’ve also learn how to write a simple stored procedure by using CREATE PROCEDURE statement and call it from command line by using CALL statement.

Declaring variables
Variables are used in stored procedure to store the immediate result. You can declare a variable by the following syntax:
DECLARE variable_name datatype(size) DEFAULT default_value;
Followed DECLARE statement is the variable name. The variable name should follow the naming convention and should not be the same name of table or column in a database. Next you can specify the data type of the variable, it can be any primitive type which MySQL supports such as INT, VARCHAR and DATETIME…along with the data type is the size of the variable. When you declare a variable, its initial value is NULL. You can also assign the default value for the variable by using DEFAULT statement.  For example, we can define a variable name total_sale with the data type INT and default value is 0 as follows:
DECLARE total_sale INT DEFAULT 0
To declare two or more variables with the same data type we can use only just one DECLARE such as:
DECLARE x, y INT DEFAULT 0
We declared two variables x and y with the same data type INT and their default value is zero.
Assigning variables
Once you declared a variable, you can start using it. To assign other value to a variable you can use SET statement, for example:
DECLARE total_count INT DEFAULT 0
SET total_count = 10;
The total_count variable’s value now is ten (10).
Beside SET statement, we can use SELECT … INTO to assign a query result to a variable.
DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products
In the example above, we declare a variable total_products and initialize its value to zero. Then we use SELECT … INTO statement to assign the variable total_products with the total products in products database table.
Variables scope
A variable has its own scope. If you declare a variable inside a stored procedure, it will be out of scope when the END of stored procedure reached. If you defined a variable inside block BEGIN/END inside a stored procedure it will be out of scope if the END reached. You can declare two variables or more variables with the same name in different scopes; the variable only is effective in its scope.
A variable with the ‘@’ at the beginning is session variable. It exists until the session end.

 » MySQL Stored Procedure » Stored Procedure Parameters
Stored Procedure Parameters
In this tutorial, you will learn how to write stored procedures with parameters. We will also give you a couple of stored procedure examples to help you understand more about parameters in stored procedures
Almost stored procedures you develop require parameters. Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.
IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter. Suppose you pass parameter Id, which is equal 10, into stored procedure GetAll(Id), after executing the stored procedure the value of Id is still 10 even though the GetAll stored procedure can change the value of it.
OUT this mode indicates that stored procedure can change this parameter and pass back to the calling program.
INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.
The syntax of defining a parameter in stored procedure is as follows:
MODE param_name param_type(param_size)
MODE could be IN, OUT or INOUT depending on the purpose of parameter you specified.
param_name is the name of the parameter. The name must not be the same as the column name of tables and following naming convention. Followed the parameter name is the type of parameter and its size.
Each parameter is separated by a comma if the stored procedure more than one parameter.

Let’s practice with following examples to understand more.
The first example is a stored procedure to get all offices in a country. Here is the SQL source code:
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT city, phone
FROM offices
WHERE country = countryName;
END //
DELIMITER ;

As you can see we use countryName as the IN parameter with its type is varchar and its size is 255. In body part of stored procedure, we retrieve all offices which its country is the countryName.
Suppose you want to retrieve all office in USA, just pass the value to the stored procedures like this:
CALL GetOfficeByCountry('USA')
It returns all offices in USA
To get all offices in France just call pass France to the stored procedure like following:
CALL GetOfficeByCountry(‘France’)
It returns all offices in USA
The second example, we will write a store procedure to count the order in a specific order status such as shipped, resolved, cancelled, on hold, disputed or in process. Here is the stored procedure
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
IN orderStatus VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(orderNumber)
INTO total
FROM orders
WHERE status = orderStatus;
END$$
DELIMITER ;

The CountOrderByStatus stored procedure has two parameters:
orderStatus parameter is IN parameter; we pass order status such as shipped or on hold in to get the number of it
total parameter is the OUT parameter which we use to get the total order by a specified status back.
So to get number of shipped orders, we just perform following statements
CALL CountOrderByStatus('Shipped',@total);
SELECT @total AS total_shipped;
To get number of in process we do the same as above
CALL CountOrderByStatus('in process',@total);
SELECT @total AS total_in_process;
 
In the third procedure, we will demonstrate the INOUT parameter. The stored procedure capitalizes all words in a string and returns it back to the calling program. The stored procedure source code is as follows:
DELIMITER $$
CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024))
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
DECLARE outstr VARCHAR(1000) DEFAULT str;
WHILE i <= CHAR_LENGTH(str) DO
SET myc = SUBSTRING(str, i, 1);
SET pc = CASE WHEN i = 1 THEN ' '
ELSE SUBSTRING(str, i - 1, 1)
END;
IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
SET outstr = INSERT(outstr, i, 1, UPPER(myc));
END IF;
SET i = i + 1;
END WHILE;
SET str = outstr;
END$$
DELIMITER ;
Here is the usage of the Capitalize stored procedure
SET @str = 'mysql stored procedure tutorial';
CALL Capitalize(@str);
SELECT @str;

And the @str value is ‘Mysql Stored Procedure Tutorial’
Conditional Control in Stored Procedure
Conditional control enables you to execute the code based on the value of an expression or a combination of expression using logical operators. MySQL supports two conditional control statement such as IF and CASE.
The IF Statement
The syntax of IF statement is simple as follows:
IF expression THEN commands
[ELSEIF expression THEN commands]
[ELSE commands]
END IF;
The commands associated with IF or ELSEIF or ELSE only executed when the expression is evaluated as TRUE. One of the common trap of IF statement is NULL value; When the expression is evaluated as NULL it is neither TRUE nor FALSE. Here are several combination of IF statement
IF expression THEN commands
END IF;
IF expression THEN commands
ELSE commands
END IF;
IF expression THEN commands
ELSEIF expression THEN commands
ELSE commands
END IF;
You can have IF statement nested with other IF statements.
The CASE Statement
When multiple conditions are used with IF statement the code is not easy to read. At this time, the CASE can be used to make the code clearer. The syntax of  the CASE statement is as follows:
CASE
WHEN expression THEN commands
…
WHEN expression THEN commands
ELSE commands
END CASE;

Loop in Stored Procedures
MySQL stored programming language supports loop which allows you to process commands iteratively. The standard loops are discuss as follows
WHILE loop
The syntax of while loop is as follows:
WHILE expression DO
Statements
END WHILE
First the while loop checks the expression, if it is true it will executes statement until the expression become false. Because while loop checks the expression before statements executed, it is often known as pretest loop. Here is an example of using while loop in stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS WhileLoopProc$$
CREATE PROCEDURE WhileLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END$$
DELIMITER ;

In stored procedures above, we build string repeatedly until the variable x greater than 5 and then we output the built string into console screen by using SELECT statement. One of common trap almost developers encounter is if the variable x is not initialized, its default value is NULL so the condition in while loop is always true; the code block inside while loop is executed indefinitively until your database server crashed.
 
REPEAT loop
The syntax of repeat loop is as follows:
REPEAT
Statements;
UNTIL expression
END REPEAT
First the statements are executed, and then the expression is evaluated. If the expression is evaluated as true the statements are executed again and again until its value become false. Because the repeat loop checks the expression after the execution of statements so it is also known as post-test loop. We can rewrite the stored procedure above by using repeat loop as follows:
DELIMITER $$
DROP PROCEDURE IF EXISTS RepeatLoopProc$$
CREATE PROCEDURE RepeatLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
REPEAT
SET str = CONCAT(str,x,',');
SET x = x + 1;
UNTIL x > 5
END REPEAT;
SELECT str;
END$$
DELIMITER ;

Be noted that there is no delimiter (;) after UNTIL expression
LOOP loop, LEAVE and ITERATE
Leave statement allows you to leave the loop. It is a bit like break in other languages such as Java, C#...
Iterate statement allows you to start the loop again. It is like continue in Java or C#.
MySQL also support a LOOP loop which allows you to execute statements repeatedly more flexible. Here is an example of using LOOP loop.
 
DELIMITER $$
DROP PROCEDURE IF EXISTS LOOPLoopProc$$
CREATE PROCEDURE LOOPLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,',');
END IF;

END LOOP;
SELECT str;
END$$
 
DELIMITER ;
The stored procedure only constructs string with even numbers. First we define a loop label, if a variable x is greater than 10 the loop is ended because of leave statement. Otherwise if the variable x is odd, the ITERATE ignores everything bellow it and continues, if the variable x is even, the block after ELSE constructs strings with even numbers.


SQL Cursor in Stored Procedures
MySQL supported cursor in stored procedures, functions and triggers. Cursor is used to iterate through a set of rows, which returned by a query, and process individual row. Currently with all versions greater 5.x, MySQL cursor has following properties:
Read only: it means you cannot update the cursor.
Non-scrollable: it only can traverse in one direction and cannot skip, move back or forth in result set.
Asensitive: you should avoid update table while open a cursor on that table otherwise you may get unexpected results.
MySQL supports following statements for working with cursor.
First you have to declare a cursor using DECLARE statement:
DECLARE cursor_name CURSOR FOR SELECT_statement;
Second you have to open the cursor using OPEN statement. You must open cursor before fetching rows from it.
OPEN cursor_name;
Next you can retrieve next row from cursor and move the cursor to the following row in a result set by using FETCH statement.
FETCH cursor_name INTO variable list;
And finally, you must close the cursor to deactivate it and release the memory associated with that cursor. To close the cursor you use CLOSE statement:
CLOSE cursor_name;
One of the most important point when working with cursor is you should use a NOT FOUND handler to avoid raising a fatal “no data to fetch” condition.
We use a stored procedure example bellow to demonstrate cursor.
 
DELIMITER $$
DROP PROCEDURE IF EXISTS CursorProc$$
CREATE PROCEDURE CursorProc()
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
DECLARE prd_code VARCHAR(255);
            DECLARE cur_product CURSOR FOR
SELECT productCode FROM products;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_products = 1;

/* for loggging information */
CREATE TABLE infologs (
Id int(11) NOT NULL AUTO_INCREMENT,
Msg varchar(255) NOT NULL,
PRIMARY KEY (Id)
);
OPEN cur_product;

FETCH cur_product INTO prd_code;
REPEAT
SELECT quantityInStock INTO quantity_in_stock
FROM products
WHERE productCode = prd_code;

IF quantity_in_stock < 100 THEN
INSERT INTO infologs(msg)
VALUES (prd_code);
END IF;
FETCH cur_product INTO prd_code;
UNTIL no_more_products = 1
END REPEAT;
CLOSE cur_product;
SELECT * FROM infologs;
DROP TABLE infologs;
END$$
DELIMITER;

The stored procedure is very simple and can archive the same result by SQL query. We use it only for demonstrating how cursors work.
We use a cursor for products table and loop though the products result set. If the quantity in stock of a product is less than 100, we log it into to a temporary table and after the loop we select all products to print it on screen.
Remember you must declare cursor first and then declare a NOT FOUND handler; otherwise you will get an error.

Introduction to Using Disposable Windows SharePoint Services Objects

http://msdn.microsoft.com/en-us/library/aa973248.aspx#sharepointobjmodel__spsiteobjects

The objects in the Windows SharePoint Services 3.0 object model serve as an interface for working with Windows SharePoint Services data. Frequently, developers call into the object model to read data from or write new data to the Windows SharePoint Services store.
The Windows SharePoint Services object model contains objects that implement the IDisposable interface. You must take precautions when using these objects to avoid their long-term retention in memory in the Microsoft .NET Framework.
Specifically, you should explicitly dispose of those SharePoint objects that implement IDisposable when you are finished using them.
In scenarios in which you use SharePoint objects extensively—for example, in SharePoint sites that use custom Web Parts—you can cause the following unusual behaviors by not disposing of SharePoint objects when you are finished with them.

• Frequent recycles of the Windows SharePoint Services application pool, especially during peak usage
  
  
• Application crashes that appear as heap corruption in the debugger
  
  
• High memory use for Microsoft Internet Information Services (IIS) worker processes
  
  
• Poor system and application performance
  
  

This article serves as a guide to the proper procedures for handling and disposing of SharePoint objects that implement IDispose. The issues discussed in this article are also flagged by the SharePoint Dispose Checker Tool, a free program available as a download that inspects your assemblies for coding practices that cause memory leaks because of improper handling and disposal of SharePoint objects.

Why Dispose?

Several of the Windows SharePoint Services objects, primarily the SPSite class and SPWeb class objects, are created as managed objects. However, these objects use unmanaged code and memory to perform the majority of their work. The managed part of the object is much smaller than the unmanaged part. Because the smaller managed part does not put memory pressure on the garbage collector, the garbage collector does not release the object from memory in a timely manner. The object's use of a large amount of unmanaged memory can cause some of the unusual behaviors described earlier. Calling applications that work with IDisposable objects in Windows SharePoint Services must dispose of the objects when the applications finish using them. You should not rely on the garbage collector to release them from memory automatically.

Finding Incorrectly Disposed Objects

You can identify the potential presence of incorrectly disposed objects by asking the following questions:

1. Does your application pool recycle frequently, especially under heavy loads (assuming that the application pool is set to recycle when a memory threshold is reached)?
   
   The memory threshold should be 800 MB–1.5 GB (assuming at least 2 GB of RAM). Setting the recycle of the application pool to occur closer to 1 GB gives the best results, but experiment to determine what settings work best for your environment. If the recycle setting is too low, you experience performance issues because of frequent application pool recycles. If the setting is too high, your system experiences performance problems because of page swapping, memory fragmentation, and other issues.
   
   
2. Does your system perform poorly, especially under heavy loads?
   
   As memory usage begins to increase, the system must compensate, for example, by paging memory and handling memory fragmentation.
   
   
3. Does your system crash or do users experience unexpected errors such as timeouts or page-not-available errors, especially under heavy loads?
   
   Again, when memory usage increases or gets fragmented, some functions fail because they cannot allocate memory for other operations. In many cases, the code does not properly handle the "out of memory" exception, which leads to false or misleading errors.
   
   
4. Does your system use custom or third-party Web Parts or custom applications?
   
   You might not be aware that they must dispose of SharePoint objects and why, assuming that garbage collection performs this function automatically. However, that is not true in all cases.
   
   

If you answer "yes" to number 4, and to one or more of the other questions, there is a good chance that your custom code is not disposing of items properly.
If your sites are displaying any of the unusual behaviors described previously, you can determine whether the cause is a memory leak due to incorrectly disposed objects by checking the ULS logs (available at C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\LOGS) for entries related to the SPRequest object. Each instance of SPSite and SPWeb contains a reference to an SPRequest object that, in turn, contains a reference to an unmanaged COM object that handles communications with the database server. Windows SharePoint Services monitors the number of SPRequest objects that exist in each specific thread and in parallel threads, and adds useful entries to the logs under the three following scenarios:

• The total number of SPRequest objects exceeds a configurable threshold.
  
  
• An SPRequest object continues to exist at the end of a thread.
  
  
• An SPRequest object was garbage collected.
  
  

The first scenario occurs most frequently, especially if your site uses the default threshold value of eight SPRequest objects. Whenever the number of SPRequest objects exceeds this threshold, the following entry appears in the ULS logs:
"Potentially excessive number of SPRequest objects (number of objects) currently unreleased on thread number of thread. Ensure that this object or its parent (such as an SPWeb or SPSite object) is being properly disposed. Allocation Id for this object: {GUID}"
The best threshold varies according to the nature of your site and the applications running on it. When your sites are experiencing problems with performance, you should monitor your installation's ULS logs to understand how many SPRequest objects your site applications are creating. This helps you determine whether the designs of your sites and applications are creating too many SPRequest objects. Even if incorrect disposal of objects is not the cause of your performance problem, you might need to redesign your sites or custom site applications to reduce overall memory consumption caused by excessive proliferation of SPRequest objects.
Because the very low default threshold may not apply to many sites, you can change this threshold by editing the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\HeapSettings
LocalSPRequestWarnCount = desired threshold value
After you determine that incorrect disposal of objects might be causing SPRequest objects to proliferate and unnecessarily increase the memory footprint of your sites, you can find specific instances of incorrect disposal by looking for the following two entries. Both messages point to cases where memory is being wasted because of incorrect disposal of SharePoint objects, and both relate to the number and state of SPRequest objects on a single thread:

• "An SPRequest object was not disposed before the end of this thread. To avoid wasting system resources, dispose of this object or its parent (such as an SPSite or SPWeb) as soon as you are done using it. This object will now be disposed. Allocation Id: {GUID}To determine where this object was allocated, create a registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\HeapSettings. Then create a new DWORD named SPRequestStackTrace with the value 1 under this key."
  
  This message indicates that an SPRequest object was disposed because it still existed at the end of a thread.
  
  
• "An SPRequest object was reclaimed by the garbage collector instead of being explicitly freed. To avoid wasting system resources, dispose of this object or its parent (such as an SPSite or SPWeb) as soon as you are done using it. Allocation Id: {GUID} To determine where this object was allocated, create a registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\HeapSettings. Then create a new DWORD named SPRequestStackTrace with the value 1 under this key."
  
  This message indicates that the garbage collector disposed of an SPRequest object.
  
  

To identify the code that causes the problem, you can search in the logs for entries that contain the allocation identifiers, or follow the instructions in the warnings and add the following subkey setting to the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\HeapSettings SPRequestStackTrace = 1
This subkey setting ensures that the stack trace of the original SPRequest allocation (which occurs whenever an SPSite or SPWeb object is created) is added to the logs when these warnings occur.
The following sections describe several coding techniques you can use to ensure that the objects are disposed of properly.

Coding Techniques to Ensure Object Disposal

You can employ certain coding techniques to ensure object disposal. These techniques include using the following in your code:

• Dispose method
  
  
• using clause
  
  
• try, catch, and finally blocks
  
  

Dispose vs. Close Method Usage

The Dispose and Close methods for the SPWeb object and SPSite object function in the same way. The Dispose method calls the object's Close method. We recommend calling the Dispose method, instead of Close, because SPWeb and SPSite objects implement the IDisposable interface, and standard .NET Framework garbage collection calls the Dispose method to free any resources associated with the object from memory.

The using Clause

You can automatically dispose of SharePoint objects that implement the IDisposable interface by using the Microsoft Visual C# using statement.
The following code provides an example.

C#

String str;

using(SPSite oSPsite = new SPSite("http://server"))
{
  using(SPWeb oSPWeb = oSPSite.OpenWeb())
   {
       str = oSPWeb.Title;
       str = oSPWeb.Url;
   }
}  

Taking advantage of using statements can greatly simplify your code. As noted in the C# Reference (using Statement), the common language runtime translates using clauses into try and finally blocks, and any objects that implement the IDisposable interface are disposed for you. In many cases, however, using statements are not advisable, or must be used with some caution and understanding of what the runtime is doing. The following code example shows one case where you would not want the runtime to construct a finally block and dispose objects for you. In this case, SPContext returns an SPWeb object.

C#

// Do not do this. Dispose() is automatically called on SPWeb. 
using( SPWeb web = SPControl.GetContextWeb(HttpContext.Current)) { ... }

SPContext objects are managed by the SharePoint framework and should not be explicitly disposed in your code. This is true also for the SPSite and SPWeb objects returned by SPContext.Site, SPContext.Current.Site, SPContext.Web, and SPContext.Current.Web.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_220.

You must be cautious and aware of what the runtime is doing whenever you combine SharePoint object model calls on the same line. Leaks arising from this scenario are among the hardest to find.
In the following code example, an SPSite object is instantiated but not disposed, because the runtime ensures disposal of only the SPWeb object returned by OpenWeb.

C#

void CombiningCallsLeak()
{
    using (SPWeb web = new SPSite(SPContext.Current.Web.Url).OpenWeb())
    {
        // ... New SPSite will be leaked.
    } // SPWeb object web.Dispose() automatically called.
}

You can fix this problem by nesting one using statement within another.

C#

void CombiningCallsBestPractice()
{
    using (SPSite siteCollection = new SPSite(SPContext.Current.Web.Url))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
        //Perform operations on site.
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called.
}

If you are not performing any operations on the SPSite object, you could write this more succinctly, as in the following code example.

C#

void CombiningCallsBestPractice()
{
    using (SPSite siteCollection = new SPSite(SPContext.Current.Web.Url))
    using (SPWeb web = siteCollection.OpenWeb())
        {
        //Perform operations on site.
        } // SPWeb object web.Dispose() automatically called; SPSite object 
          // siteCollection.Dispose() automatically called.
}

In other cases, you must construct your own try, catch, and finally blocks. The most obvious examples are scenarios where you need to handle exceptions, and therefore must include a catch block. The following section provides guidelines on when and how to use try, catch, and finally blocks.

The try, catch, and finally Blocks

Using try, catch, and finally blocks obviously makes sense whenever you need to handle exceptions. Any code within a try/catch block should have a governing finally clause, which ensures that the objects that implement IDisposable are disposed. Notice that in the following code example you should fill the catch block with code that handles the exception. Never leave a catch block empty. Also note the best practice of testing for null before disposing.

C#

String str;
SPSite oSPSite = null;
SPWeb oSPWeb = null;

try
{
   oSPSite = new SPSite("http://server");
   oSPWeb = oSPSite.OpenWeb(..);

   str = oSPWeb.Title;
}
catch(Exception e)
{
   //Handle exception, log exception, etc.
}
finally
{
   if (oSPWeb != null)
     oSPWeb.Dispose();

   if (oSPSite != null)
      oSPSite.Dispose();
}

Try and finally blocks or a using statement would be required to avoid potential leaks when you create a disposable object within a foreach block, as shown in the following code example.

C#

public static void SPSiteCollectionForEachBestPractice()
{
     string sUrl = "http://spvm";
 
      using (SPSite siteCollectionOuter = new SPSite(sUrl))
     {
         SPWebApplication webApp = siteCollectionOuter.WebApplication;
         SPSiteCollection siteCollections = webApp.Sites;

                  SPSite siteCollectionInner = null;
                  foreach (siteCollectionInner in siteCollections)
             {
                      try  //Should be first statement after foreach.
                      {
                          Console.WriteLine(siteCollectionInner.Url);
                          //Exception occurs here.
                      }
                      finally
                      {
                          if(siteCollectionInner != null)
                          siteCollectionInner.Dispose();
                 }
             }
         }
     } // SPSite object siteCollectionOuter.Dispose() automatically called.
 }

Response.Redirect with try, catch, and finally Blocks and using Statements

The finally block executes after calls to Response.Redirect in the try block. Response.Redirect ultimately generates a ThreadAbortException. When this exception is raised, the runtime executes all finally blocks before ending the thread. However, because the finally block can do an unbounded computation or cancel the ThreadAbortException, there is no guarantee that the thread will end. Therefore, before any redirection or transfer of processing can occur, you must dispose of the objects. If your code must redirect, implement it in a way similar to the following code example.

C#

String str;
SPSite oSPSite = null;
SPWeb oSPWeb = null;

try
{
   oSPSite = new SPSite("http://server");
   oSPWeb = oSPSite.OpenWeb(..);

   str = oSPWeb.Title;
   if(bDoRedirection)
   {
       if (oSPWeb != null)
          oSPWeb.Dispose();
    
       if (oSPSite != null)
          oSPSite.Dispose();

       Response.Redirect("newpage.aspx");
   }
}
catch(Exception e)
{
}
finally
{
   if (oSPWeb != null)
     oSPWeb.Dispose();

   if (oSPSite != null)
      oSPSite.Dispose();
}

Because a using clause instructs the runtime to create a finally block, whenever you use Response.Redirect within a using clause, ensure that objects are disposed properly. The following code example shows how you can do this.

C#

using (SPSite oSPSite = new SPSite("http://server"))
using (SPWeb oSPWeb = oSPSite.OpenWeb(..))
{
    if (bDoRedirection)
        Response.Redirect("newpage.aspx");
}

Recommendations to Reduce Long-Term Object Retention

You can reduce long-term retention of SharePoint objects by following these general recommendations.

• If you create the object with a new operator, ensure that the creating application disposes of it.
  
  

  Note:
  This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_110.

  Good Coding Practice #1
  
  Explicitly Disposing
  
  
  C#

  void CreatingSPSiteExplicitDisposeNoLeak()
  {
      SPSite siteCollection = null;
      try
      {
          siteCollection = new SPSite("http://moss");
      }
      finally
      {
          if (siteCollection != null)
              siteCollection.Dispose();
      }
  }
  

  
  
  Good Coding Practice #2
  
  Automatically Disposing
  
  
  C#

  CreatingSPSiteWithAutomaticDisposeNoLeak()
  {
      using (SPSite siteCollection = new SPSite("http://moss"))
      {
      } // SPSite object siteCollection.Dispose() is called automatically.
  }
  

• Dispose of items created by SharePoint methods that return other SPWeb objects (such as OpenWeb).
  
  

  Note:
  This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_120.

  Good Coding Practice
  
  
  C#

  void OpenWebNoLeak()
  {
      using (SPSite siteCollection = new SPSite("http://moss"))
      {
          using (SPWeb web = siteCollection.OpenWeb())
          {
          } // SPWeb object web.Dispose() automatically called.
      }  // SPSite object siteCollection.Dispose() automatically called.
  }
  
  

• Do not share any SPRequest object (and by extension any object that contains a reference to an SPRequest object) across threads. Any coding technique that shares an SPRequest object between two or more threads, or creates an SPRequest object on one thread and disposes it on another, is not supported. This means that you cannot store any object that holds a reference to an SPRequest object in a static variable. Do not, therefore, store SharePoint objects that implement IDisposable (such as SPWeb or SPSite) in static variables.
  
  

SPSite Objects

This section describes situations in which new SPSite objects are returned and must be disposed.
In general, any time a calling application uses the new SPSite constructors (any signature), it should call the Dispose method when it is finished using the object. If the SPSite object is obtained from GetContextSite, the calling application should not dispose of the object. Because the SPWeb and SPSite objects keep an internal list that is derived in this way, disposing of the object may cause the SharePoint object model to behave unpredictably. Internally, Windows SharePoint Services enumerates over this list after page completion to dispose of the objects properly.

SPSiteCollection Class

This section describes the methods, properties, or operators in the SPSiteCollection object that require the returned SPSite object to be closed after access.

SPSiteCollection.Add Method

The SPSiteCollection.Add method creates and returns a new SPSite object. You should dispose of any SPSite object returned from the SPSiteCollection.Add method.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_240.

Bad Coding Practice

C#

void SPSiteCollectionAddLeak()
{
    SPWebApplication webApp = new SPSite("http://moss").WebApplication;
    SPSiteCollection siteCollections = webApp.Sites;
    SPSite siteCollection = siteCollections.Add("sites/myNewSiteCollection", "DOMAIN\\User", 
      "roger.lamb@litwareinc.com");
    // SPSite siteCollection leak.
}

Good Coding Practice

C#

void SPSiteCollectionAddNoLeak()
{
    SPWebApplication webApp = new SPSite("http://moss").WebApplication;
    SPSiteCollection siteCollections = webApp.Sites;
    using (SPSite siteCollection = siteCollections.Add("sites/myNewSiteCollection", "DOMAIN\\User", 
      "roger.lamb@litwareinc.com"))
    {
    } // SPSite object siteCollection.Dispose() automatically called.
}

SPSiteCollection [ ] Index Operator

The SPSiteCollection [] index operator returns a new SPSite object for each access. An SPSite instance is created even if that object was already accessed. The following code samples demonstrate improper disposal of the SPSite object.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_230.

Bad Coding Practice #1

Using Index Operator

C#

void SPSiteCollectionIndexerLeak()
{
    using (SPSite siteCollectionOuter = new SPSite("http://moss"))
    {
        SPWebApplication webApp = siteCollectionOuter.WebApplication;
        SPSiteCollection siteCollections = webApp.Sites;

        SPSite siteCollectionInner = siteCollections[0];
        // SPSite siteCollectionInner leak. 
    } // SPSite object siteCollectionOuter.Dispose() automatically called.
}

Bad Coding Practice #2

Using ForEach Loop

C#

void SPSiteCollectionForEachLeak()
{
    using (SPSite siteCollectionOuter = new SPSite("http://moss"))
    {
        SPWebApplication webApp = siteCollectionOuter.WebApplication;
        SPSiteCollection siteCollections = webApp.Sites;

        foreach (SPSite siteCollectionInner in siteCollections)
        {
            // SPSite siteCollectionInner leak.
        }
    } // SPSite object siteCollectionOuter.Dispose() automatically called.
}

Good Coding Practice #1

Using Index Operator

C#

void SPSiteCollectionIndexerNoLeak()
{
    using (SPSite siteCollectionOuter = new SPSite("http://moss"))
    {
        SPSite siteCollectionInner = null;
        try
        {
            SPWebApplication webApp = siteCollectionOuter.WebApplication;
            SPSiteCollection siteCollections = webApp.Sites;

            siteCollectionInner = siteCollections[0];
        }
        finally
        {
            if (siteCollectionInner != null)
                siteCollectionInner.Dispose();
        }
    } // SPSite object siteCollectionOuter.Dispose() automatically called.
}

Good Coding Practice #2

Using ForEach Loop

C#

void SPSiteCollectionForEachNoLeak()
{
    using (SPSite siteCollectionOuter = new SPSite("http://moss"))
    {
        SPWebApplication webApp = siteCollectionOuter.WebApplication;
        SPSiteCollection siteCollections = webApp.Sites;

        foreach (SPSite siteCollectionInner in siteCollections)
        {
            try
            {
                // ...
            }
            finally
            {
                if(siteCollectionInner != null)
                    siteCollectionInner.Dispose();
            }
        }
    } // SPSite object siteCollectionOuter.Dispose() automatically called.
}

SPSite.AllWebs Property (SPWebCollection)

This section describes the methods, properties, or operators in the AllWebs property collection that require the SPWeb object to be closed after access.

SPSite.AllWebs.Add Method

The SPSite.AllWebs.Add method creates and returns an SPWeb object. You should dispose of any SPWeb object returned from SPSite.AllWebs.Add.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_150.

Bad Coding Practice

C#

void AllWebsAddLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        SPWeb web = siteCollection.AllWebs.Add("site-relative URL");
        // SPWeb object leaked.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice

C#

void AllWebsAddNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.AllWebs.Add("site-relative URL"))
        {
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

SPWebCollection.Add Method

The SPWebCollection.Add method creates and returns an SPWeb object that needs to be disposed.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_200.

Bad Coding Practice

C#

void SPWebCollectionAddLeak(string strWebUrl)
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            SPWebCollection webCollection = siteCollection.AllWebs; // No AllWebs leak just getting reference.
            SPWeb innerWeb = webCollection.Add(strWebUrl);  // Must dispose of innerWeb.
            // innerWeb leak.
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice

C#

void SPWebCollectionAddNoLeak(string strWebUrl)
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            SPWebCollection webCollection = siteCollection.AllWebs; // No AllWebs leak just getting reference.
            using (SPWeb innerWeb = webCollection.Add(strWebUrl))
            {
                //...
            }
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

SPSite.AllWebs [ ] Index Operator

The SPSite.AllWebs [] index operator returns a new SPWeb instance each time it is accessed. An object is created during the indexing operation even if that object was already accessed. If not properly closed, the following code samples leave an SPWeb object in the .NET Framework garbage collector.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_130.

Bad Coding Practice

C#

void AllWebsForEachLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            foreach (SPWeb innerWeb in siteCollection.AllWebs)
            {
                // Explicitly dispose here to avoid out of memory leaks with large number of SPWeb objects.
            }
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice #1

Using ForEach Loop

C#

void AllWebsForEachNoLeakOrMemoryOOM()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            foreach (SPWeb innerWeb in siteCollection.AllWebs)
            {
                try
                {
                    // ...
                }
                finally
                {
                    if(innerWeb != null)
                        innerWeb.Dispose();
                }
            }
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice #2

Using Index Operator

C#

void AllWebsIndexerNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.AllWebs[0])
        {
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

SPSite.OpenWeb and SPSite. SelfServiceCreateSite Methods

The OpenWeb method and SelfServiceCreateSite method (all signatures) create an SPWeb object and return it to the caller. This new object is not stored in the SPSite object and is not disposed of anywhere in the SPSite class. For this reason, you should dispose of any object created via these methods.

Bad Coding Practice

C#

void OpenWebLeak()
{
    using (SPWeb web = new SPSite(SPContext.Current.Web.Url).OpenWeb())
    {
        // SPSite leaked !
    } // SPWeb object web.Dispose() automatically called.
}

Good Coding Practice

C#

void OpenWebNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called.
}

SPSite.RootWeb Property

An earlier version of this article indicated that the calling application should dispose of the SPSite.RootWeb property just before disposing of the SPSite object that is using it. This is no longer the official guidance. The dispose cleanup is handled automatically by the SharePoint framework. Additionally, SPSite properties LockIssue, Owner, and SecondaryContact used the RootWeb property internally. Given the updated guidance for RootWeb, it is no longer advisable to call the Dispose method on the SPSite.RootWeb property whenever any of these properties are used.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_140.

Good Coding Practice

C#

public void RootWebBestPractice()
{
    // New SPSite.
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        SPWeb rootWeb1 = siteCollection.RootWeb;
        // No explicit rootWeb1 dispose required.
    }  // siteCollection automatically disposed by implementing using().
    // rootWeb1 will be Disposed by SPSite.

    // SPContext and SPControl
    SPWeb rootWeb2 = SPContext.Current.Site.RootWeb;
    // Also would apply to SPControl.GetContextSite(Context);
    // No explicit rootWeb2 dispose required because it's obtained from SPContext.Current.Site.
}

Microsoft.Office.Server.UserProfiles.PersonalSite (Office SharePoint Server 2007 only)

The Microsoft.Office.Server.UserProfiles.PersonalSite returns an SPSite object that must be disposed.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_400.

Bad Coding Practice

C#

void PersonalSiteLeak()
{
    // Open a site collection.
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        UserProfileManager profileManager = new UserProfileManager(ServerContext.GetContext(siteCollection));
        UserProfile profile = profileManager.GetUserProfile("domain\\username");
        SPSite personalSite = profile.PersonalSite;    // Will leak.
    }
}

Good Coding Practice

C#

void PersonalSiteNoLeak()
{
    // Open a site collection.
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        UserProfileManager profileManager = new UserProfileManager(ServerContext.GetContext(siteCollection));
        UserProfile profile = profileManager.GetUserProfile("domain\\username");
        using (SPSite personalSite = profile.PersonalSite)
        {
            // ...
        }
    }
}

In another edge case, UserProfiles.PersonalSite leaks, as shown in the following code example.

C#

void PersonalSiteLeak()
{
    // Open a site collection.
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        UserProfileManager profileManager = new UserProfileManager(ServerContext.GetContext(siteCollection));
        UserProfile profile = profileManager.GetUserProfile("domain\\username");
        SPSite personalSite = profile.PersonalSite;    // Will leak.
    }
}

You can resolve this sort of leak by following the pattern shown in the following code example.

C#

void PersonalSiteNoLeak()
{
    // Open a site collection
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        UserProfileManager profileManager = new UserProfileManager(ServerContext.GetContext(siteCollection));
        UserProfile profile = profileManager.GetUserProfile("domain\\username");
        using (SPSite personalSite = profile.PersonalSite)
        {
            // ...
        }
    }
}

Also notice that you can improve performance (and avoid creating an SPSite object) by retrieving a PersonalSite object from the ProfileLoader, as shown in the following code example.

C#

UserProfile myProfile = ProfileLoader.GetProfileLoader().GetUserProfile();
using (SPSite personalSite = myProfile.PersonalSite)
{
     // ...
}

Additionally, if you are creating a Web Part for a My Site, you can use an instance of PersonalSite that does not need to be disposed.

C#

IPersonalPage currentMySitePage = this.Page as IPersonalPage;
if (currentMySitePage != null && !currentMySitePage.IsProfileError)
{
     SPSite personalSite = currentMySitePage.PersonalSite; // Will not leak.
     // ...
}

SPWeb Objects

This section describes the situations in which SPWeb objects are returned and may need to be disposed of.

SPWeb.ParentWeb Property

Updated Guidance
An earlier version of this article recommended that the calling application should dispose of the SPWeb.ParentWeb. This is no longer the official guidance. The dispose cleanup is handled automatically by the SharePoint framework.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_170.

Good Coding Practice

C#

using (SPSite site = new SPSite(http://localhost)) 
{
    using (SPWeb web = site.OpenWeb())
    {
        SPList list = web.Lists["Announcements"];
        SPWeb parentWeb = list.ParentWeb; //No explicit dispose required.
    }
}

SPWeb.Webs Property

This section describes the methods, properties, or operators in the Webs property collection that require disposal of the SPWeb object after access.

SPWeb.Webs

The SPWeb.Webs property returns an SPWebCollection object. The SPWeb objects in this collection must be disposed.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_180.

Bad Coding Practice

C#

void WebsLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            foreach (SPWeb innerWeb in outerWeb.Webs)
            {
                // SPWeb innerWeb leak.
            }
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice

C#

void WebsNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb outerWeb = siteCollection.OpenWeb())
        {
            foreach (SPWeb innerWeb in outerWeb.Webs)
            {
                try //Should be first statement after foreach.
                {
                    // ...
                }
                finally
                {
                    if(innerWeb != null)
                        innerWeb.Dispose();
                }
            }
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

SPWeb.Webs.Add

The SPWeb.Webs.Add method (or Add) creates and returns a new SPWeb object. You should dispose of any SPWeb object returned from this method call.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_190.

Bad Coding Practice

C#

void WebsAddLeak(string strWebUrl)
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            SPWeb addedWeb = web.Webs.Add(strWebUrl);   // Will leak.

        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called.
}

Good Coding Practice

C#

void WebsAddNoLeak(string strWebUrl)
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            using (SPWeb addedWeb = web.Webs.Add(strWebUrl))
            {
                //..
            }

        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called.
}

SPWeb.Webs[] Index Operator

The SPWeb.Webs[] index operator returns a new SPWeb object for each access. An SPWeb is created by calling the OpenWeb method, even if that object was already accessed. The following code samples cause long-term retention of these objects in memory used by the .NET Framework.

Bad Coding Practice #1

Using For Loop

C#

int i;

SPWeb oSPWeb, oSPWeb2;
SPSite oSPSite = SPControl.GetContextSite(Context);

oSPWeb = oSPSite.OpenWeb();

for(i = 0;i < oSPWeb.Webs.Count;i++)
{
   oSPWeb2 = oSPWeb.Webs[i];
   BuildTableRow(oDisplayTable, "Web", oSPWeb2.Title);
}

Bad Coding Practice #2

Using ForEach Loop

C#

SPWeb oSPWeb, oSPWeb2;
SPSite oSPSite = SPControl.GetContextSite(Context);

oSPWeb = oSPSite.OpenWeb();

foreach(SPWeb oSPWeb2 in oSPWebe.Webs)
{
   BuildTableRow(oDisplayTable, "Web", oSPWeb2.Title);
}

The recommended fix is to dispose at the end of each loop.

Good Coding Practice #1

Using For Loop

C#

int i;

SPWeb oSPWeb, oSPWeb2;
SPSite oSPSite = SPControl.GetContextSite(Context);

oSPWeb = oSPSite.OpenWeb();

for(i = 0;i < oSPWeb.Webs.Count;i++)
{
   oSPWeb2 = oSPWeb.Webs[i];
   BuildTableRow(oDisplayTable, "Web", oSPWeb2.Title);
   oSPWeb2.Dispose();
}

oSPWeb.Dispose();

Good Coding Practice #2

Using ForEach Loop

C#

SPWeb oSPWeb, oSPWeb2;
SPSite oSPSite = SPControl.GetContextSite(Context);

oSPWeb = oSPSite.OpenWeb();

foreach(SPWeb oSPWeb2 in oSPWeb.Webs)
{
   BuildTableRow(oDisplayTable, "Web", oSPWeb2.Title);
   oSPWeb2.Dispose();
}

oSPWeb.Dispose();

Good Coding Practice #3

Using For Loop with Automatic Disposal

C#

int i;

SPWeb oSPWeb, oSPWeb2;
SPSite oSPSite = SPControl.GetContextSite(Context);

using(oSPWeb = oSPSite.OpenWeb())
{
   for(i = 0;i < oSPWeb.Webs.Count;i++)
   {
      Using(oSPWeb2 = oSPWeb.Webs[i])
      {
         BuildTableRow(oDisplayTable, "Web", oSPWeb2.Title);
      }
   }
}

Other Objects That Require Disposal

This section describes when to call the Dispose method on other SharePoint objects.

Microsoft.SharePoint.Portal.SiteData.Area.Web Property

The Web property returns a new SPWeb object each time it is accessed. Any use of the Area.Web property should have a corresponding call to the Dispose method. Although the Area and AreaManager classes are now obsolete in Office SharePoint Server 2007, this remains a concern when migrating legacy code.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_500.

Bad Coding Practice

C#

void AreaWebLeak()
{
    // AreaManager and Area are obsolete in SharePoint Server, but this
    // should still be noted.
    Area area = AreaManager.GetArea(PortalContext.Current, new Guid("{GUID}"));
    string str = area.Web.Title;
    // SPWeb area.Web leak.
}

Good Coding Practice

C#

public void AreaWebNoLeak()
{
    // AreaManager and Area are obsolete in MOSS but this should still be noted
    Area area = AreaManager.GetArea(PortalContext.Current, new Guid("{GUID}"));
    using (SPWeb areaWeb = area.Web)
    {
        string str = areaWeb.Title;
    }
}

SPControl.GetContextSite and SPControl.GetContextWeb Methods

If the object is obtained from the SharePoint context objects (GetContextSite method and GetContextWeb method), the calling application should not call the Dispose method on the object. Doing so may cause the SharePoint object model to behave unpredictably or fail. This is due to an internal list that is kept in the SPSite and SPWeb objects derived in this way. Internally, the object model enumerates over this list after page completion to dispose of the objects properly.
You should still dispose of an object that is created from these objects, for example, if a Web site is opened from an SPSite object that you obtained by using the GetContextSite method.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_210.

Bad Coding Practice

C#

void SPControlBADPractice()
{
    SPSite siteCollection = SPControl.GetContextSite(Context);
    siteCollection.Dispose();   // DO NOT DO THIS
    SPWeb web = SPControl.GetContextWeb(Context);
    web.Dispose();  // DO NOT DO THIS.
}

Good Coding Practice

C#

void SPControlBestPractice()
{
    SPSite siteCollection = SPControl.GetContextSite(Context);
    SPWeb web = SPControl.GetContextWeb(Context);
    // Do NOT call Dispose().
}

Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager

The SPLimitedWebPartManager class contains a reference to an internal SPWeb object that must be disposed.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_160.

Bad Coding Practice

C#

void SPLimitedWebPartManagerLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            SPFile page = web.GetFile("Source_Folder_Name/Source_Page");
            SPLimitedWebPartManager webPartManager =
                page.GetLimitedWebPartManager(PersonalizationScope.Shared);
            // SPWeb object webPartManager.Web leaked.
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice

C#

void SPLimitedWebPartManagerLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            SPFile page = web.GetFile("Source_Folder_Name/Source_Page");
            SPLimitedWebPartManager webPartManager =
                page.GetLimitedWebPartManager(PersonalizationScope.Shared);
                webPartManaber.Web.Dispose();
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Microsoft.SharePoint.Publishing.PublishingWeb (SharePoint Server 2007 Only)

The GetPublishingWebs method of the PublishingWeb class returns a PublishingWebCollection object. You must call the Close method on each enumerated innerPubWeb object. When you are calling only the GetPublishingWeb method, you are not required to call Close.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_300.

Bad Coding Practice

C#

void PublishingWebCollectionLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            // Passing in SPWeb object that you own, no dispose needed on
            // outerPubWeb.
            PublishingWeb outerPubWeb = PublishingWeb.GetPublishingWeb(web);

            PublishingWebCollection pubWebCollection = outerPubWeb.GetPublishingWebs();
            foreach (PublishingWeb innerPubWeb in pubWebCollection)
            {
                // innerPubWeb leak.
            }
            // PublishingWeb will leak for each innerPubWeb referenced
        } // SPWeb object web.Dispose() automatically called.
    } // SPSite object siteCollection.Dispose() automatically called.
}

Good Coding Practice

C#

void PublishingWebCollectionNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            // Passing in SPWeb object that you own, no dispose needed on
            // outerPubWeb.
            PublishingWeb outerPubWeb = PublishingWeb.GetPublishingWeb(web);
            PublishingWebCollection pubWebCollection = outerPubWeb.GetPublishingWebs();
            foreach (PublishingWeb innerPubWeb in pubWebCollection)
            {
                try
                {
                    // ...
                }
                finally
                {
                    if(innerPubWeb != null)
                        innerPubWeb.Close();
                }
            }
        }  // SPWeb object web.Dispose() automatically called.
    } // SPSite object siteCollection.Dispose() automatically called.
}

Note:

There is a similar requirement to call Close on each PublishingWeb object created by calling the Add method on the PublishingWebCollection returned by Microsoft.SharePoint.Publishing.PublishingWebCollection.GetPublishingWebs. For a code example, see the GetPublishingWebs method in the Microsoft Office SharePoint Server 2007 SDK. This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_310.

The Microsoft.SharePoint.Publishing.PublishingWeb.GetVariation method returns a PublishingWeb object that must be disposed.

Note:
This best practice addresses the issue identified by the SharePoint Dispose Checker Tool as SPDisposeCheckID_320.

Bad Coding Practice

C#

void GetVariationLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            PublishingWeb publishingWeb = PublishingWeb.GetPublishingWeb(web);  // Passing in SPWeb object, so no Close() needed
            VariationLabel variationLabel = Variations.Current.UserAccessibleLabels[0];
            PublishingWeb variationPublishingWeb = publishingWeb.GetVariation(variationLabel);  // Must be Closed().
            // ...
        } // SPWeb object outerWeb.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Good Coding Practice

C#

void GetVariationNoLeak()
{
    using (SPSite siteCollection = new SPSite("http://moss"))
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            PublishingWeb variationPublishingWeb = null;
            try
            {
                PublishingWeb publishingWeb = PublishingWeb.GetPublishingWeb(web);  // Passing in SPWeb object, so no Close() needed.
                VariationLabel variationLabel = Variations.Current.UserAccessibleLabels[0];
                variationPublishingWeb = publishingWeb.GetVariation(variationLabel);  // Must be Closed().
                // ...
            }
            finally
            {
                if(variationPublishingWeb != null)
                    variationPublishingWeb.Close();
            }
        } // SPWeb object web.Dispose() automatically called.
    }  // SPSite object siteCollection.Dispose() automatically called. 
}

Cross Method Dispose Patterns

The following example demonstrates the common practice of holding onto the SPSite and SPWeb objects across methods in a class. Sometimes this design pattern is required, but ensure that you do not overlook the appropriate time to call Dispose when you are finished with the cross method calls. The following code example shows a pattern where SPSite and SPWeb leak when the class goes out of scope.

C#

public class CrossMethodLeak
{
    private SPSite _siteCollection = null;
    private SPWeb _web = null;

    public void MethodA()
    {
        _siteCollection = new SPSite("http://moss");
        _web = _siteCollection.OpenWeb();
    }

    public void MethodB()
    {
        if (_web != null)
        {
            string title = _web.Title;
        }
    }

    public void MethodC()
    {
        if (_web != null)
        {
            string name = _web.Name;
        }
    }
}

Conclusion

Because many SharePoint objects implement the IDisposable interface, you must take care when using these objects to avoid retaining them in memory. By following the guidelines for disposing of SharePoint objects, as described in this article, you can help to ensure reliability of your Windows SharePoint Services custom code.