DBPro How To: Initially Populating an ASP.NET Membership Database

With just the schema alone, the ASP.NET membership (role, profile, etc.) database does not have enough information to operate correctly.  There are a few records needed or else the ASP.NET membership and other providers will throw an error complaining that it may be an invalid version.  This is good, of course, so that the ASP.NET membership providers can innovate and the membership database will remain intact for existing application versions.  These additional records can be inserted with DBPro by adding a SQL script in the “Post-Deployment” folder under the DBPro project scripts folder.  We could name the file, “AspNet_SchemaRecords.sql”.  Also at this location, the file named Script.PostDeployment.sql must have an entry to run the post deployment SQL script.  After making these changes then deploying a new database with DBPro, an ASP.NET application should be able to connect and start using the database immediately.  Maybe in the future, this can be an option in the project wizard or available as an add-on script on the Microsoft site.

--  Initialize ASPNET membership schema records after a complete database rebuild.
:r .\AspNet_SchemaRecords.sql

USE DatabaseName; 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'common')
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('common', '1', 1) 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'health monitoring')
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('health monitoring', '1', 1) 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'membership')
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('membership', '1', 1) 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'personalization')
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('personalization', '1', 1) 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'profile')
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('profile', '1', 1) 

IF NOT EXISTS (SELECT * FROM aspnet_SchemaVersions WHERE Feature = N'role manager') 
      INSERT INTO aspnet_SchemaVersions (Feature, CompatibleSchemaVersion, IsCurrentVersion) VALUES('role manager', '1', 1)