DataColumn's Unique property (applies also to null values?)

[Guest]

Under SQL Server 2000, a column can be defined as null and unique at the same
time. Under these circumstances, when a new row is added, uniqueness is
ensured for only non null values.

Under ADO.Net, I would like to know whether the datacolumn's unique property
mirrors that of SQL server 2000. I am trying to create a datacolumn that can
contain DBNull.Value but is unique when its values are not DBNull.Value. My
experience with the DataColumn.Unique Property is that it will check for
uniqueness regardless of whether AllowDBNull is true or not, ie if you cannot
have several DataRows with null value in this column if the DataColumn's
Unique property is set to true.

Can anyone confirm this?

 

[W.G. Ryan eMVP]

That's correct:

DataTable dt = new DataTable();

DataColumn dc = new DataColumn("Blah",
System.Type.GetType("System.String"));

dc.Unique = true;

dc.AllowDBNull = true;

dt.Columns.Add(dc);

for(int i =0;i<5;i++)

{

DataRow dro = dt.NewRow();

dt.Rows.Add(dro);

}



This will blow up on the second pass as it stands. You may want to use some
other column and use a DefaultValue to get you to the same place.



HTH,



Bill

 

[Guest]

Thank you for the reply.

What I don't understand is why doesn't ADO.NET mirror the SQL server?
Having two definitions of uniqueness is highly misleading.

I can't see how I can implement the SQL server type of column uniqueness (ie
uniqueness only on non null values). Can anyone give more detailed pointers?

Regards,

Guy