I am using below virtual method to read the data from SQL Data Reader like:
public IList<District> GetList()
{
IList<District> _list = new List<District>();
SqlConnection con = new SqlConnection(ConStr);
try
{
string StoreProcedure = ConfigurationManager.AppSettings["SP"].ToString();
SqlCommand cmd = new SqlCommand(StoreProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
_list = new GenericReader<District>().CreateList(rdr);
rdr.Close();
con.Close();
}
finally
{
IsConnectionOpenThenClose(con);
}
return _list;
}
District Class:
public class District
{
public int id { get; set; }
public string name { get; set; }
}
And GenericReader Class as:
public class GenericReader<T>
{
public virtual List<T> CreateList(SqlDataReader reader)
{
var results = new List<T>();
while (reader.Read())
{
var item = Activator.CreateInstance<T>();
foreach (var property in typeof(T).GetProperties())
{
if (!reader.IsDBNull(reader.GetOrdinal(property.Name)))
{
Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
property.SetValue(item, Convert.ChangeType(reader[property.Name], convertTo), null);
}
}
results.Add(item);
}
return results;
}
}
Is this approach is better or still, we can refactor?