Add new columns to database during runtime

like this:

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

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.

>>ADD COLUMN

COLUMN is not allowed in this syntax.

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

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.

it is a command that you execute against the database engine

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)

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)

logical table = table

generally, it is a bad design to allow users to modify a data structure

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

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????

use SMO  (sql sever management objects)

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 https://www.experts-exchange.com/questions/25893818/Dynamicly-Add-controls-to-web-from-from-sql.html

and will there be the ability to create some sort of ui that implements this??

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??

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
https://www.experts-exchange.com/questions/25893927/DAL-with-entity-framework-and-Enterprise-framework.html

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.

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??

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 ;)

lol

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...

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.

any new suggestions would be greatly appreciated

One more thought is to use the power of XML type in SQL Server.

Check these links for more information.

http://msdn.microsoft.com/en-us/library/ms189887(v=SQL.100).aspx
http://msdn.microsoft.com/en-us/library/ms190798(v=SQL.100).aspx
http://msdn.microsoft.com/en-us/library/ms177454(v=SQL.100).aspx

help please

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?

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....

Check out this sample application:

http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx

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

https://www.experts-exchange.com/questions/25893818/Dynamicly-Add-controls-to-web-from-from-sql.html 

modus_operandi:
I will leave it open for a few more days then i will resubmit it

cheers

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

https://www.experts-exchange.com/questions/25893818/Dynamicly-Add-controls-to-web-from-from-sql.html 

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???

Sure

cheers my email is trent@xdev.com.au and I will award you the points