Adding a column adds thousands of empty rows

Question

I have an sql statement that was returning 161 rows. When I added an additional column, that is NOT a key, the number of rows I get back increases to over 5000, the vast majority of which are completely empty.

The sql statement is supposed to take a list of configurations for one customer and display all matching configurations from another customer ('matching' meaning having the same configuration name). I need to see whether it has a match or not, and also have the configuration data for those without a match.

My code:

...

    var myTable = new DataTable();
    try
    {
        string sql = "SELECT SourceConfig.ConfigurationId AS 'SourceID', " +
                            "SourceConfig.Name, " +
    /* Added this line */   "SourceConfig.ConfigurationData AS 'SourceData', " +
                            "TargetConfig.ConfigurationId AS 'TargetID' " +
                     "FROM ConfigurationTable SourceConfig " +
                     "LEFT JOIN ConfigurationTable TargetConfig ON " +
                         "(TargetConfig.CustomerID = " + getTargetID() + " AND " +
                         "SourceConfig.Name = TargetConfig.Name)" +
                     "WHERE SourceConfig.CustomerId = " + getSourceID();
        myTable = this.accessService.FetchDataFromCustomerDatabase(sql);
        myTable.PrimaryKey = new DataColumn[1] { myTable.Columns[0] }; // Error here
    }
    catch (Exception e)
    {
        // Record error message
        return;
    }

    // Do stuff with 'myTable'
...

My previous code didn't have that 3rd line of the sql statement to bring in the 'ConfigurationData', but why would adding that line cause this error? The column 'ConfigurationData' is not a key, primary or otherwise. I've tried adding another WHERE condition to exclude null values, but I still get thousands of rows.

The real kicker is that this error does NOT happen when I run the same SQL statement in 'Microsoft SQL Management Studio'. Over there I still get 161 rows returned; it's only when I run my c# code in Visual Studio that I get bombarded with these blank rows.

I think the problem is related to the new column's data type, which SQL Management Studio labels as "(XML(.), not null)". I've tried adding other columns instead of 'ConfigurationData' and it's just that one that blows up like this.

Your code is susceptible to SQL injection. You should be using parameters. — Cameron, Jun 27 '17 at 15:00
If you suspect your data layer is the problem, then investigate your data layer. Open the black box that is `FetchDataFromCustomerDatabase()`. — Jeroen Mostert, Jun 27 '17 at 15:10
I'm horrified at the idea of opening that box, but if it's the only way... also, good point on the SQL injection, I maybe shouldn't leave that for later. — TS-, Jun 27 '17 at 15:19

score 0 · Accepted Answer · answered Jun 27 '17 at 17:04

So while looking trying to protect myself against SQL injection, I ended up fixing my original problem. Go figure. I found the solution taken from here: Read SQL Table into C# DataTable

Essentially the problem was with 'FetchDataFromCustomerDatabase()', and I don't use that method if I'm doing things the safe way. The new code:

...

    var myTable = new DataTable();
    try
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            string sql = "SELECT SourceConfig.ConfigurationId AS 'SourceID', " +
                                "SourceConfig.Name, " +
                                "SourceConfig.ConfigurationData AS 'SourceData', " +
                                "TargetConfig.ConfigurationId AS 'TargetID' " +
                         "FROM ConfigurationTable SourceConfig " +
                         "LEFT JOIN ConfigurationTable TargetConfig ON " +
                             "(TargetConfig.CustomerID = @targetCustID AND " +
                             "SourceConfig.Name = TargetConfig.Name)" +
                         "WHERE SourceConfig.CustomerId = @sourceCustID";

            var command = new SqlCommand(sql, connection);
            command.Parameters.Add("@sourceCustID", SqlDbType.Int);
            command.Parameters.Add("@targetCustID", SqlDbType.Int);
            command.Parameters["@sourceCustID"].Value = sourceCustID;
            command.Parameters["@targetCustID"].Value = targetCustID;

            var da = new SqlDataAdapter(command);
            da.Fill(myTable);
            da.Dispose();

            myTable.PrimaryKey = new DataColumn[1] { myTable.Columns[0] };
        }
    }
    catch (Exception e)
    {
        // Record error message
        return;
    }

    // Do stuff with 'myTable'
...

Adding a column adds thousands of empty rows

1 Answers1