Priyang Patel’s Weblog

When returning DataReader from a function, specify CommandBehavior.CloseConnection

Posted in Visual Studio Tips and Tricks by priyangpatel on March 19, 2008

When you create an ADO.NET DataReader object, specify the CommandBehavior.CloseConnection enumeration in your call to ExecuteReader. This ensures that when you close the DataReader, the SQL connection is also closed. This is especially helpful when you return a DataReader from a function, and you do not have control over the calling code. If the caller forgets to close the connection but closes the reader, both are closed when the DataReader is created by using CommandBehavior.CloseConnection. This is shown in the following code fragment.

public SqlDataReader CustomerRead(int CustomerID)


//… create connection and command, open connection

return myCommand.ExecuteReader(CommandBehavior.CloseConnection);


//… client code

SqlDataReader myReader = CustomerRead(10248);

//… read some data

myReader.Close(); // reader and connection are closed


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: