So, I've been preaching for a long time that it's best to use the Using pattern when working with anything that implements IDisposable. That way, you don't have to worry about calling Dispose, or calling Close. The framework will just do it for you. I've answered this question on the forums several times so I thought I'd past some code I'm doing right now for simple ado.net stuff. It's actually code that is inside a business object that I hook up to an ObjectDataSource in asp.net 2.0.
The code is posted below. I think you'll get the idea.
[DataObjectMethod(DataObjectMethodType.Select, false)]
public List<DataObjectCategory> GetByPrimaryKey(int id)
{
string sqlWhere = String.Format(" WHERE id={0} ", id);
return GetByRawSQL(string.Empty, sqlWhere);
}
private List<DataObjectCategory> GetByRawSQL(string sortData, string sqlWhereClause)
{
string sqlBaseString = "SELECT ContentTypeId,Description,SortOrder,Id FROM Category ";
List<DataObjectCategory> DataTemplateODSList = new List<DataObjectCategory>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (!String.IsNullOrEmpty(sqlWhereClause))
{
sqlBaseString += sqlWhereClause;
}
using (SqlCommand cmd = new SqlCommand(sqlBaseString, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
int contenttypeid = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string description = reader.IsDBNull(1) ? "" : reader.GetString(1);
int sortorder = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
int id = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
DataObjectCategory td = new DataObjectCategory(contenttypeid,
description, sortorder, id);
DataTemplateODSList.Add(td);
}
}
catch (Exception ee)
{
throw new ApplicationException(ee.ToString());
}
}
}
}
Hope this helps!