Installing Custom Database as a SharePoint Feature

In any SharePoint project, sometimes there is a need to create an external custom RDBMS database to host non-related SharePoint information outside SharePoint's Content Database for example user credentials for form-based authentication, look up data, etc. To do this, the obvious solution is to use either one of following techniques:

• provide customer with .SQL file and installation steps
• provide customer with .MSI file and installation steps

None of the above technique is wrong!

However, I found technique no. 1 (install custom RDBMS database using .SQL file) is time-consuming and more prone to error - especially if customer who doing installation is not technical at all or the .SQL file doesn't obey the rules. For technique no. 2 - you need strong knowledge in Microsoft Windows Installer packaging tools and you need to download/configure MSI Redistributable package if you don't have one. So, the best solution is to utilize the SharePoint Feature Framework.

Features are the backbone of SharePoint development because every custom development project can and really should be deployed as a feature. Features give tremendous control over SharePoint configurations and capabilities at the administrator level. This means that developers can create features and then turn them over to SharePoint administrators without having to get involved repeatedly in small configuration changes.

I have developed a REUSEABLE FEATURE(SharePointMalaya.Feature.CustomDBInstaller.dll) that able to automate the installation of custom RDBMS database to any SharePoint’s database server. All you need to do is to generate SQL scripts using MS Server Management Studio, save the SQL scripts to SharePointMalaya.Feature.CustomDBInstaller feature folder, build WSP, install and activate the feature at SharePoint site.

SharePointMalaya.Feature.CustomDBInstaller feature comprises of SharePoint Event Handler which inherits from base abstract class SPFeatureReceiver to trap the activation, deactivation, installation, or uninstallation of a Feature. See codes below:

private const string FEATURE_FOLDER_PATH = "TEMPLATE\\FEATURES\\SharePointMalaya.Feature.CustomDBInstaller\\";
private const string TSQL_SCRIPT_INSTALLDB_FOLDER_PATH = FEATURE_FOLDER_PATH + "T-SQL\\Install\\";
private const string TSQL_SCRIPT_UNINSTALLDB_FOLDER_PATH = FEATURE_FOLDER_PATH + "T-SQL\\UnInstall\\";

/// <summary>
/// Occurs after a Feature is activated.
/// </summary>
/// <param name="properties">An <see cref="T:Microsoft.SharePoint.SPFeatureReceiverProperties"></see> object that represents the properties of the event.</param>
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    try
    {
        // Run with an account with higher privileges than the current user
        SPSecurity.RunWithElevatedPrivileges(delegate()
        {
            using (SPSite oSite = properties.Feature.Parent as SPSite)
            {
                SPWeb oWeb = null;

                if (oSite != null)
                    oWeb = oSite.RootWeb;
                else
                    oWeb = properties.Feature.Parent as SPWeb;

                if (oWeb.Exists)
                {
                    // Turn off security validation
                    oWeb.AllowUnsafeUpdates = true;

                    // Execute T-SQL scripts from <<FEATUREFOLDER>>\T_SQL\Install folder
                    ExecuteSQLScripts(oSite, TSQL_SCRIPT_INSTALLDB_FOLDER_PATH);

                    // Turn on security validation
                    oWeb.AllowUnsafeUpdates = false;
                }
                else
                    throw new Exception("Unable to open site.");
            }
        });
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

/// <summary>
/// Occurs when a Feature is deactivated.
/// </summary>
/// <param name="properties">An <see cref="T:Microsoft.SharePoint.SPFeatureReceiverProperties"></see> object that represents the properties of the event.</param>
public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
    try
    {
        // Run with an account with higher privileges than the current user
        SPSecurity.RunWithElevatedPrivileges(delegate()
        {
            using (SPSite oSite = properties.Feature.Parent as SPSite)
            {
                SPWeb oWeb = null;

                if (oSite != null)
                    oWeb = oSite.RootWeb;
                else
                    oWeb = properties.Feature.Parent as SPWeb;

                if (oWeb.Exists)
                {
                    // Turn off security validation
                    oWeb.AllowUnsafeUpdates = true;

                    // Execute T-SQL scripts to uninstall from <<FEATUREFOLDER>>\T_SQL\UnInstall folder
                    ExecuteSQLScripts(oSite, TSQL_SCRIPT_UNINSTALLDB_FOLDER_PATH);

                    // Turn on security validation
                    oWeb.AllowUnsafeUpdates = false;
                }
                else
                    throw new Exception("Unable to open site.");
            }
        });
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

/// <summary>
/// Executes the SQL scripts.
/// </summary>
/// <param name="oSite">The o site.</param>
/// <param name="scriptsPath">The scripts path.</param>
private void ExecuteSQLScripts(SPSite oSite, string scriptsPath)
{
    SqlConnection objCon = null;
    SqlCommand objCmd = null;

    try
    {
        // Get SharePoint content database connection string
        string connectionString = oSite.ContentDatabase.DatabaseConnectionString;

        // Get T-SQL script files for installing database from Feature folder
        string[] fileEntries = Directory.GetFiles(SPUtility.GetGenericSetupPath(scriptsPath));

        // files exist then open connection to database and sort file by name
        if (fileEntries.Length > 0)
        {
            // Create and open connection object
            objCon = new SqlConnection(connectionString);
            objCon.Open();

            // Sort file name
            Array.Sort(fileEntries);

            // Loop for each files in directory
            foreach (string fileName in fileEntries)
            {
                // Proceed if file name is not empty and file extension is .SQL
                if (!string.IsNullOrEmpty(fileName) && fileName.EndsWith(".sql"))
                {
                    string tsqlScript = string.Empty;
                    using (StreamReader reader = new StreamReader(fileName))
                    {
                        // Reading all lines in file and parse T-SQL
                        tsqlScript = PrepareTSQL(reader.ReadToEnd());

                        // Proceed if content of the file is not empty
                        if (!string.IsNullOrEmpty(tsqlScript))
                        {
                            // Create command object
                            objCmd = new SqlCommand(tsqlScript, objCon);
                            objCmd.CommandType = CommandType.Text;

                            // Execute non query
                            objCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objCmd != null) objCmd.Dispose();
        if (objCon.State == ConnectionState.Open) objCon.Close();
        if (objCon != null) objCon.Dispose();
    }
}

/// <summary>
/// Prepares the TSQL.
/// </summary>
/// <param name="tsqlScript">The TSQL script.</param>
/// <returns></returns>
private string PrepareTSQL(string tsqlScript)
{
    string retVal = string.Empty;
    try
    {
        // Replace "GO" statement with empty string since GO is not a valid command in ADO.NET
        retVal = tsqlScript.Replace("GO", string.Empty);
    }
    catch (Exception) { }

    return retVal;
}

Get source code here:

The following are four (4) simple steps to update the SQL scripts to SharePointMalaya.Feature.CustomDBInstaller feature folder, build WSP, install and activate the feature:

Step 1: Generate SQL Scripts using MS Server Management Studio

• See http://technet.microsoft.com/en-us/library/ms178078.aspx on how to Generate a Script using SQL Server Management Studio

Step 2: Drop the SQL Scripts to either 'Install' or 'UnInstall' folder

• For SQL Scripts that install database objects, add script files to \12\TEMPLATE\FEATURES\ Customware.CustomDBInstaller\T-SQL\Install feature folder
• For SQL Scripts that uninstall database objects, add script files \12\TEMPLATE\FEATURES\ Customware.CustomDBInstaller\T-SQL\UnInstall feature folder
• Use <<SEQUENCENUMBER>>-<<FILENAME>>.sql as script filename to control the execution of SQL Scripts, see below:

Step 3: Build WSP

• Right- click on the Project, select WSPBuilder > Build WSP from the menu (if you don't have WSPBuilder, download and install from CodePlex)

Step 4: Install and Activate SharePoint Feature

• Install the .WSP file to SharePoint site, at command prompt execute the following commands:
  • stsadm -o addsolution -filename SharePointMalaya.Feature.CustomDBInstaller.wsp
  • stsadm -o deploysolution -name SharePointMalaya.Feature.CustomDBInstaller.wsp -url http://<<SHAREPOINTURL>> -local –allowgacdeployment
• Go to SharePoint Site Collection Features page (i.e. http://<<SHAREPOINTURL>>:<<PORTNUMBER/_layouts/ManageFeatures.aspx?Scope=Site), activate the SharePointMalaya.Feature.CustomDBInstaller feature: