Links to old ASP.NET Membership / Role / Profile provider code on github

A bit hard to locate these nowadays I've been told, so I'm just gonna post them here, mostly for my own sake.

 So here they are:

Membership: https://github.com/Microsoft/referencesource/blob/master/System.Web/Security/SQLMembershipProvider.cs

 Role:
https://github.com/Microsoft/referencesource/blob/master/System.Web/Security/SQLRoleProvider.cs

Profile: https://github.com/Microsoft/referencesource/blob/master/System.Web/Profile/SqlProfileProvider.cs

Synchronizing SQL Server Stored Procedures between databases

In my earlier post Compare Stored Procedures in 2 SQL Server databases, I wrote about how it is possible to select from INFORMATION_SCHEMA to compare stored procedures.

The issue I am dealing with here is that during the development af a new system, the database stored procedures are likely to change between each release to a server (test, staging or production). You have made all the changes in your development database, and you just need to copy the changes to the other database. My old way to do this was to script each SP whenever it changed and link a section in the delivery slip to the script, which was quite tedious.

What I ended up with using in one of my current projects is the following steps:

1. Using SQL Server Management Studio, script all procedures from the source database as ALTER to the clipboard.
   
2. Connect a new query window to the target database and paste the script.
   
3. Run the script.
   
   It will fail for all the SPs that you have added to the source database, which are missing in the target database, but also it will update all existing stored procedures to the latest version.
   
   
4. Replace all “ALTER PROC” with “CREATE PROC” in the script and run it again.
   
   It will fail for all existing SPs in the target database, but more importantly, it will create all the SPs from the source database that are missing in the target database.
   
   
5. And Bob’s your uncle!

This, of course, should be easy to automate. Maybe I’ll come back with an application that does it in an upcoming post.

The routine above works for functions as well, and should/could also work with views, and maybe also tables.

Compare Stored Procedures in 2 SQL Server databases

Using the following script it is possible to get an idea if the stored procedures in two databases are equal.

SELECT A.ROUTINE_NAME, A.ROUTINE_DEFINITION, B.ROUTINE_DEFINITION
FROM sourcedatabase.INFORMATION_SCHEMA.ROUTINES A
LEFT JOIN targetdatabase.INFORMATION_SCHEMA.ROUTINES B ON A.SPECIFIC_NAME = B.SPECIFIC_NAME
WHERE RTRIM(LTRIM(SUBSTRING(A.ROUTINE_DEFINITION,CHARINDEX('CREATE',A.ROUTINE_DEFINITION),999))) <> RTRIM(LTRIM((SUBSTRING(B.ROUTINE_DEFINITION,CHARINDEX('CREATE',B.ROUTINE_DEFINITION,0),999))))
ORDER BY ROUTINE_NAME

Even if the routines have the same functionality, they may have some slight differences, like spaces in front of the CREATE PROCEDURE statement and also trailing spaces at the bottom. I have tried to remedy this by trimming and also comparing substrings where initial spaces have been removed. Still room for more improvement, I’m sure, but just a small idea.

Common Table Expression for Database Structure

When copying data from one database to another, breaking constraints is always a problem. If you do not insert data in a certain sequence, you will get foreign key violations. So I tried to work out a CTE that starts with all tables that have no foreign keys, then the tables referencing them, then the once referencing them again, and so on… So came up with this:

WITH CTE (name,object_id, lvl)
AS
(
    select name, object_id, 0 AS lvl from sys.tables
    where object_id NOT IN (select parent_object_id from sys.foreign_keys)
   
UNION ALL

    select tbls.name, tbls.object_id , lvl + 1 from sys.tables tbls
    join sys.foreign_keys keys on tbls.object_id = keys.parent_object_id
    join CTE on CTE.object_id =  keys.referenced_object_id
)
SELECT * FROM CTE
OPTION (MAXRECURSION 10000)

Note that this will not work if you have self-referencing tables, ie. tables that have a foreign key pointing to its own primary key. Also the same tables may appear many times because they reference the same tables. And also if the same table have many foreign keys it will appear many times.

The conclusion is that this does not solve my problem, but it was fun to create the CTE anyway.

Check if any new properties have been added in EPiServer 4.x

The scenario for this is that you have a Test server and a Production server. You are rolling out all changes that have been tested on the Test server to the Production server. You did, being a pro and all, of course make a note of all the changes that were done to properties on the Test server since the last rollout to Production, but just to make sure you want to check what new properties have been added.

Step 1

Add the Production server as a linked server in SQL Server Management Studio on the Test server, in this example called [LinkedServer].

Step 2

Create a new query on the Test server and paste the following query into it. You will need to substitute the names of the database and linked server with names from your environment.

-- CHECKS IF ANY NEW FIELDS HAVE BEEN ADDED ON THE TEST SERVER
-- THAT DO NOT EXIST ON THE PRODUCTION SERVER
USE EPiServerDb;
GO

IF EXISTS(
    SELECT testTable.pkID, prodTable.pkID
    FROM tblPageDefinition testTable
    LEFT JOIN [LinkedServer].[EPiServerDb].[dbo].[tblPageDefinition] prodTable 
        ON testTable.pkID = prodTable.pkID
    WHERE prodTable.pkID IS NULL)
BEGIN
    PRINT 'NEW PAGE PROPERTIES DETECTED!'
    SELECT pt.Name, pd.Name
    FROM tblPageType pt JOIN tblPageDefinition pd ON pt.pkID = pd.fkPageTypeID
    WHERE pd.pkID IN (
    SELECT testTable.pkID
    FROM tblPageDefinition testTable
    LEFT JOIN [LinkedServer].[EPiServerDb].[dbo].[tblPageDefinition] prodTable 
        ON testTable.pkID = prodTable.pkID
    WHERE prodTable.pkID IS NULL)
END
ELSE BEGIN
    PRINT 'NO NEW PROPERTIES DETECTED.'
END

Step 3

If any new properties have been added, you will see a list of the names of page templates and what properties are new.

This script was tested on EPiServer 4.61/62 only, but may also work for newer versions of EPiServer.