How do I properly use a SqlDataReader?
.net, ado.net, c#
Solution
Actually you are in fact leaving yourself open a bit. You really want to write it like this:
using (SqlConnection cnn = new SqlConnection(cnnString))
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
// use parameters in your SQL statement too, so you can do this
// and protect yourself from SQL injection, so for example
// SELECT * FROM table WHERE field1 = @parm1
cmd.Parameters.AddWithValue("@parm1", val1);
cnn.Open();
using (SqlDataReader r = cmd.ExecuteReader())
{
}
}
because you need to make sure these objects get disposed. Further, by going this direction you don't need `dataReader.Close()`. It will get called when it gets automatically disposed by the `using` statement.
Now, wrap that collection of statements inside a `try...catch` and you're in business.
Problem
I have 2 methods as below : ``` internal static SqlDataReader SelectData(string sql) { using (var sqlConnection = new SqlConnection(Constant.ConnectionString)) { sqlConnection.Open(); var sqlCommand = new SqlCommand(sql, sqlConnection); var dataReader = sqlCommand.ExecuteReader(); return dataReader; } } ``` ============ And using this method as : ``` var dataReader = SelectData(---some sql ---); private void AddData(dataReader) { while (dataReader.Read()) { Employee e = new Employee(); e.FirstNamei = dataReader["Name"].ToString(); } dataReader.Close(); } ``` I know we can merge this two method, but I am looking at better way write this, OR this can cause some problem??