Adding a column programmatically to a SQL Server database

Question

I accepted an ASP.NET application that needs to be rewritten. The main functionality of this application that I need for replication changes the SQL Server database, which is accessed through ODBC from third-party software.

A third-party application creates files that represent user-created printer tags. These label files directly reference ODBC source fields. Each row of the table represents a product that fills the label fields. (Thus, inside these files there are direct links to table column names.)

The ASP.NET application allows the user to create / update data for these tag-referenced fields by adding or editing a specific line representing the product.

It also allows you to randomly add new fields ... where it actually creates a new column in the main table referenced by the labels.

My concern: I have never programmed previously existing table columns. The existing application seems to do a great job with this functionality, but before I blindly do the same in my new application, I would like to know what errors exist if they exist ... and if there are any obvious alternatives .

Answer 1

, . KVP , .

"" .

, , XML (MS SQL XML) , , KVP. KVP , .

. "custom attribute", Attribute1, Attribute2, Attribute3, Attribute4 ( ..). , , AttrX .

, , .

Answer 2

, , , ( , ).

"-": " " , , , . (KVP )

Answer 3

, , KVP "" ( ), KVP , , .

---

EDIT: - :

:

create table Product(ProductID nvarchar(50))

insert Product values('Product1')
insert Product values('Product2')
insert Product values('Product3')

create table ProductKVP(ProductID nvarchar(50), [Key] nvarchar(50), [Value] nvarchar(255))

insert ProductKVP values('Product1', 'Key2', 'Value12')
insert ProductKVP values('Product2', 'Key1', 'Value21')
insert ProductKVP values('Product2', 'Key2', 'Value22')
insert ProductKVP values('Product2', 'Key3', 'Value23')
insert ProductKVP values('Product3', 'Key4', 'Value34')

:

declare @forClause nvarchar(max),
        @sql nvarchar(max)

select @forClause = isnull(@forClause + ',', '') + '[' + [Key] + ']' from (
    select distinct [Key] from ProductKVP /* WHERE CLAUSE */
) t

set @forClause = 'for [Key] in (' + @forClause + ')'

set @sql = '
select * from (
select 
    ProductID, [Key], [Value]
from (
        select k.* from 
        Product p
        inner join ProductKVP k on (p.ProductID = k.ProductID)
        /* WHERE CLAUSE */
    ) sq
) t pivot (
    max([Value])' +
    @forClause + '
) pvt'

exec(@sql)

:

ProductID   Key1      Key2      Key3      Key4
----------- --------- --------- --------- -------
Product1    NULL      Value12   NULL      NULL
Product2    Value21   Value22   Value23   NULL
Product3    NULL      NULL      NULL      Value34

Answer 4

One risk is a table too high. I used to maintain a terrible application that added 3 columns “automatically” when new values ​​were added to some XML (for some reason, he thought that everything would be a date or number string, hence creating 3 columns).

There are other methods, such as serializing BLOBs or designing tables in different ways, which can help.