How to close SqlConnection via DataReader. 如何通过DataReader关闭SqlConnection

It is a common issue of how to handle SqlConnection gracefully, when implementing some general helper methods like

SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText);

This method is supposed to create a SqlConnection, then execute the command and return the DataReader.

But when will the SqlConnection be closed? The SqlConnection definitely can not be closed in the same method, otherwise, the returned DataReader will be useless. Ok, then what will happen if we don't close the SqlConnection inside the method? Yes, We got the handy, "live" DataReader instance, from which we get the data we need. Then we could safely close the DataReader itself. How about the SqlConnection? it is not occupied any more, but it is still there, we cannot reach it, as it is already out of scope.

Have you ever heard noticed SqlCommond has an overload method: SqlDataReader ExecuteReader(CommandBehavior behavior) ? Just passing in the magic CommandBehavior.CloseConnection in this method, it guarantees the SqlConnection will get closed when the DataReader is closed.

--------------------------------------

SqlConnection connection = new SqlConnection("myconnectionstringhere");
SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{  ...    }

reader.Close(); //or reader.Dispose();

SqlConnectionClose 相关问题

03-31

小弟,刚刚开始做数据库方面的东西,所以好多问题也许在您看来很傻的。希望大家回答详细些,或者,及时回答我下一步的提问,谢谢。rn哦,还有啊,我现在可能没有分数给,非常对不起,因为我还没有分数。rnrn这里是源代码:rnrnusing System;rnusing System.Collections.Generic;rnusing System.Text;rnusing System.Data.SqlClient;rnusing System.Windows.Forms;rnrnnamespace Testrnrn class DB_Operatorrn rn rn static public int Login( string connString)rn //登陆端传来参数string connstring.在这里做的就是登陆操作。 rn rn Int32 newProdID = 1;rnrn using (SqlConnection conn = new SqlConnection(connString))rn rn rn tryrn rn conn.Open();rn rn rn catch (Exception ex)rn rnrn rn Console.WriteLine(ex.Message);rnrn rn rn rnrn conn.Close();rn //????????????????????????rn rn return (int)newProdID;rn rn rnrnrnrnrn static public int Login_User(string sql,string connString)rn //登陆端传来 查询用户名和密码的查询语句:sql ,connString 是连接数据库的SQL语句string .rn rn Int32 newProdID = 0;rn rn rn using (SqlConnection conn = new SqlConnection(connString))rn rn SqlCommand cmd = new SqlCommand(sql, conn);rnrnrn tryrn rn conn.Open();rn newProdID = (Int32)cmd.ExecuteScalar();rn rn catch (Exception ex)rn rn Console.WriteLine(ex.Message);rn rnrn //MessageBox.Show(conn.State.ToString(), "Info");rn conn.Close();rn //?????????????????????????????????????????rnrn //MessageBox.Show(conn.State.ToString(), "Info");rn rn return (int)newProdID;rn rn rnrnrnrnrn我想,每次只是在和用户界面代码部分调用这个类里的 方法而已。rn问题就是:当我连接数据库后,要不要立即关闭当前的连接.也就是代码中rn//???????????????????????的地方。rnrn因为,对这些连接的机制,什么的不熟悉,一时也找不到相关资料,即使找到了,因为没有应用过,所以也不明白其具体实现。rnrn如果,的确需要每次都要在使用完后,就Close()。会有什么影响没?至少我现在碰到的问题就是每次查询,插入操作,都要重新建立连接。rnrn如果,不需要立即Close(),那么我怎么使用上次的连接呢??rnrn在类中定义 SqlConnection 的一个对象conn吗(上面代码是每个方法里都有各自定义的一个conn)。如果这样可以,还有一个问题,这个连接会因为某种原因自动关闭吗??(我看有些资料,好像有这种可能)rn万一自动关闭了呢??就再连接一次??对吗?? 论坛

没有更多推荐了,返回首页