Geek asks and answers

Check table type correctly with C #

In my database, I created several types of tables that will be used as parameters of the stored procedure. They correspond to real database tables, so if they are not synchronized, a problem will arise. I would like to add a unit test that looks at two and fails if they are different, but I'm not sure where to start.

I don’t know if there is a recommended way to do this - I was going to try to somehow pull out the column information, skip it and skip the test if they are different, but this is a little strange.

Is there a better way?

+3
source share
2 answers

SQL Server 2008 sys.tables, sys.table_types sys.columns.

, candidateRoutes, () , RouteArea

:

select sys.columns.* from sys.table_types join sys.columns on sys.columns.object_id = sys.table_types.type_table_object_id where sys.table_types.name = 'candidateRoutes'
select sys.columns.* from sys.tables join sys.columns on sys.columns.object_id = sys.tables.object_id where sys.tables.name = 'RouteArea'

:

object_id  name     column_id  system_type_id  user_type_id  max_length  precision  scale  collation_name  is_nullable  is_ansi_padded    is_rowguidcol  is_identity  is_computed  is_filestream  is_replicated  is_non_sql_subscribed  is_merge_published  is_dts_replicated  is_xml_document  xml_collection_id  default_object_id  rule_object_id  is_sparse  is_column_set
215671816  RouteId  1           56              56            4          10         0      NULL            0            0                 0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0
215671816  Area     2          240             130           -1           0         0      NULL            0            0                 0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0


object_id   name       column_id  system_type_id  user_type_id  max_length  precision  scale  collation_name  is_nullable  is_ansi_padded  is_rowguidcol  is_identity  is_computed  is_filestream  is_replicated  is_non_sql_subscribed  is_merge_published  is_dts_replicated  is_xml_document  xml_collection_id  default_object_id  rule_object_id  is_sparse  is_column_set
1675153013  RouteId    1          127             127            8          19         0      NULL            0            0               0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0
1675153013  ValidFrom  2           61              61            8          23         3      NULL            0            0               0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0
1675153013  ValidTo    3           61              61            8          23         3      NULL            1            0               0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0
1675153013  Line       4          240             130           -1           0         0      NULL            0            0               0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0
1675153013  Area       5          240             130           -1           0         0      NULL            1            0               0              0            0            0              0              0                      0                   0                  0                0                  0                  0               0          0

- :

with
    TableType as
        (select name, user_type_id, max_length, precision from sys.columns where object_id = (select type_table_object_id from sys.table_types where name = 'candidateRoutes')),
    PhysicalTable as
        (select name, user_type_id, max_length, precision from sys.columns where object_id = (select object_id from sys.tables where name = 'RouteArea'))
    select * from TableType full join PhysicalTable
        on TableType.name = PhysicalTable.name
    where TableType.name is null
       or PhysicalTable.name is null
       or TableType.user_type_id <> PhysicalTable.user_type_id
       or TableType.max_length   <> PhysicalTable.max_length
       or TableType.precision    <> PhysicalTable.precision

scale, collation_name, is_nullable .., , . :

name     user_type_id  max_length  precision  name       user_type_id  max_length  precision
RouteId  56            4           10         RouteId    127           8           19
NULL     NULL          NULL        NULL       ValidFrom  61            8           23
NULL     NULL          NULL        NULL       ValidTo    61            8           23
NULL     NULL          NULL        NULL       Line       130           -1          0

, .

+2

, # DataSets, . , , , , , .

#? SQL bool (true, , false, )?

.NET, F #? , , F # # .

, F # SQL.
http://tomasp.net/blog/dynamic-sql.aspx

LINQ (, ), , . http://www.linqpad.net/WhyLINQBeatsSQL.aspx

+1

More articles:


All Articles