Data Reader will increase performance when store procedure will return millions of data

[Sudip_inn]

string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string procedure = "CustOrderHist";

// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
SqlDataReader r;

// Configure command and add parameters.
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
param.Value = "ALFKI";

// Execute the command.
con.Open();
r = cmd.ExecuteReader();
while (r.Read())
{
Console.WriteLine(r["Total"].ToString() + " of " +
r["ProductName"].ToString());
}

con.Close();
if my store proc return 20,00,000 data then should i use data reader because i heard it fetch data not in one go....should be performant ? as per my scenario i can no do pagination....please advise.

Continue reading...