Don’t do that

You really shouldn’t be modifying table schema at runtime. All sorts of things can go wrong. You could have someone try to create a column that turns out to be a reserved word. The access you are giving the user to be able to do these changes allows for things like dropping tables too.

You really shouldn’t be modifying table schema at runtime. It means you are not using much of what a database is good at.

Try an EAV table structure instead

If you are going to be writing dynamic data where columns change and are arbitrarily created, consider using an EAV table structure instead. It is well known and understood how it works.

Yes, various DBAs and purists will fuss at at it, but will you suggest your proposed solution of adding columns at runtime, they will probably give in. You can even find libraries that will let you write a model for it (such as EAV @ CodePlex).

Maybe try something out of NoSQL

Maybe you don’t want a relational database at all. Maybe you want something that has a document model and you’re just sticking json in there. Then mongo or couch would work acceptably. Or maybe you want a column oriented database like Cassandra. I’m not sure, but its something to explore if you really are after that ‘creating columns at runtime’ type thought.

Ok, you’re really doing that

You can get the metadata from a table back. In the Java world, this would be something like ResultSetMetaData which lets you find out the specifics of all of the columns, the types of data they store and other bits about it.

In C#, apparently this is stored in the DataColumn class. From Retrieving metadata (table name) from a SQL statement on Stack Overflow:

SqlConnection con = new SqlConnection(connString);
String queryString = "Select CUSTOMER_NAME from CUSTOMER_DETAIL";
SqlCommand cmd = new SqlCommand(queryString, con);
DataTable myTable = new DataTable();
myTable.Load(cmd.ExecuteReader());
DataColumn column = myTable.Columns[0];
// zero based index of column, alternatively use column name
string typeOfColumn = column.DataType.Name;
// or column.DataType.FullName to get the fully qualified name of the System.Type

If you don’t know what’s in a table, you shouldn’t be trying to display it anywhere! What if it’s full of credit card details (Yowch!) or passwords in “anonymously-named” fields (Eek!)?

And you just plump them all up on the screen for all to see?
To me, that’s bordering on irresponsible at the very least.

You need to know what’s in there before you try doing anything with it.

You say you’re creating field dynamically in your database. OK; it’s not a Good way to design your database in my book, but it’s A way. I’d suggest you create metadata tables – just more tables in your database – that describe the real tables that your application currently uses. You’ll need (at least)

• one metadata table that describes each real table and
• another metadata table that describes each column within each real table.

This metadata can be queried at run time and [accurately] describes each table and field in the database, what it contains and whether you’re allowed to display it, amongst other things. It also documents your database “design”, even though it’s created “in-flight”.

The important thing is that this is all built in to whatever U.I. you use to build your real tables – you need to be filling this stuff in (“writing it down”) before you add the field into the database.

Not the best solution, but should work.

To display table data, only input you have is name of the table. So you will do

string sql = "SELECT * from " + tableName;

And then fill a DataSet and then convert DataSet to List<List<string>> object like in this answer. Pass that on to your view. Loop through it in the view to display tabular data. The caveat here is that you will handle all datatypes as string.

I don’t think you will be able to use WebGrid

Alternative approach using PostgreSQL in bio-medical data: decomposed storage.

Idea revolves around creating a separate (object, field) table for each field and carefully constructing queries to optimize the data retrieval. So if you have object:

Student {id, name, dob, country}

You would create 4 tables:

• student (id)
• student_name (id, name)
• student_dob (id, dob)
• student_country (id, country)

The advantage compared to EAV design is that data retrieval and storage logic is a bit simpler, while maintaining small data footprint. Disadvantage is the potential cost of maintaining multiple small tables and increased number of joins.

More details here

You can query that dynamic table and fetch those results into a DataTable the traditional way.

After this you can seralize this into a json string. https://stackoverflow.com/a/12063095/145682 … shows you how.

These can then be easily consumed by js libraries like knockout.js, or, jquery, etc and rendered on the Web page.