reading from a table that I dont know the table structure

Question

I have a connection to a database view (SQL Server 2008 R2). But I don't know any of the columns. I need to select everything on that view. How can I do this by using SqlDataReader?

SqlConnection con = new SqlConnection(conString);
con.Open();
SqlCommand command = new SqlCommand("select * from vw_Haber_Baslik_Ozet", con);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    //reader.get?? I will write the things I get to a file
}

reader.Close();
con.Close();

Once I learn the structure of this view I can create a proper class to store the values. But how can I learn the structure?

You'd better fill a table with SqlDataAdapter, it's easier to get info from DataTable.Columns. — Serge, Jun 20 '13 at 13:48

score 0 · Answer 1 · answered Jun 20 '13 at 13:51

If just want to be able to get a grasp of what the returned table looks like you may be better off loading the data into a dataset first and setting a breakpoint after filling the dataset so that you can inspect it:

using (var connection = new SqlConnection(conString))
{
     var command = new SqlCommand(..., connection);
     var apapter - new SqlDataAdapter(command);

     var ds = new DataSet();
     connection.Open();

     adapter.Fill(ds);

     if (ds.Tables.Count >= 1)  <<--  Set a breakpoint here and inspect the dataset
     {
        ...
     }
}

score 0 · Answer 2 · answered Jun 20 '13 at 13:51

Theres a property of the SqlDataReader called FieldCount which returms the number of columns in a row. If you iterated over the number of columns executing the GetName() method with the column number being passed as an argument then you might be able to do it like that.

score 0 · Accepted Answer · answered Jun 20 '13 at 13:52

If you read this out into a DataSet, you can use the DataTable to loop over Columns, or Rows, etc.:

        SqlConnection con = new SqlConnection(conString);
        con.Open();
        SqlCommand command = new SqlCommand("select * from vw_Haber_Baslik_Ozet", con);
        SqlDataAdapter adapter = new SqlDataAdapter(command.CommandText, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        foreach (DataColumn dc in ds.Tables[0].Columns)
        {
            String colName = dc.ColumnName;
            String valueOfCol1 = ds.Tables[0].Rows[0][dc.ColumnName].ToString();
        }
        con.Close();

score 0 · Answer 4 · answered Jun 20 '13 at 13:55

Two options available:

Read all in a DataTable

DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);

or use GetSchemaTables

DataTable schema;
SqlDataReader reader = command.ExecuteReader();
schema = reader.GetSchemaTable();

The first method requires the loading of the data while the second return a datatable where each row contains all the available information on the single columns of the table

score 0 · Answer 5 · answered Jun 20 '13 at 14:54

If all you wish to know are columns and types.

Select * from [YourTable Or View] where 1=0;

Use that with a DataAdapter fill into a DataTable.

Then read the table either programatically or with a watch.

If you use the schema methods however (GetSchemaTables), you get a whole lot more (which you may or may not want) - nullability, constraints, identity etc.

reading from a table that I dont know the table structure

5 Answers5