adding a column to a table

[Guest]

Hi;

Is there a way to add a column to a table, preferably not at the end, via
ADO.NET? And hopefully in a vendor independent way as I have to do this for
Sql Server, Oracle, DB2, and MySql.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Rad [Visual C# MVP]]

Hi;

Is there a way to add a column to a table, preferably not at the end, via
ADO.NET? And hopefully in a vendor independent way as I have to do this for
Sql Server, Oracle, DB2, and MySql.

Are you talking about a dataset or a physical table?

If it is a physical table you can use SQL, the alter table statement
provided you're not putting anything unusually fancy like constraints or
defaults that are database specific

 

[Guest]

A physical table and the default would be either null or a 0 (2 alterations).
But I need the columns to not be added at the end of the table. Is there a
way to do this?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Guest]

David,

I don't understand your need to place the column in a particular position:

You can retrieve columns in any order via a select statement.

One of the fundamental requirements of a table in a relational database is
that the order of the columns cannot matter.

Kerry Moorman

 

[Guest]

The DBA wants it there. I'll push back.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Rad [Visual C# MVP]]

The DBA wants it there. I'll push back.

If you want to add it in a particular position in a database agnostic
manner then you must do a bit of work

1) Select the data from the table into a new table
2) Drop the table
3) Execute a create table statement with the columns in the order you'd
like
4) Select the data back into the table

Though I'm very skeptical about your DBA's insistence of columns in a
particular order. Can you get him to give you a reason?

 

[Cor Ligthert [MVP]]

David

Adding a column to the end of a table is a standard method.
Table.Columns.Add("MyColumn"). It is overloaded so you can even add your
expression, type and length to it.

Moving the columns is a different task. Although that starting with the
version 2005, the dataview has a special overlaoded ToTable method to it.

http://msdn2.microsoft.com/en-us/library/h2b6ehaa.aspx

Cor

 

[Paul Clement]

¤ Hi;
¤
¤ Is there a way to add a column to a table, preferably not at the end, via
¤ ADO.NET? And hopefully in a vendor independent way as I have to do this for
¤ Sql Server, Oracle, DB2, and MySql.

Well all of the above database systems should support some form of SQL DDL, however it may not be
identical and it isn't likely that you will be able to specify the ordinal position of the column in
the table.


Paul
~~~~
Microsoft MVP (Visual Basic)