Issue with no data being returned from SQL query when a temp table is used

[MattNorman]

I am trying to improve the speed of a query that pulls data for a report.

For some reason the query I have written works perfectly fine in SQL Management Studio and returns the expected data.

If I run it from my app it does not return any data.

I have tried the following:

• Wrap query in a transaction
• Tried using a global temporary table instead of local
• Tried executing query to create temp table and then separate query to get data

Can anyone see a reason this would not be returning data?

using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();
    SqlDataReader dr;

    // Create command.
    var cmd = new SqlCommand("", connection, transaction);

    query += $@"
                        CREATE TABLE #CallerIDs
                        (
                            [CallerID] NVARCHAR(50)
                        )
                        INSERT INTO #CallerIDs ([CallerID])
                        SELECT [CallerID]
                        FROM [ApexCCM].[dbo].[HistoricalCallDetailData]
                        WHERE Date >= @StartDate
                            AND Date <= @EndDate
                            AND [Time] >= @StartTime
                            AND [Time] <= @EndTime
                            AND LEN(CallerID) > 5
                            AND LEFT(CallerID, 1) <> 'T'
                        ";

    if (filters.Skills.Count > 0) { query += ("AND [SkillName1] IN (@Skills)"); }

    query += $@"
                        GROUP BY [CallerID]
                        HAVING COUNT(*) > 1
                        ";

    query += $@"
                        SELECT h.[RecordID], h.[Date], h.[Time], h.[CallerID], h.[DispositionName], h.[SkillName1]
                        FROM [ApexCCM].[dbo].[HistoricalCallDetailData] h
                        JOIN #CallerIDs c ON h.[CallerID] = c.[CallerID]
                        WHERE h.[Date] >= @StartDate
                            AND h.[Date] <= @EndDate
                            AND h.[Time] >= @StartTime
                            AND h.[Time] <= @EndTime
                        ";

    query += $@"
                        DROP TABLE #CallerIDs
                        ";

    // Parameters.
    _ = cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(filters.StartDate));
    _ = cmd.Parameters.AddWithValue("@EndDate", DateTime.Parse(filters.EndDate));
    _ = cmd.Parameters.AddWithValue("@StartTime", CMSTimeFromTimeSpan(filters.StartTime));
    _ = cmd.Parameters.AddWithValue("@EndTime", CMSTimeFromTimeSpan(filters.EndTime));

    // Skill name parameters.
    string selectedSkillNames = string.Join(",", filters.Skills.Where(a => a.IsChecked).Select(a => $"'{a.SkillName}'"));
    if (!string.IsNullOrEmpty(selectedSkillNames))
    {
        _ = cmd.Parameters.AddWithValue("@Skills", selectedSkillNames);
    }

    // Update command text.
    cmd.CommandText = query;

    Console.Write(query);

    // Get data.
    dr = cmd.ExecuteReader();
    returnData = GetCallDetailRepeatCallersModelListFromDataReader(dr);
    dr.Close();

    // Get policy matches.
    _ = Parallel.ForEach(returnData, model =>
                         {
                             model.MatchedPolicies = DataManagerPolicyLookup.GetPolicyMatches(model.CallerID);
                         });

    transaction.Commit();
}

 

[Skydiver]

So you are saying that GetCallDetailRepeatCallersModelListFromDataReader(dr) does not have any data to iterate over? Or did you just forget to return data from that method?

 

[MattNorman]

So you are saying that GetCallDetailRepeatCallersModelListFromDataReader(dr) does not have any data to iterate over? Or did you just forget to return data from that method?

That method is ok and has not changed with this attempt to improve the speed of the query.

If I add a breakpoint I can see that the data reader does not have any rows before that method is called.

Oddly I have also amended a different query. The query itself has not changed, I have just changed from using a StringBuilder to a string:

Old:

 = query.Append("SELECT Date, Time, CallerID, DialedNumber, SkillName1, DispositionName, COUNT(CallerID) FROM ApexCCM.dbo.HistoricalCallDetailData ");
_ = query.AppendLine("WHERE Date >= @StartDate AND Date <= @EndDate ");
_ = query.AppendLine("AND Time >= @StartTime AND Time <= @EndTime ");
_ = query.AppendLine("AND LEN(CallerID) > 6 ");
_ = query.AppendLine("AND CallerType = 'External' ");
_ = query.AppendLine("AND SkillName1 IS NOT NULL ");
_ = query.AppendLine("AND DispositionName = 'Call Abandoned (ACD)' ");

New:

query += $@"
    SELECT [Date], [Time], [CallerID], [DialedNumber], [SkillName1], [DispositionName], COUNT([CallerID])
    FROM [ApexCCM].[dbo].[HistoricalCallDetailData]
        WHERE [Date] >= @StartDate AND [Date] <= @EndDate
            AND [Time] >= @StartTime AND [Time] <= @EndTime
            AND LEN([CallerID]) > 6
            AND [CallerType] = 'External'
            AND [SkillName1] IS NOT NULL
            AND [DispositionName] = 'Call Abandoned (ACD)'
    ";

The second query also fails to return any data even though I have only changed the way in which the query string is constructed. There seems to be something about writing the query this way that is causing it even though it does not return any errors.

 

[Skydiver]

Sounds like a SQL problem rather than a C# problem.

 

[MattNorman]

Sounds like a SQL problem rather than a C# problem.

Possibly but if I output the query to console and then copy that to SQL Management Studio it works fine. I will have to do some further testing.

 

[MattNorman]

Issue solved. It was due to the way I was added the parameter for the IN clause.

The criteria for that clause is multiple string values and each value has to be it's own parameter.

I had created a helper method for this exact reason but hadn't looked at it in a while and thought I was just adding redundant complexity.

 

[Skydiver]

So it was a SQL problem. C# doesn't have an IN clause.

 

[cjard]

Use dapper; it will do the IN expansion for you..

..but you need to form the SQL like:

WHERE someColumn IN @someValue

without the parentheses