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 */
}
}
}
}
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.
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?
Edit: 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.