c# - Bind default value if excel sheet column value null in sql bulk copy upload -

i created mvc 4 application can upload data database using sql bulk copy upload method.

once select excel file, can upload data system.

this working fine, want add default value if excel field null

this how extract column values of excels

     "s1.gender, " +      "s1.country_of_birth, " +      "s1.date_of_birth, " + 

i want add default value code level , handle using database level.

for example add default value "date of birth" added following constraint

alter table [dbo].[tbl_hei_student] add  constraint [df_tbl_hei_student_date_of_birth]  default (getdate()) [date_of_birth] 

but when upload excel file db ,sql bulk copy upload method ignoring adding default value database .

how can add default value controller method

your problem must column allows nulls.

in case column allows null , provide null value on insert null will inserted. if no value provided column default considered.

setup column not null.

alter table [dbo].[tbl_hei_student] alter column [date_of_birth] date not null 

here default definition.

edit:

as have noticed, can't bulkinsert nulls columns , make default value prevail on sqlserver side, , not null suggested enforce either no value supplied or other null.

i can see few options.

• either deal them .net side setting value default (you have show .net code if want that)
• doing 1 bulk rows value , rows without , removing mapping column, default constraint take action
• setting row allow nulls, bulk doing, , doing update after bulk set nulls default value.

i know bulk insert , bcp both have qualifier shown here deal null values. same option available in .net sqlbulkcopy options.

here question related problem.

keep in mind database schema should enforce business rules , keep data clean. if date should never null, keep column not null , solution accordingly.