MySql.Data Reference

I previously wrote a post about configuring MySQL for use with a Microsoft MVC application.  I noticed after publishing that my application could not connect to the database and returned the following exception:

System.ArgumentException: The ADO.NET provider with invariant name ‘MySql.Data.MySqlClient’ is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details. —> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.

In order to solve this I had to do two things:

  1. In the Solution Explore under ‘References’ set Copy Local to true for the MySql.data reference
  2. Add the database provider configuration to your Web.config as seen below, with your particular version
<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" 
         invariant="MySql.Data.MySqlClient" 
         description=".Net Framework Data Provider for MySQL"  
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

The reason for #2 is that I am trying to run my code on a machine where the provider is not installed, so I have to specify the provider in my configuration file and then installation adds it to the machine.config registering the provider on the remote server.

Configure MySQL with ADO.NET EF in VS 2013

MySQL, ADO.NET & MVC

Even though we are using a Microsoft framework (MVC) and ADO.NET Entity Framework (EF) we may not want to connect to a Microsoft Database.  In my case I am going to connect to a MySQL database which means I have to configure MySQL for use in my project.

I had to download the latest version of MySQL for Visual Studio installer and once downloaded run the installer and follow the on-screen prompts to install.  I went with the Typical install option.

I then downloaded the latest MySQL connector and ran the installer also using the Typical install option.

Once this is done you can open your project in Visual Studio and right click on the solution in the solution explorer to Manage NuGet Packages… as in Figure 1 below.

Figure 1: Manage NuGet Packages
Figure 1: Manage NuGet Packages

You then go to the Online section and search for MySQL.  You then want to install the MySQL.Data.Entities package, highlighted in Figure 2 below, by clicking install which will then install the NuGet package and it’s dependencies (Figure 3) and once it’s done a green check mark will show that the package installed correctly (Figure 4).

Figure 2: Search MySQL and Select
Figure 2: Search MySQL and Select
Figure 3: Installing MySQL and Dependencies
Figure 3: Installing MySQL and Dependencies
Figure 4: Shows Installed - Close NuGet
Figure 4: Shows Installed – Close NuGet

After you have MySQL configured you need to add the MySQL provider to your Web.config by replacing the Entity Framework tag with the following:

<entityFramework>
     <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6"/>
     <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
     </providers>
</entityFramework>

Then you save the Web.config file and add an ADO.NET Entity Model by adding a new item (Figure 5) to your solution.  The new item is an ADO.NET Entity Model which provides the connection to your database (Figure 6) by selecting Visual C# from the left menu followed by ADO.NET Entity Data Model, giving it a name (in my case TrackerDB) and clicking Add.

Figure 5: Add New Item to Solution
Figure 5: Add New Item to Solution
Figure 6: Select Visual C# ADO.NET Entity Data Model
Figure 6: Select Visual C# ADO.NET Entity Data Model

In the window that appears select the option EF Designer from Database and click Next (Figure 7).  In the window that appears, Figure 8, click the New Connection button to create a connection to your database and select MySQL Database from the list of options, Figure 9.  Make sure to deselect the option to always use this selection and click Continue.

 

Figure 7: Select EF Designer From Database
Figure 7: Select EF Designer From Database
Figure 8: New Connection
Figure 8: New Connection
Figure 9: Select MySQL Database & Uncheck Always use this selection
Figure 9: Select MySQL Database & deselect ‘Always use this selection’

The Connections Properties menu will open where you will need to provide the details for the database server your application is going to connect to, as in Figure 10.  In my case I entered the server name as an ip address since no DNS friendly name exists along with the username and password required to connect to the database server.  You can click the Test Connection button to ensure your information is correct; however, in my case I also noticed that you can attempt to select the database you want to.  If your credentials are incorrect an error message will appear, otherwise a list of databases the username/password combination has access to will appear.    If the connection succeeds click OK, returning you to the previous screen.

Figure 10: Enter MySQL Database Information
Figure 10: Enter MySQL Database Information

As an aside I find it best to create a username/password combination that is unique to each database, rather than one to rule them all, so that if the credentials get compromised only access to a particular database will be granted.  If you have finer grained control over database usernames it is best to only grant a user the rights to do the minimal tasks required.

After clicking OK I received an error message (Figure 11) about the XML declaration.  I clicked OK which brings you back to a screen like Figure 8 above, however, information will be populated under the connection string.  I determined this was caused by me having extra lines, which I had commented out as part of my experimentation, at the top of my Web.Config file.  After deleting these lines making the <?xml …> tag the first line in the file I was able to proceed (canceled, deleted the connection and started from the beginning) without error which brought me back to the screen as seen in Figure 12.  I stuck with the default name provided for the connection string in my Web.Config file and choose yes for storing my password since the account only has the writes required.  I may decide to change this later and how you wish to handle this is up to you.  You then want to click the Next button.

Figure 11: XML Error Message - Click OK
Figure 11: XML Error Message – Click OK
Figure 12: Yes/No to Password and Connection Setting
Figure 12: Yes/No to Password and Connection Setting

Update (Aug 22, 2014): You may want to provide a better name for your connection settings in your Web.Config file as that acts as your context.  That is if your database is called tracker then db_nameEntities could be TrackerContext.

On the next screen you will see “Retrieving database information, please wait..” followed by the various elements you can select to be included in your model.  In my case I selected only Tables as I currently have no Views or Stored Procedures and Functions for my database, as seen in Figure 13.  I left the default to pluralize or singularize generated object names as well as to include foreign keys since I want my model to include the foreign keys.  Click Finish to complete the Entity Data Model Wizard.

Update (Aug 22, 2014): You may want to provide a more friendly model name for your database model, again if database is called Tracker you could use TrackerModel.

Figure 13: Select Database Object and Finish
Figure 13: Select Database Object and Finish

You will receive the message “Running this text template can potentially harm your computer. Do not run if you obtained it from untrusted source.. ”, as a reminder that you are about to execute someone else’s code which you should only do if you trust the source of that code.  I am confident in this case that I can run this template so I click OK, which I had to do a couple of times.  If you want you can disable these warnings by going to Tools Options… Text Templating and setting the Show Security Message to False.

Figure 14: Click OK to Run Text Template
Figure 14: Click OK to Run Text Template

Under Data Connections in the Solution Explorer I able to see the database I added.  At first it showed as having a red ‘X’ next to the database icon, but once I expanded the connection to see the contents, Figure 15, the icon refreshed and the database contents were displayed which signifies that I now have a data connection established to my database for use with my application.

Figure 15: View Database Contents to Confirm
Figure 15: View Database Contents to Confirm

Finally now that everything is configured you are able to see an ERD diagram of you database open up as well as the database schema in the Solution Explorer that you can navigate, shown in Figure 16.

Figure 16: Tracker DB in Solution Explorer and Diagram
Figure 16: Tracker DB in Solution Explorer and Diagram

The connection string added to my Web.Config file under the <connectionStrings> tag looks similar to below, but with the actual values for server, user id, and database.  The reason for the connection string (with the space) and the &quot sections is because it is escaping the connection string generated for the database I am connecting to and putting it into the web configuration file under the connectionString attribute.  I will have to see if this works as generated by the wizard or if I will have to modify its results.

<add name="tracker_appEntities" connectionString="metadata=res://*/TrackerDB.csdl|res://*/TrackerDB.ssdl|res://*/TrackerDB.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=192.168.2.1;user id=user_name;password=db_password;persistsecurityinfo=True;database=db_name&quot;" providerName="System.Data.EntityClient" />

Note:  You may not run into this, however, there were times throughout when Visual Studio would go into a non-responsive state (sometimes flashing in and out of them) throughout the process.  I found that I just had to be patient and give the application time to complete the necessary tasks.

Publish Web Project to GoDaddy

The web hosting provider I am using is GoDaddy and I chose Windows hosting since some of my ideas include developing web applications using Microsoft technologies.  The following explains how I set up my first web project I am in the process of developing, which was my tracker application.

The first step is to get the web deploy publishing settings for the location you wish to host your web project. In my case I want to host it on a subdomain (which I have already created), but the steps are the same if it is your root domain. You navigate to the subdomain section of where you would like to host your app in GoDaddy’s ‘Websites & Domains’ section.  You then click the ‘Web Deploy Publishing Settings’ link to start the download of your settings file (seen in Figure 1 below).

Step 1 - Download Web DeployPublishing Settings
Figure 1: Download Web Deploy Publishing Settings

The downloaded file will contain a .publishsettings extension with the contents of the file similar to:

<?xml version="1.0"?>
<publishData><publishProfile profileName="tracker.dwcryan.com - Web Deploy" publishMethod="MSDeploy" publishUrl="tracker.dwcryan.com" msdeploySite="tracker.dwcryan.com" userName="dwcryan" destinationAppUrl="http://tracker.dwcryan.com" controlPanelLink="https://p3nwvpweb100.shr.prod.phx3.secureserver.net:8443"/></publishData>

Once you have the settings file you can configure your actual project. With your web project opened in Visual Studio you right click on the project in the Solution Explorer and select Publish.. (or something similar depending on project type) as in Figure 2.

Step 2 Right Click Project In Solution Explorer and select Publish
Figure 2: Right Click Project In Solution Explorer and select Publish

This will present you with the window in Figure 3, where you will select the Import option by left clicking.  In the displayed dialog ‘Import Publish Settings’, also displayed in Figure 3, you will browse to your publish settings file, select it, and then click Open; thus, displaying the path to your file in the ‘Import Publish Settings’ dialog as in Figure 4, and then click OK.

Step 3 - Click Import
Figure 3 – Click Import
Step 4 - Browse For Publishing File
Figure 4: Browse For Publishing File

Once the settings have loaded enter the password for your account and click Test Connection.  If everything is OK you will see a check mark next to the test connection button as in Figure 5.

Step 5 - Enter Password and Validate Connection
Figure 5: Enter Password and Validate Connection

Clicking Next will then take you to the ‘Settings’ tab.  I did not change anything here as I did not see the need for any file publishing options and I do not want the publish to modify my database.  I may need to update my publish settings and insert a connection string for my database once I have that configured within my application and will update this post with any such changes.  Clicking Next again brings me to the ‘Preview’ tab as in Figure 6.

Step 6 - Preview and Publish
Figure 6: Preview and Publish

Clicking ‘Start Preview’  in the middle of the screenshot above (Figure 6) will provide a list of the files being uploaded and the action being performed (i.e. update for files being modified and add for new files being pushed to the destination) as in Figure 7.

Step 7 - Clicking Preview
Figure 7: Clicking Preview

Lastly you click Publish to finish off the process which will publish your application to its destination.  The output of the file will look similar to that of Figure 8 (I cropped out the middle section as to highlight the start and end sections as well as displaying some of the adds and updates.  The web application (at the URL provided) will then open up in your default web browser.

Step 8 - Output
Figure 8: Output

If their are issues with the settings in your application when running on the remote server an error page will be displayed with some information as to what you have to do.  At this point it becomes a matter of experimenting.  In my case I got a security exception stating “The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application’s trust level in the configuration file”.

I was able to solve this by adding the following line to my web.config file between the system.web tags and publishing again:

<trust level="Full" />

Through further investigative work I determined a second option is changing the configuration on your hosting provider, if they allow it. In my case that involved selecting the option ASP.NET Settings in the same view as Figure 1 above, which in my case was at the bottom of the 3rd column (from the left).  Once in there you set the CAS trust level to Full, Figure 8 below, and while here make sure that the Default webpage language is correct for your application.

CAS trust level
Figure 8: CAS trust level

That appears to be it for now and will have to see what happens once my web application contains a database and actions are being taken if any further modifications to the publishing settings need to be added or modified.

Subdomain within Main Domain Folder

Well, today I was doing some experimenting and investigating how I could get my Tracker application published to a subdomain through automation instead of manually copying the files every time.  It’s only an MVC template right now but I wanted to be able to test that the default functionality was going to work with my web hosting, prior to proceeding.  Things were going pretty good until I realized that publishing my application broke my WordPress site.  By broke what I mean is that critical files and settings got overwritten causing my site navigation menu to get messed up as well as links to pages not working (404 errors) and only my home page could be displayed.

I hypothesized the cause of the overwrite to be due to the subdomain folder being contained within the root folder for my website instead of up one level so I deleted the subdomain and the folder it was using.  I then recreated it so its root folder is at the same level as my main sites root folder.  Apparently trying to organize your subdomains within your root domain folder is not a good idea.

I was then able to follow the steps again to publish a web application, which I outline here.

I took the opportunity of my site already being broken to install the latest WordPress update.

Since my incorrect publish broke my custom links I determined how to fix this was from my WordPress dashboard you go to Settings -> Permalinks and switch the option to Default and save your changes.  Then you switch it back to your custom selection and save the changes again.  Prior to discovering this not even deleting the pages permanently and recreating them removed the 404 error for me.

Tracker Database Design

Tracker Database v.1

What is an application without some sort of storage mechanism behind the scenes?  Above represents my database design which I am going to go into more details about.

At the heart of the database is the User table which stores user information and associates a reading list, measurement, or golf round with a corresponding user such that users can only see their data when logged in.  This also makes it easy to track additional information by adding the corresponding tables which will then get associated with a user if I choose to expand the application at a later time.  The other tables which stores, what I will call public data, is visible to all users to avoid duplication of data.  This includes golf course information (Golf Course, Address, Region and Course Par Information tables) and book information (Author, Book, and what authors are associated with which book). The measurement and course par info tables each have a ‘special’ column.  The measurement table contains a column called Type which takes a number from 1-4.  Since this never changes the application can handle the numbers, rather than creating an additional static look-up table in the database.  The number representation is as follows:

  1. Weight
  2. Neck
  3. Waist
  4. Hips

The course par information table contains a column called IsCNine to accommodate courses that have more than 18 holes with a format of: (Course Color)*( )(Front|Back).  Where there can be 0 or more colors   Examples of this would be:

  • White Front
  • Blue Front
  • Blue Back
  • Front
  • Back

This format allow for courses which provide 27 holes of golf as they typically have a white, blue, and red course, each with 9 wholes.  I’d imagine both would also have a front and back nine.  I have never played at a 27 whole golf course but I try to develop for future (or potential) possibilities.

I’ll explain the user profile dynamic content and dashboards text boxes in the diagram in my next post about the dynamic content and dashboards I plan to provide in my application.