Update model from database (VS2010) doesn't work

[gr33d]

Please let me know if this should have been posted in the Entity Developer forum.

Entity Developer 3.0.10.0
dotConnect for MySQL 6.0.10.0
Visual Studio 2010

The subject pretty much explains it. I updated one table by adding a column. I was using mysql connector, but the release notes for dotConnect seemed to be indexed by Google well enough to grab my attention when searching for a reason the "update model from database" function wasn't working, so I changed over to dotConnect. The install seemed to go well enough to recognize the new column when I re-added the connection in VS Server Explorer. However, whether I add or refresh the modified table in my model designer, it doesn't pick up the new field. I've even removed the model altogether from the project and re-added--no luck.

Is this a license feature I need to purchase? I downloaded dotConnect for MySQL Beta 6.00 from http://www.devart.com/dotconnect/mysql/download.html. The release notes say it's been added in this version. I also noticed the release notes for Entity Developer 3.00 suggest the same, but apparently the download I installed contained this.

If I missed it in the documentation, please point it out. Thanks in advance!

[StanislavK]

It should be possible to use the Update Model wizard in the Beta 6.0.10 version of dotConnect for MySQL. Could you please specify the following:
- whether you are working with a LinqConnect or Entity Framework model;
- if there are any errors or warnings in Error List when you complete the Update Model wizard;
- the script needed to create the table with which the problem occurs;
- the version of MySQL server you are using.

Also, what exactly do you mean by saying that the new field was not picked up? Don't you see this field in the designer, or, maybe, you are unable to access the corresponding property of the class from the generated code?

We couldn't reproduce the issue in our environment with simple tables like

Code: Select all

CREATE TABLE MyTable
(
  id INT(11) NOT NULL DEFAULT 0,
  column1 VARCHAR(255) DEFAULT NULL,
  column2 VARCHAR(255) DEFAULT NULL,   -- This field is added later.
  PRIMARY KEY (id)
)

[gr33d]

Entity Framework model
No errors, it appears to flicker and regenerate the designer code. Though, there don't appear to be any changes.
MySQL v5.1.41
Here is a dump of the table schema from phpmyadmin:

Code: Select all

CREATE TABLE IF NOT EXISTS `Profile` (
  `ProfileID` int(10) NOT NULL AUTO_INCREMENT,
  `StatusID` int(10) DEFAULT NULL,
  `DateCreated` datetime DEFAULT NULL,
  `CompanyID` int(10) DEFAULT NULL,
  `TollFreeNumber` int(10) DEFAULT NULL,
  `MediaPlacedID` int(10) DEFAULT NULL,
  `PBXCallID` int(10) DEFAULT NULL,
  `FirstName` varchar(50) DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  `DOB` datetime DEFAULT NULL,
  `Gender` varchar(1) DEFAULT NULL,
  `AccountNumber` int(10) DEFAULT NULL,
  `Pin` varchar(4) DEFAULT NULL,
  `WelcomeCall` datetime DEFAULT NULL,
  `WelcomeCallBy` int(10) DEFAULT NULL,
  `WelcomeCallNumberCalled` int(10) DEFAULT NULL,
  `AccountBlocked` bit(1) DEFAULT NULL,
  `Language` int(10) DEFAULT NULL,
  `IPAddress` varchar(16) DEFAULT NULL,
  `OptInMarketing` bit(1) DEFAULT NULL,
  `ListID` int(10) DEFAULT NULL,
  `SecretQuestion` varchar(50) DEFAULT NULL,
  `WebPassword` varchar(20) NOT NULL,
  PRIMARY KEY (`ProfileID`),
  KEY `FK_Profile_Company` (`CompanyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

WebPassword is the newly added field.

When using mysql connector, no matter how many times I refreshed, I did not see this field. I upgraded to dotConnect, removed the old connection and created a new one (This is in the Server Explorer in Visual Studio), and I do see this field. However, whether I refresh the Profile table, delete it and re-add, or create an entirely new model, the designer never picks up this field.

This field doesn't appear in the generated code, nor am I able to access it in my code after instantiating an instance of Profile.[/list]

[gr33d]

Ok, restarting MySQL seemed to fix this...any ideas why?

Should I not be adding fields in phpmyadmin, but by some other method? I'd hate to have to restart MySQL any time my schema changes and I need to update a model.

[AndreyR]

Could you please upgrade to the 6.0 Beta version of dotConnect for MySQL?
I have just performed a test, and the refreshed table was added successfully (and the changes were visible in Database Explorer after clicking the Refresh button).
Please note that you need to delete the entity from both conceptual and store parts of the model, in the other case the cached Store entity will be added to the model.

[gr33d]

I'm using dotConnect Beta 6.0.10.0

How can I delete the entity from both of these locations? I'm guessing the conceptual is the designer. Where is the store part of the model?

[AndreyR]

Yes, you are correct, the conceptual part of the model is represented by the model designer.
As for the store part, go to Model Explorer-> Model.Store, you will see a tree with the store tables that correspond to conceptual entities. Delete the necessary ones.

[gr33d]

I found the Model.Store in Model Explorer. I've tried several scenarios, none work:

-Delete designer element first, then delete Model.Store table. Re-add
-Delete Model.Store table first, then delete designer element. Re-add
-Delete Model.Store table, attempt update.
-Delete Model.Store table first, attempt to add. This adds another (not updated) table with a "1" appended to the name.

It seems after more than a day it will update and re-add without even attempting to delete the Model.Store table. Could it be cached somewhere else? I'm certain that there were no Visual Studio shutdowns.

[AndreyR]

Here are the steps I have performed to update the database:
1. Add a new Profile table from Database Explorer. It does not have the WebPassword field.
2. Go to dbForge Studio for MySQL and add the column to the table ("ALTER TABLE test.profile
ADD COLUMN WebPassword VARCHAR (20) NOT NULL").
3. Return to Visual Studio and click the Refresh button in Database Explorer. The Profile table now contains the WebPassword field.
4. Go to Model Explorer and delete the entity from Model.Store part (I choose "Yes" to delete the entity from the conceptualschema as well).
5. I add the table from Database Explorer again. It has the WebPassword property.
Could you please tell me in which of the uppermentioned steps you encounter the error?

[gr33d]

I had inadvertently installed dbForge Fusion trial after dotConnect was installed, though I have never used it. Is it required? If so, why?

I couldn't figure out how to simply add a column to a table in dbForge Fusion--not very intuitive I guess. I have been modifying the Profile table on the backend using PhpMyAdmin. Then, I delete the Model.Store Profile part. Then, I add the table from the "Update Model from Database..." dialog. At this point, I don't see the new column.

You also seem to have introduced a new window that I just found, Database Explorer, which I was able to load after adding a new connection from the Tools --> dbForge Fusion for MySQL --> Database --> New Connection dialog. Here, the right-click context menu didn't seem to have anything to update or add entities to the model. In your step 5, you make it sound like this is possible--how?

[AndreyR]

It is possible in the Devart Entity model.
As far as I understand, you were using the Microsoft Update model from Database wizard.
It has a number of shortcomings, for example, complete recreating of the Store model part.
I recommend you to add a Devart Entity model to your project. It has the same functionality as the Microsoft Entity model does, but has a bit more convenient designer.
All steps that I have described were performed in Devart Entity Developer (it is integrated in Visaul Studio and is launched automatically when you open a Devart entity model).
Please let me know if anything goes wrong with Devart Entity model.
When talking about Database Explorer I was meaning the window that comes with Entity Developer, not OraDeveloper Tools.