Chiudere la connessione dal DataReader

When you create a DataReader, you call .ExecuteDataReader. This method accepts a parameter that can be CommandBehavior.CloseConnection. This parameter tells the DataReader that when it is closed, the underlying connection should be closed as well. This is an example function that shows how you can return a DataReader and ensure that it is closed by the calling method:

// C# version

public static IDataReader SelectByRoyalty(int Percentage)
{ 
 SqlDataReader dr=null;
 SqlConnection cn=new SqlConnection
                  ("Server=Aron1;Database=pubs;Trusted_Connection=True;");
 cn.Open();
 try
 {
  SqlCommand cmd=new SqlCommand("byRoyalty",cn);
  cmd.CommandType=CommandType.StoredProcedure;
  cmd.Parameters.Add("@Percentage",Percentage);
   
  dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
 }
 catch ( Exception Ex )
 {
  if ( dr!=null )
  {
   dr.Close();
   cn.Close();
  }
  throw Ex;
 }
 return (IDataReader)dr;
}

' VB version

Public Shared Function SelectByRoyalty(ByVal Percentage As Integer) As IDataReader
    Dim dr As SqlDataReader = Nothing
    Dim cn As New SqlConnection("Server=Aron1;Database=pubs;Trusted_Connection=True;")
    cn.Open()
    Try
        Dim cmd As New SqlCommand("byRoyalty", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@Percentage", Percentage)

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    Catch Ex As Exception
        If dr <> Nothing Then
            dr.Close()
            cn.Close()
        End If
        Throw Ex
    End Try
    Return DirectCast(dr, IDataReader)
End Function

Then, you could call the method as follows:

IDataReader dr=Coatings.SelectCoating(CoatingID);
try
{
 // Use the DataReader..
}
Finally
{
 dr.Close();
}

Failure to use a pattern like this will lead to a great deal of difficulty with connections that are not closed in a timely fashion. I believe that Microsoft initially pushed DataReaders as the preferred way to do database access, at least for ASP.NET applications. As time went on, I think the company discovered that many developers misused DataReaders, not properly closing the connection. Recent Microsoft presentations have often emphasized DataSets even for ASP.NET applications.