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?

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

"Guy" <(E-Mail Removed)> wrote in message
news:2310AF35-DD75-4A06-9E92-(E-Mail Removed)...
> 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?

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


"W.G. Ryan eMVP" wrote:

> 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
>
> "Guy" <(E-Mail Removed)> wrote in message
> news:2310AF35-DD75-4A06-9E92-(E-Mail Removed)...
> > 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?
>
>
>