Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method. : SqlCommand « ADO.net Database « ASP.NET Tutorial

This parameter causes the database connection associated with the SqlDataReader to close automatically 
after all the records have been fetched from the SqlDataReader.

File: App_Code\Product.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

public class Product
{
    private static readonly string _connectionString;

    public SqlDataReader GetAll()
    {
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand("SELECT Title,Director FROM Products", con);
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }    
    static Product()
    {
        _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
    }
}

File: ShowProduct.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Product</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcProducts"
        TypeName="Product"
        SelectMethod="GetAll"
        Runat="server" />

    </div>
    </form>
</body>
</html>
File: Web.config

<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>

18.3.SqlCommand
	18.3.1.	Create SqlCommand from sql statement and connection
	18.3.2.	Executing a Command
	18.3.3.	Executing a Command with Parameters
	18.3.4.	Returning a Single Value
	18.3.5.	Read scalar data by using SqlCommand
	18.3.6.	Execute insert command by using SqlCommand
	18.3.7.	Execuate select command by using the SqlCommand
	18.3.8.	Execute update command
	18.3.9.	Attach SqlCommand to DataGrid
	18.3.10.	Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
	18.3.11.	Executing Asynchronous Database Commands
	18.3.12.	Avoid SQL injection
	18.3.13.	Avoid SQL Injection attack
	18.3.14.	Browser Snoop