3

In my project I am dynamically creating table by giving table name (ex. student) and adding fields to that table and then save table. Now, my table is created in SQL Server database.

Assume table has data and I want to show the data of table (ex student) on WebGrid. But, the problem is I have no model for that table (ex. student). So, how I can create model for newly created table or how I can show the the data on WebGrid?

  • Each field of the table has a type. To represent a tuple of a table have a list of key-value pairs, where the key is the name of a column, and the value contains the type as a string and the value of the column as a string. – superM May 9 '14 at 13:35
  • any demo code. i am not getting what you trying to say. – Vicky May 9 '14 at 13:41
  • model = Map <column_name, <column_type, column_value>>. For example, an item <"age", <"int", "15">> would be an entry for that map. – superM May 9 '14 at 13:44
  • @superM: Yeah, I don't get what you're saying either. – Robert Harvey May 9 '14 at 14:04
  • @superM: i have to use basic sql select query with ado .net & load into Data Table but after that what i can do using above code – Vicky May 9 '14 at 14:21
7

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
1

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.

0

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

  • thanks for reply. I also need column name to display on table header – Vicky May 9 '14 at 15:14
  • techno-soft.com/index.php?/… – IsmailS May 9 '14 at 17:37
  • 2
    I see so much of this type of code in the application I inherited, it saddens me. tableName = "Users; truncate table Users;"; // at least you'd get to keep your table's structure, right? I'm not saying that you have to move away from SQL in your code and embrace EF/LINQ, but do parameterize it. – Robert May 12 '14 at 17:56
  • Yeah! Agree! The other two answers by @MichaelT and Phill are much better than mine. – IsmailS May 12 '14 at 18:29
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.