Fillschema does not import constraints

[Danilo P.]

Hi everyone.



I'm trying to interact with ADO.NET and its DataAdapter and DataSet classes
to discover the potential this tool offers.



After studied all concern the theory I start to play with compiler and in
one of my code snippet I try to fill the schema of a SQL Server table in a
DataTable object with FillSchema method.



The table's definition is this:



CREATE TABLE [dbo].[Employs] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[Name] [varchar] (20) NULL ,

[Surname] [varchar] (20) NULL

) ON [PRIMARY]



This table has no primary key but it has two unique distinct constraints,
one for field.

The lack of a primary key is only for show what happen.



ALTER TABLE [dbo].[Employs] ADD

CONSTRAINT [IX_ Employs] UNIQUE NONCLUSTERED

(

[Name]

) ON [PRIMARY] ,

CONSTRAINT [IX_ Employs _1] UNIQUE NONCLUSTERED

(

[Surname]

) ON [PRIMARY]





Then, I create a DataAdapter object who interact with such table.



Dim dsData As New DataSet

Dim cn As New SqlConnection("Data Source=(local);...etc...")

Dim da As New SqlDataAdapter("SELECT * FROM Employs ", cn)

da.FillSchema(dsData, SchemaType.Source, " Employs ")



Once executed the FillSchema method I show the Table object constraints:



debug.WriteLine(dsData.Tables(0).Constraints.Count) --> 1

debug.WriteLine(dsData.Tables(0).Columns("Name").Unique) --> True

debug.WriteLine(dsData.Tables(0).Columns("Surname").Unique) --> False



This behaviour is the same also if I precede the FillSchema method with an
assignment like this:



da.MissingSchemaAction = MissingSchemaAction.AddWithKey




The matter is: why the second constraints is not correctly imported in the
DataTable definition?

Fillschema method works only with one constraints, perhaps?



Thank you in advance.



Regards,

Danilo P.



P.S.

If I add a primary key in the ID field, FillSchema will import correctly
this key but not the follow two.

 

[David Sceppa]

Danilo,

This behavior is intentional.

DataAdapter.FillSchema translates to Command.ExecuteReader with a
combined CommandBehavior of SchemaOnly + KeyInfo. The Command asks the
back end for some additional schema information including base table and
column names and key information to uniquely identify the rows in the
resultset. It does not return all schema - additional unique constraints,
check constraints, defaults, etc.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.