How to get a DataTable object from a database using its tablename in C#?

Question

I'm trying to get a DataTable object out of a database using a table name that I have already. (I mean I want to choose between multiple tables. For this I only have a string which represents a table name. So I'm looking for a way to get my hands on the actual object.)

How can I do that?

I have already tried:

DataTable table = new DataTable(TableName);

But I believe this is wrong. (How is the application supposed to know where that table name comes from or where to search for it?)

I tried using con.GetSchema("Tables"), but that gives out only table names which are strings, and not DataTable objects. I also tried this but it seems DataTables are not enumerable:

public static DataTable GetTable(string TableName, string conncetionstring)
{
    SqlConnection con = new SqlConnection(conncetionstring);
    foreach (DataTable table in con.GetSchema("Tables"))
    {
        if (table.TableName == TableName)
        {
            return table;
        }
    }
    return null;
}

why exactly DataTable? why not use EF, SubSonic, NHibernate, ...?! — user57508
– user57508, Commented Jul 31, 2012 at 11:39
Hossein, you know there's no excuse to not uppercase sentences, nor for the odd spacing before punctuation. Please fix that? — Arjan
– Arjan, Commented Aug 2, 2012 at 5:37
Nice. I fixed a bit more, but your effort was good enough for an upvote ;-) — Arjan
– Arjan, Commented Aug 3, 2012 at 7:54

Gary Kindel · Accepted Answer · 2012-07-31 17:44:05Z

4

You are correct. the schema needs to come somewhere Here is typical code I use to connect and get a table.

string Sql="SELECT * FROM MYTABLE WHERE 1=0";
string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MYDATA.MDB;"

OleDbConnection new OleDbConnection(connectionstring);
conn.Open();
OleDbCommand cmd = new OleDbCommand(Sql, conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();

edited Jul 31, 2012 at 17:44

answered Jul 31, 2012 at 11:32

Gary Kindel

17.9k7 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gary Kindel Over a year ago

Here is a article from c-sharpcorner.com/uploadfile/suprotim/… dealing with retrieving schema for tables.

Hossein Over a year ago

by the way what does that "WHERE 1=0" mean ? would it not give an empty datatable ? with no rows ? cause 1 never equals to 0 and thus no row will be selected !! cant we just say "SELECT * FROM MYTABLE" ?

Gary Kindel Over a year ago

WHERE 1=0 is a way of getting an empty structure. It works from MS Access and SQL server. Not sure about MySQL.

Hossein Over a year ago

so i was right then :) by the way shouldnt we open the connection prior to using adapter? or close it afterwards?

Collectives™ on Stack Overflow

How to get a DataTable object from a database using its tablename in C#?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related