Adding columns to a database table programmatically...

[=?Utf-8?B?Y2RiaWdncw==?=]

Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB

[zacks@construction-imaging.com]

Re: Adding columns to a database table programmaticall y...

On Sep 7, 3:22 pm, cdbiggs <cdbi...@discus sions.microsoft .comwrote:

Hi,
>
I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?
>
Any help is deeply appreciated...
>
CDB

Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.

[Philippe]

Re: Adding columns to a database table programmaticall y...

zacks@construct ion-imaging.com wrote:

On Sep 7, 3:22 pm, cdbiggs <cdbi...@discus sions.microsoft .comwrote:

>Hi,
>>
>I've been tasked to write a windows app that allows people to enter
>transactions . For each transaction, there can be an unknown number of items,
>and as a person enters an item, the program asks if there are more items. If
>yes, a new set of input boxes are created dynamically. What I was wondering
>is this - not knowing how many items are going to be entered, is there a way
>that I can change the layout of a database table depending on the number of
>items entered? After the initial creation of the database, with a set number
>of item columns, is there a way for me, (ie. the program), to alter/change
>the database layout?
>>
>Any help is deeply appreciated...
>>
>CDB

>
Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.
>

That *would* be the way to do it, but frankly it would be a disastrous
way to do things from a DBA side of things.. Really..

Basic table with ID column, client/transaction column and item column.
The ID is invisible, and you can get any/all items per transaction nice
and simply.. It also allows for basic querying based on transaction *or*
item, if you're thinking long term.

but yah.. having a table that grows/shrinks columns based on arbitrary
item count is going to make your DBA cry.

P.

[Gregg Walker]

Re: Adding columns to a database table programmaticall y...

CDB,

Here are some other options to consider so (like Nicholas said) you can keep
the DBA happy (maybe :)).

1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.

2. Some databases allow for the creation of multi-valued columns in a table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).

3. Some databases allow for the creation of XML valued columns. Sql Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.

There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).

And of course you can always just alter the table by adding more columns.

HTH
--
Gregg Walker

[=?Utf-8?B?Y2RiaWdncw==?=]

Re: Adding columns to a database table programmaticall y...

For some reason, I didn't get Nicholas' reply, but, what you wrote really
helps. I don't know why I didn't think of setting up another table to accept
each item in its own row. Also, I like the idea of possibly making the items
comma seperated.

Thank you all for the help. If you could post what Nicholas wrote so I
could see that would be most helpful.

Thanks

CDB

"Gregg Walker" wrote:

CDB,
>
Here are some other options to consider so (like Nicholas said) you can keep
the DBA happy (maybe :)).
>
1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.
>
2. Some databases allow for the creation of multi-valued columns in a table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).
>
3. Some databases allow for the creation of XML valued columns. Sql Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.
>
There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).
>
And of course you can always just alter the table by adding more columns.
>
HTH
--
Gregg Walker
>
>
>
>

[Nicholas Paldino [.NET/C# MVP]]

Re: Adding columns to a database table programmaticall y...

CDB,

Here is the link to my original response:

http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/browse_thread/thread/4261317bbe71ccbb/f847dd7c7b28fe39?lnk=st&q=Nicholas+Paldino+CDB&rnum=3#f847dd7c7b28fe39

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"cdbiggs" <cdbiggs@discus sions.microsoft .comwrote in message
news:4BA1A650-C9BD-4D6E-BB71-D52EA6C2226C@mi crosoft.com...

For some reason, I didn't get Nicholas' reply, but, what you wrote really
helps. I don't know why I didn't think of setting up another table to
accept
each item in its own row. Also, I like the idea of possibly making the
items
comma seperated.
>
Thank you all for the help. If you could post what Nicholas wrote so I
could see that would be most helpful.
>
Thanks
>
CDB
>
"Gregg Walker" wrote:
>

>CDB,
>>
>Here are some other options to consider so (like Nicholas said) you can
>keep
>the DBA happy (maybe :)).
>>
>1. You could possibly store all items in a large varchar column as a
>delimitted string (i.e. comma, etc.).
> Of course this assumes that all potentially entered items would fit.
>>
>2. Some databases allow for the creation of multi-valued columns in a
>table.
>I don't believe Sql Server
> supports this yet. Multi-valued columns allow for 0-n items in a
>single
>column where n is unknown at
> design time. These have actually been around for quite a while in
>databases like universe and pick.
> Some relational vendors have recently added support for them to their
>products (i.e. Informix, DB2).
>>
>3. Some databases allow for the creation of XML valued columns. Sql
>Server
>2005 supports this column
> type. If you wanted to go this route you can stuff whatever XML you
>want in the column.
>>
>There are other ways of going about it (like Nicholas fine suggestion of
>using one row per item in a
>separate related table).
>>
>And of course you can always just alter the table by adding more columns.
>>
>HTH
>--
>Gregg Walker
>>
>>
>>
>>