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

Solution 1

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

Solution 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.

Comments

[Marc Gravell]

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.

[Freelancer]

@MarcGravell may be. I was not knowing above coding style. I always code in this style. So i suggested this one.

[iakovl2]

it comes from a list the i create, no invalid option there. still have the same problem

[Marc Gravell]

@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

[Steve]

@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?

[Marc Gravell]

@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.