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.
Asked
Active
Viewed 616 times
3
-
In "corporate" enviroments, what you propose would be considered bugs, not features. – Robert Harvey Nov 16 '09 at 15:51
-
That's why features is quoted. Take a look here http://www.sqlite.org/faq.html#q3 – Raminder Nov 16 '09 at 15:52
-
Can you modify the ADO.NET provider? – Robert Harvey Nov 16 '09 at 15:58
-
Yes the provider's source is available, could you give some sample code? – Raminder Nov 16 '09 at 16:19
-
Not yet. :) I'd have to look at the source and see where some validation code could be punched in. Which ADO.NET provider is it? – Robert Harvey Nov 16 '09 at 16:54
3 Answers
5
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')
)
tuinstoel
- 7,248
- 27
- 27
1
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.
JeffreyABecker
- 2,724
- 1
- 25
- 36
0
Validate in your c# Domain Model POCO objects rather than in the SQLite db using something like DataAnnotations or maybe even this method.
Community
- 1
- 1
Matt Kocaj
- 11,278
- 6
- 51
- 79