What is the best way to write a validation layer for SQLite

Question

I'm using an ADO.net provider of SQLite. I want to steer around some of the "features" of SQLite, like allowing a string in an integer field and allowing a string longer than n in a field of type varchar(n). What is the best way to achieve this kind of validation? Stored procedures? triggers? I'm looking for a generic solution that applies to any database not just my database schema.

Answer 1

Validate in your c# Domain Model POCO objects rather than in the SQLite db using something like DataAnnotations or maybe even this method.

Answer 2

My personal experience is that what you're worried about is almost never a problem. When it is, the problem is due to grossly wrong code (inserting mothersmaidenname into age or something). The best way of keeping it 'not a problem' is to have (and use) good data access layers which effectively abstract the database.

Answer 3

You can add column constraints.

create table example
( 
  age integer not null check (typeof(age)='integer'),
  name text not null check (length(name) between 1 and 100),
  salary integer check (salary is null or typeof(salary)='integer')
)