Close connection automatically while Closing DataReader

Question

0.00/5 (No votes)

See more:

Hi guys

i want to close my Data Base connection automatically when i close my Data reader.
Is there any property of Data Reader with the help of which i can achieve that.

I dont want to call Connection.Close Method()

Thanks

Posted 23-Sep-11 19:41pm

Mahendra.p25

Updated 17-Jul-22 22:00pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mario Majčica · Answer 1 · 2011-09-23T20:14:00

Solution 1

How about the using statement?

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

C#

using (SqlConnection connection = new SqlConnection(
               connectionString))
{
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
}

Cheers

Posted 23-Sep-11 20:14pm

Mario Majčica

Comments

Mahendra.p25 24-Sep-11 2:29am

Thanks for a reply but iam using 3 tier architecture and connection property is in data layer and iam accessing Data Reader in business layer so requirement is different...

Mehdi Gholam 24-Sep-11 2:31am

Just use the same "using" statement in your data layer.

Mario Majčica 24-Sep-11 2:37am

Once you return the DataReader from your DAL, close the connection (or let using doing it)

///

/// Retrieves all articles
///

public override List<articledetails> GetArticles(int pageIndex, int pageSize)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("tbh_Articles_GetArticles", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
cn.Open();
return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
}
}

where

///

/// Returns a new ArticleDetails instance filled with the DataReader's current record data
///

protected virtual ArticleDetails GetArticleFromReader(IDataReader reader)
{
return GetArticleFromReader(reader, true);
}
protected virtual ArticleDetails GetArticleFromReader(IDataReader reader, bool readBody)
{
ArticleDetails article = new ArticleDetails(
(int)reader["ArticleID"],
(DateTime)reader["AddedDate"],
reader["AddedBy"].ToString(),
(int)reader["CategoryID"],
reader["CategoryTitle"].ToString(),
reader["Title"].ToString(),
reader["Abstract"].ToString(),
null,
reader["Country"].ToString(),
reader["State"].ToString(),
reader["City"].ToString(),
(reader["ReleaseDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["ReleaseDate"]),
(reader["ExpireDate"] == DBNull.Value ? DateTime.MaxValue : (DateTime)reader["ExpireDate"]),
(bool)reader["Approved"],
(bool)reader["Listed"],
(bool)reader["CommentsEnabled"],
(bool)reader["OnlyForMembers"],
(int)reader["ViewCount"],
(int)reader["Votes"],
(int)reader["TotalRating"]);

if (readBody)
article.Body = reader["Body"].ToString();

return article;
}

Mario Majčica 24-Sep-11 2:38am

Also you can find an valid example here

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584642,descCd-DOWNLOAD.html

Cheers

Mahendra.p25 24-Sep-11 2:33am

Anyway got an answer via google (CommandBehavior.CloseConnection)property thanks i was searching for this