开发者

C# SQL: What is the best way to implement a dynamic table?

开发者 https://www.devze.com 2022-12-26 06:55 出处:网络
I want to allow the user to add columns to a table in the UI. The UI: Columns Name:______Columns Type: Number/String/Date

I want to allow the user to add columns to a table in the UI.

The UI: Columns Name:______ Columns Type: Number/String/Date

My Question is how to build the SQL tables and C# objects so the implementation will be efficient and scalable.

My thought is to build two SQL tables:

TBL 1 - ColumnsDefinition:

ColId开发者_如何学Go, ColName, ColType[Text]

TBL 2 - ColumnsValues:

RowId, ColId, Value [Text]

I want the solution to be efficient in DB space,

and I want to allow the user to sort the dynamic columns.

I work on .NET 3.5 / SQL Server 2008.

Thanks.


I believe that is essentially how the WebParts.SqlPersonalizationProvider works, which doesn't necessarily mean it's the best, but does mean that after some smart people thought about it for a while, that's what they came up with.

Sorting on a given field will be a bit tricky, particularly if the field text need a non-text sorting (i.e., if you want "2" to come before "10").

I'd suggest that from C#, you do one query on ColumnsDefinition, and based on that, choose one of several different queries for selecting/sort the data.


Add a DefaultValue to your ColumnDefinition. Only add a value in ColumnsValues if the value is not the default value. This will speed up things a lot.

The thing I hate about these kind of systems is that it is very difficult to transfer changes betwween dev/stage/production because you will have to keep structure and content of tables in sync.

0

精彩评论

暂无评论...
验证码 换一张
取 消