CODEWITHSUNDEEP
c#sqlparameterized
David M
David M

Reputation: 2943

SQL Query works but equivalent C# query doesn't. .

I've got a not particularly complex SQL query that returns exactly what I want when run in Server Mgt Studio, but when I run it in C# the DataGrid comes up blank. (And yes - hostnames sanitized, I don't just have a terrible naming scheme.)

Hopefully someone with sharper eyes than I can pick up the issue.

SQL query: 

SELECT MachineName, InstanceName, CounterName, 
AVG(CASE WHEN CounterValue > 0 THENCounterValue ELSE NULL END)/1024 as 'Mean (KB)'
FROM DataCollector.dbo.JobCounterSummary
WHERE 
((MachineName = '\\HOST001' AND (InstanceName = 'D:' OR InstanceName = 'E:')) OR
(MachineName = '\\HOST002' AND (InstanceName = 'E:' OR InstanceName = 'G:')) OR
(MachineName = '\\HOST003' AND (InstanceName = 'G:' OR InstanceName = 'H:')) OR 
(MachineName = '\\HOST004' AND (InstanceName = 'G:' OR InstanceName = 'H:') OR
(MachineName = '\\HOST005' AND InstanceName = 'G:') OR 
(MachineName = '\\HOST006' AND InstanceName = 'C:') OR 
(MachineName = '\\HOST007' AND InstanceName = 'C:'))) AND
InstanceName != '_Total' AND 
InstanceName NOT LIKE 'Harddisk%' AND 
(CounterName = 'Avg. Disk Bytes/Write' OR CounterName = 'Avg. Disk Bytes/Read') 
GROUP BY MachineName, CounterName, InstanceName 
ORDER BY MachineName, InstanceName, CounterName

And the C# code:

public DataTable dtJobReadWrite = new DataTable();
    private void GetReadWriteStats()
    {
        dtJobReadWrite.Clear();
        string connstr = @"Server=SQLSERVER\SQL;Database=DataCollector;Trusted_Connection=True; MultipleActiveResultSets=true";
        SqlConnection conn = new SqlConnection(connstr);
        conn.Open();
        string commandstr =
            "SELECT MachineName, InstanceName, CounterName, AVG(CASE WHEN CounterValue > 0 THEN CounterValue ELSE NULL END)/1024 as \"Mean (KB)\" " +
            "FROM dbo.JobCounterSummary " +
            "WHERE " +
                "((MachineName = @HOST001 AND (InstanceName = @DriveD OR InstanceName = @DriveE)) OR " +
                "(MachineName = @HOST002 AND (InstanceName = @DriveE OR InstanceName = @DriveG)) OR " +
                "(MachineName = @HOST003 AND (InstanceName = @DriveG OR InstanceName = @DriveH)) OR " +
                "(MachineName = @HOST004 AND (InstanceName = @DriveG OR InstanceName = @DriveH) OR " +
                "(MachineName = @HOST005 AND InstanceName = @DriveG) OR " +
                "(MachineName = @HOST006  AND InstanceName = @DriveC) OR " +
                "(MachineName = @HOST007 AND InstanceName = @DriveC))) AND " +
                "InstanceName != @_Total AND " +
                "InstanceName NOT LIKE @HardDisk AND " +
                "(CounterName = @AvgDiskBytesWrite OR CounterName = @AvgDiskBytesRead) " +
            "GROUP BY MachineName, CounterName, InstanceName " +
            "ORDER BY MachineName, InstanceName, CounterName";

        SqlCommand jobsCommand = new SqlCommand(commandstr, conn);
        jobsCommand.Parameters.AddWithValue("@HOST001", "\\HOST001");
        jobsCommand.Parameters.AddWithValue("@HOST002", "\\HOST002");
        jobsCommand.Parameters.AddWithValue("@HOST003", "\\HOST003");
        jobsCommand.Parameters.AddWithValue("@HOST004", "\\HOST004");
        jobsCommand.Parameters.AddWithValue("@HOST005", "\\HOST005");
        jobsCommand.Parameters.AddWithValue("@HOST006", "\\HOST006");
        jobsCommand.Parameters.AddWithValue("@HOST007", "\\HOST007");
        jobsCommand.Parameters.AddWithValue("@DriveC", "C:");
        jobsCommand.Parameters.AddWithValue("@DriveD", "D:");
        jobsCommand.Parameters.AddWithValue("@DriveE", "E:");
        jobsCommand.Parameters.AddWithValue("@DriveG", "G:");
        jobsCommand.Parameters.AddWithValue("@DriveH", "H:");
        jobsCommand.Parameters.AddWithValue("@_Total", "_Total");
        jobsCommand.Parameters.AddWithValue("@HardDisk", "Harddisk%");
        jobsCommand.Parameters.AddWithValue("@AvgDiskBytesWrite", "Avg. Disk Bytes/Write");
        jobsCommand.Parameters.AddWithValue("@AvgDiskBytesRead", "Avg. Disk Bytes/Read");
        SqlDataAdapter jobsAdapter = new SqlDataAdapter(jobsCommand);
        jobsAdapter.Fill(dtJobReadWrite);
        dgvReadWrites.DataSource = dtJobReadWrite;
        conn.Close();
    }

Upvotes: 0

Views: 201

Answers (2)

Mr47
Mr47

Reputation: 2655

as \"Mean (KB)\"

I guess those ought to be single qoutes...
And probably you don't have to escape them in that case too.

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

My guess is that it's your strings:

"\\HOST001"

You're not escaping out your backslashes, so the strings only contain a single backslash. Fix it by either:

"\\\\HOST001"
//or
@"\\HOST001"

Upvotes: 5

Related Questions