Common Data validation best practice SQL & .Net?
-
I have the fun job of increasing the field lengths of some of our SQL Table columns, Stored Proc parameters, Data Access Layer, GUI, etc. I am looking for a better way than the current way of having hard coded, or constants scattered throughout our code and SQL to define a field length of say 30. Thinking big, wouldn't it be nice to define a field length or schema in one location that both SQL and code could use for validation? Anyone have any best practices for this problem they would like to share? Thanks, Bill
-
I have the fun job of increasing the field lengths of some of our SQL Table columns, Stored Proc parameters, Data Access Layer, GUI, etc. I am looking for a better way than the current way of having hard coded, or constants scattered throughout our code and SQL to define a field length of say 30. Thinking big, wouldn't it be nice to define a field length or schema in one location that both SQL and code could use for validation? Anyone have any best practices for this problem they would like to share? Thanks, Bill
SQL Server already has all this meta data in the system views, these can be used for validation based on the existing tables. Adding another layer of abstraction to manage all the data field lengths/formats sounds like a disaster looking for somewhere to happen. We use code generators so we can reproduce the relevant parts of the DAL/Model/ViewModel code, based on the database tables, this leaves us with the table and the UI/View to be refactored when a change is required. A utility to validate the property based on the table would be trivial but you now need a property with a length attribute in your DAL/Model layer. If you define a field standard lenght (eg name will always be 150 characters in length) there will always be exceptions.
Never underestimate the power of human stupidity RAH
-
SQL Server already has all this meta data in the system views, these can be used for validation based on the existing tables. Adding another layer of abstraction to manage all the data field lengths/formats sounds like a disaster looking for somewhere to happen. We use code generators so we can reproduce the relevant parts of the DAL/Model/ViewModel code, based on the database tables, this leaves us with the table and the UI/View to be refactored when a change is required. A utility to validate the property based on the table would be trivial but you now need a property with a length attribute in your DAL/Model layer. If you define a field standard lenght (eg name will always be 150 characters in length) there will always be exceptions.
Never underestimate the power of human stupidity RAH
A solution to one part of the problem can be to define types in sql and then extract that information for code validation. I'd be interested in any tools to do the latter, but this is a nice way to define "constants" or types in sql. /*Define INENTIFIER as varchar(50*/ IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDENTIFIER' AND ss.name = N'dbo') CREATE TYPE dbo.IDENTIFIER from varchar(50) null /*Use the newly defined system type.*/ ALTER TABLE [dbo].[Customers] ALTER COLUMN [Id] IDENTIFIER /* varchar(50) */ You can use IDENTIFIER for your stored procs and have more control over hard coded lenghts, etc.
-
A solution to one part of the problem can be to define types in sql and then extract that information for code validation. I'd be interested in any tools to do the latter, but this is a nice way to define "constants" or types in sql. /*Define INENTIFIER as varchar(50*/ IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDENTIFIER' AND ss.name = N'dbo') CREATE TYPE dbo.IDENTIFIER from varchar(50) null /*Use the newly defined system type.*/ ALTER TABLE [dbo].[Customers] ALTER COLUMN [Id] IDENTIFIER /* varchar(50) */ You can use IDENTIFIER for your stored procs and have more control over hard coded lenghts, etc.
I have never actually needed to use custom types, you would then need to maintain the type in both SQL and your code as SQLDBType would not include your IDENTIFIER type. Not a big deal until someone forgets to sync a new type!
Never underestimate the power of human stupidity RAH