Add new columns to database during runtime

Hello Experts

I am working on a web application for a client and they have requested the ability to create tables and columns online... I was wondering how do I acheive this???
LVL 7
XGISAsked:
Who is Participating?
 
SriVaddadiCommented:
when you say EF4 I guess you mean ADO.Net Entity Framework.  You cannot take advantage of automatic entity generation with dynamically changing schema.

Automatic entity generation approach is to start with model(Db) and modelling the entities around it. This is not a very good option when your schema changes regularly.

Another approach is to design your entities, model(DB) separately and create mappings using the entity framework  xml schema.

I am not sure if you have started this project already or not. But unless you do some prototyping you will not be able to really judge upfront the issues involved in different approaches.

I did not really get some time to develop a prototype for you on what I was talking about but I surely would do some prototyping for myself when I get time.
0
 
chapmandewCommented:
like this:

if columnproperty(object_id('tablename'), 'newcolumnname','columnid') is null
alter table tablename
add columnname int

0
 
gplanaCommented:

You should execute this SQL command:

ALTER TABLE yourTable ADD COLUMN yourcolumn text;

where yourTable is the name of the table where you wnat to add a column, yourcolumn is the name of the column you want to add, and text is the datatype of the column (you can change text by varchar(50), decimal(7,2), etc...

For a complete syntas on ALTER TABLE please see this link: http://msdn.microsoft.com/es-es/library/ms173393%28SQL.90%29.aspx

Hope this helps. Regards.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chapmandewCommented:
>>ADD COLUMN

COLUMN is not allowed in this syntax.
0
 
XGISAuthor Commented:
Chapmandew:

thank you for your quick response.

how and where would I implement that, would it go into a public void? is 'columnproperty' a variable if so where is it declared is 'alter table tablename' and 'add columnname int' wrapped in a set of fish braces? I need a bit of a description to understand.... as i like to learn from questions so I don't need to ask them again
0
 
SriVaddadiCommented:
XGIS,

I would say adding columns at runtime i.e dynamically changing columns is not a good idea. There are lot of implications in this design.

I suggest to take a step back and review the design. It might be good idea to provide creating logical tables and modifying logical tables.

Altering table schema dynamically is not a good practice.
0
 
chapmandewCommented:
it is a command that you execute against the database engine
0
 
XGISAuthor Commented:
crikey fast responses lol I cant type fast enough...... gplana: thanks so your saying to add sql script in click event??? the user was hoping for something graphical (maybe not possible)
0
 
XGISAuthor Commented:
SriVaddadi: the users all have individual schemas, so when they add a table or add a column they are adding directly to there own schema.. why is it not a good practice and what are logical tables(maybe a silly question)
0
 
chapmandewCommented:
logical table = table

generally, it is a bad design to allow users to modify a data structure
0
 
SriVaddadiCommented:
Well, I understand your requirements. Usually dynamically adding columns comes into picture in this kind of scenarios like yours and we all know customer is the boss :) and cannot put down their requests.

Disadvantages of dynamic schema changes

We will be locking the db objects for a bit longer time creating probability for deadlocks
Triggers, Views etc if any have to be re-built
Sps have to use the dynamic SQL to cope up with the changes to the table schema

When i say logical tables, I meant row based approach. It is a pattern for this kind of problems.

User will have an impression that they are creating physical tables but we maintain the schema of those tables and do not physicall create tables.

I mean lets say user is creating TableA with Col A ,Col B, Col C

We store the TableA information  in a physical table
We store the Columns information in a phyical table and associate them with the table
We store the values in separate tables.

So the physical tables grow vertically and never grow horizontally.  

Hope this makes sense


0
 
XGISAuthor Commented:
I understand, the problem is, that the user has requested this feature and is a competent operator... this will not be avaliable to all users unless requested by them. so I will need a way to graphicly create and alter tables and I need to take into concideration PK and FK so is it possible to build something like ssmse online????
0
 
chapmandewCommented:
use SMO  (sql sever management objects)
0
 
XGISAuthor Commented:
SriVaddadi: Yes that does make sence how will that handle the relationships that the customer may intend to create?? and how can the customer bind controls?? as mentioned in Question http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_25893818.html

and will there be the ability to create some sort of ui that implements this??
0
 
XGISAuthor Commented:
chapmandew: I have attempted once before to utilize SMO in web forms and failed miserably but I suppose I could attempt to use in a class library and build a web.ui library for it (learning curve) would you have any samples utilizing SMO??
0
 
XGISAuthor Commented:
I should mention that I intend to use entity framework as well.... as I heard that it can handle this type of thing.... uncertain as I have never used ORM or n-teir architecture lol that swings into this question
http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/Q_25893927.html
0
 
BodestoneCommented:
I am going to wholeheartedly agree that allowing the client to create and alter the schema online is a really bad idea in most instances. before continuing you need to assess the actual business need rather than what they think they want.

Ask them why they think they need to do this, ask for a model and examples of the situations leading to this need. Chances are you will be able to develop a static schema and front end that is capable of meeting their needs.

As an example I was asked somethign similar a while back. They wanted a new table to hold data for assessments of each of their sub-businesses since each assessment would have different questions and answer choices which could change from year to year. This was perfectly achievable with a set of very minimal tables.

AssessmentTemplates
  TemplateID
  SubBusinessID
  TemplateName
  TemplateStarts
  TemplateEnds

TemplateSections
  TemplateID
  SectionID
  SectionName

TemplateQuestions
  SectionID
  QuestionID
  QuestionName

QuestionResponses
  QuestionID
  QuestionResponseID
  QuestionResponseText

Assessments
  AssessmentID
  Sub-BusinessID
  AssessmentTemplateID
  AssessmentDate
  The Rest

AssessmentResponses
  AssessmentID
  TemplateQuestionID
  QuestionResponseID


So, as you see, a client then can build up a template of questions year to year, add and remove sections and questions, apply them to various sub businesses.
At the front end for each assessment only the relevant options are displayed for the template that assessment is for.

OK, so this is specific to somethign I have done but I thought a real life example of where the client wanted a new rable for each sub-business they assessed and to add columns every time the assessment criteria changed might throw light on things.

The main point though is to not go with what the client first says they want. Ask them why they want it and what they are hoping to achieve and thern think of ways in which you may do this better.
0
 
XGISAuthor Commented:
Bodestone: thank you for your response,  But the customer is alway correct i suppose I advised against this today after what you all have said and they still want to go ahead with it..
SriVaddadi: can you please explain your method in more detail with some sample code??
0
 
BodestoneCommented:
OK, fair enough. It's why we added consultancy into the description of our services so they were right in what they wanted but we could consult with them on the best methods of acheiving it ;)
0
 
XGISAuthor Commented:
lol
0
 
XGISAuthor Commented:
well at the moment I have found two graphical sql managers online... I have downloaded source for them.... but now i will need to limit access to the users schema only..... shouldn't be to difficult but the suggestion by SriVaddadi sounds intriguing but unsure as to whether or not I can provide ui for it...
0
 
SriVaddadiCommented:
XGIS, Sorry I could not find time to get back. You could always provide UI and even maintain relations and do all the stuff.

I don't have sample code ready to share with you. In the UI you will provide all the options as required to create a table like Table Name, Column Name, Type, Length as n where applicable. (Type information would be required to handle DateTime columns) .

For managing relations, once the tables are created you could list the available tables, available cols on the selected tables and allow user to specify the relation.

Coming to databinding, they will follow the same approach as they would when there is a physical table except that their query would join tables and retrieve data instead of straight fetch from a single table.


Tomorrow if someother client wants the same kind of extensibility you dont need to do anything you would have the same tables and different clients can have different tables.
0
 
XGISAuthor Commented:
any new suggestions would be greatly appreciated
0
 
SriVaddadiCommented:
0
 
XGISAuthor Commented:
help please
0
 
SriVaddadiCommented:
Are you looking for a different solution? or more help on my approach?

Please provide more information like which approach you decided to take and what specific issues you are facing?

0
 
XGISAuthor Commented:
SriVaddadi,

I would like some more help on your approach

"Well, I understand your requirements. Usually dynamically adding columns comes into picture in this kind of scenarios like yours and we all know customer is the boss :) and cannot put down their requests.

Disadvantages of dynamic schema changes

We will be locking the db objects for a bit longer time creating probability for deadlocks
Triggers, Views etc if any have to be re-built
Sps have to use the dynamic SQL to cope up with the changes to the table schema

When i say logical tables, I meant row based approach. It is a pattern for this kind of problems.

User will have an impression that they are creating physical tables but we maintain the schema of those tables and do not physicall create tables.

I mean lets say user is creating TableA with Col A ,Col B, Col C

We store the TableA information  in a physical table
We store the Columns information in a phyical table and associate them with the table
We store the values in separate tables.

So the physical tables grow vertically and never grow horizontally.  

Hope this makes sense"

some samples would be most appreciated....
0
 
XGISAuthor Commented:
deepaknet: Thanks for your input but
A: I use C#
and
B: the EF4 model needs to be updated dynamicly and t4 classes generated.. I was thinking of some how on the button click event run a sql script, then check for schema changes in db vs model schema if schema changed = true then sync ef4 model and generate classes(but i don't know how to do this).  then popup window with are you finished editing you database if yes then server.Transfer to the designer page where they will make there page to bind to the newly created table or modify existing page if there was only table mod. and this relates to

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_25893818.html 
0
 
XGISAuthor Commented:
modus_operandi:
I will leave it open for a few more days then i will resubmit it

cheers
0
 
XGISAuthor Commented:
the EF4 model needs to be updated dynamicly and t4 classes generated.. I was thinking of some how on the button click event run a sql script, then check for schema changes in db vs model schema if schema changed = true then sync ef4 model and generate classes(but i don't know how to do this).  then popup window with are you finished editing you database if yes then server.Transfer to the designer page where they will make there page to bind to the newly created table or modify existing page if there was only table mod. and this relates to

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_25893818.html 
0
 
XGISAuthor Commented:
SriVaddadi: Thank you for the advice I will research further.. and if you do get a prototype built would would mind emailing a copy to me???

0
 
SriVaddadiCommented:
Sure
0
 
XGISAuthor Commented:
cheers my email is trent@xdev.com.au and I will award you the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.