MemoryTin

ASP.net: MySQL Membership Provider

If you are like me you can’t afford a production version of Microsoft SQL Server and even more crazy you can’t afford a license for each processor you have running in your production server. There is no way you are going to use SQL Server Express because you need to allow more than one application to hit the membership database and you can’t stand the file locking that occurs on all express database files.

That being said the best alternative and one I really really like is good old MySQL Community Server, it is amazing! An what is even more cool is that MySQL comes with a set of pre-built .Net connectors which you can download from their site and install. When you install the .Net connector it adds config entries to your Machine.config for all of the .Net Membership, Role and Profile providers they created for MySQL. Once this is done your system is ready to access all .Net membership using the MySQL DBMS. Simply amazing.

To set up your ASP.net website to use the MySQL membership providers you have a couple of config entries to add to your Web.config file. they are as follows:

1. Set up your Connection String

  <connectionStrings>

    <remove name="LocalMySqlServer"/>

    <add name="LocalMySqlServer"

         connectionString="Datasource=YOURSERVER;Database=YOURDATABASE;uid=YOURUSERNAME;pwd=YOURPASSWORD;"

         providerName="MySql.Data.MySqlClient"/>

  </connectionStrings>

2. Set your default Membership Provider and Role Provider

<membership defaultProvider="MySQLMembershipProvider" />

    <roleManager enabled="true" defaultProvider="MySQLRoleProvider" />

3. Optional but extremely helpful on your development machines is to set up the MySQL membership provider to install the membership schema in the database automatically if it does not exist. Saves you from manually running scripts. After installation of the .Net Connector go to your Machine.config and locate the system.web section, within it you should see entries for Membership, Profile and RoleManager. In addition to the default ASP.net providers that come with .Net you will see a new entries in each of those config sections for MySQL. Locate the MySQLMembershipProvider and add a new attribute autogenerateschema=”true”. It should look something like this:

    <membership>

      <providers>

        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />

        <add name="MySQLMembershipProvider" autogenerateschema="true" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />

      </providers>

    </membership>

    <profile>

      <providers>

        <add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

        <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />

      </providers>

    </profile>

    <roleManager>

      <providers>

        <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

        <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

        <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />

      </providers>

Now you should be able to open the website administration tool from within Visual Studio and walk through setting up Users and Roles as well as Access Rules just like you would if you used the built in ASP.net membership providers.

Good Luck!

NHibernate: Identity columns which are not primary key columns

SQL: Adding Identity Column to Existing Table

In MS SQL Server run the following query and an auto-generated identity column will be added to an existing table with existing data and each row will be automatically assigned an Integer in the new column.

Code:
alter table Example
add NewColumn int identity(1,1)

C#: Type Safe ICloneable Implementation

By default the ICloneable interface defines a Clone() method which returns an object of type Object and therefore is not a type safe action. To make your implementation of the ICloneable interface type safe for the following. By adding a typed Clone() method to your class and then adding an Explicit implementation of the ICloneable.Clone() interface method which returns the results of your Clone() method the returned object will be typed correctly.

        #region Public Methods

///

        /// Clones this instance.

///

///

        public Program Clone()

            Program cloneProgram = new Program();

            cloneProgram.Id = Id;

            cloneProgram.Name = Name;

            cloneProgram.EnrollDate = EnrollDate;

            cloneProgram.SubmitDate = SubmitDate;

            cloneProgram.EvalDate1 = EvalDate1;

            cloneProgram.EvalDate2 = EvalDate2;

            cloneProgram.EvalDate3 = EvalDate3;

            cloneProgram.Link = Link;

            cloneProgram.Style = Style;

            cloneProgram.Behaviors = Behaviors;

            cloneProgram.Interests = Interests;

            return cloneProgram;

        #endregion

        #region Interface Methods

///

        /// Creates a new object that is a copy of the current instance.

///

///

        /// A new object that is a copy of this instance.

///

        /// 2

        object ICloneable.Clone()

            return Clone();

        #endregion

C#: Cloneable Generic List Extension Method

Unfortunately the built in C# generic list does not have a Clone() method. If you need to clone a list for manipulation purposes you can always use an extension method and add a Clone() method yourself. Below is an Extension Method for Cloning a generic list in C#:

public static class ListExtension

        public static IList Clone(this IList listToClone) where T : ICloneable

            return listToClone.Select(item => (T)item.Clone()).ToList();