CODEWITHSUNDEEP
visual-studio-2010asp.net-membershipdatabase-project
Bernie
Bernie

Reputation: 286

Visual studio database project and aspnet_regsql.exe

I am using a visual studio database project to do change control on a database for a website. I would like to use the membership/role model as created by the aspnet_regsql tool for managing the access to the website.

Up till now, I run aspnet_regsql before deploying the database, so that the database project merges the website specific tables into the database. I have been looking into a way to include the aspnet_ tables in the database project, but cannot figure out how to do this.

Did anybody ever try this? Are there good reasons not to do it like this?

Upvotes: 3

Views: 1496

Answers (1)

Flory
Flory

Reputation: 2849

Yeah, I have done this. In researching it, I have seen a number of places where they don't recommend it, Pro ASP.Net 4.0 for one. The implication, I think, is that the implementation of the script might be different from one framework version to another. It might be good advice...or it might be overzealous.

I compared both the structure and the data when I was finished using RedGate Compare and I cannot find anything different with the result...so I think it works fine... YMMV.

What I did to do this was I created a new database and ran aspnet_regsql on it. I then created a new database project in VS2010 and imported that database in. I then went and copied all the files in my new projects Schema Objects directory and dropped them onto my existing database project Schema Objects directory. I opened up my old project, did a show all files from the project menu, and then included all the new files in my project.

You also need to copy all the database permissions from the Database.sqlpermissions file of the new project.

Finally you need to add this to your post-deployment script.

-- Add 6 rows to [dbo].[aspnet_SchemaVersions]
IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'common') AND [CompatibleSchemaVersion] = N'1')
  BEGIN
    INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'common', N'1', 1)
  END

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'health monitoring') AND [CompatibleSchemaVersion] = N'1')
  BEGIN  
    INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'health monitoring', N'1', 1)
  END

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'membership') AND [CompatibleSchemaVersion] = N'1')
  BEGIN   
    INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'membership', N'1', 1)
  END

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'personalization') AND [CompatibleSchemaVersion] = N'1')
  BEGIN 
    INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'personalization', N'1', 1)
  END

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'profile') AND [CompatibleSchemaVersion] = N'1')
  BEGIN 
    INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'profile', N'1', 1)
  END

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_SchemaVersions] WHERE [Feature] = (N'role manager') AND [CompatibleSchemaVersion] = N'1')
  BEGIN 
   INSERT INTO [dbo].[aspnet_SchemaVersions] ([Feature], [CompatibleSchemaVersion], [IsCurrentVersion]) VALUES (N'role manager', N'1', 1)
  END

Upvotes: 2

Related Questions