I created mvc 4 application that can upload data into database using sql bulk copy upload method.
Once I select excel file, it can upload data into the system.
this is working fine, now I want to add default value if excel field is null
this is how it extract column values of excels
"s1.Gender, " + "s1.Country_of_Birth, " + "s1.Date_of_Birth, " +
I want to add default value from code level , I already handle this using database level.
for example to add default value for "date of birth" I added following constraint
ALTER TABLE [dbo].[tbl_HEI_student] ADD CONSTRAINT [DF_tbl_HEI_student_Date_of_Birth] DEFAULT (getdate()) FOR [Date_of_Birth]
But when I upload excel file into db ,SQL bulk copy upload method is ignoring adding that default value database .
How can I add default value from controller method
Your problem must be that the column allows NULLS.
In case your column allows NULL and you provide a NULL value on the insert the NULL WILL be inserted. If no value is provided for the column the DEFAULT will be considered.
Setup your column to NOT NULL.
ALTER TABLE [dbo].[tbl_HEI_student] ALTER COLUMN [Date_of_Birth] DATE NOT NULL
Here is the Default Definition.
As you have noticed, you can't BulkInsert the NULLS to that columns and make the DEFAULT value prevail on SqlServer side, and the NOT NULL i suggested will enforce that either no value is supplied or something other than NULL.
I can see a few options.
Here is another question related to your problem.
Keep in mind your database schema should help you enforce your business rules and keep your data clean. If the date should never be NULL, keep the column as NOT NULL and do your solution accordingly.