C# -> Retrieving dataset from SQL Server 2008

Question

I have a table called NAMES in my SQL Server database. I am trying to retrieve the entire table and put it into a dataset:

//get the connection string from web.config
string connString = ConfigurationManager.ConnectionStrings["Platform"].ConnectionString;
DataSet dataset = new DataSet();

using (SqlConnection conn = new SqlConnection(connString))
{
    SqlDataAdapter adapter = new SqlDataAdapter();                
    adapter.SelectCommand = new SqlCommand("NAMES", conn);
    adapter.Fill(dataset);
}  

This throws a sql exception though,

"Invalid Object Name NAMES"...

What am I doing wrong?

Solution 1

Open the connection !!!!!!

 //get the connection string from web.config
 string connString = ConfigurationManager .ConnectionStrings["Platform"].ConnectionString;
 DataSet dataset = new DataSet();

 using (SqlConnection conn = new SqlConnection(connString))
 {
     SqlDataAdapter adapter = new SqlDataAdapter();                
     adapter.SelectCommand = new SqlCommand("select * from [NAMES]", conn);
     conn.Open(); 
     adapter.Fill(dataset);
 }  

Solution 2

You're not passing an actual SQL select command to the SqlCommand constructor.

Comments

[Amen Ayach]

adapter.SelectCommand = new SqlCommand("select * from [NAMES]", conn);

[user559142]

if I change "NAMES" to "SELECT * FROM NAMES" I still get the same error

[Chris]

opening the connection would certainly help but I don't think that is the exception you'd see. Invalid object name certainly suggests that it had reached the database. I assume the code above is a cut down version that just accidentally omitted the DB opening.

[Chris]

Have you tried [NAMES] instead (as per @AmenAyach's suggestion)? Names is an ODBC reserved keyword and is thus goign to cause potential confusion in parsing SQL unless you escape it properly.

[Amen Ayach]

Can you access the database through SQL management studio to check if this table really exists

[marc_s]

Actually, using the SqlDataAdapter you don't have to open the connection yourself - the adapter will do this for you!

[Alex]

when using sqldataadapter you don't open the connection, the adapter opens and close the connection by itself