Saving hundreds of thousands of records at a time in SQL Server using ASP.Net

I have a query that fet开发者_如何转开发ches 200,000 records at a time with 74 columns from the server and then I have to insert them into my table on a client machine. What is the best way to do this without getting an "out of memory" error in ASP.Net (using C# and SQL Server 2005 database).

You can use a DataReader and pass that as input to SqlBulkCopy using an appropriate batchsize.

In Class.cs

 public int Saving(int s)
 {
    SqlCommand cmd = new SqlCommand();
    using (SqlConnection cn = new SqlConnection(classname.ConnectionString)){
    cn.Open();
    SqlTransaction trans;
    trans = cn.BeginTransaction();
    try{
    cmd = new SqlCommand("Storedprocedurename", cn, trans);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@spparameter", SqlDbType.Int).Value = s;
    int DataResult =Convert.ToInt32(cmd.ExecuteScalar());
    if (DataResult != 0)
    {
    trans.Commit();
    return DataResult;
    }
    else
    {
    trans.Rollback();
    return -1;
    }
    }
    catch (Exception ex)
    {
    string msg = ex.Message;
    trans.Rollback();
    trans.Dispose();
    return 0;
   }
}