Add a column to a table and assign value, dynamically, in MVC3 app?

Hi,

I’m building a MVC3 web application. I have a new request, which will require me to write code in the application, to allow the admin to add a column to a table – not adding a new row; but actually change the definition of a table by adding a new column.

Is this doable? Assuming that for this new column, the value can be null for all existing rows; and the admin can change value for them later via a separate page. This feature is only accessible by the admin, and probably will not be used often at all.

If not, I will need to change the current definition of this table, plus change quite a bit of the existing logic.

Before I go down that route, I want to check to see if it is technically possible to add a column to a table via logic?

Thanks,

Claudia

Hi Claudia,

I would like to know before answering your question.

Are you using entity framework / services for database communication?

Do you have UI for the admin to select the table and enter the column name along with data type and other options?

It is possible to add a new column through the code. You may have to write the SQL Server (I presume, you use SQL) permission logic in your service.

Thanks for the response.

We do use EF to write to database. We do use SQL.

We do not have UI right now to allow admin to add a column to a table.

Can you provide any sample code for "add a new column through the code"? Or point me to a post with sample code to do so.

Thanks,

Claudia

to dynamically add a column you just use transact sql to add the column via your favorite database library. EF will allow you to write sql statements, but plain ado.net will be just as easy. you can get the connect string from EF.

This is the plain ado.net & sql.

http://stackoverflow.com/questions/20719449/c-sharp-alter-table-and-add-a-column-programmatically-asp-net-sql-server

If you want to add through EF, then you have to use the model builder to alter the table.

Thanks mgn_v.

I need to use EF .. do you mind providing some sample code on "use the model builder to alter the table "?

Thanks,

Claudia

you probably can not use the model builder, as it requires a class definition. if you want to use EF its just:

using (var db = new MyDBEntities())
{
   db.MyTable.SqlQuery(string.Format("alter table myTable add column {0} {1}",cName,cType));
}

Thanks bruce.

After the column is added, as you suggested – how do I update the .edmx via code?

What I want to achieve is: right after the column is added via code, I need to assign a value for this column, for each row in this table (there are only 5 rows in this table).

However, in VS, since the .demx is not automatically updated to recognize this NEW column, it shows a red line under this column name in my controller action, like this:

mytable.NewColumn = …

So – is there a way to automatically update .edmx, fater the new column is added?

Thanks,

Claudia

Leave a Reply