如何管理数据访问层SqlDataReaders？ [英] How to manage SqlDataReaders in a data access layer?
问题描述
我试图让我去耦代码，代码重用等。
I am trying to get a better handle on decoupling my code, code reuse, etc.
更好地处理我已经厌倦了打字每一次低于我想读一些行：
I'm tired of typing the below every time I want to read some rows:
using(SqlConnection conn = new SqlConnection(myConnString))
{
using(SqlCommand cmd = new SqlCommand(cmdTxt, conn))
{
conn.Open();
using(SqlDataReader rdr = cmd.ExecuteReader())
{
while(rdr.Read())
{
/* do something with rows */
}
}
}
}
我明白了还有的LINQ to SQL（我不喜欢它），实体框架（还是个婴儿）。我不必键入我的查询出来没有问题，我只是不希望有键入命令敷设渠道，排迭代，每次等。
I understand there is LINQ to SQL (I don't like it), and the Entity Framework (still a baby). I have no problems having to type my queries out, I just don't want to have to type the command contruction, row iterator, etc each time.
我环顾四周，发现的东西，我认为会为我工作，并试图实现它使事情更容易为我。正如你可以在注释中看到的，我得到了SqlDataReader的关闭错误。我猜这是因为using语句的int DataFactory.ExecuteReader（）方法的可能。当返回的读者，Dispose方法被称为我的SqlConnection和的SqlCommand变量。我说得对吗？ ？如果是的话，人们应该如何管理连接和命令变量
I looked around and found something that I thought would work for me, and tried to implement it to make things easier for me. As you can see in the comment, I get an error that the SqlDataReader is closed. I'm guessing it's probably because of the using statement int the DataFactory.ExecuteReader() method. When the reader is returned, the dispose method is called on my SqlConnection and SqlCommand variables. Am I right there? If so, how should one manage the connection and command variables?
编辑：我更新了我的代码示例，以更好地反映我在做什么。
I updated my code example to better reflect what I am doing.
public class DataFactory
{
public DataFactory()
{}
public DataFactory(string connectionString)
{
_connectionString = connectionString;
}
protected _connectionString = "Data Source=Localhost, etc, etc";
private string ConnectionString
{
get{return _connectionString;}
}
public SqlConnection GetSqlConnection()
{
return new SqlConnection(ConnectionString);
}
public SqlDataReader ExecuteReader(string cmdTxt)
{
using(SqlConnection conn = new SqlConnection(ConnectionString))
{
using(SqlCommand cmd = new SqlCommand(cmdTxt, conn))
{
conn.Open();
return cmd.ExecuteReader();
}
}
}
}
public IRepository<T>
{
T GetById(int id);
}
public MyTypeRepository: IRepository<MyType>
{
private static DataFactory _df = new DataFactory();
public MyType GetById(int id)
{
string cmdTxt = String.Format("SELECT Name FROM MyTable WHERE ID = {0}", id);
using(SqlDataReader rdr = _df.ExecuteReader(cmdTxt))
{
if(rdr.Read()) /* I get an error that the reader is already closed here */
{
return new MyType(
Convert.ToInt32(rdr["Id"]),
rdr["Name"]);
}
else
{
return null;
}
}
}
}
public class MyType
{
public MyType(int id, string name)
{
_id = id;
_name = name;
}
private string _name;
public string Name
{
get{return _name;}
}
private int _id;
public int Id
{
get{return _id;}
}
public override void ToString()
{
return string.Format("Name: {0}, Id: {1}", Name, Id);
}
}
public class Program
{
private static MyTypeRepository _mtRepo = new MyTypeRepository();
static void Main()
{
MyType myType = _mtRepo.GetById(1);
Console.WriteLine(myType.ToString());
}
}
我也想知道什么，我这样做使任何意义，或者，如果没有，如何实现类似的东西，这样我就不必键入连接创建等经常。
I also would like to know if what I'm doing makes any sense, or, if not, how to achieve something similar so that I don't have to type the connection creation, etc so often.
推荐答案
您的ExecuteReader方法将关闭返回在读者面前的连接。相反，它应该实现这样的：
Your method ExecuteReader will close the connection before returning the Reader. Instead it should be implemented something like:
public IDataReader ExecuteReader(string cmdTxt)
{
SqlConnection conn = new SqlConnection(...);
try
{
SqlCommand cmd = new SqlCommand(cmdTxt, conn);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
conn.Close();
throw;
}
}
ExecuteReader方法的
呼叫者需要处置的IDataReader
Callers of the ExecuteReader method will need to dispose the IDataReader:
using(IDataReader reader = ExecuteReader(commandText))
{
...
} // reader will be disposed here and will close the connection.
请注意，上面并没有调用Dispose SqlCommand对象上。根据我的经验，并从反射看的SqlCommand这是没有必要，只要SqlConnection的配置。但我相信下面的工作，如果你要处理的：
Note that the above does not call Dispose on the SqlCommand object. In my experience and from looking at SqlCommand with Reflector it's not necessary as long as the SqlConnection is disposed. But I believe the following will work if you do want to dispose it:
public IDataReader ExecuteReader(string cmdTxt)
{
SqlConnection conn = new SqlConnection(...);
SqlCommand cmd = null;
try
{
cmd = new SqlCommand(cmdTxt, conn);
conn.Open();
IDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Dispose();
return reader;
}
catch
{
if (cmd != null) cmd.Dispose();
conn.Close();
throw;
}
}
这篇关于如何管理数据访问层SqlDataReaders？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
