MySQL for Visual Studio 1.2 introduces several new features:
- Web Configuration support for MySQL Personalization Provider.
- Web Configuration support for MySQL SiteMap Provider.
- Web Configuration support for MySQL Simple Membership Provider.
- MySQL ASP.NET MVC Project Wizard.
- MySQL Windows Forms Project Wizard.
In this post we are going to see the last one, The MySQL Windows Forms Project Wizard, in action.
The MySQL Windows Forms Project Wizard generates a full Windows Forms Application (with a data model backed by a MySQL database chosen by the user). The generated application is made up of several CRUD screens (screens with functionality for Creating, Reviewing, Updating & Deleting records in a given table).
Other features included are:
- Supports as the data access technology for the model any of Entity Framework 5 (EF5), Entity Framework 6 (EF6) or ADO.NET Typed DataSet.
In the case of EF, Database First is used, for EF5 the model is ObjectContext based, for EF6 it is DbContext based.
- Supports different type views for each CRUD screen (Single controls, Grid, Master Detail).
- Generates built-in & configurable validations for input controls.
- Generates code for either C# or VB.NET.
The Windows Forms Project Wizard has the following requirements (beyond installing MySQL for Visual Studio 1.2.x itself):
- Have any of VS2010 / VS2012 / VS2013 (any edition except Express).
- A Connector/NET installed (not through Nuget, but machine wide through MSI installer) either 6.7.5+, 6.8.3+ or 6.9.2+ versions.
- A recent version of Nuget like 2.8+. When Entity Framework is chosen as the access method, the respective nuget package is downloaded (older versions of Nuget had issues to programmatically get a package).
- For Entity Framework 6, you'll need VS2013 (you can still generate apps backed by EF5 or Typed Dataset in other Visual Studio versions).
The Wizard is available in two ways:
- From the main menu: File | New Project. Filter list by 'MySQL'
- From the MySQL toolbar.
Following is an example of using the Wizard:
- First you launch the wizard from the toolbar.
- This first screen is a project selector (available when launching from the toolbar only) pick the 2nd project type (for C#) or the fourth (for VB.NET). In the sample we picked C#, set a custom project name and press OK.
- The Windows Forms Wizard first screen allows to configure a database connection and data access technology, in the sample we use sakila database & Entity Framework 6 (if you are not using VS2013, then you can pick EF5 or Typed Dataset).
Also select 'yes' to store sensitive data in the connection string (which means if your connection to the given MySQL database is using password, it will be hardcoded in the source code (for Typed DatatSet) or in app.config (for Entity Framework). Have you choosen No, the password won't be hardcoded, but the generated app will not run properly (you'll still need to modify it to provide a password by other means).
Press Next button.
- On next screen you select which tables will have CRUD screens generated. Each row is one database table, the table will have a CRUD if the leftmost column (checkbox with header 'Select') is checked. The rightmost column describes the layout for the given table (by default single column); you can customize it for each row by pressing the 'Customize' button in the column of the same name. In this sample we will select all tables from sakila by checking the "Select all tables" checkbox. Don't press Finish yet.
- Let's say we want to customize some of the table layouts, on 'customer' table click on its Customize button.
- A new sub wizard will show up, "View type Selection" where you can select the layout you want. We picked Master-Detail, then a combo box is enabled with a list of constraints associated with 'customer', pick the 'fk_payment_customer'. This will display a customer record (with textboxes) and a grid with the customer payments, as you move to other customer record, the payments will filter out always showing the ones for the current customer. Don't press Finish yet on this dialog.
- You'll notice the Next button is disabled and the Finish button enabled. This is governed by the "Show all the advanced steps in the wizard". Check it, now the Finish button is disabled and Next is enabled. This will allow you to customize validations. Press Next.
- A Datagrid displays with attributes (datagrid columns) for each table column (datagrid row) in the master table. The grid already has reasonable defaults (those are the values that have been applied had you choose not to go through advanced options).
The following is a description of the validation grid columns. Summary of Validation grid columns Column Highlights
Column Name The name of the table column. Read only. Req (Required) Whether if the column value is required in the table for a given row. Read only. Data Type The column type right from the database. Read only. Default To define a default value for a new record. Can be modified. Max length Defines the maximum character length allowed for the given column value. Can be modified. Minimum Value The minimum value for a given column value (only applies to numeric types). Can be modified. Maximum Value The maximum value for a given column value (only applies to numeric types). Can be modified. Has lookup Lookup is only enabled for highlighted (in gray) rows (those which are foreign keys), when checked will render a combo box for that column (instead of a textbox). The combo box in this last column displays all the columns available from the referenced table for the foreign key of the current data grid row (table column), so the user entering data in the CRUD screen can pick a friendly name (instead of memorizing ids).
The whole set of rows in the referenced table for that column will be loaded into combo box in the generated CRUD. (If your referenced table is too big (more than a few thousand rows), you can choose to uncheck "Has Lookup" column value, and a textbox will be rendered instead).
Lookup ComboBox Displays the list of columns from the referenced table.
For the tutorial, pick for the 'address_id' from the "Lookup column" combo the 'District' column (from 'address' table). With this setting, the user can enter a new address_id for a given record by picking the District value (instead of picking the address_id itself). Internally in the database table the address_id for the row that matches the District chosen will be stored.
- The next screen shows the same validation grid, but for the columns of the detail table 'Payment' (since you chose the master detail layout with payment table as detail). Here there are three foreign keys, set its Lookup column as follows:
- customer_id, lookup column: last_name.
- staff_id, lookup column: username.
- rental_id, lookup column: return_date.
- Back in the multiple table selection, you can repeat the exercise of setting a master detail relationship for the 'film' table (using 'film_actor' constraint) and settings its lookup columns at will.
- Again, back in the multiple table selection, for the tables 'staff' and 'store' choose as layout Datagrid. Then back in the multiple table selection screen, press Finish, to terminate the wizard.
- Now will take a while to generate the full project.
The last step of the Wizard is to compile the whole solution.
After the project is finished, you can inspect possible errors in the General output window of Visual Studio. This log will also include log errors (for example if a database connection could not contact a server).
You can inspect in the Solution Explorer the components of the generated application, there is one form for every database table (row in the grid of multiple table selection you filled during the wizard).
The Model generated can be viewed / modified with the standard tools in Visual Studio in this case we are seeing a part of the diagram (by double cliking in the file Model1.edmx in Server Explorer).
As soon as the wizard is finished, you can press F5 to run the application. The main form only has a menu to access all the individual CRUD screens generated.
Let's start with the Customer screen, all CRUD screens have the same toolbar with eight controls, its functions from left to right are:
- Move to First record.
- Move to Previous record.
- (Input Box) enter number of record to navigate to it directly.
- Move to Next record.
- Move to Last record.
- Add a new record.
- Delete current record.
- Commit all changes to the database (any record editions, deletions or added won't be persisted if changes are not committed).
Also note the type of controls rendered:
- All foreign key are rendered as ComboBoxes or TextBoxes (depending on your choice during Wizard).
- Date columns that are editable (no timestamp) are rendered as DateTime pickers.
- Boolean types are entered with a checkbox control. This may include some small integer types, for the rules see here, section Boolean Types.
- Otherwise they are textboxes.
As an example of some edition, locate record 4, then change the address_id (currently 'Mandalay' to 'Texas'). Now press 'Save' to commit the changes into the database.
All CRUD screens have built-in validations, for same in same customer record, for the column first_name, change it from 'BARBARA' to '' (blanket), then attempt to Save. Instead of saving, you'll get an error that the field is required.
Now place, the cursor in the first_name textbox, and press Ctrl-Z (to restore the previous value).
As another example, press the 'Add New' button to add a new customer, fill it as shown in the screen and press Save. That will have created a new customer, the 600th.
With that customer selected, press the 'Delete' button and again press 'Save'. This commit the record as \ deleted into the MySql database.
You can also edit records in grid layout, to delete a record, select the whole row & press Delete key. To add a new record, start editing the last row (the one in blank). To finish editing, click in other row. You again need to commit changes to the database using the toolbar.
You can check other screens from the main Menu, for example Film (another master detail), Staff (grid layout), Address (single column layout).
Notice that for any screen, you can resize it and it will keep the toolbar fixed to the top of the client area.
And with this we finish the tour on the Windows Forms WizardCONCLUSION: As you learned in this tutorial, it is very easy to create from scratch a Windows Forms Application with CRUD screen for any number of tables from an existing MySQL database. Stay tuned for more exciting features announcements in this blog.
- MySQL for Visual Studio download page
- MySQL for Visual Studio documentation
- MySQL Community Bugs
- MySQL for Visual Studio Forum