none
Automatically Create DataColumns within DataTable RRS feed

  • Question

  • Is there an easy way to create a DataTable at runtime and have it automatically create the columns by reading the schema from the database?

    If there is no built-in method for doing this, has anyone come across some code that does it?

    Thanks,
    Chris
    Friday, January 11, 2008 1:25 AM

All replies

  • You could create empty DataTable based of simple SQL statement that does not select anything, like

     

    SELECT Column1, Column2,... FROM SomeTable WHERE 1=0

     

    This kind of statement returns no rows and if you use DataAdapter to execute it, then you will get DataTable with all the column definitions.

     

    Friday, January 11, 2008 2:50 AM
    Moderator
  • The code below assumes that you are using Microsoft Application Data Blocks. If not, create your own command object and read the results of the query into the dataset or datatable.

    Code Block

     

    public DataTable BuildTableFromSchema(string targetTableName)

    {

    string commandText = "SELECT TOP 1 * FROM " + targetTableName;

    DataTable dataTable = null;

    DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, commandText);

    if (ds.Tables.Count > 0)

    {

    dataTable = ds.Tables[0];

    dataTable.Rows.Clear();

    }

    return dataTable;

    }

     

     

    or as mentioned before, drop the clear statement and add "WHERE 1 = 0" to the query
    Saturday, January 12, 2008 12:57 AM