I want to create a method which should return the data-reader because I don't want to open and close connections again and again. Can Anyone help me to create a method which I just need to pass command text and parameter.
2 Answers
Well, you could do that, you might even want to do that, but you really should not do that.
Data readers can only work in a connected mode, meaning that to work with a data reader it's required to keep an open connection to the database - so if your method returns a data reader it must leave the connection open, and it's the responsibility of the calling method to close and dispose it once it's done using the data reader.
What you should do is use an ORM such as Entity Framework or Dapper (my personal favorite) to get the results from the database directly into classes so that the connection to the database can be closed as soon as the SQL statement have executed.
If you can't, or don't want to use an ORM, here is one way you can reduce code repetition:
Use a generic method that takes in the sql to execute, a Func<IDbDataReader, T>, the parameters needed for the sql command, and returns an instance of T:
public T Execute<T>(
// sql statement to execute
string sql,
// E.g CommandType.StoredProcedure
CommandType commandType,
// this holds the logic to create an instance of T from the data reader
Func<IDbDataReader, T> populator,
// parameters required to the sql
params IDbDataParameter[] parameters)
{
using(var con = new SqlConnection(_connectionString))
{
using(var cmd = new SqlCommand(sql, con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(parameters);
con.Open();
using(var reader = cmd.ExecuteReader())
{
return populator(reader);
}
}
}
}
Now, the populator will execute inside the using statements so everything that needs to be disposed gets disposed exactly when it should.
Comments
I created this common method to read data from DB using SQL data-reader and it works fine for me.
public static SqlDataReader ExecuteCommonReader(string Query, CommandType type, params IDataParameter[] sqlParams )
{
try
{
SqlCommand _cmd = new SqlCommand();
string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var _con = new SqlConnection(connString);
_con.Open();
_cmd.Connection = _con;
_cmd.CommandType = type;
_cmd.CommandText = Query;
if (sqlParams != null)
{
foreach (IDataParameter para in sqlParams)
{
_cmd.Parameters.AddWithValue(para.ParameterName, para.Value);
}
}
return _cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
return null;
}
}
SqlConnectionfor every query/command. Notice that ADO.NET's connection pool will reuse physically opened connections, so price for open/close connection will be simply creating/disposing instance of the class.