unique string column on DataTable ?

[Guest]

Hi All,

I'm trying to make the following code not throw an exception ... (C#, .Net
2.0)

System.Data.DataTable tab = new System.Data.DataTable();
System.Data.DataColumn col = new System.Data.DataColumn("one",
typeof( System.Data.SqlTypes.SqlString ) ) ;
tab.Columns.Add(col);

System.Data.UniqueConstraint unique = new
System.Data.UniqueConstraint(col);
tab.Constraints.Add(unique);

tab.Rows.Add("hello");
tab.Rows.Add("hello ");
tab.Rows.Add(" hello ");

The three strings are unique .. note the trailing spaces.

Exception is thrown on the second add.

Is it possible to make a column not trim the spaces for comparison purposes ?

Any ideas ?

Thanks,

Richard

 

[Guest]

Hi,
Below code is working:
DataSet ds = new DataSet();
System.Data.DataTable tab = new System.Data.DataTable();
System.Data.DataColumn col = new System.Data.DataColumn("one",
typeof(System.Data.SqlTypes.SqlString));
tab.Columns.Add(col);

System.Data.UniqueConstraint unique = new
System.Data.UniqueConstraint(col);
tab.Constraints.Add(unique);
tab.BeginLoadData();
tab.Rows.Add("hello");
tab.Rows.Add("hello ");
tab.Rows.Add(" hello ");

ds.Tables.Add(tab);

 

[Guest]

Hi,
i am sorry for previous message as it will disable constraints and hence it
will accept duplicate values also.Kindly ignore previous post as i did it in
hurry.

 

[Guest]

Well ... it did stop the exception

Thanks for trying.

R.

 

[WenYuan Wang [MSFT]]

Hi Richard,

The trailing spaces of key will be removed in Unique/Primary Constraint. I
think this make sense. If you do same steps in SQL database, you will also
receive the same error message (duplicate key) in SQL Database.

By the way, the string "Hello" and "Hello " are recognized as the same key
in ADO.net. If you create a relationship between two database (the child
table contain foreign keys "Hello" and "Hello "), ParentTable.getChild()
method will return both two rows for you.

In your scenario, a trick is adding some special character ('$') at the end
of space such as "Hello$","Hello $".

Hope this helps. If you still have anything unclear, please let me know.
I'm glad to assist you.
Have great day, Thanks

Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thanks WenYuan,

Yup I know SQL Server and other DBs do this ... I was just hoping that there
might be some way to alter the behaviour of a DataTable object as it's in C#.

You have a good day too.

Richard

 

[WenYuan Wang [MSFT]]

Dear Richard,
Thanks for your reply.

I'm sorry to say I think it is also not possible in ADO.net. ADO.net should
have the same behavior as database. Otherwise, we will meet a BIG problem
when update datatable to underlying database. Say we have a typed dataset
(which has the same schema as the table in your database). When we insert
some rows ("hello", "hello ", "hello ") into this dataset, it works fine
in ADO.net. However, sometimes, if we want to update this dataset to
Database, Database will throw an exception. We don't want to meet such
issue. It is difficult to understand.

Have a great day. Let me know if you still have any concern. I'm glad to
assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.