Fill DataTable from SQL Server database

Question

This one is a mystery for me, I know the code I took it from others, in my case the datatable it returns is empty

conSTR is the connection string, set as a global string

public DataTable fillDataTable(string table)
    {
        string query = "SELECT * FROM dstut.dbo." +table;

        SqlConnection sqlConn = new SqlConnection(conSTR);
        sqlConn.Open();
        SqlCommand cmd = new SqlCommand(query, sqlConn);

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        sqlConn.Close();
        return dt;
    }

EDIT 1
The whole point is to later show this table in a datagrid view on a tabcontrol, here is the question on that displaying multiple datatable in tabcontrol C#

Here it just show's me a blank datagridview

EDIT 2
Tried them all, when I try to display the table, the datagridview is empty, have the right amount of rows but now value

@Steve table = table name from the database (there are several) — iakovl2, Jun 06 '13 at 09:19
@Freelancer the table in the database isn't empty, the funny part is that i can get the row count of the table (tested it) but not it's values — iakovl2, Jun 06 '13 at 09:19
also, on a "PSA" note: `DataTable` is not necessarily the best way to handle your data requirements — Marc Gravell, Jun 06 '13 at 09:21
@iakovl2 you say about getting the row count; to play devil's advocate, then - if the query was `"SELECT COUNT(1) FROM dstut.dbo." +table;`, and you use `var count = cmd.ExecuteScalar();` - what is `count`? Sorry if this sounds pedantic, but it is worth investigating the "obvious" first... — Marc Gravell, Jun 06 '13 at 09:24
@MarcGravell when i get the table i return it to another function that needs to show it in a tabcontrol datagridview, i get the values of a column, the right amount of rows per table, but the table display i "bad". so i went to the source, "filldatatable" — iakovl2, Jun 06 '13 at 09:41
@iakovl2 the right number of rows is interesting; I will add an answer on your other post that might be relevent — Marc Gravell, Jun 06 '13 at 09:44
@iakovl2 I think this code here is fine; the problem looks to be in the UI code consuming this table — Marc Gravell, Jun 06 '13 at 10:13
@iakovl2 meaning the splitTable method; can you say: what is `dt.Columns.Count` and `dt.Rows.Count` before you `return dt;` ? if those are both non-zero, then this code ***is fine*** - and the problem is not in the code shown in this question — Marc Gravell, Jun 06 '13 at 10:15
@MarcGravell ok, we have progress... the ROW count is good, the COLUMN count is zerp.... now how to fix it? — iakovl2, Jun 06 '13 at 10:18

score 32 · Answer 1 · edited Oct 22 '15 at 10:27

32

If the variable table contains invalid characters (like a space) you should add square brackets around the variable.

public DataTable fillDataTable(string table)
{
    string query = "SELECT * FROM dstut.dbo.[" + table + "]";

    using(SqlConnection sqlConn = new SqlConnection(conSTR))
    using(SqlCommand cmd = new SqlCommand(query, sqlConn))
    {
        sqlConn.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }
}

By the way, be very careful with this kind of code because is open to Sql Injection. I hope for you that the table name doesn't come from user input

edited Oct 22 '15 at 10:27

Luke Girvin

13,221
9
64
84

answered Jun 06 '13 at 09:20

Steve

213,761
22
232
286

it comes from a list the i create, no invalid option there. still have the same problem – iakovl2 Jun 06 '13 at 09:22
@iakovl2 it is a shame that didn't help - but please do learn from Steve's use of `using` here; correct use of `using` is actually ***really*** important. If I was picky I'd mention that Steve missed one (around the reader)... but obviously I'm not gauche enough to even mention that in passing... ;p – Marc Gravell Jun 06 '13 at 09:27
@MarcGravell but here the reader is passed directly to the Load. Do you mean that it is better to split the line in two to apply the using to a SqlDataReader object? – Steve Jun 06 '13 at 09:30
2

@Steve personally, yes I would prefer that - so that it is explicitly disposed via the `using`. `DataTable` makes no guarantee to dispose it (although it does have a `Close()` - on the success scenario only). In truth, though, it probably doesn't really matter much unless you are relying on `CommandBehavior.CloseConnection` to close the connection (which you aren't here). I just have a really really simple but effective approach to `IDisposable`: if my code is *responsible* for an `IDisposable` object, then my code is responsible for making sure it gets disposed. – Marc Gravell Jun 06 '13 at 09:35

score 2 · Answer 2 · edited Dec 05 '19 at 08:07

2

Try with following:

public DataTable fillDataTable(string table)
    {
        string query = "SELECT * FROM dstut.dbo." +table;

        SqlConnection sqlConn = new SqlConnection(conSTR);
        sqlConn.Open();
        SqlCommand cmd = new SqlCommand(query, sqlConn);
        SqlDataAdapter da=new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        sqlConn.Close();
        return dt;
    }

Hope it is helpful.

edited Dec 05 '19 at 08:07

Arialdo Martini

4,427
3
31
42

answered Jun 06 '13 at 09:19

Freelancer

9,008
7
42
81

2

I would be somewhat surprised if this changes anything; worth a try, of course, but the `DataTable.Load` method works fine too. Also: why did you `ExecuteNonQuery` that? you shouldn't be executing the `cmd` *at all* - the adapter will do that. – Marc Gravell Jun 06 '13 at 09:19
@MarcGravell may be. I was not knowing above coding style. I always code in this style. So i suggested this one. – Freelancer Jun 06 '13 at 09:21

Fill DataTable from SQL Server database

2 Answers2

Linked