DataTable: Corrupted SQL Table Properties with generalized SqlDataAdapter?

[Spectre1337]

Hello,

I've encountered a rather puzzling problem with SqlDataAdapter. I've set up a number of elaborate tables in the Microsoft SQL Server Management Studio Express (including several table key relationships), and I've been successfully accessing all of that information through the classes generated by the "visual" DataSet file in Web Developer Express (Solution > Add New Item > Data Set).

Prior to my last code modification I've successfully read the SQL table relationships from DataSet.Relatio ns, or, in my case, ProjectsDataSet .Relations (my main table is called "Projects", ProjectsDataSet was generated by Web Developer Express).

For the next step of my project I had to abandon the classes from the "visual" DataSet files, such as ProjectsDataSet and ProjectsDataAda pter, and start making the program work with generalized DataSets, SqlDataAdapters and SqlConnections.

So instead of relying on the classes from the DataSet file, I began to feed my application the following manually created instances:

string tableName = "Projects";
 
SqlConnection connection = new SqlConnection( connectionString );
 
DataSet dataSet = new DataSet();
SqlDataAdapter dataAdapter = new SqlDataAdapter( "select * from " + tableName, connection );
SqlCommandBuilder b = new SqlCommandBuilder( dataAdapter );
dataAdapter.Fill( dataSet, tableName );
 

Suddenly my program no longer detects any relations in the DataSet, even though it comes from the exact same source as the one that I allocated "by hand" instead of using the DataSet file. The DataSet.Relatio ns list has no elements at all.

What's worse, the AllowDBNull properties of my DataColumns are all "true" even though most of my columns don't allow null values. I've explicitly set the column properties in MS Server Management Studio!

It seems that unlike the ProjectsDataSet class that was created by Visual Web Developer, the manually-allocated DataSet object isn't pulling all information out of the database definitions.

Any ideas?

[iburyak]

Microsoft is famous to call this type of programming as disconnected datasets.
It makes intelligent assumption on data structure but not actual like allow null values where they are not allowed in underlying database. I am sure you will not be able to save this dataset to a database with null value in a column.
First time - you linked to the database and this time - you imported data and created your own disconnected database that is independent from the server.
This way it is easy to import XML and SQL data into the same DataAdapter and join together seamlessly.

For more info check this:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx

Sorry not sure how to help you here.

Irina.

[Spectre1337]

Thanks. I had already read through that page and actually based parts of my program on it.

Is there another elegant way to retrieve table/column information, like AllowDBNull or Relations, other than looking at the DataColumn object of the DataTable?

[Spectre1337]

Just in case anyone is having the same problem, after a long and annoying search I've finally found a solution:

You need to generate an XSD database schema file using Web Developer Express (Or by any other means) and then,prior to Fill(), load it into the DataSet using ReadXmlSchema() .