By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

DataTable: Corrupted SQL Table Properties with generalized SqlDataAdapter?

P: 16
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.Relations, 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 ProjectsDataAdapter, 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:

Expand|Select|Wrap|Line Numbers
  1. string tableName = "Projects";
  2.  
  3. SqlConnection connection = new SqlConnection( connectionString );
  4.  
  5. DataSet dataSet = new DataSet();
  6. SqlDataAdapter dataAdapter = new SqlDataAdapter( "select * from " + tableName, connection );
  7. SqlCommandBuilder b = new SqlCommandBuilder( dataAdapter );
  8. dataAdapter.Fill( dataSet, tableName );
  9.  
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.Relations 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?
Apr 17 '07 #1
Share this Question
Share on Google+
3 Replies


iburyak
Expert 100+
P: 1,017
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.
Apr 17 '07 #2

P: 16
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?
Apr 17 '07 #3

P: 16
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().
Apr 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.