Fetch data from database either with a stored procedure or a query
My C# is quite rusty, so take everything I'm saying with a grain of salt; I stand by the general principles of what I'm writing, but the syntax/style might be a little off.



The first problem is that the two branches of your function are identical except for the SqlCommand being used. You should factor out how you create the SqlCommand and then do the rest the same way. For example,

private SqlCommand GetSqlCommandFromTableIfExists(string table, int? start, int? end, SqlConnection conn, string default)
{
    SqlCommand cmd;
    if (_sqlToRun.ContainsKey(table))
    {
        string sql = _sqlToRun[table];
        cmd = new SqlCommand(sql, conn) { CommandType = CommandType.StoredProcedure };

        var startParam = new SqlParameter
        {
            ParameterName = "@startRowNum",
            Value = start,
            SqlDbType = SqlDbType.Int
        };

        cmd.Parameters.Add(startParam);

        var endParam = new SqlParameter
        {
            ParameterName = "@endRowNum",
            Value = end,
            SqlDbType = SqlDbType.Int
        };

        cmd.Parameters.Add(endParam);
        var outputParam = cmd.Parameters.Add("@recordCount", SqlDbType.Int);
        outputParam.Direction = ParameterDirection.Output;
    } 
    else
    {
        // You could easily not make the default a parameter if you prefer
        cmd = new SqlCommand(default, conn);
    }

    return cmd;
}


Then your function becomes

private async Task&lt;Response&gt; GetDataAsync(string table, int? start, int? end)
{

    var data = new List&lt;Dictionary&lt;string, object&gt;&gt;();
    var resp = new Response();

    using (var conn = new SqlConnection(_ConnectionString))
    {
        var cmd = GetSqlCommandFromTableIfExists(table, start, end, conn, "SELECT * FROM " + table);
        await conn.OpenAsync();
        using (var rdr = await cmd.ExecuteReaderAsync())
        {
            while (await rdr.ReadAsync())
            {
                var dictionary = new Dictionary&lt;string, object&gt;();
                foreach (var i in Enumerable.Range(0, rdr.FieldCount))
                {
                    var dbValue = string.IsNullOrWhiteSpace(rdr.GetValue(i).ToString()) == true
                        ? string.Empty
                        : rdr.GetValue(i);

                    dictionary.Add(rdr.GetName(i), dbValue);
                }

                data.Add(dictionary);
            }
        }

        Int32.TryParse(cmd.Parameters["@recordCount"].Value.ToString(), out var recordCount);
        resp.Data = data;
        resp.RecordCount = recordCount;
    }

    return resp;
}




Type unsafety is bad

Right now you're using a raw object as the value-type in your dictionary and Response - why? I assume its because this is a general function that is independent of the data you're grabbing, but it means you're losing out on what data you're adding and any kind of type safety. I think making the function and your Response type generic, and tweaking your implementation, you can ensure type-safety.



Don't make some random data object; just use resp.Data.