SQL Express Membership API Provider Utility

• Download SQL Express Membership API Provider Utility.zip

Introduction

This article discusses the building of a Visual Basic .NET console
application for adding the Membership API database schema to a SQL Server
Express database created within Visual Studio.  The console application
prompts the user for the full path and filename with extension for a SQL Server
Express database file.  The user can enter the filename manually, or click
on the icon to paste the text via the menu.  When the user presses enter,
the database is updated with the Membership API database schema.  Once the
console application has finished running, prompts the user to press any key
before exiting.  This way, the user gets a chance to see any error messages
in case anything goes wrong.

The main code that makes all this possible is:

' Install the Membership API Provider tables, views, and stored procedures into the user's database.
System.Web.Management.SqlServices.Install(databaseName, SqlFeatures.All, connectionString)

Note: The database name is the full path, filename, and file extension.

The screenshot below shows what the finished tool looks like.

Background

When I create a database within an ASP.NET application's App_Data folder, I
cannot use the aspnet_regsql utility to insert the tables, views, and stored
procedures associated with the Membership API. The only way to do this is to
detach the database from Visual Studio, attach the database to SQL Server
Management Studio, run the aspnet_regsql utility, detach the database from SQL
Server Management Studio, and reattach the database to Visual Studio.

Using the Code

To use this code create a Visual Basic .NET console application, and add the
SQLExpress.vb
module displayed below.  Then you can compile and run the tool.  A
project is included for you to load up into Visual Studio 2008.

' ================================================================================================================
' Mondule Name       : SQLExpress.vb
' Module Description : Accepts the filename for a SQL Server database file, and creates the Membership API
'                    : provider tables, views, and stored procedures in that database.
' Author             : Jason W. Alls
' License            : Use of this software is subject to your acceptance of the The Code Project Open License 
'                    : (CPOL) 1.02 included with this project.
' Date Created       : 11th July 2010, 21:27
' ================================================================================================================
Imports System.Data.SqlClient   ' Needed for the database connection.
Imports System.Web.Management   ' Needed to add the Membership API Provider Schema to the user's database.
 
''' <summary>
''' Accepts the filename for a SQL Server database file, and creates the Membership API
''' provider tables, views, and stored procedures in that database.
''' </summary>
''' <remarks></remarks>
Module SQLExpress
 
    Dim userInput As String             ' Stores the text entered by the user.
    Dim attachDbFilename As String      ' The SQL Server database full path, filename, and file extension.
    Dim connectionString As String      ' The database connection string.
    Dim databaseName As String          ' The SQL Server database full path, filename, and file extension.
    Dim connection As New SqlConnection ' The SQL Server database connection object.
 
    ''' <summary>
    ''' The application entry point.
    ''' </summary>
    ''' <remarks></remarks>
    Sub Main()
        ' Clear the console window, set the title and header, and set the command prompt.
        Console.Clear()
        Console.Title = "SQL Express Membership Provider Utility, Version 1.0.0"
        Console.WriteLine("SQL Express Membership Provider Utility, Version 1.0.0")
        Console.Write("Enter Database Filename:> ")
        ' Read in the database filename entered by the user.
        attachDbFilename = Console.ReadLine()
        ' Set the database connection.
        connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & """" & attachDbFilename & """" & ";Integrated Security=True;User Instance=True"
        ' Set the database name (full path, filename, and file extension!).
        databaseName = attachDbFilename
        Try
            ' Set the connection string, and open the database connection.
            connection.ConnectionString = connectionString
            connection.Open()
            ' Install the Membership API Provider tables, views, and stored procedures into the user's database.
            System.Web.Management.SqlServices.Install(databaseName, SqlFeatures.All, connectionString)
            Console.WriteLine("SqlFeatures added to database: " & databaseName)
        Catch ex As Exception
            ' This utility could be further improved by writing exceptions to an XML file,
            ' and formatting it with XSL to provide users with an easy to read error report.
            Console.WriteLine(ex)
        Finally
            ' Close the database connection.
            If Not connection Is Nothing Then
                If (connection.State = ConnectionState.Open) Then
                    connection.Close()
                End If
            End If
        End Try
        ' Freeze the console to give the user chance to read any on screen messages before closing the console.
        Console.Write("Press any key to exit.")
        Console.ReadKey()
    End Sub
End Module

Points of Interest

This application was developed as a quick and dirty solution to solve the
issue explained above in the background section.  When you use this tool,
Visual Studio can be open with the database attached.  There is obviously
room for improvement. I believe the best way to improve this tool will be to
convert it to a Visual Studio add-in.  The add-in would add an 'Insert
Membership API schema' menu item to the database context menu.  Clicking
this option would then perform the database upgrade, and refresh the server
explorer window.

History

15th July 2010, 21:27 Initial Version (1.0.0) Build Completed.