Taking long time to fill data table from data adapter C#

Question

I have created a method to get a datatable, it was taking more than a minute for this line of code

da.Fill(dt)

but in SQL Server, with the same parameters, the procedure will execute with in a second:

protected DataTable RetrieveDataTable(string staffId, string 
    DtFrom, string DtTo, string Stm, string JrId,int ActiveFlag)
{
    dt = new DataTable();

    SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"]);
    Conn.Open();

    SqlCommand cmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();

    try
    {
        cmd = new SqlCommand("Proc_QCArticleDetails", Conn);
        cmd.Parameters.Add(new SqlParameter("@StaffId", staffId));
        cmd.Parameters.Add(new SqlParameter("@FrmDt", DtFrom));
        cmd.Parameters.Add(new SqlParameter("@ToDt", DtTo));
        cmd.Parameters.Add(new SqlParameter("@ActiveFlag", ActiveFlag));

        try
        {
            int jrid = Convert.ToInt32(JrId);
            cmd.Parameters.Add(new SqlParameter("@journalId", JrId));
        }
        catch (Exception)
        {
        }

        cmd.Parameters.Add(new SqlParameter("@Statement", Stm));
        cmd.CommandTimeout = 250;
        cmd.CommandType = CommandType.StoredProcedure;

        da.SelectCommand = cmd;
        da.Fill(dt);     // taking a long time to run here
    }
    catch (Exception x)
    {
        MessageBox.Show(x.ToString());
    }
    finally
    {
        cmd.Dispose();
        Conn.Close();
    }

    return dt;
}

Please update your post to include `Proc_QCArticleDetails`. Also please update it to include how many records are typically returned. — mjwills, Oct 27 '17 at 12:24
Yuck! Empty catch and try-catch to parse string to int, use `int.TryParse`. Also, use the `using`-statement for everything that implements `IDisposable` like the `SqlConnection`. — Tim Schmelter, Oct 27 '17 at 12:24
Post the query. I'm pretty sure you *don't* execute the same query in SSMS even if you think you do. For example `dtFrom`, `dtoTo` look like dates but have a *string* type. Are you *sure* that string is correctly parsed? Are you comparing those strings with date fields or string fields? If strings, you have a serious bug and probably return the wrong data. Pass *properly typed parameters*. — Panagiotis Kanavos, Oct 27 '17 at 15:12

score 0 · Answer 1 · answered Oct 27 '17 at 13:06

0

Please take a look at below discussion. Adapter.Fill takes long

According to this post there might be issue because of method of adding parameters. Also use Using and int.TryParse as suggested in comment.

answered Oct 27 '17 at 13:06

Happy

1

Don't post an answer to point out a duplciate, flag the question as a duplicate instead. – Scott Chamberlain Oct 27 '17 at 15:47

score 0 · Answer 2 · answered Oct 28 '17 at 06:34

Even though procedure execute with in second it takes long time to fill data table (da.fill(dt)),I Not able to find exact Issue but i resolved by removing where condition in stored procedure and filter data table in C# using data view Finally issue was resolved for me.

Taking long time to fill data table from data adapter C#

2 Answers2