TrussedDev
TrussedDev

Reputation: 1

C# SqlDataAdapter slow reading data from stored procedure execution

I have searched endlessly for an answer I have ssms query that runs in about a second.

It returns a lot of data but they are all sums and so really it just returns one row. The query in SSMS executes in a little over a second. Does anyone have any ideas?

Don't get to caught up with property info portion it basically just abstracts the request model into stored procedure parameters and works fine but the SqlDataAdapter hangs for 8+ seconds. Again it's one row of data, 5 or 6 columns and executes in SSMS in less than 2 seconds.

public DataTable GetProcedureWithFilterDt(string procedure, ReportClientRequest request)
{
    DataTable dt = new DataTable();

    try
    {
        SqlConnection sqlConn = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
        SqlCommand sqlCmd = new SqlCommand(procedure, sqlConn);

        foreach (PropertyInfo property in request.GetType().GetProperties())
        {
            var str = property.GetValue(request)?.ToString() ?? null;
            string param = "@" + property.Name;
            string value = str;

            if (!String.IsNullOrWhiteSpace(str))
            {
                if (property.PropertyType == typeof(List<int>))
                {
                    List<int> list = property.GetValue(request) as List<int>;
                    if (list == null) continue;

                    DataTable TableDefined = new DataTable();
                    TableDefined.Columns.Add("id", typeof(Int32));

                    foreach (var id in list)
                    {
                        var row = TableDefined.NewRow();
                        row["id"] = id;
                        TableDefined.Rows.Add(row);
                    }
                    sqlCmd.Parameters.Add(new SqlParameter(param, SqlDbType.Structured)
                        {
                            TypeName = "dbo.idTableType",
                            Value = TableDefined
                        });
                }
                else if (property.PropertyType == typeof(List<long>))
                {
                    List<long> list = property.GetValue(request) as List<long>;
                    if (list == null) continue;

                    DataTable TableDefined = new DataTable();
                    TableDefined.Columns.Add("id", typeof(Int64));

                    foreach (var id in list)
                    {
                        var row = TableDefined.NewRow();
                        row["id"] = id;
                        TableDefined.Rows.Add(row);
                    }

                    sqlCmd.Parameters.Add(new SqlParameter(param, SqlDbType.Structured)
                        {
                            TypeName = "dbo.bigIdTableType",
                            Value = TableDefined
                        });
                }
                else
                {
                    value = property.GetValue(request).ToString();
                    sqlCmd.Parameters.Add(new SqlParameter(param, value));
                }
            }
        }

        sqlCmd.CommandTimeout = 60;
        sqlCmd.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCmd);
        dataAdapter.Fill(dt);
    }
    catch (Exception exp)
    {
    }

    return dt;
}

Upvotes: 0

Views: 42

Answers (0)

Related Questions