MySQL Membership and Roles Providers for ASP.NET

Recently I started working on a personal project and before diving into some real coding, I decided to go ahead and refresh some of my ASP.NET skills and knowledge as I didn’t do any serious ASP.NET development in more than 1.5 years. As most probably I will host the project on GoDaddy and will start with Economy plan until some upgrades will be necessary I am planning to use MySQL as data repository as they offer 10 MySQL databases of 1GB each- enough space for what I need at the beginning.

Since my project will need to support authentication of users and different roles within the application, I started refreshing my knowledge on the Membership and Roles providers. As my choice for database is MySQL I was afraid I will have to write custom providers to work with membership and roles stored in a MySQL database. Fortunately, I found out there is a much easier way of doing this thanks to the latest MySQL Connector for .NET.

Here are the steps I followed in order to set-up Membership and Roles providers using the MySQL .NET Connector.

  • download and install the latest MySQL .NET Connector. I am using version 6.3.4
  • create a new Web Project in Visual Studio and after add references to MySql.Data and MySql.Web
  • the MySQL .NET Connector will modify your machine.config file after installation and will add new entries in the membership, roleManager and profile providers. You will need to locate the machine.config file under the following location: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG and edit it. You will need to add one extra attribute to the MySql Membership Provider defined in there: autogenerateschema=“true”.  The autogenerateschema=“true” attribute will cause MySQL Connector/NET to silently create, or upgrade, the schema on the database server, to contain the required tables for storing membership information. After this change the MySql Membership Provider in the  machine.config file should look like this:
XML
  1. <add name="MySQLMembershipProvider"
  2. autogenerateschema="true"
  3. connectionStringName="LocalMySqlServer"
  4. ... />
  • one other change made in the machine.config file by the MySQL .NET Connector is in the connectionsStrings section where a new connection for MySql is added:
XML
  1. <add name="LocalMySqlServer" connectionString="" />

We will need to override this connection string in our web.config in order to specify the details on the MySQL database used to create the tables structure that supports the membership and roles providers. Because the membership and roles providers defined in the machine.config file use the already defined MySql connection string, we will need to remove it and then add it again. Our connectionStrings section in web.config file should look like:

XML
  1. <connectionStrings>
  2. <remove name="LocalMySqlServer"/>
  3. <add name="LocalMySqlServer"
  4. connectionString="Datasource=localhost;Database=db_name;uid=user;pwd=password;"
  5. providerName="MySql.Data.MySqlClient"/>
  6. </connectionStrings>
  • although this step can be done from the Website Administration Tool, I prefer to do it directly in the web.config file as it’s very simple. In order to support forms authentication, locate the authentication section in your web.config file and change the mode attribute from Windows to Forms:
XML
  1. <authentication mode="Forms"/>
  • open the Website Administration Tool and switch to the Provider tab and then select the Select a different provider for each feature (advanced) link to set the providers. You should see the MySQLMembershipProvider and MySQLRoleProvider listed. I selected both of them as I want both membership and roles to be stored in my MySQL database. After you close the Administration Tool, your web.config file should be get modified and you’ll notice that under the system.web section the following lines where added:
XML
  1. <roleManager defaultProvider="MySQLRoleProvider"/>
  2. <membership defaultProvider="MySQLMembershipProvider"/>
  • save your solution and build it. If you check your database you should see the tables structure generated:

tables

  • once you finish this step and want to go ahead and use the MySQL Membership and Roles providers in your code you should modify your web.config file once again. I replaced the above mentioned entries for role and membership sections with the entries found in the machine.config file:
XML
  1. <roleManager enabled="true" defaultProvider="MySQLRoleProvider">
  2. <providers>
  3. <clear/>
  4. <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider"
  5. connectionStringName="LocalMySqlServer" applicationName="MysqlTest" />
  6. </providers>
  7. </roleManager>
  8.  
  9. <membership defaultProvider="MySQLMembershipProvider">
  10. <providers>
  11. <clear/>
  12. <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider"
  13. connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false"
  14. enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="MysqlTest"
  15. requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5"
  16. minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1"
  17. passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
  18. </providers>
  19. </membership>

Of course you can customize the attributes of the role and membership providers as you wish. I changed the applicationName attribute to be able to differentiate later between application entries in the tables.

  • from this moment you should be able to use Login related controls in your application. Just for testing purposes I added one CreateUserWizard control which works fine without writing  any code, and then I used one Login control for which I implemented the OnAuthenticate event as follows:
C#
  1. protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
  2. {
  3. bool authenticated = false;
  4. authenticated = Membership.ValidateUser(Login1.UserName, Login1.Password);
  5. e.Authenticated = authenticated;
  6.  
  7. if(authenticated)
  8. {
  9. FormsAuthentication.SetAuthCookie(Login1.UserName, true);
  10. Response.Redirect("Secret.aspx");
  11. }
  12. else
  13. {
  14. // do some error handling
  15. }
  16. }

In order to implement this I followed as references:

  1. http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-asp-roles.html
  2. http://blog.nolanbailey.com/2008/03/08/mysql-aspnet-membership-and-role-provider/194

Hope this comes useful for others as well!

Nice intro screencast: Learn jQuery with FireBug, jQuerify and SelectorGadget

Twitter proved to be useful once again and provided me a nice link to an article from Encosia: Hear me talk jQuery and ASP.NET on the jQuery Podcast. While listening to the podcast (very interesting and full of useful information) I found another article on the site: Updated: See how I used Firebug to learn jQuery. The article is about 1 year old, but still, I found it useful! I’ve been using Firebug for some time to debug JavaScript and jQuery, but never paid attention to the console.

The article contains a 10 minutes screencast in which Dave Ward shows how we can use the built-in console from Firebug to learn and test jQuery scripts. He also uses two scripts:

  • jQuerify – a little bookmarklet to load jQuery on pages that don’t already have it
  • SelectorGadget – an open source bookmarklet that makes CSS selector generation and discovery on complicated sites a breeze

which can make your life easier when it comes to playing around with jQuery.

Watching the screencast I decided to implement the same “feature” on my blog just for test. In order to do that I had to edit two files in the admin interface of WordPress:

  • first of all I had to add jQuery to my blog and decided to use the CDN provided by jQuery. For that I edited header.php and added on line inside the <head> tag:
Javascript
  1. <script type="text/javascript" src="http://code.jquery.com/jquery-1.4.2.min.js"></script>
  • second and last step, I edited the footer.php file and right before the closing </body> tag I added the following script:
Javascript
  1. <script>
  2. $(document).ready( function() {
  3. $("h3").mouseover( function() {
  4. $(this).css("cursor", "pointer");
  5. })
  6. .mouseout( function() {
  7. $(this).css("cursor", "default");
  8. }).click( function() {
  9. $(this).next().slideToggle();
  10. });
  11. });
  12. </script>

The result is the same as in the screencast: if you click on the headers from the right side bar you will hide/show the contents of that list. The only difference is that I used method chaining to change the cursor style based on the mouseover and mouseout events to make it clear for the user that clicking on the header will actually do something.

Here is the screencast from the article:

 

Enjoy!

Implementing a transparent row selection in a DataGridView control

During a recent project I was involved in, I had to implement a transparent row selection for the DataGridView control in a Winforms C# 2.0 application. This meant that instead of getting the usual dark blue color (SystemColors.Highlight) for each cell of the selected row to paint only a certain cell as dark blue and a dark blue border around the visible part of the row, the rest of the cells having to keep their already set colors.

The first thing that we need to take care in the grid view control in case we want to have a row selection is to set the SelectionMode property of the grid control to FullRowSelect. This will make sure that each time we select one cell, the entire row will be selected.

full_row_select 

Once we complete this step we should start working on the actual “transparent” selection. In order to customize the look of the cells we need to implement the CellFormatting event of the DataGridView control and to change the way the cell looks when it’s selected we can customize two properties: SelectionBackColor (to change the background color of the cell in the selected state) and SelectionForeColor (to handle the color of the text in the cell in the selected state).

In order to have the first cell selected normally and the rest of the cells in the row to have the “transparent” selection look I used the following code for the CellFormatting event:

Code Snippet
  1. private void musicGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
  2.         {
  3.             // for the first cell paint paint the background as highlight and text as white in case of selection
  4.             if (e.ColumnIndex == 0)
  5.             {
  6.                 e.CellStyle.SelectionBackColor = SystemColors.Highlight;
  7.                 e.CellStyle.SelectionForeColor = Color.White;
  8.             }
  9.             else
  10.             {
  11.                 e.CellStyle.SelectionBackColor = e.CellStyle.BackColor;
  12.                 e.CellStyle.SelectionForeColor = e.CellStyle.ForeColor;
  13.             }
  14.         }

 

If we run the application after this step well get something like the following screenshot:

selection_no_border

This looks fine, but it’s not enough – this looks like we would have cell level selection allowed only for the first column. This would look much better if we would paint a border around the selected row, to make it clear which row is selected and what type of selection mode we have. To achieve this I implemented the RowPostPaint event and used the following piece of code to draw a rectangle around my selected row:

Code Snippet
  1. private void musicGridView_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
  2.         {
  3.             var dgv = (DataGridView)sender;
  4.             // run this piece of code only for the selected row
  5.             if (dgv.Rows[e.RowIndex].Selected)
  6.             {
  7.                 int width = musicGridView.Width;
  8.                 Rectangle r = dgv.GetRowDisplayRectangle(e.RowIndex, false);
  9.                 var rect = new Rectangle(r.X, r.Y, width – 1, r.Height – 1);
  10.                 // draw the border around the selected row using the highlight color and using a border width of 2
  11.                 ControlPaint.DrawBorder(e.Graphics, rect,
  12.                     SystemColors.Highlight, 2, ButtonBorderStyle.Solid,
  13.                     SystemColors.Highlight, 2, ButtonBorderStyle.Solid,
  14.                     SystemColors.Highlight, 2, ButtonBorderStyle.Solid,
  15.                     SystemColors.Highlight, 2, ButtonBorderStyle.Solid);
  16.             }
  17.         }

 

Once we implement this event for our grid view we’ll reach the result we wanted:

transparent_selection

This way of selecting a row in a DataGridView control can be very useful in case in the CellFormatting event we implement some logic to paint certain cell in some specific way based on some business logic. For example a red background for a cell in case of an error for that grid entry and so on… Such formatting would not be visible in case of the row beiong selected and we having left the default row selection implementation.

Implementing such a selection is not a very difficult task, but if you are not very familiar with the DataGridView control it can be tricky to know where and how to implement this. Hope this short article will be useful for other people as well.