DefaultValue vs. NoNullAllowedException

[Bragadiru]

Hi,

I defined in my Sql Server db a table with 2 int columns that don't allow
null values + their default values are set to 0.

I'm using a DataAdapter to read data, but it doesn't bring the default
setting :

using (SqlConnection conn = new SqlConnection("MyConnectionString"))

{

SqlDataAdapter adapter = new SqlDataAdapter("Select * from MyTable",
conn);

DataSet result = new DataSet();

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

conn.Open();

adapter.Fill(result);

conn.Close();

if (null != result && result.Tables.Count > 0)

{

DataRow dr = result.Tables[0].NewRow();

dr["Column1"] = 11;

result.Tables[0].Rows.Add(dr); // <= NoNullAllowedException for
Column2, even it has a DefaultValue defined

}

}



I don't want to set manually in the code the AllowDbNull or DefaultValue
properties.

What am I doing wrong ?

I can this sql statement without pbs :

INSERT INTO [Test] ([Column1]) VALUES (11)

Thanks for any advice

 

[WenYuan Wang]

Hi Bragadiru,

According to your description, you noticed that .net framework will throw
NoNullAllowedException for Column; even through it has a DefaultValue
defined in Database. If I misunderstand anything here, please don't
hesitate to correct me.

As far as I know, I'm afraid that DataAdapter will fill the DataColumn
properties such as AllowDBNull,AutoIncrement,MaxLength ,ReadOnly,Unique
into DataTable, but * NOT* include Default Value.
We have to define the default value manually in the code, otherwise we will
get NoNullAllowedException.
I'm sorry to say this is by design.

But another way to approach it, we will use typed dataset. We can define
the default value in typed dataset first and then add rows into table.

If there is anything unclear, please feel free to reply me and we will
follow up. I'm glad to work with you.
Hope this helps!

Best regards,
Wen Yuan

 

[Bragadiru]

Thanks a lot for your confirmation.
BUT, why this is by design ?? I have a property to get the schema,
MissingSchemaAction, but it brings me only a part of schema : AllowDBNull is
set to false, but no setting for DefaultValue !!???

Thanks for the tip with typed dataset.

 

[WenYuan Wang]

Hi Bragadiru,
Thanks for your reply.

MissingSchemaAction will enable dataadapter to fill schema from database,
and then DataAdapter will call FillSchema method to get schema.
As you know, DataAdapter.FillSchema Method can get the AllowDBNull property
from database, but it can not get the Default Value.
You can get more detailed information from the below document.
http://msdn2.microsoft.com/en-us/library/229sz0y5.aspx

I think this is related to the difference between each database system.
ADO.net can't read this property from every database now. For this reason,
this feature isn't supported.

If there is anything we can help with, please feel free to reply me and we
will follow up.
Have a great day!
Wen Yuan

 

[Bragadiru]

Thanks again.

MS should read DefaultValue (it's a very important part of the table's
schema), at least for SqlDataAdapter. They don't have any excuse.

 

[WenYuan Wang]

Hi Bragadiru,
Thanks for your reply.

I'm agree with you. The default value is really an important part of the
table's schema. I apologize for the inconvenience this may have caused.

We suggest you can send feedback to our product team at the following
website. Our product team may communicate with you directly on the issue.
http://connect.microsoft.com/site/sitehome.aspx?SiteID=210

Any suggestion from our customer is greatly appreciated.
Best Regards,
Wen Yuan

 

[Bragadiru]

I sent a suggestion to your product team to read DefaultValue for
SqlDataAdapter with MissingSchemaAction.AddWithKey.

 

[WenYuan Wang]

Hi Bragadiru

Thanks very much for your feedback on the product. This is a great feature.
We appreciate your input very much.

Thanks again for participating the community.
Best regards,
Wen Yuan