Creating a datareader in C#

/******************************************************************************/

/*Creates a SQL reader for a sqlstring passed*/
public static SqlDataReader GetDr( string sqlText)
{
SqlDataReader dr;
SqlConnection sqlConn = new SqlConnection(connString);
SqlCommand sqlCmd = new SqlCommand(sqlText,sqlConn);
sqlCmd.Connection.Open();
dr = sqlCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return dr;
}
/******************************************************************************/

// Calling this function would be like this :-
// The code to initialize all the labels and the picture by pulling out the record...
string sql = "SELECT * from SomeTable";
try
{
//Its an update ...yuppiee load the values from the table
SqlDataReader profileReader = databaseCalls.GetDr(sql);
}
catch(Exception r)
{

Messages1.errMessageException("", "" ,0,2,3,r);
Messages1.Visible=true;
}

//Basically used to read a perticular set of values from the database into an array...in case of update usually

I like to do as little work as I can when I code, so I used to like the DataSet. It can be filled and ready to go in just 3 lines of code, and then iterated using a nice, simple foreach loop (it’s even easier if you use typed DataSets!). It’s a nice collection to work with. But often, performance is required at the expense of elegance -- especially on a performance-critical Web application.

The DataSet actually uses a DataReader to populate itself. A DataReader is a lean, mean access method that returns results as soon as they’re available, rather than waiting for the whole of the query to be populated into a DataSet. This can boost your application performance quite dramatically, and, once you get used to the methodology, can be quite elegant in itself.


SqlConnection conn = new SqlConnection(connectionString);
SqlCommand comm = new SqlCommand("select * from mytable", conn);
comm.Connection.Open();
SqlDataReader r =
comm.ExecuteReader(CommandBehavior.CloseConnection);
while(r.Read())
{
Console.WriteLine(r.GetString(0));
}
r.Close();
conn.Close();

Here, the inspection is made as soon as data is available by employing the while loop, where r.Read() returns false if no more results are found. Not only can we therefore inspect as we go, but the DataReader only stores one result at a time on the client. This results in a significant reduction in memory usage and system resources when compared to the DataSet, where the whole query is stored.

Now, there are times when only a DataSet will suffice. Often, you’ll need to serialize your results, or pass the query results on to the next tier of your application. In these cases, a collection is required, and the DataSet provides a well-supported mechanism for doing this. For example, you can quickly serialize a DataSet to XML by calling the WriteXML method, or pass a DataSet in a SOAP method. While you can create your own collections to store your results, with all this in-built, optimized functionality at hand, the DataSet is still a powerful type to keep in mind.

However, for the majority of queries employed by Web applications, where data is found, displayed, and then forgotten, the DataReader will increase your performance drastically, with only a little extra work. Sounds like a good deal to me!