SqlDataAdapter.Fill() Timeout - Underlying Sproc R

Question

I have a SqlDataAdapter that is being populated with 21 rows of data (4 columns). The sproc that drives it returns in a couple seconds in SQL Mgmt Studio, but the .Fill() takes 5 minutes.

    ArrayList ret = new ArrayList();
    SqlDataAdapter da = null;
    SqlCommand cmd = null;  
        cmd = base.GetStoredProc("usp_dsp_Stuff"); //Returns immediately in MSSMS.
        cmd.CommandTimeout = 3600; // Set to 6 min - debug only
        base.AddParameter(ref cmd, "@Param1", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam1);
        base.AddParameter(ref cmd, "@Param2", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam2);
        base.AddParameter(ref cmd, "@Param3", ParameterDirection.Input, SqlDbType.Char, 1, 'C');
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt); //Takes 5 minutes.

Any ideas?

Thanks in advance! -Chris

Answer 1

I know this is too late, like 7 years too late! but I came up against this issue today and wanted to share my fix. In my instance the data been pulled from SQL was a table valued function. The table valued function only returned about 3500 rows and took less than 1 second, but it timed out on the Fill() in the c# code. I don't know who or how it works but dropping and re-creating the function fixed it. I think it is something to do with how .NET reads data given by SQL, like the way a view is needed to be recreated if you make changes to it after it's been used in say a report. Again i;m not 100% sure whats happening behind the scenes but for me it was a quick fix