Dynamically rebuilding Datatable?
- Thread starter DerekC
- Start date
DataTable's don't have SQL to change. DataTables are built either by your own code or implicitly when you call Fill on a DataAdapter. If you want to create a DataTable automatically then just call Fill on a DataAdapter and it will create one. It's up to you what SQL code you execute.
Alternatively you can add to the Columns collection of an existing DataTable. If you're going to clear it and then Fill it again though, you might as well just let a new one be created automatically. If you want a grid to reflect those changes then just assign the new DataTable to the DataSource and it should create the new columns automatically. If it doesn't, you might try Clearing its Columns first.
Having said all that, an application that adds columns to a database table is a very, very rare thing. When people say that's what they're doing it usually means their design is faulty. Would you mind explaining exactly what your app does and why it adds new columns to a table?
Alternatively you can add to the Columns collection of an existing DataTable. If you're going to clear it and then Fill it again though, you might as well just let a new one be created automatically. If you want a grid to reflect those changes then just assign the new DataTable to the DataSource and it should create the new columns automatically. If it doesn't, you might try Clearing its Columns first.
Having said all that, an application that adds columns to a database table is a very, very rare thing. When people say that's what they're doing it usually means their design is faulty. Would you mind explaining exactly what your app does and why it adds new columns to a table?
HI, Thanks for the reply.
Sorry, I actually mean the TableAdapter. I have a table called TBL_CUSTOMERS and my fill select is simply SELECT CUSTOMER_NO, CUSTOMER_NAME ETC. I have added a new field to TBL_CUSTOMERS called CUSTOM_FIELD_1. Now in order for my tableadpater to pick this up, I add it to the select at design time, and this is reflected in the update and insert statements. I would like to do this at run-time seen as I will be dynamically adding columns to the underlying SQL Server Table. Thanks for your help.
Sorry, I actually mean the TableAdapter. I have a table called TBL_CUSTOMERS and my fill select is simply SELECT CUSTOMER_NO, CUSTOMER_NAME ETC. I have added a new field to TBL_CUSTOMERS called CUSTOM_FIELD_1. Now in order for my tableadpater to pick this up, I add it to the select at design time, and this is reflected in the update and insert statements. I would like to do this at run-time seen as I will be dynamically adding columns to the underlying SQL Server Table. Thanks for your help.
Again, why would you be adding columns at run time?
I want to give users the ability to create their own fields. For example, I have a form that allows them to add an integer field, or a string field or datetime field. So for example they add 'Age' and select integer as the type. On my Customers screen, in my 'Custom Fields' tab, they see their newly created field called 'Age'. This is bound to a field called 'CUSTOM_FIELD_1' on the TBL_CUSTOMERS table. This field was added to the TBL_CUSTOMERS table at run-time when the user created their 'Age' field.
Hope this makes sense.
That is very much the wrong way to go about it. You should have a table that describes custom fields and then the act of adding a custom field to a table consists of adding a record to that table. You'd then have another table that contains the values for those custom fields. It would have a column to contain the ID of the custom field, a column to contain the ID of the record it belongs to and a column for the value. An application should not be changing the schema of its database. Your app should only add rows, not columns. The schema should only change when you deploy a new version of the app that requires it for new functionality.
Why not change the schema at run-time? It's easier than creating a new table just for custom fields. I solved the problem anyway by dynamically creating a SqlDataAdapter, dataset and binding source. It works great. Thanks for your input.
Similar threads
