SQLDataAdapter not returning last row of data

Question

I'm writing a program to pull data from an SQL database and input it into Excel. I have it all working, except that I noticed the rows I have returned in Excel do not match what I see in SQL. The last row is consistently trimmed when filling the DataTable object.

Info: Visual Studio 2015, SQL Server 11.0.5058.

I have tracked down the problem to how I am retrieving the SQL data in the below method. I put in a check after going through this method to output how many rows were returned, and it's consistently one less than I should have (queries are identical). I think it's an indexing issue but I can't see how, given the simplicity of the below method. I can't figure out why the last row is being trimmed out when put into the data table.

private static DataTable PullData(string connstr, string query)
    {
        // Creating connection to SQL server
        SqlConnection conn = new SqlConnection(connstr);
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();
        DataTable dataTable = new DataTable();
        // create data adapter
        using (SqlDataAdapter da = new SqlDataAdapter(query, conn))
        {
            da.SelectCommand.CommandTimeout = 3600;
            // query database and return the result to your datatable
            da.Fill(dataTable);
            da.Dispose();

        }

        conn.Close();
        return dataTable;
    }

****EDIT****: Thank you Tim for helping me track down the issue.. Turns out it was not in my DataTable but in my Excel Range object. There must be differences between how indexing works in Excel/SQL/C# from the last time I used this method of writing data to Excel. Since Excel technically sees Row 1 as the column header, I had to add 1 to the number of rows for the Excel Range object in order for it to accept the correct total number:

Excel.Range range = wsheet.Range["A2", String.Format("{0}{1}", GetExcelColumnName(columns), rows+1)];

The code above doesn't contain any reason to not return what you have asked in the query passed. So the problem is probably in your query text. Do you have some WHERE condition in that _query_? — Steve, Sep 14 '16 at 12:04
Also, you should always use sql parameters. This method prevents doing that since it accepts a string and doesn't give access to the `SqlCommand`'s parameter or provide a way to pass them. So it encourages sql-injection. — Tim Schmelter, Sep 14 '16 at 12:06
an identical query in SQL Studio returns all the requested data. I.E.: If a table returns 10 rows in SQL, it should return 11 rows to this method (because column names becomes the first row (row 0)). — Alex, Sep 14 '16 at 12:09
wouldn't the DataTable object return a total number of rows that includes the column names in datatable.rows.count? — Alex, Sep 14 '16 at 12:13
Acc. your last edit: The first row (header) in excel is 1 not 0 as in C#. So the first data row starts with 2. — Tim Schmelter, Sep 14 '16 at 12:39
@TimSchmelter Yes that's why the first cell in the range is A2. I actually wrote this like 2 years ago with a different database and am adapting it for new use now. Thanks again for all the help — Alex, Sep 14 '16 at 12:46

Tim Schmelter · Accepted Answer · 2016-09-14T12:28:53.560

an identical query in SQL Studio returns all the requested data. I.E.: If a table returns 10 rows in SQL, it should return 11 rows to this method (because column names becomes the first row (row 0)).

Why do you think that the column names are in the first row? You get the names via dataTable.Columns:

foreach(DataColumn col in dataTable.Columns)
{
    Console.WriteLine("Column:{0} Type:{1}", col.ColumnName, col.DataType);
}

wouldn't the DataTable object return a total number of rows that includes the column names in datatable.rows.count

No, dataTable.Rows only returns the DataRows which contain the records, not columns.

So you could f.e. list all fields of all DataRows in this way:

for(int i = 0; i < dataTable.Rows.Count; i++)
{
    DataRow row = dataTable.Rows[i];
    foreach (DataColumn col in dataTable.Columns)
    {
        Console.WriteLine("Row#:{0} Column:{1} Type:{2} Value:{3}",
            i + 1,
            col.ColumnName, 
            col.DataType,
            row[col]);
    }
}

Could the above foreach block be used to populate a two dimensional array? I'm using the below to dump all of the data into Excel: object[,] data = new object[dt.rows.count, dt.columns.count];

Yes, that's possible. Modify the loops accordingly:

object[,] data = new object[dataTable.Rows.Count, dataTable.Columns.Count];
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
    for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++)
    {
        data[rowIndex, colIndex] = dataTable.Rows[rowIndex][colIndex];
    }
}

Ahhh, this helps me. Thanks for the information. I'll look at my code again for a different solution and get back to you. — Alex, Sep 14 '16 at 12:16
Could the above foreach block be used to populate a two dimensional array? I'm using this to dump all of the data into Excel at once: object[,] data = new object[dt.rows.count, dt.columns.count]; — Alex, Sep 14 '16 at 12:22
Thank you very much for the information. You helped me track down where the problem was. I'll edit my question to explain... — Alex, Sep 14 '16 at 12:34

SQLDataAdapter not returning last row of data

1 Answers1