Alex Alex - 1 year ago 116
C# Question

SQLDataAdapter not returning last row of data

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);
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


return dataTable;

Answer Source

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,

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];
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download